Clustered VS Non Clustered Index

Index is a database object which contains an entry of each record that appears in the Indexed column to provide faster access of data.  Indexes are used in a View or a Table. Knowing the concept of  Index in detail  is very crucial to database administrators, database developers, and anyone who likes to work with database. The proper use of Index makes your query and database perform faster. But it might hurt your query performance and add overhead to database if not used properly. With this article, you will walk away with learning the characteristics, Index types, SQL syntax, query consideration, column consideration and the key difference between Clustered and non Clustered Index.

Clustered Index: 

An Index that sort and store the data row in the table or view on their key values. You can have one Clustered Index per table as the data rows sort in only one order. A table with Clustered Index sorts the data based on the column where there is Clustered Index. The table with Clustered Indexed is also called Clustered table. A table without a Clustered Index is a heap table because it is not sorted in any order.  A Clustered Index is created while creating a Primary Key or can be created later after the table creation.

Properties:

1. Sorts and stores data rows in the table.
2. One Clustered Index per table
3. Every table should have Clustered Index
4. Clustered Index is created by default when creating a PK
5. Improved query performance
6. Rebuild and Reorganized can be performed to avoid table fragmentation.
7. View can have Clustered Index

SQL Syntax:

USE adventureworks2012; 
go 
-- Create a new table with three columns. 
CREATE TABLE employee 
  ( 
     empno    INT, 
     ename    VARCHAR(10), 
     job      VARCHAR(9), 
     mgr      INT NULL, 
     hiredate DATETIME, 
     sal      NUMERIC(7, 2), 
     comm     NUMERIC(7, 2) NULL, 
     dept     INT 
  ) 
go 
-- Create a clustered index called IX_Employee_Empno 
-- on the dbo.employee table using the empno column. 
CREATE CLUSTERED INDEX ix_employee_empno 
  ON dbo.employee (empno); 
go

Primary Key:

CREATE TABLE employee
  (
     empno    INT PRIMARY KEY,
     ename    VARCHAR(10),
     job      VARCHAR(9),
     mgr      INT NULL,
     hiredate DATETIME,
     sal      NUMERIC(7, 2),
     comm     NUMERIC(7, 2) NULL,
     dept     INT
  )

Index Verify:

USE adventureworks2012; 
go 
SELECT *
FROM   sys.indexes
WHERE  object_id = (SELECT object_id
                    FROM   sys.objects
                    WHERE  NAME = 'employee') -- Your table name 

Query Considerations:
Understanding how the table data will be accessed and used in any applications is very crucial while designing a database. This also help us pick the right Index type for a table. You should consider Clustered Index if your queries do the following:

1. Returns ranges of values by using Operators such as BETWEEN, >, >=, <, and <=.
2. Return large result sets
3. Use JOIN clauses
4. Use ORDER BY, or GROUP BY clauses. (data already sorted, db engine don't need to sort data which improved the performance )

Column Considerations:
Detail understanding of data and how the table will be accessed and used in an application will help us to pick the right column for a Clustered Index to maximize the performance. You should consider Clustered Index on a column that has:

1. Are UNIQUE or contain more distinct values
2. Are accessed sequentially
3. Column that is defined as IDENTITY
4. Used frequently to sort data retrieved from a table

Limitations:
Clustered Indexed is not a good choice if

1. Wide Keys or Composite key with wide column length
2. Column that under go frequent changes

Non Clustered Index: 

A regular Index which most database systems come with. The non Clustered Index contains key values where each key value entry has a pointer to the data work that contains the key value. You can have multiple non Clustered Index as you are not sorting data on that record like in Clustered Index. When you create a UNIQUE constraint, a unique non-clustered Index is created to enforce a UNIQUE constraint by default. An Index created as part of creating PK or UNIQUE constraint is given the same name as the constraint name. Clustered Index can be created on a column other than a primary key column if a non-clustered primary key constraint was specified.

Properties:

1. UNIQUE constraints creates non clustered Index by default
2. Can have more than 1 but less than 1000 non Clustered Index per table
3. Not mandatory for a table
4. Improves query performance
5. Rebuild and Reorganized can be performed to avoid table fragmentation.
6. View can have non-clustered Index

SQL Syntax:

USE adventureworks2012; 
go 
-- Create a new table with three columns. 
CREATE TABLE employee 
  ( 
     empno    INT, 
     ename    VARCHAR(10), 
     job      VARCHAR(9), 
     mgr      INT NULL, 
     hiredate DATETIME, 
     sal      NUMERIC(7, 2), 
     comm     NUMERIC(7, 2) NULL, 
     dept     INT 
  ) 
go 
-- Create a non clustered index called IX_Employee_Empno 
-- on the dbo.employee table using the empno column. 
CREATE NONCLUSTERED INDEX ix_employee_empno 
  ON dbo.employee (empno); 
go

Unique Constraint:

USE adventureworks2012; 
go 
--drop table dbo.employee;
-- Create a new table with three columns. 
CREATE TABLE employee 
  ( 
     empno    INT NOT NULL, 
     ename    VARCHAR(10), 
     job      VARCHAR(9), 
     mgr      INT NULL, 
     hiredate DATETIME, 
     sal      NUMERIC(7, 2), 
     comm     NUMERIC(7, 2) NULL, 
     dept     INT 
     CONSTRAINTS AK_employee_cmpno UNIUQE(empno)
  ) 
go 

Index Verify:

USE adventureworks2012; 
go 
SELECT *
FROM   sys.indexes
WHERE  object_id = (SELECT object_id
                    FROM   sys.objects
                    WHERE  NAME = 'employee') -- Your table name 

Query Considerations: Understanding how the table data will be accessed and used in any applications is very crucial while designing a database. This also help us pick the right Index type on a table. You should consider non Clustered Index if your queries do the following:

1. User JOIN or GROUP BY clauses 
2. Queries that do not return large result sets. 
3. Query that has filter conditions like WHERE clause 

Column Considerations: After understanding data and how the table will be accessed and used in an application you will need to pick the right column for your non Clustered Index to maximize the performance. Any columns where it does not meet the criteria for Clustered Index, you may consider using non Clustered Index.

Congratulations! You have successfully graduated from Index University! Now, It is your time to check your databases to see if any of the tables needed Index. If it does, you now should also able to tell pick the correct Index type for your table. Also, I recommend checking the existing Index to verify if they are designed correctly, it does not hurt to double check as it does not hurt the performance. Using Index inappropriately can kill your database therefore, learn Index, use Index well, and prolong your database health.

Source: https://msdn.microsoft.com/en-us/library/ms190457.aspx