HomeНаука и техникаRelated VideosMore From: Voluntary DBA

Clustered vs. Nonclustered Index Structures in SQL Server

5047 ratings | 261023 views
Clustered and nonclustered indexes share many of the same internal structures, but they're fundamentally different in nature. Watch Microsoft Certified IT Professional Jon Seigel explain the similarities and differences of clustered and nonclustered indexes, using a real-world example to show how these structures work to improve the performance of SQL queries. Blog post on primary key vs. the clustered index: http://voluntarydba.com/post/2012/10/02/The-Primary-Key-vs-The-Clustered-Index.aspx CREATE INDEX statement reference: http://technet.microsoft.com/en-us/library/ms188783.aspx ALTER INDEX statement reference: http://technet.microsoft.com/en-us/library/ms188388.aspx Index navigation internals by example: http://sqlblog.com/blogs/paul_white/archive/2011/08/09/sql-server-seeks-and-binary-search.aspx Sample index data is from the AdventureWorksLT2008R2 sample database: http://awlt2008dbscript.codeplex.com/releases/view/46169 Visit my channel for more database administration videos: https://www.youtube.com/voluntarydba Subscribe to get notified about my latest videos: https://www.youtube.com/voluntarydba?sub_confirmation=1 Read additional content on my blog: http://voluntarydba.com Follow on Twitter: https://twitter.com/voluntarydba Like on Facebook: https://facebook.com/voluntarydba
Html code for embedding videos on your blog
Text Comments (278)
SKB ___ (4 days ago)
Excellent explanation. This truly helped.
Max Hansen (28 days ago)
The models you used were great! Thanks!
Kamal Balasubramanian (1 month ago)
Best explanation with visuals. Kudos!!
Oleg Ryklin (1 month ago)
Is he high? :)
Voluntary DBA (1 month ago)
No
Fabian B. (1 month ago)
great post. Thanks!
Hugo Kornelis (1 month ago)
Now i can safely use indexes, good!
tony a (2 months ago)
that was a great explanation. Thank you very much sir !
Al Bundy (2 months ago)
A to Zed. Must be Canadian eh.
Justin Li (2 months ago)
so well explained
Gaurav Babbar (2 months ago)
Best explanation i have seen for this topic. Thumbs up man.
D. Refaeli (3 months ago)
When adding new data/rows to a clustered-indexed table - does it has to re-arrange the entire data again? Also, for non-clustered indexed table - does it has to re-arrange the entire new structure?
Voluntary DBA (3 months ago)
Good question. For the clustered index, if there's no free space on the page where the data should go (as the clustered index defines the physical order to the data), the database does what's called a page split, where it creates a new page and splits the existing data between them, and then inserts into the free space. The same concept applies to any index pages that would be affected (including any applicable non-clustered indexes). For heaps, the row is inserted into the first free page as there's no physical order to the data.
Cami KD (3 months ago)
THANKS<3
ALLCPL (3 months ago)
Very good
Rahul Jha (3 months ago)
Detailed Awesome Explanationation, But I am Hypnotized 8)
Отлично все поведал. Спасибо огромное!
Monika Thakran (4 months ago)
to the point, concise, thanks
Nilanshu Sharma (4 months ago)
Thanks so much. I feel enlightened :)
Mahmud Abdullah Al Noman (5 months ago)
so brilliant
SAADAOUI Ismail (5 months ago)
very vell explained
Phạm Duy Trung (6 months ago)
Christian Eriksen is so good at DB, thanks for your nice explaination
福貓貓 (6 months ago)
this is so good an explanation, I love you!!! good mate and good job.
Mike Yoniles (6 months ago)
This has been a great straightforward video that really helped explain the difference. Thank you keep up the good work!
Jayrich Zhanra (7 months ago)
Thank you for sharing! I appreciate!
ezl100 (7 months ago)
realy good explanation and in a simple way thks
James Jakubowski (7 months ago)
Thank you!!  I particularly liked your examples and your methodical delivery of the information.  I found the examples to be very helpful as well!
Seetharam phanindra (8 months ago)
Really helpful video with simple examples
RussianVideoPodcast (8 months ago)
I finally understood indexes...Very good video! Thank y ou
Andres Hernandez (8 months ago)
260 mph bro.
Uday Kiran (8 months ago)
Perfect explanation
이승환 (9 months ago)
you have saved my life !!!!! genius explanation. I have been struggling to understand this for months after months
Leandro Sarmiento (10 months ago)
Thank you a lot
Superlinux (10 months ago)
Hi, one question what do you use to create the screencast?
Voluntary DBA (10 months ago)
Photoshop Elements, Blender, and a lot of patience. :)
Jono Baker (10 months ago)
This video helped me ALOT, I've been watching many videos and this really clearly cleaned things up for me. The moving graphics were a big part of that too. thankyou!
Meghjeet Vartak (11 months ago)
Great work ...I liked the examples used.....Keep posting
Mrmenara11 (11 months ago)
Is there a diffrence between A Clustered Index and AN IOT ?? or are they the same , another thing in a Nonclustered index the pointers are RowIDs what does the Clustered uses to point to the data ?
Voluntary DBA (11 months ago)
Hi, if by IOT you mean index organized table (had to Google that), they are the same thing. For your second question, a non-clustered index points using RIDs (row IDs) if the base table is a heap, or the clustered index key (plus uniquifier if applicable) if the table is a clustered index.
Amanda Ong (11 months ago)
THANK YOU THANK YOU SUPER HELPFUL
nshusa A (11 months ago)
nick did you lose some weight? good for you
gizem tekiner (11 months ago)
it was so helpful. thanks
Devilfairy1 (11 months ago)
Best indexing explanation I've seen on internet.
Naresh Nama (11 months ago)
Thank you ...very help full and wonderful demo..we are expecting to see more demos off SQL server from you..
jaquesaulait (1 year ago)
Nice, clear delivery, good explanation. And he said "zed"! He speaks proper English..!
Familia Linda (1 year ago)
Excelente didática.
Mr. Saturn (1 year ago)
Very useful, thanks for making the video and explaining the concepts so well!
Sridharan Rangasamy (1 year ago)
Excellent explanation. I don't think this can be described any better. Great visuals. Thank you so much
chandu chinimilli (1 year ago)
Really Excellent..
David Hewes (1 year ago)
This was great, thanks. I would like to ask a question in this topic... Does a nonclustered lookup need to navigate the btree in the clustered or does it point directly to the page?
Voluntary DBA (1 year ago)
Rereading your question, it's very confusing, and I might have misunderstood. A non-clustered index has it's own b-tree, separate from the clustered index b-tree. A non-clustered lookup navigates its own b-tree, and if the index cannot satisfy the query on its own (non-clustered provides: non-clustered index key columns; INCLUDEd columns, if any; clustered index key columns), the clustered index key is used to go back to the clustered index to get the rest of the data, as I described above. I believe the only situation where a non-clustered index stores references to page numbers (i.e., RID) is when the table is a heap. It doesn't make sense to store both a RID and a clustered index key for every row. If the index you're examining is small, it may be using a special structure that stores all components of the index on a single page, which I didn't cover in this video for the sake of simplicity.
David Hewes (1 year ago)
Voluntary DBA Cool.. was not sure since the page number is stored in there also. I was thinking what's the sense of the btree if the page number is in there.
Voluntary DBA (1 year ago)
Hi David, In the context of a clustered index, non-clustered indexes contain the clustered index key (including the uniqueifier if the clustered index is non-unique), which can be used to navigate back to the exact row in the clustered index if necessary. This is exactly like an index seek on the clustered index, which navigates the b-tree.
Excellent :)
jalaj61 (1 year ago)
Very good explanation, thanks.
Ajay Surya (1 year ago)
Good job...Loved it absolutely.
Kislay Pandey (1 year ago)
Sir 5/5 for this video , the visualization was a gr8 help in understanding . I had some knowledge that B-tree was the index data structure but could not grasp it , but after your video it helped me a lot. Plz do post more such videos.
Pradeep Deep (1 year ago)
Nice Video, The way he explaining is extraordinary
William Chin-fook (1 year ago)
Great Explanation and analogy about clustered-indexes 5 star!
joshgpro (1 year ago)
Excellent video. It goes from very simple to advanced. Good use of visual aids. Very clear and understandable. Please, keep it up. Please, try to put on a little warm smile next time. Good job overall. I'm subscribing.
Ezekiel Rdz (1 year ago)
it can't be better explained, excelent work
ziv (1 year ago)
Since you are still answering on this video, first of all, thanks a bunch, it's a great video :) , Second, if I create an index without specifying what type it is, what does it default to? I assume, non-clustered. (I'm not a DBA, so I'm sorry if I'm missing some basics, I'm pretty much self taught(still learning))
Voluntary DBA (1 year ago)
Indexes and unique constraints default to non-clustered; primary key constraints default to clustered if there isn't a clustered index already. Microsoft TechNet has very detailed documentation about the behaviour, so if you're ever wondering about this stuff, definitely take a look there first because it probably has the answer.
Lauren Dai (1 year ago)
if have some power point on screen is much better to see the speaker face only
Voluntary DBA (1 year ago)
Thanks for the feedback
John Deer (1 year ago)
great visual representation and model choice. subscribed!
Mohsen Sichani (1 year ago)
Awesome tutorial, Visualised and explained well, Fantastic, Many thanks
Can you explain how composite clustered index works in a similar way.
Voluntary DBA (1 year ago)
A composite key simply means there's more than 1 column in the index (sorting) key.
Jiahe Xu (1 year ago)
The video is so useful!
Matt Trangmar (1 year ago)
Brilliant
soho1080 (1 year ago)
Clean and precise to the point unlike so many other garbage videos.. And I agree with the Nicholas Cage comment haha :)
Vincent Won (1 year ago)
Thank you so much for posting this! I was having a hard time understanding the differences between the two until i watched the video.
Vibhavari Vyas (1 year ago)
Thanks very well explained, any source which gives clarity for column-store indexes in similar manner ?
Voluntary DBA (1 year ago)
Sorry, I don't know of one, but you can try reading this article: https://rusanu.com/2012/05/29/inside-the-sql-server-2012-columnstore-indexes/
Tiji Mathew (1 year ago)
Very well illustrated and explained thoroughly. Well done.
Kiran Mandapaka (1 year ago)
great explanation.
Mateusz Machaj (1 year ago)
Thank you !!!
tepeyac10 (1 year ago)
You did great when you used graphics. You lose everyone when explaining it without them.
Voluntary DBA (1 year ago)
Thanks for the feedback!
HerdingDogRescuer (1 year ago)
Thanks for posting! I learned a lot!
Mimi Mimi (1 year ago)
thank you it is very helpfull
AKshay Raut (1 year ago)
its simple to understand with graphical representation. Keep up with them. Great work.
Brenda Olson (1 year ago)
Very good explanation of the subject.. Thank you
Utsav Jambusaria (1 year ago)
I must have gone through tons of articles before I ended up understanding what clustered/non-clustered indexes are, right here. Thank you!
Mike Rotch (1 year ago)
Make more videos.
Josito MTL (1 year ago)
Very clarifying video. Many thanks sir. I lost a job oportunity because not knowing the difference between Primary key and Unique key... :(
Voluntary DBA (1 year ago)
I'm sorry to hear that, but I'm glad you learned something from the video to prepare you for next time.
Talha Temuri (1 year ago)
Nice reading... I feel like watching a news lol
doumkatek Z (2 months ago)
Harsh. It's good information. I think maybe giving presentations is not your natural habitat, but that's okay, it was useful and well explained. Don't listen to the critics DBA
Voluntary DBA (1 year ago)
But did you learn anything?
lotusi thok (1 year ago)
wow
Calvin T (1 year ago)
thanks for the videos. very helpful. does it worth if we index every columns in a table?
Calvin T (1 year ago)
thank you very much. I will check it out.
Voluntary DBA (1 year ago)
That's a complicated question, as it depends on what types of queries you're going to be making. I would suggest asking a question on https://dba.stackexchange.com that includes details about your specific scenario.
George Smith (1 year ago)
I liked the visualizations best. The phrase "a picture is worth a thousand words" definitely applies to this video. Thanks for the concise and well delivered video. And, please, ignore the "Nicolas Cage" references below.
Hokibukisa (1 year ago)
This is way too high energy. I feel savaged. Please calm down when talking about sql I can't take all of this excitement.
Stephane Caix (11 days ago)
fucking lol :D
ALLCPL (3 months ago)
Hokibukisa
TheLegend 27 (1 year ago)
Ignas Kaz Your conclusion is intellectually limited. As is your grammar.
Ignas Kaz (1 year ago)
You guys are clearly intelectually limited
Hokibukisa (1 year ago)
TheLegend 27 I know how you feel. We were both effectively sexually assaulted by this creepy predator over YouTube.
Artem Mikhaylov (1 year ago)
Perfect explanation I've ever seen before! Please do not stop!
Corbin Bohne (1 year ago)
This is a fantastic explanation that a lowly analyst like me can understand :)
Sven rogue knight (1 year ago)
Well explained, Thanks
Pablo Cazares (2 years ago)
very good! much better then others I've watched
vivek singh (2 years ago)
Explained in very short time .. Thanks very much :)
Michael Gebreselassie (2 years ago)
I have been looking for different sources just to objectively understand the difference between clustered and non-clustered indexes and to know how they really work. This very short and precise video have put an end to all of my Qs. Thank you.
Shadow Pawned (2 years ago)
A lot wannabe blogs explained it with much much worse example, even a lot that even do not define from the scanning process through accumulate the results. Nice vid!
KUNAL (2 years ago)
Nicely explained!
Samir El Souki (2 years ago)
Thx dude!
Dan M (2 years ago)
wow....best explanation. I'm studying for the mcsa exam. I've been struggling with this topic. thanks!!
EnigmaMist (2 years ago)
This is the best explanation, I had till now about indexes. After seeing this video, I was curious, is it possible to visualize or map the tree structure using SQL queries. I have created a Clustered table with only 2 rows, just to see how it works. I tried DBCC Page with TRACEON and DBCC IND, but I am not able to see how the tree is formed. It maybe be a dumb question after reaching DBCC commands, but could you please help me out on this.
Voluntary DBA (2 years ago)
Yeah, choosing which type of index to use is really situation-dependent, and it can be as much an art as a science. It's key, IMO, to understand how the database engine will use each type of index and when -- then you as a developer will have the tools to make the best decision in any situation.
EnigmaMist (2 years ago)
Thank you, I got it after I putting few more records. We can get the details using the DBCC commands but it took me some time to understand how these commands work. Basically the trouble I am facing is to understand when to use which index (Normal Clustered and NonClustered, ColumnStore - Clustered and NonClustered).
Voluntary DBA (2 years ago)
Honestly it's been a while since I've needed this depth of knowledge, so I've forgotten a lot of it, but from what I remember, the structure is different if the whole index is small enough to fit on a single page. I didn't cover that case in the video because it's far less important to understand for a number of reasons. Try creating a larger index that takes up a few pages, and then inspect. You can reduce the number of rows necessary to do that for simplicity by using long string values.
brickman jim (2 years ago)
Wow, I could not image a man can explain it in such a simple way! Thank you!
satish ramapragada (1 year ago)
brickman jim d4t
Sky Wolf (2 years ago)
Your very calm person
Paulina Hyży (2 years ago)
Great explanation, thanks!
Samir Ramouche (2 years ago)
Great video
TheGreenlipton (2 years ago)
Very well explained!!
Cos (2 years ago)
It's clear! Crystal clear! Thanks!
Abd El-Rahman Ibn Omar (2 years ago)
Never before did Nicolas cage explain something so well, good job Nic. joking :D it was so helpful. thanks.
doumkatek Z (2 months ago)
And Nicolas Cage old has an odd sort of hottness. :)
Dias Neto (1 year ago)
kkkkkkkkkkkkkk
Voluntary DBA (2 years ago)
+Abd El-Rahman Ibn Omar lol
Shilpa G (2 years ago)
Thank You so much!!
Sid Gudiduri (2 years ago)
Wow Dude! Awesome. Thank you

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.