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
You can see the clustered index name and the column on which the clustered index has been created by default.
A primary key index is created by default when a table is created with a primary key specified.
Generally, nonclustered indexes are created to improve the performance of frequently used queries not covered by the clustered index or to locate rows in a table without a clustered index (called a heap). You can create multiple nonclustered indexes on a table or indexed view.
The syntax for creating a non-clustered index is similar to the clustered index. Just use the keyword "NONCLUSTERED" instead of "CLUSTERED". The following syntax is to create a new non-clustered index on a table. For example, the following creates a non-clustered index on the Email column of the Employee table.
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)
Just to spell it out - the Result of Pinal Dave's example are indexes similar to the following:
TestTable PK__TestTabl__3214EC2703317E3D CLUSTERED
TestTable UQ__TestTabl__A259EE55060DEAE8 NONCLUSTERED
Which can be explained as follows:
PK Clustered
If a table is created with a primary key, then it is a Clustered Table, and the Clustered Index is defaulted to the Primary Key unless you specify otherwise. (Tables without a Clustered Index are Heaps)
UQ Nonclustered
SQL does not usually create any non-clustered indexes on a table by default.
However, as Marc has pointed out, because the table has a column with a UNIQUE
constraint, (Col1 INT NOT NULL UNIQUE)
, MS SQL implements the constraint as a unique, non-clustered index on that column.
See also: Is the Sql Server Unique Key also an Index?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With