SQL Indexes

 

 

SQL  Indexes

Contents 1       Types of Indexes. 2 1.1        Clustered Index. 2 1.2        Non-Clustered Index. 2 1.3        Full-Text Index. 2

 

1         Types of Indexes

-          Indexes are used to speed-up query process in SQL Server, resulting in high performance.

-           

1.1       Clustered Index

-          A clustered index defines the order in which data is physically stored in a table.  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.

-          An index that is created on more than one column is called “composite index”.

-          How to Create Indexes?

o   SSMS > Databases > Database_Name > Tables > Table_Name > Select Column(s)  RtClikc > Primary Key OR

o    

use schooldb   CREATE CLUSTERED INDEX IX_tblStudent_Gender_Score ON student(gender ASC, total_score DESC)

o    

-          How to see the existing Indexes?

o   sp_helpindex >> EXE sp_helpindex TableName

o   SSMS > Databases > Database_Name > Tables > Table_Name > Indexes

-           

1.2       Non-Clustered Index

·                A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place, more than one non-clustered index per table can create.  due to this additional step that non-clustered indexes are slower than clustered indexes. 

·                How to Create Indexes?

o         

use schooldb   CREATE NONCLUSTERED INDEX IX_tblStudent_Name ON student(name ASC)

 

1.3       Full-Text Index.