0

Similar to varchar that stores strings of varying length, is there an alternative for numbers of varying length that allocates space depending on the size of the number?

I have a large table with 50 columns holding about a billion rows including 15 numeric(10) columns. My quick proof of concept with 4 million showed that I can save a bit of disk-space by reducing the size of these 15 columns, hence this question. Please advise.

POC details - I reduced the column size from numeric(22) to numeric(5) for 15 columns with 3.5 mil rows and disk usage dropped from 1398.02 MB to 981.23 MB.
I will have to push this up to numeric(10) as size 5 will not suffice.

Note: numeric(22) was a bad design I inherited. Changing to integer data type reduced disk usage even further - 907.27 MB. Integer should help if our data model standards team allows this [since it contains no precision]. Will keep posted.

The server version and edition is

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun 17 2016 19:14:09 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

closed as unclear what you're asking by hot2use, Marco, Andriy M, mustaccio, James Anderson Aug 23 '17 at 13:43

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Neither vardecimal or compression are available to you. – Martin Smith Aug 23 '17 at 6:05
  • Good to know about vardecimal, thank you Martin. – rsreji Aug 23 '17 at 6:09
1

You can use row compression Row Compression Implementation that in designed to solve exactly this problem.

And you can also think about page compression that first applies row compression and then tries to achieve space savings using dictionary

Update: available also in Standard started with SQL Server 2016 SP1

  • Okay, thanks for the feedback, I'm missing out here on the version. – rsreji Aug 23 '17 at 6:08

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