James Dingle,
I've tried your style of query to obtain the execution plan and it is the worst performing out of all the attempts so far.
Please find details below. I created a table with 1 million rows to test it:
Create test data:
IF OBJECT_ID('Product','U') IS NOT NULL
DROP TABLE [Product]
IF OBJECT_ID('Colors','U') IS NOT NULL
DROP TABLE [Colors]
CREATE TABLE [Product]
(
[ProductId] int IDENTITY(1,1) NOT NULL,
[Name] nvarchar(100) NOT NULL,
[Color] nvarchar(100) NOT NULL,
[ListPrice] money NOT NULL,
[ModifiedDate] datetime NOT NULL
CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED ([ProductID] ASC)
)
CREATE TABLE Colors(Color nvarchar(100))
INSERT INTO Colors(Color) VALUES
('Amber'),('Beige'),('Black'),('Blue'),('Brown'),('Dark blue'),('Dark brown'),
('Dark coral'),('Gold'),('Green'),('Indigo'),('Ivory'),('Light blue'),
('Light brown'),('Pale blue'),('Pale brown'),('Pastel blue'),('Pastel brown'),
('Pastel green'),('Red'),('Rose'),('White'),('Yellow')
INSERT INTO Product
(
Name,
Color,
ListPrice,
ModifiedDate
)
SELECT TOP 1000000
A.name + '-' + CAST( ROW_NUMBER() over(order by NEWID()) as nvarchar(100)),
B.Color,
ABS(CHECKSUM(NEWID())) % 5000,
Cast('2000-01-01' as datetime) + ABS(CHECKSUM(NEWID())) % 3000
FROM master.sys.columns A
CROSS JOIN Colors B
CROSS JOIN master.sys.columns C
CREATE NONCLUSTERED INDEX Product_X1 ON Product(Name)
CREATE NONCLUSTERED INDEX Product_X2 ON Product(ModifiedDate)
CREATE NONCLUSTERED INDEX Product_X3 ON Product(Color)
GO
Run the queries (Your's are Query 7 & 8)
DECLARE @Name nvarchar(100)
SET @Name = 'Hello'
-- Query 1
SELECT *
FROM [Product]
WHERE (@Name IS NULL OR [Name] = @Name)
-- Query 2
SELECT *
FROM [Product]
WHERE ([Name] = @Name OR @Name IS NULL)
-- Query 3
SELECT *
FROM [Product]
WHERE [Name] = Coalesce(@Name, [Name])
-- Query 4
SELECT *
FROM [Product]
WHERE [Name] = CASE WHEN @Name IS NULL THEN [Name] ELSE @Name END
-- Query 5
SELECT *
FROM [Product]
WHERE 1 = (CASE WHEN @Name IS NULL THEN 1
WHEN @Name IS NOT NULL AND [Name] = @Name THEN 1
ELSE 0
END)
-- Query 6
SELECT *
FROM [Product]
WHERE [Name] = ISNULL(@Name, [Name])
DECLARE @Color nvarchar(100),
@ListPriceMax money,
@ListPriceMin money,
@ModifiedDateMin datetime,
@ModifiedDateMax datetime
SET @Color = NULL
SET @ListPriceMax = NULL
SET @ListPriceMin = NULL
SET @ModifiedDateMin = NULL
SET @ModifiedDateMax = NULL
-- Query 7
;
WITH Filtering AS
(
SELECT ProductId
FROM [Product]
WHERE @Name IS NOT NULL AND @Name = Name
UNION ALL
SELECT ProductId
FROM [Product]
WHERE @Color IS NOT NULL AND @Color = Color
UNION ALL
SELECT ProductId
FROM [Product]
WHERE NOT (@ListPriceMin IS NULL AND @ListPriceMax IS NULL)
AND (@ListPriceMin IS NOT NULL OR ListPrice >= @ListPriceMin)
AND (@ListPriceMax IS NOT NULL OR ListPrice <= @ListPriceMax)
)
SELECT *
FROM [Product]
WHERE ProductID IN (SELECT ProductID
FROM Filtering)
AND [Name] = Coalesce(@Name, [Name])
AND [Color] LIKE Coalesce(@Color, [Color] )
AND [ListPrice] >= Coalesce(@ListPriceMin, [ListPrice])
AND [ListPrice] <= Coalesce(@ListPriceMax, [ListPrice])
AND ModifiedDate >= Coalesce(@ModifiedDateMin, ModifiedDate)
AND ModifiedDate <= Coalesce(@ModifiedDateMax, ModifiedDate)
-- Query 8
;
WITH Filtering AS
(
SELECT ProductId
FROM [Product]
WHERE @Name IS NOT NULL AND @Name = Name
)
SELECT *
FROM [Product]
WHERE ProductID IN (SELECT ProductID
FROM Filtering)
AND [Name] = Coalesce(@Name, [Name])
-- Query 9
SELECT *
FROM [Product]
WHERE [Name] = @Name
And here is a picture of all the execution plans:
Your query without any other columns included in it (Query 8) is as good as the dynamic sql but as soon as some of the other columns are added (Query 7) it becomes the worst performing out of all of them. The dynamic sql (Query 9) would only include WHERE [Name] = @Name if the other columns are null but your's would have all the columns listed as it is not dynamic.