Partition removed by adding index

by Allan Svelmøe Hansen April 29, 2008 13:17

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? Innocent ) 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.

Bookmark and Share DotnetKicks dotnetshoutout

Powered by BlogEngine.NET 1.6.1.0
Theme by Mads Kristensen | Modified by Mooglegiant

About:
Allan Svelmøe Hansen

My real name is Allan Svelmøe Hansen.
I live in Denmark, where I work as a developer for hedal:kruse:brohus using SQL Server and the .NET framework since 2004.
My primary fields of expertise is back-end data integration, database design and optimization.


       View Allan Svelmøe Hansen's profile on LinkedIn     

Disclaimer

The opinions expressed herein are my own personal opinions and thoughts and does not represent my employers view in any way, nor are my results guaranteed for all situations.
Content is presented "as is", with no warranty.