SQL Server 2008 INDEX

SQL Server Indexing features - 

SQL server gave us the feature of indexing for speeding up the performance in how we retrieve data through queries. Imagine we have a book with many chapters, if we are required to turn pages and find a particular chapter of our interest it would take more time.

Indexing in a book gives a flexibility to look-up the page number of the book and directly go to that page number to find our chapter of interest and hence in a way more quick.

Similarly we have indexing in SQL server to retrieve data based a key value.

Types of indexes in SQL server

• Clustered
• Non-clustered
• Unique
• Filtered
• XML
• Full text
• Spatial
• Column store
• Index with included columns
• Index on computed columns

Today we will be discussing about Clustered, Non-Clustered and Unique indexes.

Clustered Index - 

It determines the way in which the data is physically ordered in a table. A table can have only one clustered index.

To understand this better let us create a table as shown below :-

Create table tblEmployee

(

[EmpID] int primary key,

[EmpName] varchar(20),

[Gender] char(1),

[EmpSalary] int

)

After creation of the table open "Object Explorer", expand tables in the database where the table tblEmployee was created expand "Key" and "Indexes", you can see the creation of primary key and an Index.

So we can now conclude for the above example that creation of primary key creates clustered index by default. Please see below screenshot for reference.

So whats actually the use of this clustered index creation!!!

As we stated above that indexing determines the way data is physically stored in a database, in the below example we will see how it stores the data.

In the creation of the table tblEmployee we have used EmpID as the primary key and the clustered index is created on this column. Lets try to insert values in the table tblEmployee.

Insert into tblEmployee values(3,'Indranil','M',25000)

Insert into tblEmployee values(1,'Vipul','M',35000)

Insert into tblEmployee values(5,'Deepika','F',18000)

Insert into tblEmployee values(4,'Mukul','M',19000)

Insert into tblEmployee values(2,'Shikha','F',21000)

We can see that while insertion of values in the table, the order of the EmpID is not inserted orderly, but as we have the clustered index on the EmpID listed, when we view the data through select statement, the data are automatically ordered in ascending order of EmpID. So the clustered index on EmpID helped the data get stored in accordance to ascending order of EmpID.

select * from tblEmployee