Thursday, 5 April 2018

Clustered and Nonclustered Index - SQL Server

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.
A table or view can contain the following types of indexes:
Clustered
Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
Syntax:   CREATE CLUSTERED INDEX IX_PKtest_Code ON dbo.pktest (Code);   
Nonclustered
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. This is similar to a textbook where the book content is located in one place and the index is located in another. This allows for more than one non-clustered index per table.
It is important to mention here that inside the table the data will be sorted by a clustered index. However, inside the non-clustered index data is stored in the specified order. The index contains column values on which the index is created and the address of the record that the column value belongs to.
When a query is issued against a column on which the index is created, the database will first go to the index and look for the address of the corresponding row in the table. It will then go to that row address and fetch other column values. It is due to this additional step that non-clustered indexes are slower than clustered indexes.
Syntax:    CREATE NONCLUSTERED INDEX IX_tblStudent_Name ON student(name ASC) 
     Both clustered and nonclustered indexes can be unique. This means no two rows can have the same value for the index key. Otherwise, the index is not unique and multiple rows can share the same key value.
What are advantages of Indexes?
Indexes are memory objects which are used to improve the performance of queries which allows faster retrieval of records.

Following are advantages of Indexes:

1.It allows faster retrieval of data
2.It avoids the Full table scan so that the performance of retrieving data from the table is faster.
3.It avoids the table access alltogether
4.Indexes always speeds up the select statement.
5.Indexes used to improve the Execution plan of the database

What are disadvantages of Indexes?

1.Indexes slows down the performance of insert and update statements.So always we need follow best practice of disabling indexes before insert and update the table
2.Indexes takes additional disk space so by considering memory point indexes are costly.

What are different types of indexes?

There are following types of indexes:
1.Normal Indexes
2.Bit map indexes
3.B-tree Indexes
4.Unique Indexes
5.Function Based Indexes



0 comments:

Post a Comment

Topics

ADFS (1) ADO .Net (2) Ajax (1) Angular (1) Angular Js (17) Angular2 (28) ASP .Net (14) Authentication (1) Azure (1) Breeze.js (1) C# (50) CD (1) CI (2) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DevOps (4) DI (4) Dotnet (22) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (8) Lamda (3) Linq (11) Mongodb (1) MVC (50) NodeJS (7) RDLC (1) Report (1) SDLC (1) Sql Server (30) SSIS (3) SSO (1) SSRS (2) UI (1) WCF (13) Web Api (11) Web Service (1) XMl (1)