Inequality predicates do not trigger scans

I see sometimes there is an understanding that when using inequality operators in the where clause, this will make the query optimizer decide to do a table scan for the table or index and will not use seek operations. Sometimes this is called non-SARGable predicate. This blog post is to discuss this understanding, but if you do not have time to read it until the end then this is not true, at least for SQL Server 2008 onward.

For testing this, I created a sample database in a SQL 2012 instance; I used the DMF sys.dm_db_database_page_allocations just to build some data which is only available in 2012 only. You can however copy the objectsPages table to SQL 2008/R2 and you will get the same results. The code below

IF DB_ID('inequality') IS NOT NULL

DROP DATABASE inequality;

GO

CREATE DATABASE inequality;

GO

USE inequality;

GO

 

IF EXISTS(SELECT name FROM sys.tables WHERE name='objectsPages')

DROP TABLE objectsPages;

 

CREATE TABLE objectsPages

(object_id bigint,index_id int,allocation_unit_id bigint,allocation_unit_type_desc nvarchar(50),allocated_page_iam_page_id bigint,

allocated_page_page_id bigint PRIMARY KEY, is_allocated bit,is_iam_page bit,is_mixed_page_allocation bit)

GO

 

INSERT objectsPages

SELECT object_id,index_id,allocation_unit_id,allocation_unit_type_desc,

allocated_page_iam_page_id,allocated_page_page_id,is_allocated,is_iam_page,is_mixed_page_allocation

FROM sys.dm_db_database_page_allocations(DB_ID('AdventureWorks2012'),null,null,null,'Limited');

GO

 

--Create indexes

CREATE INDEX is_iam_page ON objectsPages(is_iam_page);

 

CREATE INDEX is_mixed_page_allocation ON objectsPages(is_mixed_page_allocation);

 

To discuss the SARGability or whether or not SQL Server will consider a predicate as searchable or not, we have to differentiate between two operations

    -SQL Server is doing scan (table or index doesn't matter) because the Query Optimizer doesn't know how many rows will be returned

    -SQL Server is doing scan because SQL knows how many rows to be returned BUT the cost of scanning is calculated to be less than the cost of seeking

The first case, SQL Server consider the predicate non-SARGable and the second case, SQL Server is capable of seeking but decided to do a scan. Although the output is the same (scan operation) but the reason is totally different.

 

Now if we tried two queries like

SELECT allocated_page_page_id FROM objectsPages WHERE is_iam_page = 1;

 

SELECT allocated_page_page_id FROM objectsPages WHERE is_iam_page <> 1;

 

The execution plan will be as below. Note that in both cases SQL Server used seeks not scanning, and SQL Server can tell the expected number of Rows because we have statistics created with the index and SQL can get the estimates. However SQL Server has to convert the query to be equality by using seek predicates of < and > instead of <> (not equal). You can check that in the seek predicate in the index seek. The part of the execution plan marked with the blue marker is the part where SQL Server prepare the values for > and <

 

But if we changed the query a little bit and instead of only selecting the clustered index key; selecting the whole table

SELECT * FROM objectsPages WHERE is_iam_page = 1;

 

SELECT * FROM objectsPages WHERE is_iam_page <> 1;

 

The execution plan shows in both cases that SQL Server didn't use the predicate and decided to scan the table because the cost of scanning is estimated to be lower than the cost of index seek + key lockups operations. Whether or not we are using inequality is irrelevant in that case.