9

Is there a way to get the max value that can be stored in a bigint, without hardcoding it?

Is there a function or a constant that returns/contains this value?

Corovei Andrei
  • 1,646
  • 6
  • 28
  • 42

3 Answers3

18

A bigint is always going to support

-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

SQL Server TSQL does not define this as a constant, but it is always going to be -2^63 to 2^63 - 1

Ref.: int, bigint, smallint, and tinyint (Transact-SQL)

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • 2
    a bigint is always going to be 8 bytes. ALWAYS. – Mitch Wheat Aug 17 '11 at 12:34
  • so what exactly are you doing with this bigint? – Mitch Wheat Aug 17 '11 at 12:35
  • 7
    @Corovei - they will add a new datatype like `HUGEint` before they change an existing datatype. – JNK Aug 17 '11 at 12:36
  • 1
    What I wanna do with the bigint is out of the purpose of my question. I just wanted to find its limits. – Corovei Andrei Aug 17 '11 at 12:37
  • 2
    @Corovei Andrei: experience shows that sometimes people don't ask the right question... – Mitch Wheat Aug 17 '11 at 12:37
  • 2
    @JNK: exactly. MS have not broken a datatype in twenty years AFAIK. – Mitch Wheat Aug 17 '11 at 12:38
  • 4
    @Corovei Andrei Such a change is highly unlikely for the reason you described yourself - Microsoft will rather create a new integer type (when a new date type was added, `datetime` was left intact and a new `datetime2` type was created). – Marek Grzenkowicz Aug 17 '11 at 12:39
  • @corovei andrei : Here is another way to look at it. Question - "How does one deal with the possibility that the size of a datatype may change?" Answer - "Institute a guarantee that it won't". And from the feed back you are receiving, repeatedly, you can now be certain that a BIGINT won't change in size. – MatBailie Aug 17 '11 at 13:02
8

See the answer provided in this similar question. There is no way, as far as I know, to programmatically find the answer you're looking for.

Based on the comments you posted on another answer, this would allow you to only have to change your values in one place, as opposed to multiple places.

Community
  • 1
  • 1
Sam DeHaan
  • 10,246
  • 2
  • 40
  • 48
5

You could also create a simple user defined function that returns the max bigint value:

CREATE FUNCTION maxbigint() RETURNS bigint
AS 
BEGIN
    RETURN CAST(0x7FFFFFFFFFFFFFFF AS bigint)
END
GO

Then you can use it wherever you want by invoking it: dbo.maxbigint().

Elian Ebbing
  • 18,779
  • 5
  • 48
  • 56
  • you can cast directly instead of using HEX. `RETURN CAST(9223372036854775807 as bigint)` – Venkataraman R Mar 27 '19 at 10:15
  • @VenkataramanR What makes you feel like casting (0x7FFFFFFFFFFFFFFF) isn't "casting directly"? ;-) It also has the advantage that it clearly, visually represents an edge value whereas 9223372036854775807 just looks like a bunch of random digits. – Craig Tullis Mar 03 '23 at 20:54
  • @CraigTullis, I get your point. Hex is showing as edge value. But, as function itself tells that it is maxBigInt, if we put BIGINT value directly it will be easier to understand that it is the max BIGINT value. Hex convert is bringing one intermediate layer. – Venkataraman R Mar 04 '23 at 11:20
  • 1
    @VenkataramanR Hex is NOT bringing an intermediate layer. That's what I'm actually saying. 0x7FFFFFFFFFFFFFFF is no less valid of a numeric representation for the machine than 9223372036854775807. The value 0x7FFFFFFFFFFFFFFF is a big integer. It isn't converted to a bigint any more than 9223372036854775807 is. An additional benefit is that just by looking at it you can see that it is at the far end of the range. – Craig Tullis Mar 09 '23 at 17:23
  • @CraigTullis, agree with you. thanks for your detailed comment. appreciate your insights. – Venkataraman R Mar 10 '23 at 09:02