Indexes ,Clustered Index, Non Clustered Index ,Unique Index in Sql Server ?


How sql server stores data init :

-> Sql server internally stores data init under a physically entity known as datapage where a database is a minused storage location for storing the data with a size of 8kb.

-> Every 8 datapages are stored under a logical container known as a extent.

Note: Whenever all the datbases are filled up and if we want to save new data.sql server will at a time create an extent with 8 pages but not a single data page.

How does sql server search for the inofrmation under the database when we query?

Sql server to search the inforamtion from the database will be adopting two different types of query mechanisms

           1) Full Page Scan

           2) Index Scan

-> In the first case sql server stores searching for the required inforamtion from the first extents first page to the last extents last page to get the required if there is fuze vloumes of data under the table it will be consuming lot of time for retriving the information.

-> In the second case sql server without searching in to each extent & each page will be first searching into the index page of the table if present to locate the address of the records which have to be retrived and jumps directly to there address locations and retrives the information.

-> If sql server has to use an index scan first the index must be present for the table.

-> Indexes are created for a table basing on a cloumn or cloumns fo the table.

For Example:

If an Index is created on the Employee numbe cloumn of the Employee table internally the index page is maintained as following.

               Index page

              EmpNo            Address
           ----------        --------------

             1001              --------
             1002              --------
             1003              --------
             1004              --------

Syntax to create a Index

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED ] INDEX <Index-Name> On <Object or tableName> (column[Asc | Desc][,.........n])

Indexes are of two types :

 1)Clustered Index
 2)Non Clustered Index

1) Clustered Index

    In this case the arrangement of the data in the index page will be exaclty same as the arrangement of data in the datapage for the table
    Example: An Index in the start of a book.

2) Non Clustered Index

    In this case the arrangement of the data in the index page will not be same as the arrangement of data in the actual data page for the table.
    Example: An Index in the end of a book

How many Indexes a table can have ?
 We can create maximum of 250 indexes on a table in which clustered index can only be one and remaining 249 must be non clustered

Unique index

 If a unique index is created on any cloumn that cloumn will not allow duplicate values in to it that is the index works like a unique constraint on a clomun.

Implicit Index Scan
 We can create on index explicitly on any column of a table but some times indexs gets created implicitly on tables that is if a primary key constraint is imposed on any cloumns of a table

Creating an Index on Employee Table

 Create Index Employee-EName-Index on Employee(Ename)

      In the above case the Employee table already has a unique clustered index on the EmpNo cloumn because it has a primary key constraint so a part from that we are creating additional indexes as per our requirement.

Note: If unique is not specified in the create index statement,it creates a non unique index and also if clustered or non clustered is not specified then default is nonclustered

Create NonClustered index Employee-sql-index on Employee(Sal)


Create Table Student(Sno int,SName Varchar(50))

Create Unique Clustered Index Student-index on Student(Sno,SName)

  Note : if we impose a unique clustered index on any columns of a table a primary key constraint is not added implicitly but will provide the behavior of primary key.In the above case the index works like composite primary key constraint.
About Author: author 4 + years of Information Technology experience in understanding and analyzing the software and hardware engineering requirements, onsite and offshore management of product development. Expert work on design and development for windows,web,mobile Expert knowledge of C#,ADO.NET, ASP.Net MVC, ENTITY FRAMEWORK, LINQ,COLLECTIONS, JAVASCRIPT,AngularJs 1.0,Angular2, Jquery,Jquery-Ajax, SQL SERVER, XAML,Windows phone 8.1 Good knowledge of WCF-Restful services,WCF, WEB API-Services ,Web- Services,MIcrosoft-Azure,JUICE UI,HTML,CSS,AJAX Read More...

Join him on Google+ | Facebook | Linkedin

No comments