Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How do we calculate the Space used per row Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, October 29, 2012 6:30 AM
 SSC Veteran Group: General Forum Members Last Login: Today @ 3:56 AM Points: 225, Visits: 196
 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 Group: General Forum Members Last Login: Thursday, October 1, 2015 5:02 AM Points: 496, Visits: 603
 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_12345VALUES (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) == 1So 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.comVisit http://www.DerekColley.co.uk to find out more about me.
Post #1378234
 Posted Monday, October 29, 2012 7:17 AM
 SSC Journeyman Group: General Forum Members Last Login: Friday, January 31, 2014 4:06 AM Points: 83, Visits: 296
 even for avg Data Sizesp_spaceused TableNameOutput name rows reserved data index_size unusedTableName 11 32 KB 8 KB 24 KB 0 KBthen reserved/rows gives avg data size
Post #1378237

 Permissions