Guid into NewId column in SQL Server

Question

I'm currently trying to add a unique Guid property on row insert in SQL Server. I am doing this via Entity Framework 6. To add the column to the table, I used the following query:

 ALTER TABLE dbo.Reservation ADD ConfirmationID UNIQUEIDENTIFIER  DEFAULT (NEWID()) WITH VALUES

Whenever I create a new row in this table, the NEWID() is always set to 00000000-0000-0000-0000-000000000000. I have tried to create a new Guid for the property in C# before my insert, however this returns the same result.

Here is the C# code I am calling as part of the insert function:

public ReservationClientModel Create(ReservationClientModel clientModel)
    {
        var reservation = new Reservation();
        reservation = Mapper.Map<ReservationClientModel, Reservation>(clientModel, reservation);
        reservation.ConfirmationID = new Guid();

        _repository.Add(reservation);

        _context.Commit();

        return Mapper.Map<ReservationClientModel>(reservation);
    }

Here is the code generated from EF as seen from EF Profiler:

INSERT [dbo].[Reservation]
   ([RoomID],
    .....
    .....
    [ConfirmationID])

VALUES (15 /* @0 - [ID] */,
    .... 
    ....
    '00000000-0000-0000-0000-000000000000' /* @13 - [ConfirmationID] */)

Is there any way I can set this property to add a NEWID() on insert, without setting the property in C# before hand?

Are you using `DEFAULT` in the insert? `values(1, 2, DEFAULT)` ? — Alex K., Jun 17 '15 at 13:01
you are also calling default on the guid, get rid of default — Brino, Jun 17 '15 at 13:01
@Brino: This question is about a SQL statement, not about C# code. — stakx - no longer contributing, Jun 17 '15 at 13:06
This might help. http://stackoverflow.com/questions/29642068/how-to-get-ef-6-to-handle-default-constraint-on-a-database-during-insert — ughai, Jun 17 '15 at 13:09
@Brino This actually works, but ideally I'd like the value to be set in the SQL end, rather than in C# — moikey, Jun 17 '15 at 15:03

score 0 · Answer 1 · answered Jun 17 '15 at 13:05

0

This works for me:

CREATE TABLE #temp(id int, val uniqueidentifier DEFAULT (NEWID()) )

INSERT INTO #temp(id) values(1)
SELECT * FROM #temp

DROP TABLE #temp

But beware if those confirmationId should be your primary key in your design. This would be very bad and will perform very bad.

answered Jun 17 '15 at 13:05

Ionic

3,884
1
12
33

The issue is when inserting using EF – ughai Jun 17 '15 at 13:07
yes but would should be different? If it's inside the dbms, it should work the same. – Ionic Jun 17 '15 at 13:14
Your comment about performance is not true. A GUID as a primary key can perform just fine. It is when the GUID is the clustered index that the performance goes downhill. – Sean Lange Jun 17 '15 at 13:15
Yes and if you poorly use the SSMS - which most beginners do - the system takes the primary as template for the clustered index indirectly. ;-) I just wanted to warn before the performance is decreased. ;-) – Ionic Jun 17 '15 at 13:18
Nothing wrong with warning. I was clarifying because your statement was not true. Any table that has a primary key defined will by default also be the clustered index unless you explicitly state that it should be nonclustered or if a clustered index already exists. – Sean Lange Jun 17 '15 at 13:32
@Ionic - if the application passes `00000000-0000-0000-0000-000000000000` your default constraint on the database side will be ignored. OP wants to exclude the column using EF – ughai Jun 17 '15 at 13:39
Ah ok. Sorry didn't get that. Well another idea is to set a after insert trigger which will react on this case and generate a new guid for this. – Ionic Jun 17 '15 at 13:40
@Ionic, That is a good idea, but I will try and see if it is doable as part of the transaction first. – moikey Jun 17 '15 at 13:43
Reasonable yes. Because trigger could have side-effects. But if this fails, I would suggest the trigger. Do you get it by your self with the trigger or do you need a example? – Ionic Jun 17 '15 at 13:44
@Ionic, Yeah I can implement a trigger if need be, thanks though! – moikey Jun 17 '15 at 13:47

Guid into NewId column in SQL Server

1 Answers1