table size with where condition

Question

Get the table data size with the where clause. i.e., table size based on specific rows.

Answer 1

For example, you have the next query:

select Column1, Column2
from MyTable
where Column3 > 100

Then you can evaluate the size using this query:

select sum(datalength(Column1) + datalength(Column2))
from MyTable
where Column3 > 100

Answer 2

Hi @Chary ,

You can dump that specific rows in another temp table and get size of that table, that would be the size of specific rows. I did a test in my environment. We can also compare the data size of specific rows and a whole table.

------create a temp table to store specific data  
  
create table dbo.tableA2(id int, name varchar(50))  
  
------ insert specific rows in temp table  
  
INSERT INTO tableA2 (id, name)  
SELECT id, name  
FROM tableA  
WHERE id>100  
  
------get the size of the whole table and specific rows  
  
EXEC sp_spaceused N'dbo.table2';    
GO    
  
EXEC sp_spaceused N'dbo.tableA';    
GO    
  
drop table dbo.tableA2  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

SQL Server does not track space by row, it uses 8K pages. Do you need an exact value, or a general value? If you just need a general idea, you can use the (total size / total number of rows) * number of rows in where clause. That will give you an average size of rows.

If you need an exact number, your best option is to insert the rows into a new table and see the size of the new table.