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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply