自增长键列统计信息的处理方法

 -- Create a simple orders table
 CREATE TABLE Orders
 (
  OrderDate DATE NOT NULL,
  Col2 INT NOT NULL,
  Col3 INT NOT NULL
 )
 GO
 
 -- Create a Non-Unique Clustered Index on the table
 CREATE CLUSTERED INDEX idx_CI ON Orders(OrderDate)
 GO
 
 -- Insert 31465 rows from the AdventureWorks2008r2 database
 INSERT INTO Orders (OrderDate, Col2, Col3) SELECT OrderDate, CustomerID, TerritoryID FROM AdventureWorks2008R2.Sales.SalesOrderHeader
 GO
 
 -- Rebuild the Clustered Index, so that we get fresh statistics.
 -- The last value in the Histogram is 2008-07-31.
 ALTER INDEX idx_CI ON Orders REBUILD
 GO
 
 -- Insert 200 additional rows *after* the last step in the Histogram
 INSERT INTO Orders (OrderDate, Col2, Col3)
 VALUES ('20100101', 1, 1)
 GO 200

 -- => 1st update the Statistics on the table with a FULLSCAN
  UPDATE STATISTICS Orders WITH FULLSCAN
  GO
  
  -- Insert 200 additional rows *after* the last step in the Histogram
  INSERT INTO Orders (OrderDate, Col2, Col3)
 VALUES ('20100201', 1, 1)
  GO 200
  
 -- => 2nd update the Statistics on the table with a FULLSCAN
 UPDATE STATISTICS Orders WITH FULLSCAN
 GO
 
 -- Insert 200 additional rows *after* the last step in the Histogram
 INSERT INTO Orders (OrderDate, Col2, Col3)
 VALUES ('20100301', 1, 1)
 GO 200
 
 -- => 3rd update the Statistics on the table with a FULLSCAN
 UPDATE STATISTICS Orders WITH FULLSCAN
 GO

-- Now we query the newly inserted range which is currently not present in the Histogram.
-- With Trace Flag 2389, the Query Optimizer uses the Density Vector to make the Cardinality Estimation.
SELECT * FROM Orders
WHERE OrderDate = '20100401'
OPTION (RECOMPILE, QUERYTRACEON 2389)
GO

1 -- With the new Cardinality Estimator SQL Server estimates 28.4516 rows at the Clustered Index Seek operator.
2 SELECT * FROM Orders
3 WHERE OrderDate = '20100401'
4 OPTION (RECOMPILE, QUERYTRACEON 2312)
5 GO