It is very easy to find out some basic details of any table using the following Stored Procedure.
USE AdventureWorks
GO
EXEC sp_spaceused [HumanResources.Shift]
GO
Above query will return following resultset
The above SP provides basic details such as rows, data size in table, and Index size of all the indexes on the table.
If we look at this carefully, a total of three indexes can be found on the table HumanResources.Shift.
USE AdventureWorks
GO
SELECT *
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('HumanResources.Shift')
GO
The above query will give result with query listing all the index on the table.
There is a small puzzle for all of you here. The puzzle is to write a query that will return the size for each index that is listed in above query. We need a query that will return an additional column in the above listed query and it should contain the size of the index. In our case, we will have three different sizes, which should add up to a total of 40 KB as shown in earlier query, where the total size is displayed.
I will publish the solution with due credit on this blog.
Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
