SQL Server series: Index Basics

1. Index Concepts

Indexes are used to quickly find the rows of a particular value in a column, and without an index, the database must begin reading the entire table from the 1th record, knowing that the required rows are found. The larger the table, the more time it takes to query the data. If the columns queried in the table are indexed, the database can quickly reach a location to find the data without having to traverse all the data.

An index is a separate database structure, stored on disk, that contains reference pointers to all records in the data table. Using indexes to quickly find rows with specific values in one or more columns, using indexes on related columns is the best way to reduce query operation time. The index contains the keys generated by one or more columns in the table or attempt.

Advantages of the index:

◊ by creating a unique index, you can guarantee the uniqueness of each row of data in a database table.

◊ can greatly speed up the query speed of the data, which is the most important reason to create the index.

◊ to achieve referential integrity of data, you can accelerate the connection between tables and tables.

◊ When you use grouping and sort clauses for data queries, you can significantly reduce the time to group and sort in queries.

Disadvantages of the index:

◊ It takes time to create indexes and maintain indexes, and increases the amount of time that is consumed as the volume of data increases.

◊ indexes require disk space, and each index occupies a certain amount of physical space in addition to the data tables that occupy the data space. If you have a large number of indexes, the index file may reach the maximum file size faster than the data file.

◊ when the data in the table is added, modified, and deleted, the index needs to be maintained dynamically, which reduces the maintenance speed of the data.

2. Classification of indexes

There are two types of indexes in SQL Server: Clustered and nonclustered indexes. The difference is in how the physical data is stored.

2.1> Clustered Index

The clustered index sorts and stores the data rows within the table based on the key values of the data rows. Each table can have only one clustered index, because the data rows themselves can only be stored in one order.

Several factors to consider when creating a clustered index:

◊ There can be only one clustered index per table

◊ The physical order in the table is the same as the physical order of the rows in the index, and the clustered index is first created before any nonclustered indexes are created because the clustered index alters the physical order of rows in the table.

◊ The uniqueness of key values is explicitly maintained by using the unique keyword or by the internal unique identifier.

◊ during the creation of the index, SQL Server temporarily uses the disk space of the current database, so make sure there is enough space to create the clustered index.

2.2> Nonclustered Indexes

Nonclustered indexes have a structure that is completely independent of the data rows, and non-clustered indexes do not have to sort the data of the physical data pages by columns. Nonclustered indexes contain index key values and row locators that point to the table data storage location.

You can create multiple nonclustered indexes on a table or indexed view. Nonclustered indexes are designed to improve the performance of frequently used queries that do not establish a clustered index.

When the query optimizer looks for a data value, it finds the nonclustered index to find the location of the data value in the table, and then retrieves the data directly from that location. This makes the nonclustered index the best choice for an exact match query, because the index contains items for the exact position of the data values that are being looked up in the table.

Consider a query using a nonclustered index:

◊ Use the JOIN or GROUP BY clause. You should create multiple nonclustered indexes for the columns involved in the join and grouping operations, and a clustered index for any foreign key columns.

◊ A field that contains a large number of unique values.

◊ queries that do not return large result sets. Create a filtered index to overwrite a query that returns a well-defined subset of rows from a large table.

◊ The columns that are frequently included in the search criteria for the query.

3. Create an index

Two methods of creating an index in SQL Server: In the Object Explorer of SQL Server Management Studio, created with a graphical tool or by using T-SQL statements.

3.1> creating using SQL Server Management Studio Object Explorer

◊ in Object Explorer, expand Databases to locate the data table node for which you want to create an index, expand the child nodes under that node, right-click the Indexes node, and select New index, nonclustered index, on the shortcut menu that pops up.

◊ Create an index by selecting the column that you want to create an index in the new index interface that opens.

SQL Server series: Index Basics