---------------------------------------------------------------------
-- Filtered Indexes and Statistics
---------------------------------------------------------------------

USE AdventureWorks2008;

CREATE NONCLUSTERED INDEX idx_currate_notnull
  ON Sales.SalesOrderHeader(CurrencyRateID)
  WHERE CurrencyRateID IS NOT NULL;

-- Implicit Exclusion of NULLs
SELECT *
FROM Sales.SalesOrderHeader
WHERE CurrencyRateID = 4;

CREATE NONCLUSTERED INDEX idx_freight_5000_or_more
  ON Sales.SalesOrderHeader(Freight)
  WHERE Freight >= $5000.00;

-- Subintervals
SELECT *
FROM Sales.SalesOrderHeader
WHERE Freight BETWEEN $5500.00 AND $6000.00;

CREATE NONCLUSTERED INDEX idx_territory5_orderdate
  ON Sales.SalesOrderHeader(OrderDate)
  INCLUDE(SalesOrderID, CustomerID, TotalDue)
  WHERE TerritoryID = 5;

-- Coverage
SELECT SalesOrderID, CustomerID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE TerritoryID = 5;

-- Coverage Plus Range Scan
SELECT SalesOrderID, CustomerID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE TerritoryID = 5
  AND OrderDate >= '20040101';

-- Filtered Statistics
CREATE STATISTICS stats_territory4_orderdate
  ON Sales.SalesOrderHeader(OrderDate)
  WHERE TerritoryID = 4;
GO

-- Unique with Multiple NULLs
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;

CREATE TABLE dbo.T1(col1 INT NULL, col2 VARCHAR(10) NOT NULL);
GO

CREATE UNIQUE NONCLUSTERED INDEX idx_col1_notnull
  ON dbo.T1(col1)
  WHERE col1 IS NOT NULL;
GO
  
INSERT INTO dbo.T1(col1, col2)
  VALUES(1, 'a');

-- fails
INSERT INTO dbo.T1(col1, col2)
  VALUES(1, 'a');

/*
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.T1' with unique index 'idx_col1_notnull'.
The statement has been terminated.
*/

INSERT INTO dbo.T1(col1, col2)
  VALUES(NULL, 'a');
INSERT INTO dbo.T1(col1, col2)
  VALUES(NULL, 'a');

-- Cleanup
USE AdventureWorks2008;
DROP INDEX Sales.SalesOrderHeader.idx_currate_notnull;
DROP INDEX Sales.SalesOrderHeader.idx_freight_5000_or_more;
DROP INDEX Sales.SalesOrderHeader.idx_territory5_orderdate;
DROP STATISTICS Sales.SalesOrderHeader.stats_territory4_orderdate;
DROP TABLE dbo.T1;