What are SQL Indexes?
Indexes are used to help the SQL engine to identify faster the required data. There are associated with the data from the tables or the views.
If you don’t have indexes on your columns that you use in the WHERE clause, then the database server has to query the entire table. For instance, you have a table where you keep all the citizens of a city. Using an index on the Social Security Number column will give you the possibility to get the needed row right away by using the identifier.
select * FROM Citizens WHERE SSN='710-18-5673' -- If a index is used on the SSN column, the server will identify directly the row without to search in the entire table
SQL Server Indexes Types
Clustered Index
The clustered index sorts the data based on the value of that column. We use the clustered index when the data has an order. The primary key creates this type of index on that column.
There can be only one clustered index on the table. The reason for this limitation is that you can store the records in only one order.
CREATE TABLE Employee
(
Id INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Salary decimal(9,1) NOT NULL
)
insert into Employee Values (7,'Catwoman',4000)
insert into Employee Values (6,'Spiderman',199.9)
insert into Employee Values (1,'Super Man',4000)
insert into Employee Values (3,'Batman',100)
insert into Employee Values (2,'John Doe',100)
select * FROM Employee
If you execute the above script, you will see that the records will be sorted even if you didn’t insert them in the correct order.
For most tables, the Id column will be the primary key and have a clustered index.
Nonclustered Index
A nonclustered index contains the key value and a pointer to the actual row representing it. For this type of index, the physical order of the rows doesn’t matter.
You should create nonclustered indexes for the columns used in the WHERE clause. Also, if you have a column used for a join operation and it isn’t a primary key, you should create a nonclustered index.
CREATE INDEX IX_Employee_Name ON Employee (Name ASC);
The index can also have a unique constraint.
DROP INDEX Employee.IX_Employee_Name; -- drop previous index CREATE UNIQUE INDEX IX_Employee_Name ON Employee (Name ASC);
Execution Plan
Microsoft SQL Server Management Studio offers the option to include the actual execution plan. Press CTRL + M and execute the query.
A new tab will show up. You can see how the query was performed and what step took the most time. You can hover your mouse over every step to find different statistics and information.
Usually, you will want to see more Index Seek operations instead of Table Scan. This means that the SQL server will look at the index and will not look at every row.
Imagine a table with millions of records. A table scan will take many seconds to give you the desired result. An index seek operation will get your result much faster.
T-SQL Missing Indexes
The below script will give you a list of possible indexes that you should create. Be careful and don’t create every index that is recommended, because this will cause an overhead.
Every time you run a DML operation (INSERT, UPDATE, DELETE) then the index is automatically updated by the SQL Server.
SELECT TableName = d.statement, d.equality_columns, d.inequality_columns, d.included_columns, s.user_scans, s.user_seeks, s.avg_total_user_cost, s.avg_user_impact, AverageCostSavings = ROUND(s.avg_total_user_cost * (s.avg_user_impact/100.0), 3), TotalCostSavings = ROUND(s.avg_total_user_cost * (s.avg_user_impact/100.0) * (s.user_seeks + s.user_scans),3) FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle WHERE d.database_id = db_id() ORDER BY TableName, TotalCostSavings DESC;
This SQL script will give you statistics about what the saving will be after an index will be created. This is a good starting point to create indexes. For every table, look at the recommended indexes and choose only the columns that will have the greatest impact on total saving time.
List T-SQL Indexes
If you want to see all indexes and how many times were used in seek operations or updated after a DML operation, then you should run the following query.
SELECT OBJECT_NAME(s.object_id) as TableName, i.name AS IndexName, i.type_desc as IndexType, user_seeks + user_scans + user_lookups as TotalUsage, user_seeks, user_scans, user_lookups, user_updates FROM sys.dm_db_index_usage_stats s RIGHT OUTER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.database_id=DB_ID() AND i.name is NOT NULL and OBJECTPROPERTY(s.[object_id], 'IsMsShipped')=0 ORDER BY s.object_id,s.index_id
If the user_updates column is higher than the user_seeks column this means that the SQL performed more operations to update the index than use it in the queries. You must delete the indexes that require more updates than seek operations if there are no special specifications.