newsequentialid() incrementing

• prabhurenga

  Valued Member

  Points: 69

  More actions

  May 1, 2015 at 11:21 pm

  #398400

  Hi,

  I got id value with newsequentialid() like below

  3564A919-18EF-E411-83A1-D4AE52C8DF89

  3664A919-18EF-E411-83A1-D4AE52C8DF89

  3764A919-18EF-E411-83A1-D4AE52C8DF89

  first two digits are incrementing sequential.

  I what to increment 17 to 20 digits with hexadecimal values like below

  3564A919-18EF-E411-0100-0EC241552704

  3564A919-18EF-E411-0200-0EC241552704

  3564A919-18EF-E411-0300-0EC241552704

  3564A919-18EF-E411-0400-0EC241552704

  3564A919-18EF-E411-0500-0EC241552704

  3564A919-18EF-E411-0600-0EC241552704

  3564A919-18EF-E411-0700-0EC241552704

  3564A919-18EF-E411-0800-0EC241552704

  3564A919-18EF-E411-0900-0EC241552704

  3564A919-18EF-E411-0A00-0EC241552704

  3564A919-18EF-E411-0B00-0EC241552704

  3564A919-18EF-E411-0C00-0EC241552704

  3564A919-18EF-E411-0D00-0EC241552704

  3564A919-18EF-E411-0E00-0EC241552704

  3564A919-18EF-E411-0F00-0EC241552704

  3564A919-18EF-E411-1000-0EC241552704

  3564A919-18EF-E411-1100-0EC241552704

  3564A919-18EF-E411-1200-0EC241552704

  3564A919-18EF-E411-1300-0EC241552704

  3564A919-18EF-E411-1400-0EC241552704

  3564A919-18EF-E411-1500-0EC241552704

  3564A919-18EF-E411-1600-0EC241552704

  3564A919-18EF-E411-1700-0EC241552704

  3564A919-18EF-E411-1800-0EC241552704

  3564A919-18EF-E411-1900-0EC241552704

  3564A919-18EF-E411-1A00-0EC241552704

  3564A919-18EF-E411-1B00-0EC241552704

  3564A919-18EF-E411-1C00-0EC241552704

  3564A919-18EF-E411-1D00-0EC241552704

  3564A919-18EF-E411-1E00-0EC241552704

  3564A919-18EF-E411-1F00-0EC241552704

  3564A919-18EF-E411-2000-0EC241552704

  Help me to solve task

  With regards

  Prabhu

• Eirikur Eiriksson

  SSC Guru

  Points: 182951

  More actions

  May 2, 2015 at 5:03 am

  #1795173

  Quick suggestion, generate the sequential bits separately and use the stuff function to merge it into the guid.

  😎

• Eirikur Eiriksson

  SSC Guru

  Points: 182951

  More actions

  May 2, 2015 at 8:09 am

  #1795177

  Quick example

  😎

  USE tempdb;

  GO

  SET NOCOUNT ON;

  GO

  /* SMALLINT MAX = 65535 */

  DECLARE @SAMPLE_SIZE INT = 65535;

  ;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

  , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

  SELECT

  STUFF('3564A919-18EF-E411-0100-0EC241552704',20,4,

  SUBSTRING(CONVERT(VARCHAR(6),CONVERT(BINARY(1),NM.N & 0xFF,0)

  + CONVERT(BINARY(1),(NM.N / 256) & 0xFF,0),1),3,4)) AS SEQ_GUID

  FROM NUMS NM;

  TOP(10) and LAST(10) Results

  SEQ_GUID

  ------------------------------------

  3564A919-18EF-E411-0100-0EC241552704

  3564A919-18EF-E411-0200-0EC241552704

  3564A919-18EF-E411-0300-0EC241552704

  3564A919-18EF-E411-0400-0EC241552704

  3564A919-18EF-E411-0500-0EC241552704

  3564A919-18EF-E411-0600-0EC241552704

  3564A919-18EF-E411-0700-0EC241552704

  3564A919-18EF-E411-0800-0EC241552704

  3564A919-18EF-E411-0900-0EC241552704

  3564A919-18EF-E411-0A00-0EC241552704

  -----

  3564A919-18EF-E411-F6FF-0EC241552704

  3564A919-18EF-E411-F7FF-0EC241552704

  3564A919-18EF-E411-F8FF-0EC241552704

  3564A919-18EF-E411-F9FF-0EC241552704

  3564A919-18EF-E411-FAFF-0EC241552704

  3564A919-18EF-E411-FBFF-0EC241552704

  3564A919-18EF-E411-FCFF-0EC241552704

  3564A919-18EF-E411-FDFF-0EC241552704

  3564A919-18EF-E411-FEFF-0EC241552704

  3564A919-18EF-E411-FFFF-0EC241552704

• Jeff Moden

  SSC Guru

  Points: 1004696

  More actions

  May 2, 2015 at 8:24 am

  #1795178

  prabhurenga (5/1/2015)

  ---

  Hi,

  I got id value with newsequentialid() like below

  3564A919-18EF-E411-83A1-D4AE52C8DF89

  3664A919-18EF-E411-83A1-D4AE52C8DF89

  3764A919-18EF-E411-83A1-D4AE52C8DF89

  first two digits are incrementing sequential.

  I what to increment 17 to 20 digits with hexadecimal values like below

  3564A919-18EF-E411-0100-0EC241552704

  3564A919-18EF-E411-0200-0EC241552704

  3564A919-18EF-E411-0300-0EC241552704

  3564A919-18EF-E411-0400-0EC241552704

  3564A919-18EF-E411-0500-0EC241552704

  3564A919-18EF-E411-0600-0EC241552704

  3564A919-18EF-E411-0700-0EC241552704

  3564A919-18EF-E411-0800-0EC241552704

  3564A919-18EF-E411-0900-0EC241552704

  3564A919-18EF-E411-0A00-0EC241552704

  3564A919-18EF-E411-0B00-0EC241552704

  3564A919-18EF-E411-0C00-0EC241552704

  3564A919-18EF-E411-0D00-0EC241552704

  3564A919-18EF-E411-0E00-0EC241552704

  3564A919-18EF-E411-0F00-0EC241552704

  3564A919-18EF-E411-1000-0EC241552704

  3564A919-18EF-E411-1100-0EC241552704

  3564A919-18EF-E411-1200-0EC241552704

  3564A919-18EF-E411-1300-0EC241552704

  3564A919-18EF-E411-1400-0EC241552704

  3564A919-18EF-E411-1500-0EC241552704

  3564A919-18EF-E411-1600-0EC241552704

  3564A919-18EF-E411-1700-0EC241552704

  3564A919-18EF-E411-1800-0EC241552704

  3564A919-18EF-E411-1900-0EC241552704

  3564A919-18EF-E411-1A00-0EC241552704

  3564A919-18EF-E411-1B00-0EC241552704

  3564A919-18EF-E411-1C00-0EC241552704

  3564A919-18EF-E411-1D00-0EC241552704

  3564A919-18EF-E411-1E00-0EC241552704

  3564A919-18EF-E411-1F00-0EC241552704

  3564A919-18EF-E411-2000-0EC241552704

  Help me to solve task

  With regards

  Prabhu

  Your turn, please. Why is this necessary? What is the business reason for this?

  --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)