Avoiding data normalization with data compression

December 26, 2018
Been a ColdFusion Developer since 1996
Wizard 21 posts
Followers: 11 people
Web Developer
4

Avoiding data normalization with data compression

Been a ColdFusion Developer since 1996
Wizard 21 posts
Followers: 11 people
December 26, 2018
I am going to argue against data normalization and I am going to be using traffic tracking as an example. I am going to introduce some better approaches to get the job done. I was thinking of all kinds of titles for this video
  • Don’t normalize that data
  • Intro to DB Compression
  • Normalization is not the answer
  • Stop wasting your time with normalization
  • Traffic tracking for the rest of us
  • How I learned to stop worrying and learn to love data compression
  • My cat’s data is not normal, and neither is yours

Resources

Comments (4)
2018-12-26 16:33:22
2018-12-26 16:33:22

Good approach!  One thing to note about using data compression in SQL Server, if your table does not have a clustered index on it, compression may not behave like you would expect.

When compressing a Heap (a table w/o clustered index), SQL Server will only compress the data that is currently there, and not compress any future incoming data.  You will have to periodically rebuild the Heap by removing and reapplying compression.  This is not the case with a clustered index table, once you apply compression it will continue to compress new incoming data.

Also, adding or removing compression to a Heap forces all nonclustered indexes on the table to also be rebuilt behind the scenes so that they have pointers to the new row locations in the Heap.  However, on tables with clustered indexes the table and nonclustered indexes can be compressed and rebuilt independently from each other.

Like
(3)
>
Eric Cobb
's comment
2018-12-26 17:20:22
2018-12-26 17:20:22
>
Eric Cobb
's comment

Interesting? When I ran the

EXEC sp_estimate_data_compression_savings ‘dbo’, ‘Traffic’, NULL, NULL, ‘NONE’ ;

I thought that was confirming that the compression was working. Is there a better way verify that the table compression is working?

 

 

 

Like
>
James Mohler
's comment
2018-12-26 22:16:30
2018-12-26 22:16:30
>
James Mohler
's comment

sp_estimate_data_compression_savings just provides an estimate of what the table size would be if you applied the specified compression level.  (typically “NONE”,”PAGE”, or “ROW”)

You can run the below query to determine whether or not compression is enabled on a table or index, and whether the object is clustered, nonclustered, or a heap.  How it “works” is dependent upon whether the table is clustered or a heap.  That’s why I posted my original comment, because MS isn’t always clear on what’s going on and it’s hard to know that the same compression settings works differently on each.

SELECT DISTINCT SCHEMA_NAME(o.schema_id) + ‘.’ + OBJECT_NAME(o.object_id) AS TableName,
i.name AS IndexName, p.data_compression_desc AS CompressionType, i.type_desc AS StorageType
FROM sys.partitions p
INNER JOIN sys.objects o ON p.object_id = o.object_id
JOIN sys.indexes i ON p.object_id = i.object_id AND i.index_id = p.index_id
WHERE p.data_compression > 0
AND SCHEMA_NAME(o.schema_id) <> ‘SYS’

 

Like
>
Eric Cobb
's comment
2018-12-27 02:13:07
2018-12-27 02:13:07
>
Eric Cobb
's comment

I think you are right on this one.

I have updated Github to have an index on dbo.Traffic.

https://github.com/jmohler1970/DB_Compression

Thanks for the feed back

 

 

 

 

Like
Add your comment