I have a base table used everywhere called Customers. Of course, its unlikely that table will ever grow past int size, but it is keyed on a bigint nonetheless.

I don't believe that I can change the type of this identity column to int. But what is your advice for building stored procedures, table type variables etc that refer or carry data for this column? Should I perpetuate bigint in these routines? Will they run leaner if I use int, leaving it to the engine to run implicit conversions?

  • Implicit conversions can hurt: sqlskills.com/blogs/jonathan/… – dfundako Sep 27 '16 at 18:14
  • What is your concern with bigint to begin with? Like are you concerned with physical storage size versus int? From my perspective, bigint is 3 character more typing, but other than that, who cares? It all depends of course, for example if you have a humongous DB containing nothing but round numbers < max int, then you can save 50% space, but otherwise any difference is going to pale compared with the size of varchar data most likely – ajeh Sep 27 '16 at 21:16
  • @ajeh Just as an example... say I have stored procs called from a web app that push data in batch via TVP's. Now that %50 difference in size isn't saving space on disk... its saving space over the wire (or is it?)... which is a much more scarce resource. – cocogorilla Sep 27 '16 at 21:28
  • Realistically, what is your estimated storage difference in GB per year? – ajeh Sep 27 '16 at 21:38
  • @ajeh I don't care about storage. This may be a micro optimization. I appreciate you're trying to bring perspective to the question, but I'm still interested in the answer. Is there a real performance difference between pushing 100K ints vs 100K bigints over the wire to a stored proc via TVP? Is there a real memory savings? And if so, is that offset by the fact that many of those joins and work will involve implicit conversions due to the storage format? – cocogorilla Sep 27 '16 at 21:49

It is always a recommended practice to keep the parameters to be the same data type. This is to prevent potential implicit conversion issues with predicates which can lead to the query optimizer not being able to utilize statistics, or indexes, which leads to poorer performance, and can lead to serious CPU and storage performance issues.

  • 2
    Makes sense on the surface, but as the chart points out that dfundako linked to, ints and bigints can seek on each other's indexes. I think specific citations to justify your answer would help me accept it. – cocogorilla Sep 27 '16 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.