February 7, 2015 at 4:14 am
As a .NET web-developer, I find myself producing lots of stored procedures and functions (SQL Server 2012). I know my understanding of query performance issues could/should be better (we don't have a DBA), so I decided to work through the above book. I confess to finding some of the concepts tricky, so I thought I might ask for clarification on issues as I come across them.
On page 71, the following simple example is given (on the AdventureWorks database - 2012 in my case).
SELECT ct.ContactTypeId
FROM Person.ContactType AS ct
WHERE Name LIKE 'Own%'
Primary key, clustered - ContactTypeId
Unique, non-clustered - Name
The above query resulted in a non-clustered index seek, which I understand. As a way of learning, I play around with the query and try to predict what will happen. I removed the WHERE clause from the above and predicted a clustered index scan, as the clustered index would be the only index referenced. It produced a non-clustered index scan, which I don't understand.
Can anyone shed any light?
Paul
February 12, 2015 at 4:31 am
As non clustered index contain Index Search key (Name) and clustered index key (ContactTypeId) in its leaf node.
SQL Server will scan Non clustered index.
Please refer to http://www.sqlservercentral.com/stairway/72399/
to learn about clustered and non clustered indexes
February 12, 2015 at 7:12 am
I understand clustered and non-clustered indexes.
The Name column is not being referenced in the query, so why would SQL Server reference the non-clustered index on the Name column?
February 12, 2015 at 7:14 am
bpw (2/12/2015)
I understand clustered and non-clustered indexes.The Name column is not being referenced in the query, so why would SQL Server reference the non-clustered index on the Name column?
Imagine the table contains 200 wide columns. What would be quicker, reading the index - which contains the two referenced columns, or the clustered index?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 12, 2015 at 7:28 am
But there aren't two referenced columns, the modified query is only referencing the primary-key column - hence my question.
February 12, 2015 at 7:31 am
bpw (2/12/2015)
But there aren't two referenced columns, the modified query is only referencing the primary-key column - hence my question.
Point taken, sorry.
Ok try this: what two columns does the ordinary index contain?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 12, 2015 at 7:41 am
I'm not sure I understand the question. By 'ordinary' do you mean non-clustered?
I understand that all items in a non-clustered index will include a reference to the related item in the clustered index (assuming one exists).
I was assuming that the quickest route with this simple query would be to return the clustered index, rather than use the non-clustered index to return the related keys.
I'm sure the issue is not crucial, but I wondered whether I was missing something fundamental.
February 12, 2015 at 7:49 am
All nonclustered indexes contain the clustered index key.
Hence, that nonclustered index on Name contains both Name and ContactTypeId
Your query has no where clause, hence there's no possibility for anything other than a scan. The only column it needs is ContactTypeId, which is present both in the clustered index and in the nonclustered index (because it's the clustered index key)
Which is more efficient, scan the table (the clustered index), which contains about 8 columns iirc, or scan the nonclustered index which contains two columns only?
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply