What are the differences between
CREATE INDEX and
CREATE STATISTICS and when should I use each?
Indexes store actual data (data pages or index pages depending on the type of index we are talking about), and Statistics store data distribution. Therefore,
CREATE INDEX will be the DDL to create an index (clustered, nonclustered, etc.) and
CREATE STATISTICS is the DDL to create the statistics on columns within the table.
I recommend you read about these aspects of relational data. Below are a couple of beginner, introductory articles. These are very broad topics, and therefore the information on them can go very wide and very deep. Read up on the general idea of them below, and ask more specific questions when they arise.
BOL reference on Table and Index Organization
BOL reference on Clustered Index Structure
BOL reference on Nonclustered Index Structures
SQL Server Central on the Introduction to Indexes
BOL reference on Statistics
Here is a working example to see these two parts in action (commented to explain):
use testdb; go create table MyTable1 ( id int identity(1, 1) not null, my_int_col int not null ); go insert into MyTable1(my_int_col) values(1); go 100 -- this statement will create a clustered index -- on MyTable1. The index key is the id field -- but due to the nature of a clustered index -- it will contain all of the table data create clustered index MyTable1_CI on MyTable1(id); go -- by default, SQL Server will create a statistics -- on this index. Here is proof. We see a stat created -- with the name of the index, and the consisting stat -- column of the index key column select s.name as stats_name, c.name as column_name from sys.stats s inner join sys.stats_columns sc on s.object_id = sc.object_id and s.stats_id = sc.stats_id inner join sys.columns c on sc.object_id = c.object_id and sc.column_id = c.column_id where s.object_id = object_id('MyTable1'); -- here is a standalone statistics on a single column create statistics MyTable1_MyIntCol on MyTable1(my_int_col); go -- now look at the statistics that exist on the table. -- we have the additional statistics that's not necessarily -- corresponding to an index select s.name as stats_name, c.name as column_name from sys.stats s inner join sys.stats_columns sc on s.object_id = sc.object_id and s.stats_id = sc.stats_id inner join sys.columns c on sc.object_id = c.object_id and sc.column_id = c.column_id where s.object_id = object_id('MyTable1'); -- what is a stat look like? run DBCC SHOW_STATISTICS -- to get a better idea of what is stored dbcc show_statistics('MyTable1', 'MyTable1_CI'); go
Here is what a test sample of statistics can look like:
Notice that Statistics are the containment of the data distribution. They help SQL Server determine an optimal plan. A good example of this is, imagine you are going to life a heavy object. If you knew how much that weight because there was a weight marking on it, you'd determine the best way to lift and with what muscles. That's sort of what SQL Server does with statistics.
-- create a nonclustered index -- with the key column as my_int_col create index IX_MyTable1_MyIntCol on MyTable1(my_int_col); go -- let's look at this index select object_name(object_id) as object_name, name as index_name, index_id, type_desc, is_unique, fill_factor from sys.indexes where name = 'IX_MyTable1_MyIntCol'; -- now let's see some physical aspects -- of this particular index -- (I retrieved index_id from the above query) select * from sys.dm_db_index_physical_stats ( db_id('TestDB'), object_id('MyTable1'), 4, null, 'detailed' );
We can see from the example above that the index actually contains data (depending on the type of index, leaf pages will be different).
This post has only shown a very very very brief overview of these two large aspects SQL Server. Both of these could take up chapters, and books. Read some of the references, and then you will have a better grasp.