Alright... So instead of getting up a cutting the grass, I decided to do a little testing this morning...
For a test bench I used 1M rows generated by the script that Eirikur posted earlier. (Thanks for that too Eirikur :-))
I tested 3 versions of the code, all of which produced identical results for a given set of parameter values.
V1: Eirikur's original code posed earlier that got me interested in the 1st place.
DECLARE
@FirstName NVARCHAR(50) = 'r',
@LastName NVARCHAR(50) = 'k%o',
@Address NVARCHAR(100) = NULL,
@st DATETIME2(7);
SET @st = SYSDATETIME();
SELECT
PE.Id,
PE.FirstName,
PE.LastName,
PE.Address
FROM
dbo.Person PE
WHERE
PE.FirstName LIKE NCHAR(37) + ISNULL(@FirstName, N'') + NCHAR(37)
AND PE.LastName LIKE NCHAR(37) + ISNULL(@LastName, N'') + NCHAR(37)
AND PE.Address LIKE NCHAR(37) + ISNULL(@Address, N'') + NCHAR(37)
OPTION(RECOMPILE)
;
SELECT DATEDIFF(ms, @st, SYSDATETIME()) AS ExecTime;
V2: A variant of Eirikur's code what doesn't hard code the wildcards
DECLARE
@FirstName NVARCHAR(50) = '%r%',
@LastName NVARCHAR(50) = '%k%o%',
@Address NVARCHAR(100) = NULL,
@st DATETIME2(7);
SET @st = SYSDATETIME();
SELECT
PE.Id,
PE.FirstName,
PE.LastName,
PE.Address
FROM
dbo.Person PE
WHERE
PE.FirstName LIKE (ISNULL(@FirstName, N'%'))
AND PE.LastName LIKE (ISNULL(@LastName, N'%'))
AND PE.Address LIKE (ISNULL(@Address, N'%'))
OPTION(RECOMPILE)
;
SELECT DATEDIFF(ms, @st, SYSDATETIME()) AS ExecTime
V3: This is the old school way of doing optional parameters that usually takes flack for forcing index scans when not used with OPTION(RECOMPILE).
DECLARE
@FirstName NVARCHAR(50) = '%r%',
@LastName NVARCHAR(50) = '%k%o%',
@Address NVARCHAR(100) = NULL,
@st DATETIME2(7);
SET @st = SYSDATETIME()
SELECT
PE.Id,
PE.FirstName,
PE.LastName,
PE.Address
FROM
dbo.Person PE
WHERE
(PE.FirstName LIKE (@FirstName) OR @FirstName IS NULL)
AND (PE.LastName LIKE (@LastName) OR @LastName IS NULL)
AND (PE.Address LIKE (@Address) OR @Address IS NULL)
OPTION(RECOMPILE)
;
SELECT DATEDIFF(ms, @st, SYSDATETIME()) AS ExecTime
Each of the 3 versions was tested with & without OPTION(RECOMPILE) and with and without the leading wildcard (so, 4 tests per version).
All 12 execution plans are included in the attached zip file...
Just as an FYI... The leading wildcard versions each returned 9,151 rows and the non-leading wildcard versions each returned 688 rows.
The results are a follows... (all times are in milliseconds)
Leading wildcard...
V1 w RCV1 wo RCV2 w RCV2 wo RCV3 w RCV3 wo RC
1304941284940302296
2295946297920314312
3313963298949311314
4297939301935302307
5313951297938327314
6300963315935289314
7292952324925295306
8312950312923315290
9298945303922304286
10326952326964306311
Avgs305950.2305.7935.1306.5305
Without the leading Wildcard...
V1 w RCV1 wo RCV2 w RCV2 wo RCV3 w RCV3 wo RC
1384552495363
2355438514552
3513029293573
4313050293472
5493351352949
6343036465045
7354347375463
8354453365466
9505352395448
10524947293361
Avgs4141.145.53844.159.2
Conclusion:
When OPTION(RECOMPILE) is used, all 3 perform about the same. They are all able switch between index seeks and scans, as appropriate, based on the presence of a leading wildcard.
Note... To test V1 w/o the leading wild card it was necessary to delete "CCHAR(37) + " form the code itself...
It's when the OPTION(RECOMPILE) is removed that things get interesting...
V1 & V2 will cause the optimizer to opt for an index seek no matter if there is a leading wildcard or not.
V3 will cause the optimizer to opt for an index scan no matter if there is a leading wildcard or not.
So, in this round of testing, seeking when it should be scanning, caused the average execution times to increase by more that 300%.
On the other hand, scanning when it could be seeking caused the average execution times to increase by less than 50%.