CODEWITHSUNDEEP
sql-serversql-server-2016data-warehouse
askolotl
askolotl

Reputation: 1006

Should we use bigint as an artificial key?

We want to use artificial keys in our data warehouse.

Is it a good idea to use bigint as data type for the artificial keys? I think because it's 64-bit, it can be very fast processed by the CPU. Or is this wrong?

Upvotes: 0

Views: 51

Answers (2)

Greg Low
Greg Low

Reputation: 1586

While your computer will have 64 bit registers, much of the challenge in performance is about fitting things into memory. It isn't helpful to your overall performance to make objects larger than they need to be. 64 bit systems do 32 bit arithmetic just fine.

int is a pretty useful data type, and even if you stick to positive values (which I suggest you do as they compress better), you still have well over 2 billion values. If you're dealing with values like customers or products, that's never going to be too small.

If you're at a gigantic site and deal with very large numbers of transactions, you might well want to use bigint for those.

Upvotes: 3

gotqn
gotqn

Reputation: 43666

I basically use the smallest type possible - in order to read less data when operations are performed.

So, depending on the range choose the best type for you. I guess INT will be small for data warehouse (remember when the YouTube counter was broken by the video hitting 2,147,483,647 views - the upper bound of the INT range.

How fast would be CPU processing if the key is BIGINT? Don't worry about such stuff - the processing depends on other factors - indexes, row-size, execution plans, is able the engine to process rows in batches and so on. How good is the engine with certain data types is your latest worry and even if it's slow, I doubt you can use INT because of the business requirements.

Upvotes: 3

Related Questions