SQL Server Optimization – Index Scan in SELECT Statement

optimizationsql server

While testing query:

select AccountID, SchemaID, Name, Description, CurrentVersion 
from [test].[dbo].[FormSchema] 
where SchemaID like '%[^a-zA-Z0-9-_]%'

After running execution plan it is showing clustered index scan with 100% cost. There is one clustered index on column schemaId.

My question is how can I make it index seek rather than scan?

Best Answer

SchemaID like '%[^a-zA-Z0-9-_]%' is going to be problematic no matter what you do.

Having said that, if you added an index like this:

CREATE INDEX IX_FormSchema 
ON dbo.FormSchema(SchemaID) 
INCLUDE (AccountID, SchemaID, Name, Description, CurrentVersion);

And change the query to:

WHERE SchemaID like '[^a-zA-Z0-9-_]%'

you might get an index seek.

Having said that, an index scan is not inherently a bad thing. In fact, SQL Server is choosing to do an index scan because it believes that is the fastest way to get your results.

Related Question