question

Michael 1 1 avatar image
Michael 1 1 asked

Any reason to use BigInt

I have a manager who is keeps on trying to sell me on making every identity a BigInt or GUID. These are mostly small databases that are for the very basics of a small business. I just don't see a reason why an Activity lookup table that would probably never be more then 20 or so records should be a BigInt or GUID. I've compromised and allowed myself to make all the ID's INT instead of making anything smaller even if it'll never be near to have 2 million records. Is there any argument I can use to convince him not to go down the route of making all the Identities monstrously huge. Then again I've about given up rationalizing things since he says "we don't need to fully normalize since we have plenty of space"

identityguid
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Nathan Skerl avatar image
Nathan Skerl answered

Your activity lookup table is a good example: Using the bigint vs. a tinyint is not going to make such a big difference on the lookup table (20 or so records), though think about the transactional tables that will be storing the foreign key. Those tables could be huge! Just do the math (tinyint = 1byte, bigint = 8 bytes). This is not just row size either but will affect index sizes as well. So, less space = faster backups, restores, index defrags, etc.

If that isn't convincing enough put a test together to show the performance gain of using the correct datatypes.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

As Nathan Skerl said for BigInt

GUIDs are not allocated sequentially, so will cause page splits in indexes. They are also not guaranteed to be unique, long odds, but its a bug-waiting-to-happen, and best avoided unless you really need cross-server unique keys, or to generate the key client-side before passing it to the server.

(There are sequentially allocated GUIDs available now, which help with indexes where an always-ascending sequence will result in 100% packing of the index pages).

Gross overkill IMHO. We don't use Nvarchar for anything that doesn't specifically need 2-byte character sets [like Chinese]; for European accents we just use regular varchar. That halves the storage ....

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.