How do we calculate the Space used per row

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How do we calculate the Space used per row

How do we calculate the Space used per row

Rate Topic

Display Mode

Topic Options

Author

Message

krishnavamshi24

Posted Monday, October 29, 2012 6:30 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 9:00 AM
Points: 224, Visits: 141

Hi Everyone,

Just have a doubt is there any way that we could calculate the space used per row.

Thanks

Post #1378216

derek.colley

Posted Monday, October 29, 2012 7:06 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:40 AM
Points: 496, Visits: 583

It depends on the type of data you're storing. You will need a good handle on the datatypes used in SQL Server (start here: http://msdn.microsoft.com/en-us/library/ms187752.aspx ) to know the difference between, for example, CHAR and VARCHAR, TINYINT/SMALLINT/INT/BIGINT, etc. and how they store data differently.

There isn't really a magic bullet for this, it's just some calculation required on your part. There are some interesting articles out there on LOB-handling (off-row data storage) and storage required for header data. But I would start by simply calculating the average length of data in a given table, i.e. this would get an average amount of characters for a particular column in a table:

CREATE TABLE dbo.test_table_12345 ( uid INT, this VARCHAR(20))
INSERT INTO dbo.test_table_12345
VALUES (1, 'afklda'), (2, 'fkalsdjflasd'), (3, 'fjasd'), (4, 'fkadlsjfladsjf'), (5, 'dalfj') 

SELECT AVG(LEN(this)) FROM dbo.test_table_12345

Then depending on the data type, you can work out byte storage costs if that's what you want, or aggregate it for multiple columns.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??! Crazy

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Post #1378234

BriPan

Posted Monday, October 29, 2012 7:17 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, March 01, 2013 6:16 AM
Points: 81, Visits: 286

even for avg Data Size

sp_spaceused TableName

Output
name rows reserved data index_size unused
TableName 11 32 KB 8 KB 24 KB 0 KB

then reserved/rows gives avg data size

Post #1378237

« Prev Topic | Next Topic »

Permissions