SQL - How to write a SELECT statement with conditional WHERE/smart logic?-CodePudding

Lets assume I have a table, where two columns X and Y that are INTs, and other columns exist.

X	Y	OtherValue
1	1	A
1	2	B
2	1	B
3	2	C

I'm trying to get thee rows based X and Y, where the request may want a specific X or Y, or both. Otherwise, it may want to get all rows (i.e. no X or Y given).

SELECT OtherValue
FROM Table
WHERE X = @x
AND Y=@y

The closest suggestion I could find suggest something like WHERE X=@x OR @x IS NULL, which makes sense to me, but mentioned that this bad performance antipattern.

Is there another, better way I should be solving this solution?

CodePudding user response：

Yes, using an OR operator can throw off the SQL Server optimizer, and often means you'll end up with a bad plan. For example, when pulling data from the Table table, SQL needs to pick one index, and only one, that it'll use to pull all data from that one table. Assuming a NONCLUSTERED index exists on X and Y, respectively, those options could look something like this.

CLUSTERED SCAN: Table scan for all rows where X or Y are matched.
SEEK on NONCLUSTERED INDEX X (But this won't provide details on Y)
SEEK on NONCLUSTERED INDEX Y (But this won't provide details on X)

The end result is usually a SCAN of the the CLUSTERED INDEX instead. Going this path, SQL Server knows it can get X and Y in one INDEX SCAN, vs multiple followup steps required when using either of the available SEEKS.

Now, if you have individual INDEXES on each of those columns, you can write a query for each unique combination of input. This ends up being faster because each individual query uses the index that works best for its result set.

The result would look something like this.

SELECT OtherValue
FROM Table
WHERE X = @x
    AND @x IS NOT NULL
    AND @y IS NULL

UNION ALL

SELECT OtherValue
FROM Table
WHERE Y=@y
    AND @y IS NOT NULL
    AND @x IS NULL

UNION ALL

SELECT OtherValue
FROM Table
WHERE X = @x AND @x IS NOT NULL
    AND Y = @y AND @y IS NOT NULL

UNION ALL

SELECT OtherValue
FROM Table
WHERE @x IS NULL
    AND @y IS NULL

I talk more about the implication of using the OR in UPDATES on my blog. But the same logic holds true for SELECT statements.