For those people who seek seeks, here’s a “Pro Tip”.
Suppose you have a query that involves a Scan. It can be a Clustered Index Scan or an Index Scan, it really doesn’t matter. If it’s a Table Scan, then you have a Heap in play, and this tip doesn’t apply.
I’m looking for the address in AdventureWorks that’s on Pitt St. I know it’s a tall building, and that the street is in AddressLine2.
select *
from Person.Address
where AddressLine2 like '%Pitt Street';
This query scans an index called IX_Address_AddressL…something (I’m reading it from the screen, and it’s a little cut-off). Here’s the plan, and I’ve included part of the tooltip so that you can see the scan finds a single row.
Now, I’ve heard that Scans are bad, and Seeks are better. I’m sure I can make this query seek, without even adding a new index!
First I run a simple query to look up the first key column in this index.
select c.name, c.is_nullable, t.name, c.max_length
from sys.indexes i
join sys.index_columns ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
join sys.columns c
on c.object_id = i.object_id
and c.column_id = ic.column_id
join sys.types t
on t.user_type_id = c.user_type_id
where i.name like 'IX_Address_AddressL%'
and i.object_id = object_id('Person.Address')
and ic.key_ordinal = 1;
This is handy – it tells me it’s AddressLine1, and that’s a non-nullable column of type nvarchar(120).
Now, every value in this column must be at least alphabetically >= than the empty string, so adding the predicate WHERE AddressLine1 >= N'' shouldn’t affect the results (it could if it allowed NULLs though, so be careful).
select *
from Person.Address
where AddressLine2 like '%Pitt Street'
and AddressLine1 >= N'';
And hey presto, I’ve turned the Scan into a Seek! Success! Clearly my performance has been enhanced.
Of course it hasn’t.
In fact, it’s probably marginally worse, because not only am I having to check every row to see if it’s in Pitt Street, but I have to work out where to start. I’m still starting at the beginning of the index, and going through every record, but because we’re performing a search on the key column of the index, the Query Optimizer is actually doing a Seek. It’s just a Seek which is having to scan every row of the table, which is what we generally consider to be a Scan, not a Seek.
If you’ve read some of my recent posts, you will realise that this is because of the Residual Predicate in play. Both tooltips are shown below for your reading pleasure, and you’ll notice that they’re very similar. They have the same Residual Predicate which is doing all the work, it’s just that the Seek is starting off with a Seek Predicate.
So you see, this Seek is never going to perform better than the Scan. It’s all well and good to recognise that effective usage of indexes involves Seeks, but a Seek isn’t ideal just because it’s a Seek, it has to be Seeking on something effective, highlighted by a selective Seek Predicate.
...and of course, we should’ve done some far more creative indexing, such as introducing a full-text index on AddressLine2.
I’ve also submitted a Connect item to have more information shown in the query plan, at:
https://connect.microsoft.com/SQLServer/feedback/details/670391/more-information-from-seek-operations-in-plans
@rob_farley