Thursday, March 15, 2018

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.

No comments:

Post a Comment

Popular Posts

Get Sponsored by Big Brands