SARGable Query

Just by adding an index to a table doesn’t mean you get performance improvement.
To make query sargable by not altering the column side of Where predicate or join clause so that SQL Server can utilize the index on that column whithout having to do any extra work.
Non-Sargable Query example on dbo.Invetory table with 100 records:

</pre>
Select Name FROM dbo.Inventory WHERE CONVERT(CHAR(10), CreateDate, 121) = '2018-06-28' 

Here Sql Server will perform Index Scan on each row

Sargable Query example:

SELECT Name
FROM dbo.Inventory
WHERE CreateDate = CAST('2018-06-28' as datetime2)

Here Sql Server will perform Index Seek.

Reference
https://blog.bertwagner.com/how-to-search-and-destroy-non-sargable-queries-on-your-server-ff9f57c7268e

Advertisements

Leave a comment

Filed under SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s