August 28, 2012 at 9:47 am
A clustered index is in reality the table, organized by the clustering key.
But you can make a clustered index based on only a small number of columns in a table.
So, I am creating an indexed view. The view has 5 columns and the clustering key is 2 of those columns.
Im told that if i create a clustered index on those 2 columns, i dont need to include the other 3 columns, as they are automatically included as its a clustered index, i.e. the actual data.
Is this logic valid, as it does not look valid, when i check what columns are associated with the index in the index metadata.
August 28, 2012 at 10:12 am
Yes, a clustered index is the table (or the materialised view) and hence contains all columns at the leaf level as the actual data pages are found at the leaf.
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
August 28, 2012 at 10:23 am
If you want to experiment with it and see it a bit more directly, try creating a clustered index with included columns, and another view (different name, same definition) with a clustered index without any included columns; same key columns on both clustered indexes. Then compare index size. Then, write a query that will do a clustered index seek based on the Where clause, but which requires some of the non-key columns in the Select clause. Look at the execution plans, you won't see key/bookmark lookups in either case. Try the same thing on a table with a non-clustered index on the same data, where the query can do an index seek to satisfy the Where clause, but needs additional columns for the Select clause, you'll get key/bookmark lookups.
That's pretty much the big difference between clustered and non-clustered indexes. When it comes down to actual query performance issues, a clustered index will never have to result in key lookups.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2012 at 10:41 am
Edit: Blah, blah, waffle, waffle.
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
August 28, 2012 at 10:54 am
Edit: sorry, wrong class.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
August 28, 2012 at 11:00 am
GilaMonster (8/28/2012)
You can't create a clustered index with include columns. Gives an error if you try.
I know. That's part of what "You'll see what it does for yourself" was intended to accomplish.
Edit your post and add "SPOILER ALERT" at the top, please. :w00t:
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2012 at 2:36 pm
winston Smith (8/28/2012)
A clustered index is in reality the table, organized by the clustering key.But you can make a clustered index based on only a small number of columns in a table.
So, I am creating an indexed view. The view has 5 columns and the clustering key is 2 of those columns.
Im told that if i create a clustered index on those 2 columns, i dont need to include the other 3 columns, as they are automatically included as its a clustered index, i.e. the actual data.
Is this logic valid, as it does not look valid, when i check what columns are associated with the index in the index metadata.
It is valid. The clustered index does indeed include all columns in the table at its leaf level. But you're right, that can't be all there is to it, or a clustered index wouldn't have any advantage to it.
The key here is that all indexes have different levels.
The lowest/bottom level of an index is called the "leaf" level. It will include all columns in that index. For a clustered index, this will be all columns in the table. For a nonclustered index, it will be all columns defined for that specific index: key columns and INCLUDEd columns.
All upper levels are non-leaf levels, which contain only the key columns.
This makes index searches much more efficient.
The number of levels in indexes can vary (more rows can require additional levels), but there will always be at least one non-leaf level (and there may be more) and a leaf level (always only one leaf level).
So, at the leaf level, the clustered index contains all columns. But its upper levels contain only the key column(s).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply