Types of Indexes Are Available in SQL Server

Question

Hi friends, I want to know What is an index? What types of indexes are available in SQL Server?

Kunal Vaishya · Answer

Dear Friend, One of the important parts of SQL Server development and optimization is the creation of indexes. In order to create proper indexing strategies it is necessary to understand how indexes work. This tutorial will guide you step by step to fully understand indexes. There are only two different types of indexes. Clustered and NonClustered. There can only be one clustered index on a table and the reason is simple: A Clustered index is the data of table sorted according to the columns you choose. A NonClustered index is just like the index of a book. It contains data sorted so that it's easy to find, then once found, it points back to the actual page that contains the data. (In other words, it points back to the clustered index) Suppose we are reading a book about biographical information of all the U.S. Presidents, and the book itself orders the biographies starting from the first president to the latest president. This ordering of the pages would represent the clustered index. Now suppose you asked two different people to find Franklin D. Roosevelt's biography. Person-A was a historian and Person-B was unschooled. Person-A would quickly be able to find the presidents biography while Person-B would have to scan through each page in order to find the biography. Even if the Person-B used the book's index (akin to the non-clustered index), he would still have to search for the page after he found the page number. So it is always faster to find information off of the clustered index because the data in already at the "leaf-level" off the index. With this information, how do we determine what the clustered index should be? Well, it depends on the population of the people searching for the biographies. If it is mostly unschooled people, then it would be more efficient to sort the book alphabetically rather than the historical order of the presidents. Now let's say that 75% of the population are historians and the other 25% are unschooled. Let's assume the data the historians will need consists of a lot of different information regarding the president's biographies, while all the unschooled need is the president's age at the time they took office. In this scenario, it is more plausible to keep the ordering of the book (or the clustered index) based on the order of the president, then simply add the age of the president in the back index of the book (the non clustered index). That way the unschooled people do not have to look into the front of the book (clustered index) for the president's age. They could simply find it in the back of the book by doing one single lookup. The presidents age being stored in the rear index would be considered at the "leaf" level. This would satisfy both requirements and would be efficient for both historians and the unschooled group. First let's create our president's table download and run: PresidentsTable After running, let's turn on the execution plan (In SQL Server Managment Studio place your mouse in the query window and select Query -> Include Actual Execution Plan) Refer Link for More Reference http://sqlserverplanet.com/indexes/sql-server-indexes-tutorial

Satyapriya Nayak · Answer

Hi Ajay, 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. Please refer the below link http://www.c-sharpcorner.com/uploadfile/b19d5a/index-in-sql-server-2008/default.aspx Thanks

Types of Indexes Are Available in SQL Server

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (2)