bdanijel
10/28/2019 - 2:00 PM
Clustered and Non-Clustered indexes
http://csharp-video-tutorials.blogspot.com/2012/09/clustered-and-non-clustered-indexes.html
A clustered index determines the physical order of data in a table. For this reason, a table can have only one clustered index.
execute sp_helpindex tblEmployee, which will show a unique clustered index created on the Id column.
Difference between Clustered and NonClustered Index:
- Only one clustered index per table, where as you can have more than one non clustered index
- Clustered index is faster than a non clustered index, because, the non-clustered index has to refer back to the table, if the selected column is not present in the index.
- Clustered index determines the storage order of rows in the table, and hence doesn't require additional disk space, but where as a Non Clustered index is stored seperately from the table, additional storage space is required.
CREATE TABLE [tblEmployee]
(
[Id] int Primary Key,
[Name] nvarchar(50),
[Salary] int,
[Gender] nvarchar(10),
[City] nvarchar(50)
)
Insert into tblEmployee Values(3,'John',4500,'Male','New York')
Insert into tblEmployee Values(1,'Sam',2500,'Male','London')
Insert into tblEmployee Values(4,'Sara',5500,'Female','Tokyo')
Insert into tblEmployee Values(5,'Todd',3100,'Male','Toronto')
Insert into tblEmployee Values(2,'Pam',6500,'Female','Sydney')
Select * from tblEmployee
Create Clustered Index IX_tblEmployee_Name
ON tblEmployee(Name)
Create Clustered Index IX_tblEmployee_Gender_Salary
ON tblEmployee(Gender DESC, Salary ASC)Drop index tblEmployee.PK__tblEmplo__3214EC070A9D95DBCreate NonClustered Index IX_tblEmployee_Name
ON tblEmployee(Name)sp_helpindex tblEmployee