1

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?

Improve this question
1
  • 2
    The broad issue here is that your regex is not "sargable." Exactly what is and isn't sargable varies by platform, but as a heuristic, you could ask "could I quickly find someone in the phone book with this criteria?" The answer is "no," you'd need to look at every entry - in other words, an index scan. If your filter was SchemaID = 'Bob', you could skip to the Bs and flip a few pages left or right until you got to the Bobs - an index seek.
    – Jon of All Trades
    Commented Mar 8, 2017 at 22:19

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.