During some testing of partitioned tables to give myself more insight in the possibilities and effects (more numbers), I ran into something weird.
I created tables as I did in my earlier testing, however now when I added indexes to the tables, I found all performance gain was gone, and when I removed indexes, I noticed my tables were no longer partitioned.
Testing some more, backtracking my steps, I found out that it was the creation of indexes which caused my partitioned tables to become non-partitioned.
I have created the tables via SQL scripts but I created the indexes via SQL Server Management Studio (SSMS as it is usually abbreviated). This creation of indexes did not take into considerations that I had partitioned tables, because I found that adding an index changed the table to non-partitioned.
Then I started to read the documentation for create index SQL command (try first, then read the documentation …… isn’t that how it usually goes?
) and there I read that I could specify an partition scheme.
CREATE CLUSTERED INDEX <INDEX NAME>
ON TABLE (<COLUMNS>)
ON <PARTITION SCHEME>
So while I did know that you could partition your indexes, I was not aware of the fact that apparently SSMS alters your table and removes the partitioning somehow.
Annoying, but at least now I know.