SQL Server Does the order of criteria in the WHERE statement matter at all?

s4chpxco 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(122)

I have the following 2 statements:

declare @ID_Admin INT = 0, ID_Manager INT = 0
select *
from cases c
where (c.ID_Admin = @ID_Admin OR @ID_Admin = 0)
   and (c.ID_Manager = @ID_Manager OR @ID_Manager = 0)

AND

declare @ID_Admin INT = 0, ID_Manager INT = 0
select *
from cases c
where (@ID_Admin = 0 OR c.ID_Admin = @ID_Admin)
   and (@ID_Manager = 0 OR c.ID_Manager = @ID_Manager)

The intent is to tell the database that it doesn't need to filter on ID_Admin if the passed in parameter is 0 (same with ID_Manager). In one case, the first comparison is against the table ( c.ID_Admin = @ID_Admin ), following by comparison to zero ( @ID_Admin = 0 ). In the second case, the criteria are reversed.

Does the order of criteria in the WHERE statement matter at all here? In other words, is SQL Server smart enough to say that it should do @ID_CaseAdmin = 0 comparison before trying to filter rows.

I ran both queries and it produced identical execution plans. However, this is a simple contrived example. So can I make a blanket statement that SQL Server will be smart enough in this type of situation not to start filtering actual rows.

sql-server

来源：https://stackoverflow.com/questions/76640448/does-the-order-of-criteria-in-the-where-statement-matter-at-all

2条答案

按热度按时间

kxkpmulp1#

Does the order of criteria in the WHERE statement matter at all here?

No. But SQL Server will evaluate the predicate for every row even when @ID_Manager is null unless you include OPTION RECOMPILE.

赞(0）回复(0）举报 2023-08-02

t0ybt7op2#

This type of query is called a Kitchen Sink Query .

While you can use OPTION (RECOMPILE) to mitigate the impact of a poor plan, this will cause recompilation on every run.

Instead you can construct a dynamic query

declare @ID_Admin INT = 0, ID_Manager INT = 0

DECLARE @sql nvarchar(max) = '
select *
from cases c
where 1=1
';

IF @ID_Admin <> 0
    SET @sql += ' AND c.ID_Admin = @ID_Admin
';

IF @ID_Manager <> 0
    SET @sql += ' AND c.ID_Manager = @ID_Manager
';

EXEC sp_executesql @sql,
  N'@ID_Admin INT, @ID_Manager INT',
    @ID_Admin = @ID_Admin,
    @ID_Manager = @ID_Manager;

Note the use of sp_executesql to properly parameterize the dynamic query.

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Does the order of criteria in the WHERE statement matter at all?

2条答案

相关问题

热门标签

最新问答