The Tech Platform
May 25, 20228 min
A SQL Server index is considered as one of the most important factors in the performance tuning process. Indexes are created to speed up the data retrieval and the query processing operations from a database table or view, by providing swift access to the database table rows, without the need to scan all the table’s data, in order to retrieve the requested data.
There are two types of SQL Server Index:
Clustered Index
Non-Clustered Index
In the case of a clustered index, the arrangement of the data in the index table will be the same as the arrangement of the data of the actual table. Example: The index we find the start of a book. When a table has a clustered index then the table is called a clustered table. If a table has no clustered index its data rows are stored in an unordered structure.’ A table can have only one clustered index in it which will be created when the primary key constraint used in a table. A clustered index determines the physical order of data in a table. For this reason, a table can have only one clustered index.
In the case of a non-clustered index, the arrangement of data in the index table will be different from the arrangement of the actual table. A non-clustered index is analogous to an index in a textbook. The data is stored in one place, the index is another place. The index will have pointers to the storage location of the data. Since, the non-clustered index is stored separately from the actual data a table can have more than one non clustered index, just like how a book can have an index by chapters at the beginning and another index by common terms at the end. In the index itself, the data is stored in an ascending or descending order of the index key which does not in any way influence the storage of data in the table. In a table, we can create a maximum of 249 non clustered indexes.
SQL Server allows us to create only one Clustered index per each table, as the data can be sorted in the table using only one order criteria
CREATE INDEX T-SQL statement.
Using SQL Server Management Studio, by browsing the table on which you need to create an index, right click on the Indexes node and choose New Index option.
Indirectly by defining the PRIMARY KEY and the UNIQUE constraint within the CREATE TABLE or ALTER TABLE statements.
A column contains a wide range of values.
A column does not contain a large number of null values.
One or more columns are frequently used together in a where clause or a join condition.
The table is small
The columns are not often used as a condition in the query
The column is updated frequently
A table can have one of the following index configurations:
No indexes
A clustered index
A clustered index and many non-clustered indexes
A non-clustered index
Many non-clustered indexes
A well-designed index will enhance the performance of your system and speed up the data retrieval process. On the other hand, a badly-designed index will cause performance degradation on your system and will cost you extra disk space and delay in the data insertion and modification operations. It is better always to test the performance of the system before and after adding the index to the development environment, before adding it to the production environment.
A Heap table is a table in which, the data rows are not stored in any particular order within each data page. In addition, there is no particular order to control the data page sequence, that is not linked in a linked list. This is due to the fact that the heap table contains no clustered index.
A clustered table is a table that has a predefined clustered index on one column or multiple columns of the table that defines the storing order of the rows within the data pages and the order of the pages within the table, based on the clustered index key.
The heap table can be identified by querying the sys.partitions system object that has one row per each partition with index_id value equal to 0. You can also query the sys.indexes system object also to show the heap table index details, that shows, the id of that index is 0 and the type of it is HEAP.
FILLFACTOR is used to set the percentage of free space that the SQL Server Engine will leave in the leaf level of each index page during index creation. The FillFactor should be an integer value from 0 to 100, with 0 or 100 is the default value, in which the pages will be filled completely during the index creation.
PAD_INDEX is used to apply the free space percentage specified by FillFactor to the index intermediate level pages during index creation.
Index fragmentation can be resolved by rebuilding and reorganizing SQL Server indexes regularly. The Index Rebuild operation removes fragmentation by dropping the index and creating it again, defragmenting all index levels, compacting the index pages using the Fill Factor values specified in rebuild command, or using the existing value if not specified and updating the index statistics using FULLSCAN of all the data.
The Index Reorganize operation physically reorders leaf level pages of the index to match the logical order of the leaf nodes. The index reorganizes operation will be always performed online. Microsoft recommends fixing index fragmentation issues by rebuilding the index if the fragmentation percentage of the index exceeds 30%, where it recommends fixing the index fragmentation issue by reorganizing the index if the index fragmentation percentage exceeds 5% and less than 30%.
Unique Indexes, by ensuring that there are no duplicate values in the index key, and the table rows, on which that index is created.
It uses a filter predicate to improve the performance of queries that retrieve a well-defined subset of rows from the table, by indexing the only portion of the table rows. The smaller size of the Filtered index, that consumes a small amount of the disk space compared with the full-table index size, and the more accurate filtered statistics, that cover the filtered index rows with only minimal maintenance cost, help in improving the performance of the queries by generating a more optimal execution plan.
Using SSMS, by expanding the Indexes node under a database tabl, then right-clicking on each index, and choose the Properties option. The problem with gathering the indexes information using the UI method is that you need to browse it one index at a time per each table. You can imagine the effort required to see the article: all indexes in a specific database.
The sp_helpindex system stored procedure, by providing the name of the table that you need to list its indexes. In order to gather information about all indexes in a specific database, you need to execute the sp_helpindex number of time equal to the number of tables in your database.
The sys.indexes system dynamic management view. The sys.indexes contains one row per each index in the table or view. It is recommended to join sys.indexes DMV with other systems DMVs, such as the sys.index_columns, sys.columns and sys.tables in order to return meaningful information about these indexes.
It uses a filter predicate to improve the performance of queries that retrieve a well-defined subset of rows from the table, by indexing the only portion of the table rows. The smaller size of the Filtered index, that consumes a small amount of the disk space compared with the full-table index size, and the more accurate filtered statistics, that cover the filtered index rows with only minimal maintenance cost, help in improving the performance of the queries by generating a more optimal execution plan.
The Missing Index Details option in the query execution plan, if available.
The sys.dm_db_missing_index_details dynamic management view, that returns detailed information about missing indexes, excluding spatial indexes,
A combination of the SQL Server Profiler and the Database Engine Tuning Advisor tools.
SQL Server stores data in it under data pages where a data page is a memory location for storing the information.
A data page will be having a size of 8KB and every 8 data pages we stored under a logical container known as “extend”.
A composite index is an index on two or more columns. Both clustered and non-clustered indexes can be composite indexes.
If all of the information for a query can be retrieved from an Index then it is called as Covering query. A clustered index, if selected for use by the query optimizer, always covers a query, since it contains all of the data in a table.
An important feature of SQL Server is the query optimizer (component). The query optimizer’s job is to find the fastest and least resource-intensive means of executing incoming queries. An important part of this job is selecting the best index or indexes to perform the task.
We can create index on combination of columns. The general syntax for creating multi-column index is :
CREATE INDEX index_name
ON TABLE_NAME (COLUMN_NAME1, COLUMN_NAME2,.. COLUMN_NAMEN);
CREATE INDEX idx_emp_name
ON EMPLOYEES (FIRST_NAME, LAST_NAME);
Syntax:
CREATE INDEX index
ON TABLE (column1, column2,.....);
Unique indexes are used for the maintenance of the integrity of the data present in the table as well as for the fast performance, it does not allow multiple values to enter into the table.
Syntax:
CREATE UNIQUE INDEX index
ON TABLE column;
To remove an index from the data dictionary by using the DROP INDEX command.
Syntax:
DROP INDEX index;
To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege.
To modify an existing table’s index by rebuilding, or reorganizing the index.
ALTER INDEX IndexName
ON TableName REBUILD;
You can check the different indexes present in a particular table given by the user or the server itself and their uniqueness.
Syntax:
select * from USER_INDEXES;
It will show you all the indexes present in the server, in which you can locate your own tables too.
You can use the system stored procedure sp_rename to rename any index in the database.
Syntax:
EXEC sp_rename
index_name,
new_index_name,
N'INDEX';
The Tech Platform