CODEWITHSUNDEEP
t-sqlsyntaxuniqueidentifier
Ash Machine
Ash Machine

Reputation: 9921

Generating unique ids and transformation of unique id in TSQL

I am trying to update a SQL table by creating two new column values : a unique Id, and then a shortened integer version of the same unique id just created.

Using a trick I found here (see 'The NEWID() Way' towards bottom), this is what i thought would work :

Update Customer Set [UniqueId] = NEWID(), [UniqueIntegerId] = ABS(CAST(CAST([UniqueId] AS VARBINARY) AS INT))

but this generates something like

[UniqueId] [UniqueIntegerId]

3C79...5A4DEB2 754988032

1FD6...828B943 754988032

1F48...E80F511 754988032 <--- repeating! do not want!

What syntax is correct for trying to accomplish this?

Upvotes: 2

Views: 1377

Answers (1)

Andriy M
Andriy M

Reputation: 77737

That won't work as expected, because [UniqueId] in the expression is evaluated to a value before the update. This is what you could try instead:

DECLARE @uid uniqueidentifier;
UPDATE Customer
SET
  @uid = [UniqueId] = NEWID(),
  [UniqueIntegerId] = ABS(CAST(CAST(@uid AS VARBINARY) AS INT))

The @uid variable is assigned the same value as [UniqueId] and is then used instead of [UniqueId] in the expression for the other column.

Upvotes: 2

Related Questions