In the Q&A "Real vs. Auto-Created Indexes" (May 2003, InstantDoc ID 38441), you discussed auto-created statistics and how these statistics often identify columns on which you should create a real index. I have several columns that have both auto-created statistics and real indexes on them. Why did this happen, and should I eliminate the redundant auto-created statistics?
By default, SQL Server automatically creates statistics on columns if the optimizer decides at runtime that the information will be helpful in processing the query more efficiently. However, SQL Server never drops an auto-created statistic, even when you create a real index on the column. Here's an example that illustrates this behavior. The query
creates a table called NewOrders that you can use to test the interaction of statistics and indexes. Now run the following query, which makes SQL Server create a statistic on the OrderId column:
By running the following query, you can verify that SQL Server created a new statistic called something like _WA_Sys_OrderID_49C3F6B7 (SQL Server dynamically generates the suffix 49C3F6B7, so the suffix will be different on your machine):
Now, create an index on the OrderId column, run the query again, and check sysindexes' contents:
The statistic still exists, even though you created a real index. This statistic is now redundant because SQL Server can retrieve its information from the real index on the same column. But because SQL Server doesn't check for redundant statistics, it won't automatically drop this statistic. However, you can use the DROP STATISTICS command to drop the statistic manually.
Maintaining statistics usually doesn't involve a significant performance overhead, and statistics don't take up much space. However, small inefficiencies can add up quickly when your tables get large, so I recommend that you delete unnecessary auto-created statistics when you create a real index on a column. SQL Server won't do it for you.