Database indexes help speed up retrieval of data. The great benefit of indexes is that the server doesnt have to work as hard to get the data. They are much the same as book indexes, providing the database with quick jump points on where to find the full reference.
An index is a data structure that organizes data records on disk to optimize certain kinds of retrieval operations. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random look ups and efficient access of ordered records.
In a relational database, an index is a copy of one part of a table. Some databases extend the power of indexing by allowing indexes to be created on functions or expressions. Indexes may be defined as unique or non-unique. A unique index acts as a constraint on the table by preventing duplicate entries in the index. Microsoft SQL Server allows a unique index to contain a single NULL value.
There are both advantages and disadvantages to using indexes. One disadvantage is they can take up quite a bit of space, check a textbook or reference guide. By using too many indexes the database will slow down is another disadvantage. Indexes speed up finding data, but slow down inserting, updating or deleting data.
Some fields are automatically indexed. A primary key or a field marked as 'unique' for example an email address, a userid or a social security number are automatically indexed so the database can quickly check to make sure that you're not going to introduce bad data.
There are many types of Indexes in database and there are the following:
A bitmap index is a special kind of database index that uses bitmaps. An index record appears for every search key value in the file is dense index. An index is created only for a few values is called sparse index. If all the columns in table on which there is an index are used in the join clause in the same order as it is in the index definition then it is called as index covered.
Clustered and Non Clustered Index
Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. A clustered index defines the order in which the data should be stored in the database. Â This means that there can only be one clustered index per table.
What is the difference between clustered and nonclustered indexes?
There are clustered and nonclustered indexes. 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.
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.
Consider a library, where books are arranged by the names of Author in the shelves. So, if we need a book written by 'Arundhati Roy' , we can go directly to the shelf which has the books written by she, instead of going sequentially through all the books.
This is example for clustered index in database.
Now suppose, you realized that some books are rather requested by titles. But as we have already arranged books by the names of Author, we can not rearrange it with titles in sequence, so you created a list of title and it's shelf number, so you ask me a book title, and i can get it for you from the shelf noted with it. This is example for non-clustered index.
Each table can have only one clustered index as the key values in the data rows are unique and the index is built on the unique key column. When a table has a clustered index, it is known as a clustered table. Non-Clustered indexes have structures that are different from the data rows. A non clustered index key value is used to point to data rows that contain the key value. This value is known as row locator. A clustered index is the most important index you can apply to a table. If the database engine can use a clustered index during a query, the database does not need to follow references back to the rest of the data, as happens with a nonclustered index. The result is less work for the database, and consequently, better performance for a query using a clustered index.
As a general rule of thumb, every table should have a clustered index. If you create only one index for a table, use a clustered index. Not only is a clustered index more efficient than other indexes for retrieval operations, a clustered index also helps the database efficiently manage the space required to store the table. In SQL Server, creating a primary key constraint will automatically create a clustered index using the primary key column as the index key.
Sometimes it is better to use a unique nonclustered index on the primary key column, and place the clustered index on a column used by more queries. For example, if the majority of searches are for the price of a product instead of the primary key of a product, the clustered index could be more effective if used on the price field. A clustered index can also be a UNIQUE index.
A Disadvantage to Clustered Indexes
If we update a record and change the value of an indexed column in a clustered index, the database might need to move the entire row into a new position to keep the rows in sorted order. This behavior essentially turns an update query into a DELETE followed by an INSERT, with an obvious decrease in performance. A table's clustered index can often be found on the primary key or a foreign key column, because key values generally do not change once a record is inserted into the database.
Examples for Clustered Index in SQL
To select productid from table products which the productid will be list from 14 values to 25 values.
The difference between clustered index and index is that in clustered index the cost is 100% but in the index the cost is 0, 00%.
Examples for Non-Clustered Index in SQL
To select productName, Unitprice, QuantityPerUnit and Discontinued from table products , where the unitprice will be listed from 10 to 15.
The difference between non clustered index and index is that in non clustered index the cost is 100% but in the index the cost is 0, 00%.
In this topic I explained how to create, manage, and select clustered and non clustered indexes for SQL Server tables. Indexes can be created using one or more columns of a database table. Most of what we covered is true for any relational database engine. Indexes may be defined as unique or non-unique. Proper indexes are crucial for good performance in large databases. Sometimes you can make up for a poorly written query with a good index, but it can be hard to make up for poor indexing with even the best queries. Sometimes it is better to use a unique nonclustered index on the primary key column, and place the clustered index on a column used by more queries. The great benefit of indexes is that the server doesn't have to work as hard to get the data.