What is Nonclustered Index in SQL SERVER

Question

Hi friends What is Nonclustered Index in SQL SERVER?

Satyapriya Nayak · Answer

Hi Rahul, Index in sql is created on existing tables to retrieve the rows quickly. There are 2 Types of Indexes Clustered Index: A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. Clustered index is physically stored a table can have 1 clustered index Non-Clustered Index: A non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-Clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. On clustered index is logically stored a table can have 249 non Clustered index Please refer the below links http://www.c-sharpcorner.com/uploadfile/nipuntomar/clustered-index-and-non-clustered-index-in-sql-server/default.aspx http://www.c-sharpcorner.com/uploadfile/6897bc/clustered-and-non-clustered-index-in-sql-2005/default.aspx Thanks

Sandeep Singh Shekhawat · Answer

INDEX: It helps to find quickly data in database table means it speed up performance of database to retrieve data. Basically we have two type INDEX: 1. CLUSTERED INDEX 2. NON CLUSTERED INDEX CLUSTERED INDEX: It creates on PRIMARY KEY and maintain physical order of table means data will be store me basic of primary id that id is clustered index. for example NAME filed is primary key so it will be CLUSTERED INDEX. Suppose now we insert NAME value "AAA" it will be go first row, again we insert second value "CCC" it will be go in 2nd row. Now we insert "BBB" so it will shift second row means "CCC" in third row and "BBB" will be insert in 2nd row and we get output like: AAA BBB CCC and same for other which one we will insert so we can say that a single table store physically only once and particular order. we a single table cannot have two copy of data in database means there will not be physically two table for single table. SO my means to say a single table have only one physical order itself in database and can't have duplicate copy of it in database. So we can't create more than ONE CLUSTERED INDEX. SELECT * FROM EMPLOYEE WHERE NAME ="AAA" so we retrieve a row from this table base of NAME filed then use CLUSTERED INDEX of it means what we define in WHERE clause of a query if there is NAME ="CCC" then CLUSTERED index will be execute. but we can have more than one search criteria suppose we have a EMPLOYEE table that have Emp_id, NAME, Joining date, Salary where Emp_id is primary key so it will be CLUSTERED index and employee table will be sort according to Emp_id in database. But suppose we want serach according to other fields instead of Emp_id. So we can't sort table physically according to other fileds so we need to create here NON-CLUSTERED index. In non clustered index we can use other fields of table which we need in WHERE clause when we search data. It maintain a separate logical table which point to physical table according to field. Suppose we want search according to NAME="AAA" AND SALARY=1200 so in that case we create a non clustered index for NAME and SALARY fields and when a query which have WHERE claues like SELECT * FROM EMPLOYEE WHERE NAME="AAA" AND SALARY=1200 So in that case we have separet a logical table which leaf node point to base physical table here logical table will be have data sort accoring to non clustered index and accoring two WHERE cluause that non clustered will be execute and we get data from table and our point table is already sort for NAME and SALARY. So we can create logical table more than one in database so we can also create more than one non clustered indexes which will be execute as per WHERE clause in execute query to retreive data. Hope it will be helpful.

Shivendu Kesarvani · Answer

Index is used for quick searching of rows in Database. Defaultly Database will have one clustered Index based on primary key i.e ID of the table. If Table have the requirement to another indexing in column then use the non cluster index.

Veerabhadram Dhodla · Answer

Index is used for quick searching of rows in Database. Defaultly Database will have one clustered Index based on primary key i.e ID of the table. So if what to create one more index on another column like email or Phone. This time you will create a Nonclustered Index. This Nonclustered Index will be also create by using clustered index.

What is Nonclustered Index in SQL SERVER

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (4)