CODEWITHSUNDEEP
sql-serversql-server-2005t-sql
Adam McC
Adam McC

Reputation: 213

How to find the size of data returned from a table

I have a table the holds snapshots of data. These snapshots are all tagged with 'jan2010' or 'april2011'. All the snapshots will grow exponentially over time and I wanted to see if I could forecast when we'd need to upgrade our storage.

Is there any way to 

select monthlysnapshot, sum(size)
from tblclaims_liberty 
group by monthlysnapshot 
order by monthlysnapshot desc

What am I missing to get the size of the data returned? Is there a system function I can call?

Upvotes: 0

Views: 1787

Answers (3)

Vimal Kumar Prajapati
Vimal Kumar Prajapati

Reputation: 1

I think you can Insert the selected data into a table via "select * into newtable from table" and get the size of that newly created table.

Upvotes: 0

Kashif
Kashif

Reputation: 14440

Is it what you are looking for?

C# Getting the size of the data returned from and SQL query

Changed:

EXEC sp_spaceused 'tablename'

If you can do this in your code then in C# (Change code to whatever lang you are using)

long size = 0;
object o = new object();
using (Stream s = new MemoryStream()) {
BinaryFormatter formatter = new BinaryFormatter();
formatter.Serialize(s, o);
size = s.Length;

Code copied from: How to get object size in memory?

Upvotes: 1

Kapil
Kapil

Reputation: 504

EXEC sp_spaceused 'tablename'

This will return a single result set that provides the following information:

Name - the name of the table

Rows - the number of rows in the table

Reserved - amount of total reserved space for the table

Data - amount of space used by the data for the table

Index_Size - amount of space used by the table's indexes

Unused - amount of usused space in the table

Upvotes: 3

Related Questions