April 7, 2015 at 3:19 pm
Hi all,
I'm curious how SQL Server compresses integers. MSDN says:
Uses only the bytes there are needed. For example, if a value can be stored in a byte, storage will take only 1 byte
Does that mean that for a bigint, -9223372036854775808 to -9223372036854775553 takes one byte? Or that -127 to 128 takes one byte? Or some other weird storage schema?
April 7, 2015 at 4:22 pm
It means that even though you define the column as INT, if a given value would have fit within TINYINT, then that's what gets defined and stored for that value. It's not so much compression as the elimination of wasted space. Other than that, for fixed length fields like an INT, it doesn't do additional compression, at least that I've seen in the documentation or Kalen's book (I can't see the code).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 7, 2015 at 5:13 pm
Thanks, Grant. Explaining it as "whatever the smallest integer type it can fit into" gets to the core of what I was asking. Thanks.
FWIW, the reason I asked was that for an identity column starting at 1, you're essentially throwing away half the range.
In actuality, I'm working with bigints, and if it started counting from smallest to largest, it would make sense to start an identity at the smallest possible value, but since I don't think I'll get close to 18 quintillion records, it's probably more advantageous (given what you've told me) to start counting at 1, save the space, and half the number of values available.
April 7, 2015 at 5:21 pm
Yeah, that will reduce the number of values, but most people don't hit the issue. But, if you do, you can RESEED and set the seed to -1 and the increment to -1, and you're off again another umptybumptyilion numbers.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 8, 2015 at 4:29 pm
I'd expect value -127 to take 2 bytes, since smallint is the smallest data type that can hold that value. Thus, it would still save 2 bytes vs. an int and 6 bytes vs. a bigint.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply