Ya, it is a duplicate of this. But I just needs a clarification on this article by Pinal Dave, which says the following:
Scenario 4: Primary Key defaults to Clustered Index with other index defaults to Non-clustered index
In this case we will create two indexes on the both the tables but we will not specify the type of the index on the columns. When we check the results we will notice that Primary Key is automatically defaulted to Clustered Index and another column as a Non-clustered 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
The only indexes that get created automatically:
the clustered index on your primary key (unless you specify otherwise - if you define your primary key to be nonclustered, then a nonclustered index will be created)
a unique nonclustered index when you apply a UNIQUE CONSTRAINT
to a column (or set of columns)