TSQL - Size of each index in SQL Server

As we know that indexes are the most important feature in relational databases to quickly retrieve the data. SQL Server is used a system table called sysindexes that content information about indexes which are available on tables in the database. If you have created a table which does have no index then one row in the sysindexes table related to that table indicating that there is no index on that table.

So, if you want to know that index name, their size and type of indexes on a particular data table then we can use the following query –

SELECT     ----- Name of the data table     T.NAME AS TableName,                 ----- Name of the index in database                 I.NAME AS IndexName,                 ----- Index Types                 I.TYPE_DESC AS IndexType,                 ----- CONVERT SIZE OF INDEX SIZE IN KB     SUM(S.USED_PAGE_COUNT) * 8 AS IndexSizeKB                 FROM SYS.DM_DB_PARTITION_STATS AS S INNER JOIN SYS.INDEXES AS I                 ON S.OBJECT_ID = I.OBJECT_ID                 AND S.INDEX_ID = I.INDEX_ID INNER JOIN SYS.TABLES T ON T.OBJECT_ID = I.OBJECT_ID GROUP BY                 I.NAME,                 T.NAME,                 I.TYPE_DESC ORDER BY                 I.NAME,                 T.NAME,                 I.TYPE_DESC

Now, you can see that Product table does not have any index but this information also stored in the sysindexes table.

One more thing, we should keep in our mind that if we are using clustered index then the size of the index will be the size of the table itself. On the other hand if the index is nonclustered then size of index will be separate from table size.

Why Index sizes are consolidated and separated?

Whenever we create a clustered index on a table then clustered index consist a data rows in a leaf node that’s why clustered index’s size is consolidated with table size.

But whenever we create any nonclustered index on a table then nonclustered index contains a row locator at leaf node that’s why index structure is totally separated from table data structure.

Conclusion

As you know that SQL Server Management Studio is the great tool which is capable to give you a wealth of information about its data components such as data tables, indexes and stored procedures.