SQL Server: How to create an index on a table
Having the right indexes on tables are critical to making your queries performant, especially when your data grows. Not having the needed indexes will typically result in high CPU usage in your database server, slow response times, and ultimately unhappy users. Indexing also helps improve performance by avoiding duplicate records, improving JOIN operations and table partitioning, and enabling full text search capability in SQL Server. In this tutorial, you will learn how to create an index in SQL Server, and various types of indexing that you can do.
To create indexes, use the
CREATE INDEX command:
-- syntax create index index_name on table_name(column1, column2, .., columnN); -- create index on one column create index products_category on products(category);
Creating index on multiple columns
You can create an index on multiple columns. When used for columns that are frequently used together as filters, a multiple-column index performs better than multiple single-column indexes:
-- create index on multiple columns create index products_category_brand on products(category, brand_id);
Creating unique index
A unique index prevents duplicate data for a defined key:
-- two rows can not have same category and brand_id key values create unique index products_category_brand on products(category, brand_id);
Creating filter indexes
Create filter indexes to only index rows where a certain condition is met:
create index products_brand on products(brand_id) where brand_id = 1;
Creating unique filter index
You can also have a unique filter index. For example, imagine if you want to prevent rows having duplicate category and brand_id key values for particular brand:
-- this will prevent duplicate categories within a brand that has id=1 create unique index products_brand_1_category on products (category, brand_id) where brand_id = 1;
Troubleshooting indexing issues in SQL Server involves identifying problems with existing indexes and optimizing them for better performance. Here are some common indexing issues and how to address them with short code examples:
While there is always a lot of data at hand, data from a few fixed columns is used the most by the entire business. Due to missing indexes, queries become slow. If your queries are too slow, it is worth checking if there are no suitable indexes.
Creating new indexes for frequently used columns helps:
-- Identify missing indexes using SQL Server's built-in tools SELECT migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, OBJECT_NAME(mid.[object_id]) AS TableName, 'CREATE INDEX [IX_' + OBJECT_NAME(mid.[object_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + CASE WHEN mid.[equality_columns] IS NOT NULL AND mid.[inequality_columns] IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, migs.*, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE mid.database_id = DB_ID() -- Replace with your database ID ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;
Unlike missing indexes, when there are many indexes for the same columns, we end up with overlapping indexes. These indexes need to be cleaned up; otherwise, they cause redundant storage and maintenance overhead. Here’s how you can identify and remove overlapping indexes:
-- Identify overlapping indexes SELECT OBJECT_NAME(i.[object_id]) AS TableName, i.name AS IndexName, COL_NAME(ic.[object_id], ic.column_id) AS ColumnName FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.[object_id] = ic.[object_id] AND i.index_id = ic.index_id WHERE i.[object_id] = OBJECT_ID('YourTableName') -- Replace with your table name ORDER BY TableName, IndexName, ColumnName; -- Remove redundant indexes (use DROP INDEX statement) -- Example: DROP INDEX [IX_RedundantIndex] ON [YourTableName];
Fragmented indexes also cause performance issues. Here’s how you can rebuild or reorganize fragmented indexes:
-- Rebuild index to reduce fragmentation ALTER INDEX [YourIndexName] ON [YourTableName] REBUILD; -- Reorganize index to reduce fragmentation ALTER INDEX [YourIndexName] ON [YourTableName] REORGANIZE;
Indexing is a crucial performance optimization technique in SQL Server (and other relational database management systems) that helps improve the speed and efficiency of querying and retrieving data from database tables. Now you have everything you need to work with indexes in SQL Server. You learned how to create an index, create index on multiple columns, and create unique and filter indexes. You also learned how to troubleshoot indexing for your project to improve query performance and avoid redundancy.
Now that you learned how to create an index in SQL Server, it is time to undo it (only if you need to)! Learn how to drop an index in the next tutorial.
1. What is the primary purpose of indexing in SQL Server?
Indexing in SQL Server primarily serves to improve query performance by providing a faster way to locate and retrieve data. It does this by creating a data structure that allows SQL Server to quickly find the rows that match a query's search criteria.
2. When should I consider creating a clustered index, and what is its impact on data storage?
A clustered index determines the physical order of data in a table, so it should be chosen carefully. Typically, it's best suited for columns with unique values, like a primary key. However, it's essential to remember that a table can have only one clustered index, and its choice affects data storage, as the data is physically organized based on the clustered index.
3. How do I identify missing indexes in SQL Server, and what should I do when I find them?
You can identify missing indexes by using tools like SQL Server's Database Engine Tuning Advisor or by analyzing execution plans. When you find missing indexes, carefully evaluate their potential impact on query performance before implementing them. Consider factors like query workload and the balance between improving reads and the cost of maintaining the indexes.
4. What is index fragmentation, and why is it a concern?
Index fragmentation occurs when data pages within an index are disordered or scattered. This can slow down query performance as SQL Server needs to read more pages to satisfy a query. To address fragmentation, you can rebuild or reorganize indexes regularly.
5. Can too many indexes on a table be detrimental to performance?
Yes, having too many indexes on a table can lead to performance issues. While indexes improve read performance, they come with storage and maintenance overhead. Excessive indexes can lead to increased storage requirements, slower data modification operations, and potentially suboptimal query plans. It's essential to strike a balance between indexing for reads and considering the impact on data modification and overall database performance.