Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

interview

home / developersection / interviews / what is cluster and non-cluster index in sql?
Ask Question
Abhishek Srivasatava
Abhishek Srivasatava

Employee

Message

1 Answers

Abhishek Srivasatava
Abhishek Srivasatava 27 Sep 2016
Report Abuse

Clustered

Cluster index is used to store the data in a sorted format in the table and viewed on the basis of their key values. Such table is called a cluster table. There can be only one cluster index per table. When we put any cluster in any index, then it will store the data in the binary search tree format. This data stored separately stored in the disc. A cluster contains the key and the data associated with it. Hence there is a very fast operation can be possible with the help of clustered index. This type of index is automatically created once we create primary key for table.

Non-clustered

Non-clustered indexed have the index key values which is having the value of the pointer to the data row that can contain the key value. This pointer is called as row locator. A table can have more than one index.  This type of index is automatically created once we create unique key for table.

How it is work?

Let’s suppose there is a book contains 1400 pages, and index contains 20 pages.

If you need to find the word, Index is already in sorted format. So word can easily find, let's it is on page 635.

You have opened the book randomly let page is 700, now you can easily understand 635 is less than 700.

2nd attempt is 350 then search element is in between this page.

And in this manner, you can easily find the searched element.

Difference between cluster and non-cluster:

A Cluster contains the key and the data associated with it.

Whereas non-cluster contains key and pointer to the data row associated with the key.

How to create Clustered and non cluster index with the help of programming?

This is the syntax to create Clustered and non-cluster index 

CREATE TABLE EE_EMPL_CAT1(
PRSN_INTN_ID  int NOT NULL primary key,
EMPL_STAT_CD char(20)  NOT NULL ,
NAME  char(10) DEFAULT 'XXXX',
REFERENCE_ID int UNIQUE ,
);

By this statement: REFERENCE_ID int UNIQUE  --------> Non cluster index will made.


By this statement: PRSN_INTN_ID  int NOT NULL primary key --------> Cluster index will made.

How to create Clustered and non-cluster index without programming?

Expand the table.

Right-click the index folder, selects New Index, and select Clustered Index….

In Index box, enter Index name.

Click Add, under Index key columns

Select table column to be added to the clustered index.

Click OK.

Under New Index dialog box, click OK.

2nd method (manual).

Right click on the table.

Select design.

select primary and unique key for the table.

Save the table

By using UNIQUE column --------> Non cluster index will made.

By using primary key --------> Cluster index will made.

How to use index while retrieving a data?

SELECT PRSN_INTN_ID ,

EMPL_STAT_CD ,

NAME,

REFERENCE_ID

FROM Table WITH (INDEX(IndeName))

advertising