By Default, a nonclustered index gets created if keyword ‘clustered’ is not specified. Here in the above example, we have created a nonclustered index ‘IX_Employee_EmpID’ on EmpID column of Employee table.
Nonclustered index also gets created
– When we create a PRIMARY KEY on a table where a clustered index has already been defined. If the clustered index has not been defined, then clustered index gets created.
– When we create a Unique contraint.
Non-clustered index can be defined on
– Queries that return few rows.
– Queries that return small ranges of data.
– Columns that are specified in both WHERE and ORDER BY Clause of the query.
– Columns to be indexed are very wide.
NonClustered Index are not good choice on
– Columns that are not at least 95% unique. The Query optimizer may not use it.
– Table that does not have any clustered index. Make sure to define a clustered index before defining a nonclustered index. If you don’t, when the clustered index is added then all of the non-clustered index will also get rebuilt automatically.