2. Adding Non-Clustered Indexes (30 min)
That last module was horrible – it’s so much work when you only have a clustered index! In this module, you’ll get two nonclustered indexes, learn which one is more useful, and see why seek doesn’t mean awesome.
Demo Scripts
If you want to follow along with the demos, I’ll save you from the Herculean task of writing all those little queries down, heh:
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
/* How to Think Like the SQL Server Engine: Part 2, Nonclustered Indexes Video, slides, scripts: https://www.BrentOzar.com/go/engine License: Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) More info: https://creativecommons.org/licenses/by-sa/3.0/ You are free to: * Share - copy and redistribute the material in any medium or format * Adapt - remix, transform, and build upon the material for any purpose, even commercially Under the following terms: * Attribution - You must give appropriate credit, provide a link to the license, and indicate if changes were made. * ShareAlike - If you remix, transform, or build upon the material, you must distribute your contributions under the same license as the original. */ /* Doorstop */ RAISERROR(N'Did you mean to run the whole thing?', 20, 1) WITH LOG; GO /* This class is based on the Stack Overflow public data dump. You can download any size/version of it below. I'll be using the 10GB "small" version to make my index creations go quickly: https://www.BrentOzar.com/go/querystack */ USE StackOverflow2010; GO CREATE INDEX IX_LastAccessDate_Id ON dbo.Users(LastAccessDate, Id); GO SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; GO SET STATISTICS TIME, IO ON; /* Back to back comparison of indexes */ SELECT Id FROM dbo.Users WITH (INDEX = 1) WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; GO SELECT Id FROM dbo.Users WHERE LastAccessDate > '1800/01/01' ORDER BY LastAccessDate; GO |
4 Comments. Leave new
Nice
I really enjoy reading through your posts. 🙂
I am watching this video and a couple others in preparation for Brent’s two full day classes on Index and Query tuning on 2020-04–08. So the question is a little ‘after the fact’. My question is why did you include the ID field in the NonClustered index you created? The table was already clustered on the ID field. I thought SQL Server already includes the Cluster Key with each row of the NonClustered Index. So doesn’t adding the ID in this situation, mean that every row in the NonClustered index have the same ID listed twice on the page?
Never mind. Ignore the previous comment. In the next video on Key Lookup, you mention that you just added the ID field to the index to illustrate space usage on the page.