Know potential reduction in the size of the table before implementing VARDECIMAL

Before we actually go for implementing Vardecimal, we should know whether the table required Vardecimal.  It is clear if a table has no decimal or numeric field, there will be no saving, but even if you have table with decimal and numeric field whether implementing Vardecimal make any sense on storage?  There is no guarantee that you will be able to reduce the size of the table by enabling Vardecimal storage format.

SQL Server provides a stored procedure, to estimate the ‘reduction in row size’ with Vardecimal storage format. We will use this to know whether the table requires Vardecimal implementation.

Let stimulate a situation to know whether to implement Vardecimal by taking two table testWhetherNeedVarDecimal and testWhetherNeedVarDecimal2 and check which among these two require Vardecimal.

Let’s create first table testWhetherNeedVarDecimal  with two field of numeric and varchar type.

create table testWhetherNeedVarDecimal

(col1 numeric(18,2),

col2 varchar(10) default 10)

Now we have table in place let’s populate some data in it.

declare @cnt int

set @cnt=1

while (@cnt < 3000)

begin

      if (@cnt < 2000)

            insert into testWhetherNeedVarDecimal(col1) values(0)

      else

            insert into testWhetherNeedVarDecimal(col2) values((@cnt + 10))

      set @cnt +=1

end

We are ready with table1, now lets create another table testWhetherNeedVarDecimal2.

create table testWhetherNeedVarDecimal2

(col1 numeric(10,2),

col2 varchar(10) default 'asd')

Let’s populate testWhetherNeedVarDecimal2 with some data

declare @cnt int

set @cnt=1

while (@cnt < 3000)

begin

      if (@cnt < 2000)

            insert into testWhetherNeedVarDecimal2(col1) values(12345678.12) 

      set @cnt +=1

end

Now since both the table are ready, let’s try analyze which table requires Vardecimal by firing system stored procedure sp_estimated_rowsize_reduction_for_vardecimal

exec sp_estimated_rowsize_reduction_for_vardecimal 'testWhetherNeedVarDecimal'

avg_rowlen_fixed_format  avg_rowlen_vardecimal_format  row_count

-----------------------  ----------------------------  ---------

22.67                                   15.67          2999

exec sp_estimated_rowsize_reduction_for_vardecimal 'testWhetherNeedVarDecimal2'

avg_rowlen_fixed_format  avg_rowlen_vardecimal_format  row_count

-----------------------  ----------------------------  ---------

22.00                                   21.00          1999

So it is clear from the output that table testWhetherNeedVarDecimal requires Vardecimal, and by doing so user will be able to save (22.67-15.67) 7 byte per record which is significant enough to save.

Focus on code what really matters & letting go of the rest.

Post Reference: Vikram Aristocratic Elfin Share