Database Reference
In-Depth Information
Table 2-1.
Examples of refactoring Non-Sargable predicates into SARGable ones
Operation
Non-SARGable implementation
SARGable implementation
Column - 1 = @Value
ABS(Column) = 1
Column = @Value + 1
Column IN (-1, 1)
Mathematical
calculations
CAST(Column as date) = @Date
convert(datetime, convert(varchar(10),
Column,121))
Column >= @Date and
Column < DATEADD(day,1,@Date)
Date manipulation
DATEPART(year,Column) = @Year
Column >= @Year and
Column < DATEADD(year,1,@Year)
DATEADD(day,7,Column) > GETDATE()
Column >
DATEADD(day,-7,GETDATE())
LEFT(Column,3) = 'ABC'
Column LIKE 'ABC%'
Prefix search
Column LIKE '%ABC%'
Use Full-Text Search or other
technologies
Substring search
Another important factor that you must keep in mind is
type conversion
. In some cases, you can make predicates
non-SARGable by using incorrect data types. Let's create a table with a varchar column and populate it with some
data, as shown in Listing 2-9.
Listing 2-9.
SARG predicates and data types: Test table creation
create table dbo.Data
(
VarcharKey varchar(10) not null,
Placeholder char(200)
);
create unique clustered index IDX_Data_VarcharKey
on dbo.Data(VarcharKey);
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 CROSS JOIN N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 CROSS JOIN N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 CROSS JOIN N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 CROSS JOIN N4 as T2) -- 65,536 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N5)
insert into dbo.Data(VarcharKey)
select convert(varchar(10),ID)
from IDs;
The clustered index key column is defined as
varchar,
even though it stores integer values. Now let's run two
selects, as shown in Listing 2-10, and look at the execution plans.
