SQL Server UInt64 or decimal(20,0)/decimal(28,0) RRS feed

  • Question

  • I need to store Social network ID in database. One of 2 supported social network for solution use unsigned 64-bit user identifier.

    Now I'm using decimal(28,0). Is it correct?

    For example I need to store ID='18445016066725229233'

    May be System.UInt64 is supported?

    • Moved by Vicky SongMicrosoft employee Thursday, January 13, 2011 5:47 AM (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Tuesday, January 4, 2011 6:19 PM

All replies

  • SQL Server does not support unsigned integers as a data type, for example BigInt maps to Int64, not to UInt64, so if you need to deal with this you have some options, one is to use decimal, or you can simple store it in a binary(n) column. Depends on you needs and application usage. If it is only used as a key, personally I would go with binary(n), but this assumes you are only performing equality comparison and no arithmetical operations on the value. 

    GertD @ www.DBProj.com
    Tuesday, January 4, 2011 8:22 PM
  • I test binary(n) and it have very strange behavior

    If I insert number 17592186044415 it store 0x0E00.0001.FFFF.FFFF. But hex representation of 17592186044415 is FFF.FFFF.FFFF

    If I insert number 0xFFFFFFFFFFF in hex format it store 0x0FFFFFFFFFFF0000


    INSERT INTO [db].[dbo].[Table_1]
         VALUES (17592186044415 )


    INSERT INTO [db].[dbo].[Table_1]

    Thursday, January 6, 2011 12:00 AM
  • It would really help if you post the table structure, without it there is not much to do.


    declare @n numeric(28,0) = 18445016066725229233
    declare @b binary(17) = convert(binary(17), @n)
    select @n AS 'numeric', @b as 'binary', convert(numeric(28,0), @b) as 'binary2numeric'
    Results in:
    numeric	        binary	                binary2numeric
    18445016066725229233	0x00000000001C000001B1B2870563DCF9FF	18445016066725229233


    GertD @ www.DBProj.com
    Thursday, January 6, 2011 4:37 AM
  • I created a test table with two columns (bigint,binary(8)) and experimented with it.

    You sample show me the problem. In .NET Framework


    Can be stored in 64bits (8 bytes). In SQL Server it takes 12 bytes in you example.

    You can try to change

    (28,0)to numeric

    It will give a different representation of number. I decide to use decimal(x,0) because it give normal representation of number.

    I'm still searching for solution to store UInt64 in 8-byte column. I need only searching values and equality comparison

    Thursday, January 6, 2011 6:45 AM
  • SQL Server does support unsigned integers as a date type.

    For example: tinyint (0..255)

    Wednesday, February 14, 2018 8:29 PM