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: https://stackoverflow.com/q/11974939/196526 — Bastien Vandamme, Oct 17 '17 at 02: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, Oct 17 '17 at 03: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, Oct 17 '17 at 03: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, Oct 17 '17 at 03: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, Oct 17 '17 at 03:20
Does this solve it? https://stackoverflow.com/questions/3847795/linq-to-sql-handle-newid — Element Zero, Oct 17 '17 at 03:31
Also: https://stackoverflow.com/questions/17652396/entity-framework-default-values-doesnt-set-in-sql-server-table — Element Zero, Oct 17 '17 at 03:42

Michael G · Accepted Answer · 2017-10-17T03:11:54.120

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 '17 at 03:11

answered Oct 17 '17 at 03:09

Michael G

6,695
2
41
59

I believe, Check constraint is used for verifying incoming input. Can you please elaborate on this line : `[Id] <> CAST(0x0 AS UNIQUEIDENTIFIER)` – Akash KC Oct 17 '17 at 03:12
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). – Element Zero Oct 17 '17 at 03:14
I misunderstood the question. I'll update with a new answer shortly. – Michael G Oct 17 '17 at 03:15
1

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. – Bastien Vandamme Oct 17 '17 at 03:22
I don't think you're able to do this with a constraint or default value. Triggers are your only option here. – Michael G Oct 17 '17 at 03:31
I have exactly this problem, I don't know why newId() dosent work – Esi Jun 09 '18 at 11:35

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

1 Answers1