SQL Server Query Optimization: Where (Col = @Col or @Col = Null)

Question

You don’t know where to start - I’m not sure that the problem is that I am deceiving the query optimizer, or if it is something inherent for how indexes work when starting zeros.

One of the coding rules that I follow is the code of stored procedures, such as:

declare procedure SomeProc
  @ID int = null
as
  select
    st.ID,st.Col1,st.Col2
  from
    SomeTable st
  where
    (st.ID = @ID or @ID is null) --works, but very slow (relatively)

Not very useful in this simple case, of course, but useful in other scenarios when you want the stored proc to act either across the entire table or along rows that meet certain criteria. However, it is rather slow when used in large tables ... about 3-5 times slower than if I replaced the where clause:

where
    st.ID = @ID --3-5x faster than first example

, -1 , "" WHERE :

declare procedure SomeProc
  @ID int = -1
as
  select
    st.ID,st.Col1,st.Col2
  from
    SomeTable st
  where
    (st.ID = @ID or @ID=-1) --much better... but why?

, , , ? , . , , SQL Server, . , -1; , .

• SomeTable.ID
• ( , ) Sniffing ( Spoofing) SQL Server , "exec SomeProc" / proc, .. .

Answer 1

,

• sniffing

, , .

sniffing

... "NULL". , , -1 .

@ID = -1 NULL sniffing = , .

OPTIMIZE FOR UNKNOWN SQL Server 2008

OR

..

,

st.ID = ISNULL(@ID, st.ID)

, IF

IF @ID IS NULL
   SELECT ... FROM...
ELSE
   SELECT ... FROM... WHERE st.ID

UNION ALL .

() ISNULL ( )

alter procedure SomeProc
  @ID int = NULL
AS
declare @maskID int
select @maskID = @ID
...