Follow

How to add a default value to a uniqueidentifier field

The SQL Server functions to assign a new value to a uniqueidentifier field are NEWID and NEWSEQUENTIALID.

In a SQL Server User-Defined Function (UDF) you are not allowed to use side-affecting functions/operators. NEWID and NEWSEQUENTIALID are both causing side-effecting, whereas i.e. GETDATE is not. When using i.e. NEWID in a UDF, SQL Server throws an exception like "Invalid use of a side-effecting operator 'newsequentialid' within a function."

Transformations in tX is wrapped in UDF's, thus according to the above it does not allow the use of any of the two functions to assign a new uniqueidentifier value during transformation. There are at least a couple of ways to design around this.

Option one

  1. Add a new custom field to the table of type uniqueidentifier
  2. Add a default transformation, using NEWID/NEWSEQUENTIALID
  3. Add a transformation and condition to the destination field, assigning it the value of this new field when the other is empty

Please be aware that default values are not assigned to custom rows (custom data), due to the use of a SQL bulk copy operation. During a bulk copy nulls and defaults are not assigned, to allow keeping nulls from the source.

Option two

  1. Inject a custom view in between the source and destination table
  2. Add a CASE construct to assign a NEWID/NEWSEQUENTIALID to the field when empty

The following is an example of using a CASE for this purpose

SELECT
[DW_Id],
CASE
WHEN [My GUID] IS NULL THEN NEWID()
ELSE [My GUID] END AS [My GUID]
FROM [Some Owner].[Some Table]

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.