4

I want to understand why migrating data in a table with all VARCHAR(50) fields to a table with optimized smaller types caused the new table (containing 61,065,164 rows) to be 4.46 GB, which is larger than the original table which is 3.1 GB. I expected the new table to be smaller, not larger.

  1. table1 structure to optimize which is created by usual Import wizard

    CREATE TABLE [dbo].table1(
    [dw_date_key] [varchar](50) NULL,
    [dw_OBFID] [varchar](50) NULL,
    [OBFID] [varchar](50) NULL,
    [account_link_code] [varchar](50) NULL,
    [dormant_0_6_total] [varchar](50) NULL,
    [dormant_7_13_total] [varchar](50) NULL,
    [dormant_14_20_total] [varchar](50) NULL,
    [dormant_21_28_total] [varchar](50) NULL,
    [dormant_0_15_total] [varchar](50) NULL,
    [dormant_16_30_total] [varchar](50) NULL,
    [dormant_0_30_total] [varchar](50) NULL,
    [dormant_31_60_total] [varchar](50) NULL,
    [dormant_61_90_total] [varchar](50) NULL,
    [val_total] [varchar](50) NULL,
    [return_dormancy] [varchar](50) NULL,
    [new_val_total] [varchar](50) NULL,
    [gross_adds] [varchar](50) NULL,
    [platform_movement] [varchar](50) NULL,
    [keep_my_no] [varchar](50) NULL,
    [sdp_snap_ma] [varchar](50) NULL,
    [contract_type] [varchar](50) NULL,
    [dormant_days] [varchar](50) NULL,
    [registration_date] [varchar](50) NULL,
    [activation_date] [varchar](50) NULL,
    [last_activity_date] [varchar](50) NULL,
    [last_platform_movement_date] [varchar](50) NULL,
    [create_dt] [varchar](50) NULL,
    [batch_id] [varchar](50) NULL,
    [val_returne] [varchar](50) NULL
    ) ON [PRIMARY]
    
  2. table2 which mostly benefits from 18 bit columns instead of 18 varchar columns

    CREATE TABLE dbo.table2
    (
    dw_date_key int not NULL,
    dw_OBFID bigint not NULL,
    OBFID bigint not NULL,
    account_link_code varchar(50) not NULL,
    dormant_0_6_total bit not NULL,
    dormant_7_13_total bit not NULL,
    dormant_14_20_total bit not  NULL,
    dormant_21_28_total bit not NULL,
    dormant_0_15_total bit not NULL,
    dormant_16_30_total bit not NULL,
    dormant_0_30_total bit not NULL,
    dormant_31_60_total bit not NULL,
    dormant_61_90_total bit not NULL,
    val_total bit not NULL,
    return_dormancy bit not NULL,
    new_val_total bit not  NULL,
    gross_adds bit not NULL,
    platform_movement bit not NULL,
    keep_my_no bit not NULL,
    sdp_snap_ma bit not NULL,
    contract_type varchar(1) not NULL,
    dormant_days smallint not NULL,
    registration_date int not NULL,
    activation_date int not NULL,
    last_activity_date int not NULL,
    last_platform_movement_date int not NULL,
    create_dt datetime not NULL,
    batch_id int not NULL,
    val_returne bit not NULL
    )  ON [PRIMARY]
    GO
    
  3. Sample data for all columns:

    20141110,989303710761,9303710761,8068176470,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,1,P,396,20120806,20120806,20131010,20131010,2014/11/13,18:41:48,470816,0

Notes:

  • both tables have no index and sizes are based on SSMS storage Data Space in table properties and index size is very small (Index Space = 0.023 MB)
  • SQL 2012 (11.0.5058.0) and recovery model are bulk-logged.
  • I have heard that bit type actually get benefits when you having large number of them in a table because each 8 one of them occupy 1 byte theatrically.
  • I run a datalength() for all my columns and here are the 2 result for 2 tables

    8,12,10,10,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,3,8,8,8,8,19,6,1

    table1 sample row sum = 118 bytes

    4,08,08,08,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,4,4,4,4,08,4,1

    table2 sample row sum = 76 bytes (even without considering bit space compensation which is displaced all with 1 byte here)

Main question:

If a sum up the data length result it says that I have reduced 118 bytes of each row to 76 for each row on average. That means 35% less.

But why I am not close to this number and the new table structure takes up more space?

My final goal is to automate the current manual ETL process using SSIS and optimize types to reduce the database size which is currently 400GB and will get larger weekly and also better query performance and better indexing instead of simple string types.

Any help is appreciated.

update

  • rebuild does not have any effect since there is no unused space
  • making all the fields as not null was possible and does not have any effect
  • output of the sp_spaceused for the main table with all varchar(50)

    rows reserved data index_size unused 61065164 3251096 KB 3250872 KB 24 KB 200 KB

  • this new table is all not null and i updated and is rebuild after insert into command and i updated the main table script with not null

    • rows reserved data index_size unused
    • 61065164 4925216 KB 4924872 KB 32 KB 312 KB
  • i tried SELECT COUNT(IIF(RTRIM(dw_date_key )= '', 1, null)) as suggested by @srutzky for all the columns in source table but there are no empty string or null except for the 2 columns of account_link_code and contract_type which are also varchar in final table

  • Did you import the data into table A, then ALTER TABLE to make it into table B? Try creating table B from scratch and then importing the data into it. – Jon of All Trades Jan 29 '15 at 16:32
  • If you'd like to compact the table where it stands, it looks like this is covered on SO: stackoverflow.com/a/808368/565869. – Jon of All Trades Jan 29 '15 at 16:34
  • In order to reclaim space used by reducing column widths, you'll need to rebuild. – Aaron Bertrand Jan 29 '15 at 17:10
  • @JonofAllTrades, thank you, I have exported them to another table in another UAT server. compression work great in this case but i am confused about the results and like to understand it – Iman Abidi Jan 29 '15 at 19:55
  • @AaronBertrand , would you please elaborate more about the rebuild because i have not reduce them inplace in the same table as i explained it to JonOffAllTrades – Iman Abidi Jan 29 '15 at 19:57
3

Interesting question, I created both table1 and table2 in SQL Server 2014 and populated 2000 rows of the same data you have provided. When checking the amount of space consumed by each table, here is what I got:

table1 (varchar) - 392K
table2 (bit) - 168K

However when I alter all the column types in table1 to match table2, the space used grew to 540K. After rebuilding the table, the space drops down to 160K:

alter table table1 rebuild;

So you should see the disk space savings using the leaner data types after reclaiming the used space. Best of luck.

  • You are correct in terms of the effect of ALTER TABLE. However, this answer is entirely incorrect as it does not relate to the question. Please read the comments on the question; the O.P. states that they specifically copied the data to a new table and did not do an ALTER TABLE. – Solomon Rutzky Jan 30 '15 at 13:29
3

Given that:

  • Table1 is all VARCHAR(50) NULL and Table2 (a separate table) is proper datatypes, also NULLable
  • Sample data in Table1 based on DATALENGTH appears to take up more space than in Table2, regardless of any optimization of BIT fields
  • We have no additional information regarding:
    • how many rows/fields have NULLs
    • if the DATALENGTH was taken from a single row, assumed to be representative OR if showing an average across all rows
  • Applying COMPRESSION does reduce the size

I am concluding that:

  • The DATALENGTH values are not showing an average
  • There are many rows/columns that are either:
    • NULL, or
    • fewer digits than the size of the numeric field in Table2 (i.e. between 1 and 7 digits for the 3 BIGINT fields)

This is explained by the facts that:

  • NULL / empty VARCHAR fields take up 0 bytes while NULL numeric / BIT fields always take up their fixed size
  • COMPRESSION (and the SPARSE option) reduces fixed-length NULL and 0 fields to 0 bytes. COMPRESSION also optimizes other aspects that SPARSE does not cover:
    • numeric and datetime types use the smallest type required to hold the value (i.e. a value of 23 in a BIGINT field will take up 1 byte as it fits into a TINYINT)
    • NCHAR / NVARCHAR employ Unicode Compression which fits strings into VARCHAR that can be held in VARCHAR without data-loss

Run the following test to see this behavior in action. The test is purposefully biased to use numbers with fewer digits than the number of bytes taken by the numeric types that they fit into. The test shows that not only is the table with proper numeric / bit types larger for the same rows and values, but doing a REBUILD after migrating the data increases the table even more (although only slightly).

CREATE TABLE dbo.SizeTest1
(
  [object_id] VARCHAR(50) NULL,
  [schema_id] VARCHAR(50) NULL,
  [parent_object_id] VARCHAR(50) NULL,
  [bit_field] VARCHAR(50) NULL
);

INSERT INTO dbo.SizeTest1 ([object_id], [schema_id], [parent_object_id], [bit_field])
  SELECT CONVERT(VARCHAR(50), sao1.[object_id]) AS [object_id],
         CONVERT(VARCHAR(50), sao1.[schema_id]) AS [schema_id],
         CONVERT(VARCHAR(50), sao1.[parent_object_id]) AS [parent_object_id],
         CONVERT(VARCHAR(50), NULL) AS [bit_field]
  FROM [msdb].sys.all_objects sao1
  CROSS JOIN [msdb].sys.all_objects sao2
  WHERE sao1.[object_id] BETWEEN -100000 AND 1000000;
-- 901,448 rows

EXEC sp_spaceused SizeTest1;

-- rows    reserved   data       index_size   unused
-- 901448  20104 KB   20072 KB   8 KB         24 KB

ALTER TABLE dbo.SizeTest1 REBUILD;

CREATE TABLE dbo.SizeTest2
(
  [object_id] BIGINT NULL,
  [schema_id] INT NULL,
  [parent_object_id] INT NULL,
  [bit_field] BIT NULL
);

INSERT INTO dbo.SizeTest2 ([object_id], [schema_id], [parent_object_id], [bit_field])
  SELECT CONVERT(BIGINT, st1.[object_id]) AS [object_id],
         CONVERT(INT, st1.[schema_id]) AS [schema_id],
         CONVERT(INT, st1.[parent_object_id]) AS [parent_object_id],
         CONVERT(BIT, st1.[bit_field]) AS [bit_field]
  FROM dbo.SizeTest1 st1;

EXEC sp_spaceused SizeTest2;

-- rows    reserved   data       index_size   unused
-- 901448  23240 KB   23192 KB   8 KB         40 KB

-- Initial size of table with proper numeric / BIT types is larger
--   than the table with VARCHAR(50)!

ALTER TABLE dbo.SizeTest2 REBUILD;

EXEC sp_spaceused SizeTest2;

-- rows    reserved   data       index_size   unused
-- 901448  23304 KB   23200 KB   8 KB         96 KB

-- Size of table with proper numeric / BIT types after REBUILD is even larger
--   than the initial size!

IF this question had been about in-place datatype changes via ALTER TABLE, then I have addressed that scenario in this answer: What effect will reducing the size of a varchar column have on the database file?

  • 1
    thank you, as i scan throw the records there is no massive null blocks and most cells have values. compression does have around 50% reduction when is estimated by page compression . sorry for late response and i will update you with better info soon. – Iman Abidi Jan 30 '15 at 16:51
  • @imanabidi Cool. Was I correct that the DATALENGTH values were for a single row, not an average? Also, having a value vs NULL eliminates the fields moving to BIT, but still doesn't help the BIGINT and INT fields if the number of digits for a row is less than the number of bytes for the numeric types (i.e. 1 - 7 digits for BIGINT and 1 - 3 digits for INT). I will update my answer in a moment with a test that illustrates this point. – Solomon Rutzky Jan 30 '15 at 16:58
  • yes just a sample row , but all rows have the same format but i will spend more on this and feedback – Iman Abidi Jan 30 '15 at 18:29
  • @imanabidi Regarding a single sample row used, that is what I figured. Regarding the same format, that is the only way it can be, but that is not the issue. The issue is the data in the field. A value of "100" in a VARCHAR(50) is 3 bytes, but that same value in BIGINT is 8 bytes. That is what DATALENGTH measures. – Solomon Rutzky Jan 30 '15 at 18:31
  • 1
    @imanabidi The diff between the tables is about 20 bytes per row. That could be 2 BIGINT fields and 1 INT field being NULL per row. But that's an average: some rows have no NULLs and some have more. You should set any of the fields that can never have NULLs as NOT NULL. Run this after filling out the rest of the columns in the same pattern (change field name and bytes per type): SELECT COUNT(IIF([dw_date_key] IS NULL, 1, 0)) * 4 AS [dw_date_key-NullBytes], COUNT(IIF([dw_OBFID] IS NULL, 1, 0)) * 8 AS [dw_OBFID-NullBytes],... FROM [table2];. Then you will have that piece of the puzzle. – Solomon Rutzky Jan 30 '15 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.