Hi everyone!
In this quick tip, I'm going to talk about three techniques you can use to minimize the
size of your fact tables. When I say "fact table," I don't necessarily mean in a data
warehouse; it could be a larger table in an OLTP system that has smaller "code" tables
that act like dimensions.
It's advantageous for fact tables be as efficient as possible. This reduces data loading time,
and allows for more of the table to fit into memory at once, which makes queries run faster.
The first -- and most important -- technique is to carefully select the data types for
the columns in the table. This is something you should be doing anyway, but I can't
emphasize it enough because it's the foundation of how space-efficient your table will be.
In the fact table, there will be a set of foreign key columns that reference dimensions,
and then there will be a set of fact columns that will either be selected or aggregated.
The latter set I'm not going to cover here as it's a much larger discussion, but just
be aware of the size of the data types that you're using. For the set of foreign key columns,
focus instead on minimizing the size of the dimension table primary key columns, which
will get carried through to the fact table columns. Usually the primary key column of
these tables is either an integer type or a uniqueidentifier. Find the smallest data
type that will accommodate the range of values you need both now and in the foreseeable future.
Remember that you can also use negative values of the signed integer types.
When making changes, start by looking at the dimension tables that hold only a few rows,
but there are many rows referencing that table. The reason why you want to do this is to provide
the highest impact possible. It's really painful to see a uniqueidentifier column used as the
primary key in a dimension table that only holds a few rows. If a tinyint had been used
instead, this is one sixteenth of the storage required, and when you multiply that over
millions, or even billions of rows, the wasted storage adds up very quickly.
The second technique is to use Data Compression, which is an Enterprise Edition feature found
in SQL Server 2008 and higher. Of course, compressing data isn't free, and the trade-off
is increased CPU for decreased storage usage. Applying compression will also reduce access
speed to the table for both reads and writes. That isn't to say that the overhead is a lot
-- it isn't -- but it is measurable, and it may make a different in your environment.
When turning on Data Compression, there are two compression levels you can choose from:
ROW and PAGE. I'm not going to get into the technical differences here, but I do recommend
that you test both of them. If you're using table partitioning, you can selectively apply
compression to each of the partitions. It could be enabled for historical data that's
read-only, but left disabled for the active partition where most of the reads and writes
happen.
The third technique is to use SPARSE columns, which was also introduced in SQL Server 2008.
This feature, unlike Data Compression, is available in every edition. The idea of SPARSE
columns is to reduce the amount of storage required where there are a lot of NULLs in
a column. As the name implies, it's used on columns individually, not on the whole table.
The trade-off with this feature is that while NULL values take up zero space, non-NULL values
take up slightly more space than they would normally. Microsoft publishes a table with
rough guidelines for when to implement SPARSE columns, and I'll put a link to it in the
description. Be aware there are a few restrictions that you'll want to review before implementing
this feature. One that's relevant to this discussion is that, at least as of making
this video, SPARSE columns can't be used in combination with Data Compression.
To summarize, first look at and optimize the data types in the tables, which will help
in all scenarios. Then think about using SPARSE columns if you have a lot of NULLs, or use
Data Compression if you can use Enterprise Edition features.
If you found this video helpful, please click the Like button, and subscribe to the channel
to see more videos in the future.
Thanks for watching!