Does SQL Server creates Non clustered index by default
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 CONSTRAINTto 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 CLUSTEREDTestTable UQ__TestTabl__A259EE55060DEAE8 NONCLUSTEREDWhich 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.