Does SQL Server create a default non-clustered index

Question

Ya, this is a duplicate of this . But I just need to clarify this article from Pinal Dave , which says the following:

Scenario 4: The default primary key for a clustered index with a different default index is a non-clustered index

In this case, we will create two indexes in both tables, but we will not indicate the type of index in the columns. When we check as a result, we notice that the primary key automatically defaults to the clustered index and another column as a nonclustered index.

-- Case 4 Primary Key and Defaults USE TempDB GO -- Create table CREATE TABLE TestTable (ID INT NOT NULL PRIMARY KEY, Col1 INT NOT NULL UNIQUE) GO -- Check Indexes SELECT OBJECT_NAME(OBJECT_ID) TableObject, [name] IndexName, [Type_Desc] FROM sys.indexes WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable' GO -- Clean up DROP TABLE TestTable GO 

Answer 1

The only indexes that are automatically created are:

• a clustered index on your main key (unless you specify otherwise), if you define your primary key, which is non-clustered, then a non-clustered index will be created)

• a unique non-clustered index when you apply a UNIQUE CONSTRAINT to a column (or set of columns)

Answer 2

Just to state this - the result of the Pinal Dave example - these are indexes similar to the following:

 TestTable PK__TestTabl__3214EC2703317E3D CLUSTERED TestTable UQ__TestTabl__A259EE55060DEAE8 NONCLUSTERED 

What can be explained as follows:

PK Clustered

If the table is created using the primary key, then this is the Clustered Table , and the default clustered index for the primary key , unless otherwise specified. (Tables without Heaps clustered index)

UQ Nonclustered

Non- SQL SQL usually creates any non-clustered indexes in the default table.

However, as Mark pointed out, since the table has a column with a UNIQUE , (Col1 INT NOT NULL UNIQUE) , MS SQL implements the constraint as a unique, non-clustered index in this column.

See also: Is a unique Sql Server key an index?