February 4, 2012 at 11:55 am
I know every non clustered index already "includes" the Clustered Index key for the respective row. However, if you explicitly include it with the INCLUDE keyword, will it be added twice? Or is SQL Server smart enough to know it doesn't need to duplicate the data that's already there? I have some co-workers that have made a habit of unnecessarily including the Clustered Index Key in the include list and I want to know if it's taking up more space than necessary.
February 4, 2012 at 12:37 pm
No, it will not. SQL Server is smarter than that.
Adding the clustering key to the index if it is necessary is a sensible thing to. Otherwise, what happens when someone changes the clustered index to a different column?
p.s. the clustering key is not always included. Depends on whether the nonclustered index is unique or not whether the clustering key is added as key columns or include columns.
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
February 6, 2012 at 4:47 am
Q: When you use the Database Tuning Advisor (DTA) or the missing index dynamic management views (DMVs), these tools sometimes suggest nonclustered indexes that include the clustering key columns in the nonclustered index definition. I’ve heard that SQL Server includes the clustering key automatically— so isn’t it unnecessary for me to include it as well, and if I do, will SQL Server add the clustering key columns twice?
A: When tools such as DTA or the missing index DMVs come up with index recommendations, they only look at what’s required of the index to support the query’s execution. If a query requests the column (if it’s a single column) that is the clustering key, the tool is likely to show the clustering key somewhere in the nonclustered index. It’s not a problem to explicitly list the clustering key column in the definition of a nonclustered index. In fact, depending on the query being tuned, the clustering key might be required as a second or third column in a much wider index, and performance might be improved because of the clustering key’s position in the nonclustered index. So, there can be a benefit in terms of ordering.
However, if the clustering key is at the end of the index key definition or in an INCLUDE clause, then it might not matter because SQL Server adds it automatically. However, exactly how SQL Server adds it, whether it’s part of the ordered structure of the index (the key) or it’s only in the leaf level of the index (using INCLUDE), depends on whether the nonclustered index is defined as a unique index.
When a nonclustered index is unique, the clustering key is effectively the same as an included column. However, when the nonclustered index is nonunique, the clustering key is actually added to the key portion of the index for navigation.
For example, if you created a clustered index on EmployeeID and then created a unique nonclustered index on SocialSecurityNumber, the nonclustered index would look like the following in the nonclustered tree (used for navigation):
February 6, 2012 at 4:54 am
Sorry but i forget to post my article References
http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-again!.aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply