December 9, 2011 at 8:02 am
rmechaber (12/9/2011)
they can be returned with a query (eliminating the need for a bookmark lookup, if all the columns requested are included; i.e., the index is "covering" for the query), but they aren't used to filter a query by the WHERE clause.
They can be used to filter the query. They can't be used for index seeks, but they most certainly can be used for filters.
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
December 9, 2011 at 8:04 am
GilaMonster (12/9/2011)
rmechaber (12/9/2011)
they can be returned with a query (eliminating the need for a bookmark lookup, if all the columns requested are included; i.e., the index is "covering" for the query), but they aren't used to filter a query by the WHERE clause.They can be used to filter the query. They can't be used for index seeks, but they most certainly can be used for filters.
Woops, thanks, that's what I meant to say. Thanks for the correction Gail [edited later] and Ninja
Rich
December 9, 2011 at 8:07 am
Ninja's_RGR'us (12/9/2011)
rmechaber (12/9/2011)
Someone correct me if I'm wrong: columns that are part of the index definition can be used to filter a query based on the WHERE clause. Columns that are merely included are "along for the ride" -- they can be returned with a query (eliminating the need for a bookmark lookup, if all the columns requested are included; i.e., the index is "covering" for the query), but they aren't used to filter a query by the WHERE clause.Rich
They can be used to filter, they just can't be seeked (because they are not sorted).
BEGIN TRAN
CREATE TABLE #t (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, a int, b int, c int)
CREATE NONCLUSTERED INDEX #ix ON #t (a, b) INCLUDE (c)
INSERT INTO #t (a,b,c) VALUES (1, 2, 3 )
SELECT * FROM #t WHERE a = 1 AND b = 2
--Ix Seek
SELECT * FROM #t WHERE a = 1 AND b = 2 AND c = 3
--Ix Seek
SELECT * FROM #t WHERE c = 3
--Ix Scan
ROLLBACK
December 9, 2011 at 8:18 am
Ninja's_RGR'us (12/9/2011)
Ninja's_RGR'us (12/9/2011)
rmechaber (12/9/2011)
Someone correct me if I'm wrong: columns that are part of the index definition can be used to filter a query based on the WHERE clause. Columns that are merely included are "along for the ride" -- they can be returned with a query (eliminating the need for a bookmark lookup, if all the columns requested are included; i.e., the index is "covering" for the query), but they aren't used to filter a query by the WHERE clause.Rich
They can be used to filter, they just can't be seeked (because they are not sorted).
BEGIN TRANCREATE TABLE #t (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, a int, b int, c int)
CREATE NONCLUSTERED INDEX #ix ON #t (a, b) INCLUDE (c)
INSERT INTO #t (a,b,c) VALUES (1, 2, 3 )
SELECT * FROM #t WHERE a = 1 AND b = 2
--Ix Seek
SELECT * FROM #t WHERE a = 1 AND b = 2 AND c = 3
--Ix Seek
SELECT * FROM #t WHERE c = 3
--Ix Scan
ROLLBACK
Yet another reason why I love this site and the people who regularly post to it: inside of 4 minutes of me posting a misleading reply, Ninja posts a succinct demonstration clarifying the point. My bad: of course any column in a table can be used to limit a query in the WHERE clause, but a query won't be performing an index seek to do it, if the columns aren't in the index's definition.
Thanks again Ninja,
Rich
December 9, 2011 at 8:34 am
Ninja's_RGR'us (12/9/2011)
SELECT * FROM #t WHERE a = 1 AND b = 2 AND c = 3--Ix Seek
Seek and secondary filter. It can't just seek.
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
December 9, 2011 at 8:36 am
rmechaber (12/9/2011)
Yet another reason why I love this site and the people who regularly post to it: inside of 4 minutes of me posting a misleading reply, Ninja posts a succinct demonstration clarifying the point. My bad: of course any column in a table can be used to limit a query in the WHERE clause, but a query won't be performing an index seek to do it, if the columns aren't in the index's definition.Thanks again Ninja,
Rich
Just to be 100% sure about the wording.
When you have an index with included columns. You can still seek the index and then use the included columns to continue filtering. It might be slower than having all columns in the index part rather than in the include because they are not sorted and can be sought in the include, that part has to be a scan.
December 9, 2011 at 8:39 am
GilaMonster (12/9/2011)
Ninja's_RGR'us (12/9/2011)
SELECT * FROM #t WHERE a = 1 AND b = 2 AND c = 3--Ix Seek
Seek and secondary filter. It can't just seek.
I guess I wasn't clear. My point was that you could avoid the entire table scan and still have an index seek to really boost performance. Of course you then need to scan the included columns to keep filtering since they are not sorted.
I've done a lot of queries like that. Beats the hell out of a full table scan!
December 9, 2011 at 8:39 am
In the key != guaranteed a seek. You can only seek on a left-based subset of the index key.
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
December 9, 2011 at 8:45 am
GilaMonster (12/9/2011)
In the key != guaranteed a seek. You can only seek on a left-based subset of the index key.
I think we can stop now, or just link to the 15 articles & blog posts you wrote on that topic :hehe:.
Viewing 9 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply