Post reply

How do we calculate the Space used per row

  • October 29, 2012 at 6:30 am

    #266133

    Hi Everyone,

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

    Thanks

  • October 29, 2012 at 7:06 am

    #1553712

    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 https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • October 29, 2012 at 7:17 am

    #1553714

    even for avg Data Size

    sp_spaceused TableName

    Output

    name rowsreserveddataindex_sizeunused

    TableName11 32 KB8 KB24 KB 0 KB

    then reserved/rows gives avg data size

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply