How to use default NEWID()/NEWSEQUENTIALID() with Guid/uniqueidentifier?

Question

I have this table:

CREATE TABLE [dbo].[SandTable]( [Id] [uniqueidentifier] NOT NULL, [Date] [date] NULL, CONSTRAINT [PK_SandTable] PRIMARY KEY) ALTER TABLE [dbo].[SandTable] ADD CONSTRAINT [DF_SandTable_Id] DEFAULT (NEWID()) FOR [Id]

Question is not about using NEWID() vs NEWSEQUENTIALID().

I use linqPad to test the table.

SandTables.InsertOnSubmit(new SandTable { // I don't provide any value for Id Date = DateTime.Now }); SubmitChanges();

My initial idea was to create an Id column that is able to initialize itself to a value when no Id is provided but will use the id provided when one is provided.

But because Guid is a struct, not a class the Id is never null, Id is initialized to his default value (00000000-0000-0000-0000-000000000000). So SQL server consider that Id has always a value and then the NEWID() default instruction is never called.

Is it possible to force the call to NEWID() on specific value? Should I use a trigger to evaluate the value of Id and when it's (00000000-0000-0000-0000-000000000000) then call NEWID()? What are the solutions or workaround?

Here is a similar question, interesting but not responding to my specific question: stackoverflow.com/q/11974939/196526 — Bastien Vandamme
– Bastien Vandamme, Commented Oct 17, 2017 at 2:48
Perhaps a dumb question but if you know you are going to be inserting - why not create the GUID in your code with id=Guid.NewGuid()? Because you want SQL to create it? — Element Zero
– Element Zero, Commented Oct 17, 2017 at 3:09
Also is using a stored procedure for the insert an option? Then you could just check whether the guid was empty and generate a new one if so and perform the insert. — Element Zero
– Element Zero, Commented Oct 17, 2017 at 3:17
For compatibility reason because I'm in a situation where we are migrating an application. The old application INSERT new data from non optimized Stored Procedure. I cannot tell you how many version of INSERT we have. The new application use C# code. So the new application can create a Guid but I cannot revise all old query. — Bastien Vandamme
– Bastien Vandamme, Commented Oct 17, 2017 at 3:18
I use Entity Framework in C# code. I prefer to avoid having to use stored procedure. For maintenance reason I would like to keep all logic in C#. (I also simply do not trust my DBA team) — Bastien Vandamme
– Bastien Vandamme, Commented Oct 17, 2017 at 3:20

Michael G · Accepted Answer · 2017-10-17 03:11:54Z

2

You can do it with a check constraint:

ALTER TABLE [dbo].[SandTable] ADD CONSTRAINT [DF_SandTable_Id] DEFAULT (NEWID()) FOR [Id] ALTER TABLE [dbo].[SandTable] WITH CHECK ADD CONSTRAINT [CK_SandTable_Id_Empty] CHECK ( [Id] <> CAST(0x0 AS UNIQUEIDENTIFIER) )

edited Oct 17, 2017 at 3:11

answered Oct 17, 2017 at 3:09

Michael G

6,7752 gold badges45 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Akash KC Over a year ago

I believe, Check constraint is used for verifying incoming input. Can you please elaborate on this line : [Id] <> CAST(0x0 AS UNIQUEIDENTIFIER)

Element Zero Over a year ago

This would block the insert, I believe he wants it to just generate NewID() IF the passed in id is 0x0 (I could be wrong).

Michael G Over a year ago

I misunderstood the question. I'll update with a new answer shortly.

Bastien Vandamme Over a year ago

I confirm with LinqPad shen I insert a first line it work. I get the default Id and then for the next insert I get the classic "SqlException: Violation of PRIMARY KEY constraint 'PK_SandTable'. Cannot insert duplicate key in object 'dbo.SandTable'. The statement has been terminated." Logic.

Michael G Over a year ago

I don't think you're able to do this with a constraint or default value. Triggers are your only option here.

|

Collectives™ on Stack Overflow

How to use default NEWID()/NEWSEQUENTIALID() with Guid/uniqueidentifier?

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related