SQL SERVER – SQL Server Statistics Name and Index Creation

Sometimes something very small or a common error which we observe in daily life teaches us new things. SQL Server Expert Sandip (winner of Joes 2 Pros Contests) has come across similar experience. Sandip has written a guest post on an error he faced in his daily work. Sandip is working for QSI Healthcare as an Associate Technical Specialist and have more than 5 years of total experience. Let’s see SQL Server Statistics Name and Index Creation here.


Once I faced following error when I was working on performance tuning project and attempt to create an Index.

Mug 1913, Level 16, State 1, Line 1
The operation failed because an index or statistics with name ‘Ix_Table1_1’ already exists on table ‘Table1’.

The immediate reaction to the error was that I might have created that index earlier and when I researched it further I found the same as the index was indeed created two times. This totally makes sense. This can happen due to many reasons, for example, if the user is careless and executes the same code two times as well, when he attempts to create index without checking if there was index already on the object. However, when I paid attention to the details of the error, I realize that error message also talks about statistics along with the index. I got curious if the same would happen if I attempt to create indexes with the same name as statistics already created. There are a few other questions also prompted in my mind. I decided to do a small demonstration of the subject and build following demonstration script.

The goal of my experiment is to find out the relation between statistics and the index. Statistics are one of the important input parameters for the optimizer during query optimization process. If the query is nontrivial then only optimizer uses stats to perform a cost based optimization to select a plan.

Now let’s find out the relationship between index and statistics. We will do the experiment in two parts. i) Creating Index ii) Creating Statistics

We will be using the following T-SQL script for our example.

IF (OBJECT_ID('Table1') IS NOT NULL)
DROP TABLE Table1
GO
CREATE TABLE Table1
(Col1 INT NOT NULL,
Col2 VARCHAR(20) NOT NULL)
GO

We will be using following two queries to check if there are any index or statistics on our sample table Table1.

-- Details of Index
SELECT OBJECT_NAME(OBJECT_ID) AS TableName, Name AS IndexName, type_desc
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'table1'
GO
-- Details of Statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, Name AS StatisticsName
FROM sys.stats
WHERE OBJECT_NAME(OBJECT_ID) = 'table1'
GO

When I ran above two scripts on the table right after it was created it did not give us any result which was expected.

SQL SERVER - SQL Server Statistics Name and Index Creation statindex1

Now let us begin our test.

1) Create an index on the table

Create following index on the table.

CREATE NONCLUSTERED INDEX Ix_Table1_1 ON Table1(Col1)
GO

Now let us use above two scripts and see their results.

SQL SERVER - SQL Server Statistics Name and Index Creation statindex2

We can see that when we created index at the same time it created statistics also with the same name.

Before continuing to next set of demo – drop the table using following script and re-create the table using a script provided at the beginning of the table.

DROP TABLE table1
GO

2) Create a statistic on the table

Create following stats on the table.

CREATE STATISTICS Ix_table1_1 ON Table1 (Col1)
GO

Now let us use above two scripts and see their results.

SQL SERVER - SQL Server Statistics Name and Index Creation statindex3

We can see that when we created statistics Index is not created. The behavior of this experiment is different from the earlier experiment.

Clean up the table setup using the following script:

DROP TABLE table1
GO

Above two experiments teach us very valuable lesson that when we create indexes, SQL Server generates the index and statistics (with the same name as the index name) together. Now, due to the reason if we have already had statistics with the same name but not the index, it is quite possible that we will face the error to create the index even though there is no index with the same name.

A Quick Check – Statistics

To validate that if we create statistics first and then index after that with the same name, it will throw an error let us run following script in SSMS. Make sure to drop the table and clean up our sample table at the end of the experiment.

-- Create sample table
CREATE TABLE TestTable
(Col1 INT NOT NULL,
Col2 VARCHAR(20) NOT NULL)
GO
-- Create Statistics
CREATE STATISTICS IX_TestTable_1 ON TestTable (Col1)
GO
-- Create Index
CREATE NONCLUSTERED INDEX IX_TestTable_1 ON TestTable(Col1)
GO
-- Check error
/*Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics with name 'IX_TestTable_1' already exists on table 'TestTable'.
*/
-- Clean up
DROP TABLE TestTable
GO

While creating index it will throw the following error as statistics with the same name is already created.

SQL SERVER - SQL Server Statistics Name and Index Creation statindex4

In simple words – when we create index the name of the index should be different from any of the existing indexes.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – NTFS File System Performance for SQL Server
Next Post
SQL SERVER – 3 Online SQL Courses at Pluralsight and Free Learning Resources

Related Posts

14 Comments. Leave new

  • Excellent article by Sandip Pani. thanks pinal for sharing such an article to public..

    I appreciate your efforts and keep posting like these guest articles and encouraging the hidden talents of many people.

    Thanks,
    Prasad

    Reply
  • I think we should not only build statistics.

    Reply
  • SQL Server keeps track of user created statistics through a bit in sys.stats i.e. user_created. That’s why while creating a new index, to avoid this error, we must add following IF EXISTS() code, before actual index creation code.

    IF EXISTS ( SELECT *
    FROM sys.indexes
    WHERE name = N’IX_TestTable_1′
    AND object_id = OBJECT_ID(N'[dbo].[TestTable]’) )
    DROP INDEX [dbo].[TestTable].[IX_TestTable_1]
    GO
    IF EXISTS ( SELECT *
    FROM sys.stats
    WHERE name = N’IX_TestTable_1′
    AND object_id = OBJECT_ID(N'[dbo].[TestTable]’)
    AND user_created = 1 )
    DROP STATISTICS [dbo].[TestTable].[IX_TestTable_1]
    GO

    CREATE NONCLUSTERED INDEX IX_TestTable_1 ON TestTable(Col1)

    One must keep in mind that, only user created stats can be dropped, so if we just check existence of stats through name, it will return TRUE, but when it will try to drop that statistics, it will give following error.
    Cannot DROP the index ‘[dbo].[TestTable].[IX_TestTable_1]’ because it is not a statistics collection.

    Reply
  • Pinal, Please remove “SELECT 1”

    that was add to test code

    Reply
  • Indeed Insightful.

    Reply
  • Real good post……………..

    Reply
  • Very useful!

    Reply
  • very useful:) Thanks

    Reply
  • Good point! It’s easy to miss this check while creating indexes.

    Reply
  • I find this very useful in understanding about statistics

    Reply
  • While creating the index, are the statistics created first and later used for creation of an index?

    I know statistics are used by index (post index creation) while query execution but the debate in my company is that the statistics are created first and later used for index creation and hence they are crucial for index creation. Which one is correct?

    Reply

Leave a Reply

Menu