Table size without indexes in sql server

Question

Using the following query I can get the size of the indexes for a table:

SELECT
    i.name              AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(db_id(), object_id('schema.table'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name

If I execute sp_spaceused 'schema.table' does the size without indexes correspond to data column?

Any way to get the size of the table without indexes in sql server? (SELECT statement would be nice as I'd like to plug this query into a bigger query).

If I execute Get size of all tables in database, the sizes for tables are almost the same as the sizes for indexes (suggesting that something is wrong).

Answer 1

There are multiple ways of finding size of a table in the database. First and easiest way is to right click on Table and check the storage tab and this gives you Data space and Index Space. Data space is nothing but size of data in the table and similarly index space gives you size of index in the mentioned table.

The above information would exactly match with the command sp_spaceused 'schema_name.table_name' We can cross check the same for the Users Table(in dbo schema and StackOverFlow Database).

Here, if you add data, index size and unused(in KB), you will get reserved space by table.

You may also check the size of table and index by running command sp_blitzindex(from Mr. Brent Ozar). Command would be as below:

exec sp_BlitzIndex  @SchemaName = 'dbo', @TableName = 'Users'

Here, you may check the Size column. Clustered index size will give you Table size and you can also find size of each and every index as well as their usage(if interested).

Moreover you can also check table size of all of them by getting into Reports as below:

You may also run below select statement to get full size and index size of all the tables:

;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END) as pages
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
    cte.TableName, 
    cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB, 
    cast(((CASE WHEN cte.used_pages_count > cte.pages 
                THEN cte.used_pages_count - cte.pages
                ELSE 0 
          END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

If you want only the table sizes then execute the following:

SELECT
t.name as TableName
, SUM (CASE WHEN (i.index_id < 2) 
          THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
          ELSE lob_used_page_count + row_overflow_used_page_count
     END) * 8 as table_size_kb
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
where t.object_id = OBJECT_ID('schema.table')
GROUP BY t.name

Hope this helps.

Answer 2

The DMV returns the information you want, you just need to know that index_id = 0 is for Heaps, & that Index_id 1 is for the clustered index

It is these that are reported as data in the output from sp_spaceused

use tempdb

create table heap(id int identity(1,1), txt nvarchar(max) default 'hahaha')

insert heap (txt) values ('one')

select object_name(object_id) as objname, * FROM sys.dm_db_index_physical_stats(db_id(), object_id('schema.table'), NULL, NULL, 'DETAILED') AS s where index_id = 0