Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do we calculate the Space used per row Expand / Collapse
Author
Message
Posted Monday, October 29, 2012 6:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 12:27 PM
Points: 224, Visits: 145
Hi Everyone,

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

Thanks
Post #1378216
Posted Monday, October 29, 2012 7:06 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
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??!

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
Posted Monday, October 29, 2012 7:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
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 »

Add to briefcase

Permissions Expand / Collapse