April 4, 2012 at 2:18 am
What is the impact of dropping a clustered index? What type of performance hit will be caused?
Thanks.
April 4, 2012 at 2:33 am
what is the reasoning behing dropping the clustered index?
April 4, 2012 at 2:37 am
No such reason. I was going thru some blogs, wanted to dig some more.
April 4, 2012 at 2:39 am
changes the table to a heap
queries will cause table scans
non clustered indexes will cause a lookup scan
as it has to look through all of the data pages to find the data it needs, as there is no B-Tree telling it where to find the data
April 4, 2012 at 3:13 am
What about data as leaf node of a clustered index conatains data pages?
April 4, 2012 at 3:16 am
the leaf node is the bottom level of the tree which is the data pages, unless you mean something else?
April 4, 2012 at 3:47 am
I tested on a table. Dropping clustered index doesn't impact data.
April 4, 2012 at 5:02 am
anthony.green (4/4/2012)
queries will cause table scans
Not necessarily. Nonclustered indexes can still be used for seek operations
non clustered indexes will cause a lookup scan
Lookup scan?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2012 at 5:03 am
sqldba4u (4/4/2012)
I tested on a table. Dropping clustered index doesn't impact data.
Of course it won't. Clustered index is just about organisation of data. Dropping or creating indexes can't change the data in the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2012 at 5:07 am
Sorry Gail, maybe my terminology is a bit off.
Please correct me if I'm wrong, but when there is a column which is required in the result set which is not an included column, the non-clustered index will lookup the column from the clustered index, believe this is a key lookup, now as there is no clustered index if this was to happen it would scan the heap for the extra data needed to satisfy the query resulting in a "lookup scan" (might not be the correct terminology).
April 4, 2012 at 5:11 am
anthony.green (4/4/2012)
Please correct me if I'm wrong, but when there is a column which is required in the result set which is not an included column, the non-clustered index will lookup the column from the clustered index, believe this is a key lookup
Yes, that's a key lookup.
now as there is no clustered index if this was to happen it would scan the heap for the extra data needed to satisfy the query resulting in a "lookup scan" (might not be the correct terminology).
That would be a very inefficient way of doing things...
When there's no clustered index, the nonclustered indexes have, instead of the clustering key, the RID of the row in the heap. That's an 8-byte combination of File ID, Page ID and slot index. So when a lookup occurs and there's no clustered index (the base table is a heap), the nonclustered index has the exact location of the row in the heap and the lookup occurs directly to that row. It's called a RID lookup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2012 at 5:13 am
Thanks for the clarification Gail, much appreciated as always
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply