ALTER procedure [sys].[sp_db_vardecimal_storage_format]
@dbname sysname = NULL, -- database name to change
@vardecimal_storage_format varchar(3) = NULL -- vardecimal format to turn on/off
as ...
Great, but this enables the db not the table… a bit more Master DB diving turned up:
ALTER procedure [sys].[sp_tableoption]
@TableNamePattern nvarchar(776)
,@OptionName varchar(35)
,@OptionValue varchar(12)
as ...
For reduction estimation use:
ALTER procedure [sys].[sp_estimated_rowsize_reduction_for_vardecimal]
@table_name nvarchar (776)
as ...
So next thing to find is what option to set:
exec sys.sp_tableoption 'tableName', 'vardecimal storage format', 'on'
so how well does it do? here’s some code:
set nocount on
go
create database varDecimalON
go
create database varDecimalOFF
go
-- enable vardecimal storage format for varDecimalON Dababase
exec sp_db_vardecimal_storage_format 'varDecimalON', 'on'
go
use varDecimalON
if object_id('t1') is not null
drop table t1
create table t1
(
col1 decimal(18, 12),
col2 decimal(18, 12)
)
-- enable vardecimal storage format on t1 in varDecimalON database
exec sys.sp_tableoption 't1', 'vardecimal storage format', 'on'
declare @i int
set @i = 1
-- insert 100000 rows with 2 decimal places
while @i <= 100000
begin
insert into t1(col1, col2)
select convert(decimal(18, 2), rand()*1000000),
convert(decimal(18, 2), rand()*1000000)
set @i = @i + 1
end
set @i = 1
-- insert 100 rows with 12 decimal places
while @i <= 100
begin
insert into t1(col1, col2)
select convert(decimal(18, 12), rand()*1000000),
convert(decimal(18, 12), rand()*1000000)
set @i = @i + 1
end
go
select 'space used in varDecimalON Database'
exec sp_spaceUsed 't1', 'true'
/*
-- results for sp_spaceUsed
name rows reserved data index_size unused
t1 100100 2632 KB 2592 KB 8 KB 32 KB
*/
go
use varDecimalOFF
if object_id('t1') is not null
drop table t1
create table t1
(
col1 decimal(18, 12),
col2 decimal(18, 12)
)
declare @i int
set @i = 1
-- insert 100000 rows with 2 decimal places
while @i <= 100000
begin
insert into t1(col1, col2)
select convert(decimal(18, 2), rand()*1000000),
convert(decimal(18, 2), rand()*1000000)
set @i = @i + 1
end
-- insert 100 rows with 12 decimal places
set @i = 1
while @i <= 100
begin
insert into t1(col1, col2)
select convert(decimal(18, 12), rand()*1000000),
convert(decimal(18, 12), rand()*1000000)
set @i = @i + 1
end
go
select 'space used in varDecimalOFF Database'
exec sp_spaceUsed 't1', 'true'
/*
-- results for sp_spaceUsed
name rows reserved data index_size unused
t1 100100 2824 KB 2816 KB 8 KB 0 KB
*/
use master
drop database varDecimalON
drop database varDecimalOFF
set nocount off
=========================================================
Zaenkrat se mi še sanja ne, kaj pomeni zgornja koda, vam bom pa znal povedat čez dva meseca :)
Komentarji(1) 
