SQL Server Performance Forum – Threads Archive

DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS(Table.Index_Name) gives statistics. This is very different in these two cases as below. If the index is made and then the records are inserted in the table.
If records are inserted and then the index is made.
The statistics are always NULL in case 1 while in case 2 this gives comfortable analysis. Why this happens? Does case 1 lead to the conclusion that index if made before the insertion of records is not worth? I was told that if we insert the records after the creation of index this insertion is quick but why the statistics are null always?
Ashish Johri

Explain the case of 1 and 2 about indexes & queries. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

1. Table sales is an empty table and has 100 crore records. Index on column sales_id is made before insertion of records.
2. Same table same no of records but first the records are inserted and then the index is made on sales_id. DBCC SHOW_STATISTICS (Sales.Sales_ID_Index) in both the cases will give different results. Ashish Johri

You did that in both tables and after you run DBCC, whitout any user user of these tables?. If you have autostatistics on, then the statistics are created when the table is used, not before.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

If you create the index, statistics is updated with FULL SCAN sample option other wise it will use default value (10% or 20%)… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>