Programming in MS SQL Server 2012 |
Nonclustered IndexSQL Server implicitly creates a unique Nonclustered Index, when creates table with Unique constraint. The index column(s) are equal to constraint column(s) and the index name is equal to constraint name. 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 following example describes to create a Nonclustered Index on Employee table. This index helps to optimize join query while map child (Employee) table with parent (Department) table in the WHERE clause expression like Employee.Dept_ID = Department.Dept_ID table objects. --
Example 149 -- USE EBusiness
GO --
Create a new Department Table -- CREATE TABLE dbo.Department ( Dept_ID int Primary Key, Dept_Name varchar(50) Not Null ) GO --
Create a new Employee Table -- CREATE TABLE dbo.Employee ( Emp_ID int Primary Key, Emp_Name varchar(50) Not Null, DOJ datetime Default GetDate(), Salary numeric(7,2) Check (Salary > 1000 and Salary < 5000), Phone varchar(50), Dept_ID int References Department(Dept_ID) On Delete Cascade ) GO --
Find an existing index on IX_Employee and delete it if found -- IF EXISTS (SELECT name FROM sys.indexes
WHERE
name = N'IX_Employee')
DROP INDEX IX_Employee ON dbo.Employee GO --
Create Nonclustered Index on Employee Table -- CREATE NONCLUSTERED
INDEX IX_Employee_Nonclustered
ON dbo.Employee(Dept_ID) GO |
* * * * *