/*
Best Practices for Managing Statistics
The goal for using statistics in SQL Server is to allow the optimizer to get good cardinality estimates so it can find good query plans, while at the same time keeping any overhead or delay associated with gathering statistics reasonable. Below we list best practices for managing statistics in SQL Server, with the most important ones first.

1.Use auto create and auto update statistics
2.If needed, selectively use FULLSCAN statistics
3.Avoid use of local variables in queries
4.Consider limiting use of multi-statement TVFs and table variables
5.Non-foldable expressions and built-in scalar functions may result in guesses
6.Use multi-column statistics when queries have multi-column conditions
7.Be alert for any situation where SQL Server falls back to guessing selectivity
  a.Missing statistics
  b.Use of local variables in the query condition 
  c.Non-constant-foldable expressions in the query condition (e.g., T.x+1 = 0, suser_sname() = T.UserName)
  d.Complex expressions such as "Price + Tax > 100" or "Price * (1+TaxRate) > 100"
8.Avoid modification of stored procedure parameters in an SP prior to use in a query
9.Consider more frequent statistics gathering for ascending keys
10.Use asynchronous statistics update if synchronous update causes undesired delay

11. Use parameters or literals(?!) in the query instead of local variables, and the optimizer typically will be able to pick a better query plan.
*/


-- Use Parameters or Literals for Query Inputs:
-- If you use a local variable in a query predicate instead of a parameter or literal, the optimizer is unable to determine the value for the input.

-- estimates 9439.5 (1/3 of the table)
-- chooses merge join
declare @StartOrderDate datetime
set @StartOrderDate = '20040731'
select * from Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= @StartOrderDate


-- same query, caldinality 40
-- nested loop
SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= '20040731'

-- using sp_executesql uses the correct plan
EXEC sp_executesql
	N'SELECT * from Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
      WHERE h.SalesOrderID = d.SalesOrderId
      AND h.OrderDate >= @StartOrderDate',
	N'@StartOrderDate datetime',
	'20040731'

CREATE PROCEDURE GetOrders(@StartOrderDate datetime)
as
--declare @StartOrderDate datetime
--set @StartOrderDate = '20040731'
select * from Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= @StartOrderDate

execute GetOrders '20040731'

-- Even when local variables are used in a query, an estimate that is better than a guess is used in the case of equality predicates. Selectivity for conditions of the form "@local_variable = column_name" is estimated using the average value frequency from the histogram for column_name.

-- To eliminate the use of local variables, consider (1) rewriting the query to use literals instead of variables, (2) using sp_executesql with parameters that replace your use of local variables, or (3) using a stored procedure with parameters that replace your use of local variables. Dynamic SQL via EXEC may also be useful for eliminating local variables, but it typically will result in higher compilation overhead.

DROP PROCEDURE GetRecentSales
GO
CREATE PROCEDURE GetRecentSales (@date datetime) -- WITH RECOMPILE 
AS
BEGIN
	IF @date IS NULL
		SET @date = dateadd("mm",-3,(SELECT MAX(OrderDATE) 
							FROM Sales.SalesOrderHeader))
	SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
	WHERE h.SalesOrderID = d.SalesOrderID
	AND h.OrderDate > @date
END

EXECUTE GetRecentSales NULL

-- This SP, if called with NULL, will have the final SELECT statement optimized for @date = NULL. However, at run time, the date is not NULL, but three months before the latest OrderDate.  The actual number of SalesOrderHeader rows that qualify is 5,736. The optimizer chooses a nested loop join for the query when NULL is passed to GetRecentSales, whereas the optimal plan contains a merge join.

CREATE PROCEDURE GetRecentSales (@date datetime) WITH RECOMPILE AS
BEGIN
	IF @date IS NULL
		SET @date = dateadd("mm",-3,(SELECT MAX(OrderDATE) 
							FROM Sales.SalesOrderHeader))
	SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
	WHERE h.SalesOrderID = d.SalesOrderID
	AND h.OrderDate > @date
END
go

SET STATISTICS PROFILE ON
GO

execute GetRecentSales NULL WITH RECOMPILE
EXECUTE GetRecentSales '20040430' WITH RECOMPILE

EXECUTE GetRecentSales '20060810' WITH RECOMPILE
go

SET STATISTICS PROFILE OFF
GO

CREATE PROCEDURE GetRecentSales2 (@date datetime)  AS
BEGIN
	IF @date IS NULL
		SET @date = dateadd("mm",-3,(SELECT MAX(OrderDATE) 		FROM Sales.SalesOrderHeader))
	EXEC GetRecentSalesHelper @date
END
go
DROP PROCEDURE GetRecentSalesHelper
CREATE PROCEDURE GetRecentSalesHelper (@date datetime) -- WITH RECOMPILE 
AS
BEGIN
	SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
	WHERE h.SalesOrderID = d.SalesOrderID
	AND h.OrderDate > @date -- @date is unchanged from compile time,
					-- so a good plan is obtained.
END
go

execute GetRecentSales NULL
execute GetRecentSales2 NULL