Database Reference
In-Depth Information
You will observe very similar behavior in the case of unicode string parameters. Let's run the queries shown in
Listing 2-11. Figure
2-15
shows the execution plans for the statements.
Listing 2-11.
SARG predicates and data types: Select with string parameter
select * from dbo.Data where VarcharKey = '200'
select * from dbo.Data where VarcharKey = N'200' -- unicode parameter
Figure 2-15.
SARG predicates and data types: Execution plans with string parameter
As you see, a unicode string parameter is non-SARGable for varchar columns. This is a much bigger issue
than it appears. While you rarely write queries in this way, as shown in Listing 2-10, most application development
environments nowadays treat strings as unicode. As a result, SQL Server client libraries generate unicode (nvarchar)
parameters for string objects unless the parameter data type is explicitly specified as varchar. This makes the
predicates non-SARGable, and it can lead to major performance hits due to unnecessary scans, even when varchar
columns are indexed.
