1

A multi-billion-row fact table in our database has 10 measures stored as int columns. The value ranges for some of these columns won't ever be above the +/-32K range of a smallint. To save I/O, we're investigating whether it's practical to store these columns as smallint instead of int.

But we're concerned about what problems might crop up from doing this, including:

  • SUM(SomeSmallInt) will frequently overflow, so many queries would have to be rewritten to cast these smallints to ints before aggregating, e.g. SUM(CAST(SomeSmallInt as int)).
  • This table is read in many places, so changing the data type of these columns might involve a lot of review, change, and testing.

So we're wondering if there's a lower-cost solution that would store as smallint but expose the colunms as ints to readers. Like this:

  • create smallint "storage" columns that the table's (only) writer will use, but otherwise no other client will care about.
  • to support existing readers, create int-typed computed columns that are the same names as the original int columns.

What are the pros and cons of this approach? What can go wrong? Is there a better way to reduce storage & I/O without causing problems with overflow and requiring existing readers to be rewritten?

  • 2
    Data compression should do exactly what you're suggesting - an int that fits in smallint will be stored as such, and then you get further benefits from dictionary... – Aaron Bertrand Sep 29 '15 at 18:44
  • I will second @AaronBertrand 's suggestion, provided you are on Enterprise Edition :). – Solomon Rutzky Sep 29 '15 at 18:58
  • Also, data compression will benefit the buffer space needed since compressed pages are stored in memory in a compressed state. Clearly, the indicator here is to ensure you are not already CPU-bound. – Max Vernon Sep 29 '15 at 19:04
  • In my opinion you are already doing the best thing, Store the data as a SMALLINT and when you need to aggregate it you can use the CAST inside the aggregate command (Casting up is a very quick command / much quicker then decompressing the data). Compressing the data will slow comparisons and other read write tasks so unless you really need the space i do not recommend this. Bottom line is, whats more important Speed or Disk space. – Paul Spain Sep 29 '15 at 19:12
  • I think CREATE VIEW would do what you're specifically asking for. Data compression would likely be a better option than changing data types though. – Dave Sep 29 '15 at 20:35
1

To prove the benefit of data compression for this question, I quickly built the following test:

USE tempdb;

CREATE TABLE dbo.IntTest
(
    IntTest_ID INT NOT NULL
        CONSTRAINT PK_IntTest
        PRIMARY KEY CLUSTERED
)
WITH (DATA_COMPRESSION = PAGE);

CREATE TABLE dbo.SmallIntTest
(
    SmallIntTest_ID SMALLINT NOT NULL
        CONSTRAINT PK_SmallIntTest
        PRIMARY KEY CLUSTERED
)
WITH (DATA_COMPRESSION = NONE);
GO

INSERT INTO dbo.IntTest (IntTest_ID)
SELECT TOP(65535) 
    CONVERT(SMALLINT, ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id, o2.object_id) - 32768)
FROM sys.objects o
    , sys.objects o1
    , sys.objects o2
ORDER BY o.object_id, o1.object_id, o2.object_id;

INSERT INTO dbo.SmallIntTest (SmallIntTest_ID)
SELECT TOP(65535) 
    CONVERT(SMALLINT, ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id, o2.object_id) - 32768)
FROM sys.objects o
    , sys.objects o1
    , sys.objects o2
ORDER BY o.object_id, o1.object_id, o2.object_id;


SELECT o.name
    , i.name
    , p.partition_number
    , p.rows
    , au.used_pages
    , au.total_pages
    , AvgRowsPerPage = p.rows / CONVERT(DECIMAL(10,2), au.used_pages) 
FROM sys.allocation_units au WITH (NOLOCK)
    INNER JOIN sys.partitions p WITH (NOLOCK) ON ((au.type = 1 OR au.type = 3) AND au.container_id = p.hobt_id) OR (au.type = 2 AND au.container_id = p.partition_id)
    INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
    INNER JOIN sys.objects o WITH (NOLOCK) ON p.object_id = o.object_id
    INNER JOIN sys.schemas s WITH (NOLOCK) ON o.schema_id = s.schema_id
WHERE s.name = 'dbo'
    AND o.name IN (
        'IntTest'
        , 'SmallIntTest'
    );

The output from the last query, showing actual pages used by each table, is:

+-----------------+-----------+-------+------------+-------------+-------------+
|      NAME       | PARTITION | ROWS  | USED_PAGES | TOTAL_PAGES | AVG ROWS    |
|                 | NUMBER    |       |            |             | PER PAGE    |
+-----------------+-----------+-------+------------+-------------+-------------+
| PK_IntTest      | 1         | 65535 | 92         | 97          | 712.3369573 |
| PK_SmallIntTest | 1         | 65535 | 92         | 97          | 712.3369573 |
+-----------------+-----------+-------+------------+-------------+-------------+

As you can see from the above results, compressed INTs containing SMALLINTs actually consume precisely the same amount of space.

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.