What is difference between Clustered & Non-Clustered in SQL.

Question

What is the difference between Clustered and Non-Clustered Indexes in SQL Server? Please explain to me in a simple way with example. I Request you don't send me other links. If you want to answer, please explain in your words and in a simpler way. Thank you

Amit Mohanty · Answer

Clustered Index A clustered index defines the order in which data is physically stored in a table. Table data can be sorted in the only way, therefore, there can be only one clustered index per table. In SQL Server, the primary key constraint automatically creates a clustered index on that particular column. Let's take an example. CREATE TABLE tblEmployee ( EmpId INT PRIMARY KEY , EmpName VARCHAR (50) NOT NULL , Gender VARCHAR (50) NOT NULL , DOB datetime NOT NULL , City VARCHAR (50) NOT NULL ) In the above table we have set primary key constraint on EmpId column. So it's automatically creates a ciustered index in empId. You can check index by going to “Object Explorer-> Databases-> Database_Name-> Tables-> Table_Name -> Indexes”. Non-Clustered Indexes A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored in one place and table data is stored in another place. When we create a query against a column on which the index is created, the database will first go to the index and look for the address of the corresponding row in the table. Then it will go to that row address and fetch other column values. The syntax for creating a non-clustered or clustered index is similar. However, in the case of a non-clustered index keyword, “ NONCLUSTERED ” is used instead of “ CLUSTERED ”. Syntex: CREATE NONCLUSTERED INDEX IX_tblEmployee_EmpName ON tblEmployee(EmpName ASC) NOTE: There can be only one clustered index per table. However, you can create multiple non-clustered indexes on a single table. Clustered indexes are faster than non-clustered indexes since they don’t involve any extra lookup step. Clustered indexes only sort tables. Therefore, they do not consume extra storage. Non-clustered indexes are stored in a separate place from the actual table claiming more storage space.

Rajanikant Hawaldar · Answer

Hello Priyanka, clustered index mechanism of having distinct rows of data in table this means adding primary key to table so each data in primary key column is unique no duplicates so one primary key or clustered index is enough for one table so because having only one clustered index allows to retrieve data is faster. Deleting and updating bit slow non clustered index faster for updating and deleting rows but retrieving data is not fast because here more then one non clustered indexs are there means duplicate or not unique data so in this case you should do filter with where, having conditions

Ajay Gohil · Answer

Hi, Following are difference between Clustered and Nonclustered Index: A Clustered Index physically sort all rows while Nonclustered Index doesn't. In SQL, one table can only have one Clustered Index but there is no such restriction on NonClustered Index. In many relational databases, Clustered Index is automatically created on the primary key column. Clustered Index can improve the performance of data retrieval while non-clustered index should be created on columns which are used in the join, where, and order by clause.

Ravi Patel · Answer

Clustered Index Only one per table Faster to read than non clustered as data is physically stored in index order Non Clustered Index Can be used many times per table Quicker for insert and update operations than a clustered index

Rajeev Kumar · Answer

A Clustered Index physically sort all rows while Nonclustered Index doesn't. In SQL, one table can only have one Clustered Index but there is no such restriction on NonClustered Index. In many relational databases, Clustered Index is automatically created on the primary key column. Clustered Index can improve the performance of data retrieval while non-clustered index should be created on columns which are used in the join, where, and order by clause.

What is difference between Clustered & Non-Clustered in SQL.

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (5)