Indexes in SQL Server

Question

Hi, I am new with SQL SERVER programming. Please help me to understand Indexes in Sql Server with full explanation. Thanks, Abhinav

Satyapriya Nayak · Answer

Hi ABHINAV, An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance. Clustered indexes define the physical sorting of a database table's rows in the storage media. For this reason, each database table may have only one clustered index.Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. You can only have one clustered index on a table. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Please refer the below links http://www.c-sharpcorner.com/uploadfile/nipuntomar/clustered-index-and-non-clustered-index-in-sql-server/default.aspx http://www.c-sharpcorner.com/uploadfile/6897bc/clustered-and-non-clustered-index-in-sql-2005/default.aspx Thanks