Pro and Cons of Parameter Sniffing

Look at the following 3 scripts:

Transact-SQL

-- 1. Hardcoded SELECT * FROM Sales.Orders WHERE DateAndTime < '19900101' -- 2. Variable DECLARE @FilterDate DATETIME = '19900101' SELECT * FROM Sales.Orders WHERE DateAndTime < @FilterDate -- 3. Stored Procedure CREATE PROCEDURE Sales.GetOrders @FilterDate DATETIME2(0) AS SELECT * FROM Sales.Orders WHERE DateAndTime < @FilterDate GO EXECUTE Sales.GetOrders '19900101'

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

-- 1. Hardcoded

SELECT

*

FROM

Sales.Orders

WHERE

DateAndTime < '19900101'

-- 2. Variable

DECLARE @FilterDate DATETIME = '19900101'

SELECT

*

FROM

Sales.Orders

WHERE