Exclusively for you!! Trial version of Google Apps, Apply Coupon-CNHPGX6T6QRWGV!!

SQL : Clustered and NonClustered Index

sql-index
sql-index

Image courtesy of Grant Cochrane / FreeDigitalPhotos.net

SQL : Clustered and NonClustered Index

Welcome to CodeSpread!

We read about SQL indexes in our previous post, We will dive into the topic and learn the different types of indexing possible in SQL.

We have clustered and non-clustered index in SQL, let’s see the difference, their operation and advantages each one have.

We have a basic understanding that indexes are created on columns and organized into B-Tree structure for faster retrieval of data so both clustered and non-clustered index follows the same.

But the difference is,

Clustered :

1. Clustered indexes physically [actual bits on the disk] sorts and stores the data rows in the table based on the index values. means,the rows are stored physically on the disk in the same order as the index

2. A clustered index determines the order so there can be only one clustered index per table.

3. As a rule, this should be applied to the most common column which is always a part of WHERE clauses for queries.

4. Any data operation in the Table (like inserts, updates and deletes) reorders the index as clustered follows a physical sort and storing.

5. All the data operation will be slower. This is an overhead or a disadvantage.

6. As a best practice, the clustered index should be on a column which is unique and increasing such as an identity column.

7. Columns with the data type of TEXT, NTEXT and IMAGE are not included.

Non-Clustered :

1. Non-Clustered index follows logical ordering of data rows. means,  rows of data might not follow the way the index is ordered or sorted.

2. The index values will not point to the data rows but will have the address of the data rows.

3. Try to understand it more clearly, if we are looking for a value in non-clustered index then we will not get the data row directly like in the case of clustered, we will get the row locator and with the help of that we can get the physical pointer (corresponding clustered index value) of the data row.

4. 999 non-clustered indexes can be created.

5. Can contain upto 1023 included columns.

6. Columns with the data type of TEXT, NTEXT and IMAGE are not included.

What is the relation between keys and indexes?

When we assign PRIMARY KEY / UNIQUE key on table columns, Indexes are automatically created.

Example:

Let’s create a Employee table:


Create Table Employee
(
EmpId int,

EmpName varchar(100) ,

Salary int,
)

Right now, there is no index. Apply index on EmpId.


CREATE UNIQUE INDEX idxEmpId ON Employee (EmpId);

If there is already a clustered index on Table then a unique non-clustered index gets created else a unique clustered index gets created.

While defining the index, you can explicitly  mention non-clustered to create a non-clustered index but before that a clustered should exist.


CREATE NONCLUSTERED INDEX idxEmpId ON Employee (EmpId);

Caution:

Always find a balance between indexes because

1. More number of indexes are a spoilsport for inserts/updates.

2. Less number of indexes will hit the performance.

Author: hershey

A passion for knowledge drives me to do programming, A passion for programming drives me to create something different, A passion for creation drives me to spread the knowledge.

Share This Post On

0 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

More from CodeSpread:

  • SQL: Story of IndexesSQL: Story of IndexesI know many people know everything about this topic but as this is one of my favorite topics, I will make sure that I will give you some additional information or knowledge. My first thought abo...
  • IDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property: Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field. To achieve this, We have IDENTITY ...
  • SQL:Target a TriggerSQL:Target a TriggerI remember those days when I used to simply fall into the trap set by client requirements which could have been easily resolved by using a trigger. So I can definitely say, trigger is a life saver,...
  • SQL Bitwise operator can simplify DB designSQL Bitwise operator can simplify DB designFrankly speaking, I never tried SQL Bitwise operator but while doing DB design today for one of the projects, I came to know about this operator from one of my friend and I really liked it. To expl...
  • SQL:Target a SubQuerySQL:Target a SubQueryPeople ask me a lot about subquery so I thought let’s answer with a article touching a bit of everything about subquery. Before discussing SubQuery, there are few things which we should know lik...
  • Verify existence of SQL tables using Object_ID()Verify existence of SQL tables using Object_ID()Why Object_ID() ? Let’s create a SQL table “Employee” [code] CREATE TABLE Employee ( [EmployeeID] [int] IDENTITY(1,1) PRIMARY KEY, [EmployeeName] [varchar](50) NULL, [Salary] [int] NUL...
  • SQL fry: PIVOT and UNPIVOTSQL fry: PIVOT and UNPIVOTWhat is PIVOT? In our day-to-day SQL server coding, PIVOT and UNPIVOT are not very familiar words but still holds good if we understand them correctly and know when to use it.Web Definition of PIV...
  • SQL Tables,Temporary Tables,Table VariablesSQL Tables,Temporary Tables,Table VariablesSQL provides a variety of Tables. Lets summarize each one of them. Permanent Tables: Why do we require it? It is required for data persistence across the database. Once tables are created ...