NEWID() - How does it work?

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  February 25, 2013 at 4:51 pm

  #1590977

  Michael Valentine Jones (2/23/2013)

  ---

  Strictly speaking, a NEWID() would be converted to 2 BIGINTs, since a UNIQUEIDENTIFIER is 16 bytes, and a BIGINT is 8 bytes.

  Not sure what I was thinking, Michael. Thanks for the correction.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  February 25, 2013 at 6:16 pm

  #1590992

  Paul White (2/23/2013)

  ---

  I do wonder why anyone would covert a 16-byte uuid to a 36-character string though.

  I wonder where do you find someone who would not... :crazy:

  :hehe:

  _____________
  Code for TallyGenerator

• jeremysimmonsnet

  Newbie

  Points: 7

  More actions

  July 29, 2016 at 2:28 pm

  #1892722

  Casting binary(16) to a bigint will truncate it. bigint only has 8 bytes of storage.

  You must convert both the first and second sets of 8 bytes to a bigint to get the equivalent value.

  [font="Courier New"]DECLARE @uuid AS UNIQUEIDENTIFIER = NEWID()

  DECLARE @bits AS VARBINARY(16) = CAST(@uuid AS VARBINARY(16))

  SELECT

  CAST(@bits AS BIGINT) 'your answer',

  @bits 'all16bits',

  CAST(SUBSTRING(@bits, 1,8) AS BIGINT) leftBits,

  CAST(SUBSTRING(@bits, 9,8) AS BIGINT) rightBits

  guidaschar: 9E021A10-CD60-4275-A5E0-B208196B07B3

  your answer: -6493994914812328013

  all16bits: 0x101A029E60CD7542A5E0B208196B07B3

  leftBits: 1160242733253489986

  rightBits: -6493994914812328013

  [/font]