There exists an index but query does not user created index, Why?

Question

There exists an index but query does not user created index, Why?

Thinky Night

SSCommitted

Points: 1663
More actions
April 27, 2011 at 9:16 am

#238480

My Table has only one non-clustered index and, optimizer does not this index? WHY?
My Query:
SET STATISTICS PROFILE ON
go
create table TableTest1(
id int ,
name varchar(64),
surname varchar(64),
address varchar(64)
)
create nonclustered index INDX_TableTest1_2 on TableTest1 (name) INCLUDE (surname)
go
declare
@count int
begin
set @count=1
while (@count < 100)
begin
INSERT INTO TableTest1(id,name,surname,address) values (@count,'Name'+LTRIM(RTRIM(STR(@count))),'Surname'+LTRIM(RTRIM(STR(@count))),'Address '+LTRIM(RTRIM(STR(@count))))
set @count = @count + 1
end;
end
go
select * from TableTest1 where name like '%Name1%'
go
Result:
|--Table Scan(OBJECT:([test].[dbo].[TableTest1]), WHERE:
NOT INDEX SEEK OR INDEX SCAN
What causes to this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The predicate is not SARGable, the index is not covering.

SQL has no idea how many rows that predicate affects, hence it can't tell whether scanning the nonclustered index and doing RID lookups for all the other columns is cheaper than a table scan or not, hence it does a table scan

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 2

Below a threshhold number of rows in a table, the query optimizer doesn't even check for indexes, it just scans the table. Figuring out which index to use would cost more effort than a table scan at that point.

I think the general rule of thumb is about 1,000 rows, but that's just something I seem to remember from looking into several years ago. I do know that the number varies based on query complexity and other factors, so memorizing a specific number of rows seems pointless anyway.

It can also ignore non-covering indexes sometimes if the bookmark lookups would cost more than a cluster/table scan.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sturner SSC-Insane Points: 22411 More actions · Answer 3

An index can't be used for this: where name like '%Name1%'

anytime you are searching for a pattern somewhere in the middle of the column the index seek cannot be used. It may scan the index if the index can cover the query, but it will be a scan, not a seek.

The probability of survival is inversely proportional to the angle of arrival.