Transact SQL – Optimize Like Clause via “is not null”

Background

Earlier this week, I ran into a problem that I tried to address using the like clause. It is documented here.

 

Community

Dwain Camps

Dwain Camps talks about his efforts at trying to optimize same “Like Clause” via adding an additional constraint ( is not null ) and a table hint ( Forceseek ).

That effort is thankfully available here, as well.

Let us take Dwain’s code to the Lab and see what occurs.

 

Lab

Guide

Here are the steps that we will take:

  1. Create Schema
    • TestLIKESearches
  2. Table
    • Create two tables with similar construct outside of the fact that the column we are are inspecting, AString, is nullable in one and not in the other
    • The two tables
      • TestLIKESearches.AStringIsNullable
      • TestLIKESearches.AStringIsNotNullable
  3. Queries
    • Get ID1, ID2, AString filter on AString like StringPattern
    • Get ID1, ID2, AString filter on AString like StringPattern and AString is not null
    • Get ID1, ID2, AString, Value filter on AString like StringPattern
    • Get ID1, ID2, AString, Value filter on AString like StringPattern and AString is not null
    • Get ID1, ID2, AString, Value filter on AString like StringPattern; with table hint ( FORCESEEK )
    • Get ID1, ID2, AString, Value filter on AString like StringPattern and AString is not null; with table hint ( FORCESEEK )

Table

[TestLIKESearches].[AStringIsNullable]


use [DBLab]
go

if SCHEMA_ID('TestLIKESearches') is null
begin

	exec('create schema [TestLIKESearches] authorization [dbo]')
	
end
go


if OBJECT_ID('[TestLIKESearches].[AStringIsNullable]') is not null
begin

	drop table [TestLIKESearches].[AStringIsNullable]
	
end
go

CREATE TABLE [TestLIKESearches].[AStringIsNullable]
(
     ID1         INT
    ,ID2         INT
    ,AString     VARCHAR(100) NULL
    ,Value       INT
    
    , constraint [PK_AStringIsNullable]
		  PRIMARY KEY 
			(ID1, ID2)
);
go

CREATE INDEX [INDX_AString]
ON [TestLIKESearches].[AStringIsNullable]
(
	[AString]
);
go

[TestLIKESearches].[AStringIsNotNullable]


if SCHEMA_ID('TestLIKESearches') is null
begin

	exec('create schema [TestLIKESearches] authorization [dbo]')
	
end
go


if OBJECT_ID('[TestLIKESearches].[AStringIsNotNullable]') is not null
begin

	drop table [TestLIKESearches].[AStringIsNotNullable]
	
end
go

CREATE TABLE [TestLIKESearches].[AStringIsNotNullable]
(
     ID1         INT
    ,ID2         INT
    ,AString     VARCHAR(100) NOT NULL
    ,Value       INT
    
    , constraint [PK_AStringIsNotNullable]
		  PRIMARY KEY 
			(ID1, ID2)
);
go

CREATE INDEX [INDX_AString]
ON [TestLIKESearches].[AStringIsNotNullable]
(
	[AString]
);

Query

Here are queries targeting each of our two tables.

[TestLIKESearches].[AStringIsNullable]


set nocount on;
go

use [DBLab]
go

SELECT ID1, ID2, AString
FROM   [TestLIKESearches].[AStringIsNullable]
WHERE  
	(
		( AString LIKE '%21%' )
	)			
;

SELECT ID1, ID2, AString
FROM   [TestLIKESearches].[AStringIsNullable]
WHERE  
	(
			( AString LIKE '%21%' )
		and ( AString is not null )			
	)			
;

SELECT ID1, ID2, AString, Value
FROM   [TestLIKESearches].[AStringIsNullable]
WHERE  
	(
			( AString LIKE '%21%' )
	)			
;

SELECT ID1, ID2, AString, Value
FROM   [TestLIKESearches].[AStringIsNullable]
WHERE  
	(
			( AString LIKE '%21%' )
		and ( AString is not null )			
	)			
;

SELECT ID1, ID2, AString, Value
FROM   [TestLIKESearches].[AStringIsNullable]
		with (FORCESEEK)
WHERE  
	(
		( AString LIKE '%21%' )
	)			
;

SELECT ID1, ID2, AString, Value
FROM   [TestLIKESearches].[AStringIsNullable]
		 with (FORCESEEK)
WHERE  
	(
			( AString LIKE '%21%' )
		and ( AString is not null )			
	)			
;

[TestLIKESearches].[AStringIsNotNullable]


SELECT ID1, ID2, AString
FROM   [TestLIKESearches].[AStringIsNotNullable]
WHERE  
	(
		( AString LIKE '%21%' )
	)			
;

SELECT ID1, ID2, AString
FROM   [TestLIKESearches].[AStringIsNotNullable]
WHERE  
	(
			( AString LIKE '%21%' )
		and ( AString is not null )			
	)			
;

SELECT ID1, ID2, AString, Value
FROM   [TestLIKESearches].[AStringIsNotNullable]
WHERE  
	(
			( AString LIKE '%21%' )
	)			
;

SELECT ID1, ID2, AString, Value
FROM   [TestLIKESearches].[AStringIsNotNullable]
WHERE  
	(
			( AString LIKE '%21%' )
		and ( AString is not null )			
	)			
;

SELECT ID1, ID2, AString, Value
FROM   [TestLIKESearches].[AStringIsNotNullable]
		with (FORCESEEK)
WHERE  
	(
			( AString LIKE '%21%' )
	)			
		
;

SELECT ID1, ID2, AString, Value
FROM   [TestLIKESearches].[AStringIsNotNullable]
		 (FORCESEEK)
WHERE  
	(
			( AString LIKE '%21%' )
		and ( AString is not null )			
	)			
;


Output

v2014

TestLIKESearches.AStringIsNullable

Columns Request Clause Hint Scan Type
 ID1, ID2, AString  ( AString LIKE ‘%21%’ )  Non Clustered Index Scan
 ID1, ID2, AString   ( AString LIKE ‘%21%’ )
and ( AString is not null )
Non Clustered Index Seek
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ ) Clustered Index Scan
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )
and ( AString is not null )
Clustered Index Scan
ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ ) FORCESEEK  Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )
and ( AString is not null )
FORCESEEK INDEX SEEK ( NON CLUSTERED )
KEY LOOKUP ( CLUSTERED )

 

TestLIKESearches.AStringIsNotNullable

Columns Request Clause Hint Scan Type
 ID1, ID2, AString  ( AString LIKE ‘%21%’ )  Non Clustered Index Scan
 ID1, ID2, AString   ( AString LIKE ‘%21%’ )
and ( AString is not null )
 Non Clustered Index Scan
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )  Clustered Index Scan
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )
and ( AString is not null )
Clustered Index Scan
ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ ) FORCESEEK  Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )
and ( AString is not null )
FORCESEEK  Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

 

v2008/R2

TestLIKESearches.AStringIsNullable

Columns Request Clause Hint Scan Type
 ID1, ID2, AString  ( AString LIKE ‘%21%’ )  Non Clustered Index Scan
 ID1, ID2, AString   ( AString LIKE ‘%21%’ )
and ( AString is not null )
 Non Clustered Index Seek
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )  Clustered Index Scan
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )
and ( AString is not null )
Clustered Index Scan
ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ ) FORCESEEK  Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )
and ( AString is not null )
FORCESEEK  Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

 

TestLIKESearches.AStringIsNotNullable

Columns Request Clause Hint Scan Type
 ID1, ID2, AString  ( AString LIKE ‘%21%’ )  Non Clustered Index Scan
 ID1, ID2, AString   ( AString LIKE ‘%21%’ )
and ( AString is not null )
 Non Clustered Index Scan
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )  Clustered Index Scan
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )
and ( AString is not null )
Clustered Index Scan
ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ ) FORCESEEK  Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )
and ( AString is not null )
FORCESEEK  Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

 

v2005 SP4

TestLIKESearches.AStringIsNullable

Columns Request Clause Hint Scan Type
 ID1, ID2, AString  ( AString LIKE ‘%21%’ )  Non Clustered Index Scan
 ID1, ID2, AString   ( AString LIKE ‘%21%’ )
and ( AString is not null )
 Non Clustered Index Seek
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ ) Clustered Index Scan
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )
and ( AString is not null )
Clustered Index Scan
ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ ) FORCESEEK Msg 8622, Level 16, State 1, Line 1

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )
and ( AString is not null )
FORCESEEK Msg 321, Level 15, State 1, Line 3

“FORCESEEK” is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

 

TestLIKESearches.AStringIsNotNullable

Columns Request Clause Hint Scan Type
 ID1, ID2, AString  ( AString LIKE ‘%21%’ )  Non Clustered Index Scan
 ID1, ID2, AString   ( AString LIKE ‘%21%’ )
and ( AString is not null )
 Non Clustered Index Scan
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ ) Clustered Index Scan
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )
and ( AString is not null )
Clustered Index Scan
ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ ) FORCESEEK Msg 321, Level 15, State 1, Line 3
“FORCESEEK” is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.
 ID1, ID2, AString, Value   ( AString LIKE ‘%21%’ )
and ( AString is not null )
FORCESEEK Msg 321, Level 15, State 1, Line 3
“FORCESEEK” is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

 

Findings

There are a couple of things that I did not expect:

  1. v2014
    • Version :- Microsoft SQL Server 2014 – 12.0.4213.0 (X64)   Jun  9 2015 12:06:16   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
    • Index Seek
      • Table :- TestLIKESearches.AStringIsNullable
      • Column :- ID1, ID2, AString
      • Query:-  ( AString LIKE ‘%21%’ ) and ( AString is not null )
    • Index Seek & Key Lookup
      • Table :- TestLIKESearches.AStringIsNullable
      • Hint :- ForceSeek
      • Column :- ID1, ID2, AString
      • Query:-  ( AString LIKE ‘%21%’ ) and ( AString is not null )
  2. v2008/R2 – RTM
    • Index Seek
      • Table :- TestLIKESearches.AStringIsNullable
      • Column :- ID1, ID2, AString
      • Query:-  ( AString LIKE ‘%21%’ ) and ( AString is not null )
    • Index Seek & Key Lookup
      • Table :- TestLIKESearches.AStringIsNullable
      • Hint :- ForceSeek
      • Column :- ID1, ID2, AString, Value
      • Query:-  ( AString LIKE ‘%21%’ ) and ( AString is not null )

Microsoft Connect Items

Opened up a Connect Item seeking community thoughts.

  1. Reviewing “Like Predicates” sometimes leads to unpredictable Query Plans – Index Scans Versus Index Seeks
    • ID :- 2855591
    • Date :- 2016-06-24
    • Status :- Opened

 

Summary

It seems paradoxical that declaring a column nullable, and when querying it stating that only not nullable entries should be returned, yields an Index Seek.

And, this is true in practice.  And, also when one provides an Explicit Request ( through Index Hint)  for it to be so.

Same does not occur when the column is defined “Not Nullable“.

Either we get an Index Scan or when we request an Index Seek we get an error stating “Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN“.

Thanks goodness for precise errors, as this one definitely is.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s