How to INSERT into a table that uses sequential GUIDs as a primary key?

Question

Here is a simplified version of the table I am looking at:

CREATE TABLE [dbo].[FrustratingTable]
(
    [Id] Uniqueidentifier NOT NULL
    , [SecondField] [datetime]
    , [ThirdField] varchar(128)
)

I want to insert new records into this table. I have tried 3 approaches:

INSERT INTO [dbo].[FrustratingTable] (Id, SecondField, ThirdField)
    SELECT newid() as Id, 
           '6/25/2015' as SecondField, 'Example' as ThirdField

This approach inserts, but the resulting key isn't a nice sequential GUID like the other ones in the table

INSERT INTO [dbo].[FrustratingTable] (Id, SecondField, ThirdField)
SELECT NEWSEQUENTIALID() as Id, '6/25/2015' as SecondField, 'Example' as ThirdField

This fails with error

The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

INSERT INTO [dbo].[FrustratingTable] (SecondField,ThirdField)
SELECT '6/25/2015' as SecondField, 'Example' as ThirdField

This fails with the error

Cannot insert the value NULL into column 'id', table 'mydatabase.dbo.frustratingtable'; column does not allow nulls. INSERT fails.

Is it possible to solve this without altering the table definition?

when you say "resulting key isn't a nice sequential GUID..." can you provide an example? How were the existing guids created in the table? Via `newId()`? — Kritner, Jun 26 '15 at 12:44
Not using newid(), not created by me. The values in there are all end with the same 20 characters, but the first 16 vary. — Greg Viers, Jun 26 '15 at 12:46
possibly related: http://stackoverflow.com/questions/5585307/sequential-guids - could the existing table rows have been created via such a method? — Kritner, Jun 26 '15 at 12:48
`newsequentialid()` can *only* be used in a `DEFAULT` constraint, as your error message and [the MSDN documentation states](https://msdn.microsoft.com/en-us/library/ms189786.aspx). So **no** - without changing your table structure, you **cannot** fix this — marc_s, Jun 26 '15 at 12:49
@Kritner that seems to be the kind of approach I want, but that's talking about a C# function. Is there any way to get a similar behaviour from a SQL statement? — Greg Viers, Jun 26 '15 at 12:53
as marc_s stated, it sounds like you could add a default constraint to that column - though that would be altering the table :/ — Kritner, Jun 26 '15 at 12:55
also I don't know if they would look like a completely separate sequence from original set in your table or not, I've not had any reason to have sequential guids before... is there a specific reason to use those over a normal guid, or perhaps even an identity column? — Kritner, Jun 26 '15 at 12:56
@TheTTGGuy you could *possibly* use one of the C# solutions in the mentioned thread in my above comment, turn it into a CLR function, and reference the function when doing an insert. This would *not* require a modification to the table... but seems extremely complicated for problem which I'm a little confused as to why it's a problem in the first place (e.g. why do you need sequential guids) — Kritner, Jun 26 '15 at 13:04
Is it actually the PK, the code you show doesn't say it is or isn't, e.g. PRIMARY KEY CLUSTERED ([Id] ASC blah blah) ? — Paul Zahra, Jun 26 '15 at 13:33
@PaulZahra OP stated that was attempted in the question body. It is not a viable solution for OP because it isn't a sequential GUID. — Kritner, Jun 26 '15 at 13:37
See http://www.sql-server-helper.com/error-messages/msg-302.aspx - DEFAULT constraint only! — Paul Zahra, Jun 26 '15 at 13:45
Don't change the network card on the server, if the sequential nature really matters. — Jodrell, Jun 26 '15 at 14:29

Damien_The_Unbeliever · Accepted Answer · 2015-06-26T13:52:16.897

16

You may be able to do this by way of using a table variable:

declare @t table (
    ID uniqueidentifier not null default newsequentialid(),
    SecondField datetime,
    ThirdField varchar(128)
)
insert into @t (SecondField,ThirdField)
    output inserted.ID,inserted.SecondField,inserted.ThirdField
    into FrustratingTable
values
('20150101','abc'),
('20150201','def'),
('20150301','ghi')

select * from FrustratingTable

Results:

Id                                   SecondField             ThirdField
------------------------------------ ----------------------- ------------
1FEBA239-091C-E511-9B2F-78ACC0C2596E 2015-01-01 00:00:00.000 abc
20EBA239-091C-E511-9B2F-78ACC0C2596E 2015-02-01 00:00:00.000 def
21EBA239-091C-E511-9B2F-78ACC0C2596E 2015-03-01 00:00:00.000 ghi

Since the table variable sets the value via a default, we're allowed to use NEWSEQUENTIALID().

Of course, for very large data sets, there's a penalty in temporarily having two copies of the data lurking around.

An alternative would be to use an older solution, called COMBs, which were used before NEWSEQUENTIALID() was introduced:

SELECT CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)

Generates uniqueidentifiers with better locality than NEWID() by itself does.

edited Jun 26 '15 at 13:52

answered Jun 26 '15 at 13:43

Damien_The_Unbeliever

234,701
27
340
448

Yes, this is brilliant. in fact, what's to stop me from creating a scalar function that defines a local table variable, inserts one record and returns a GUID, then call this new scalar function when I need a sequential GUID? – Greg Viers Jun 26 '15 at 13:45
@TheTTGGuy - possibly. I tend to prefer to working with sets so far as possible though. Introducing the scalar function, although reusable, may impose a performance penalty when working with large data sets. – Damien_The_Unbeliever Jun 26 '15 at 13:49
ok the scalar function failed, but your solution will work within my stored procedure quite well in another way. – Greg Viers Jun 26 '15 at 13:52
2

@PhilipDevine - I always love the moment when I go from "nope, it can't be done" to "well, *obviously* you can just do X". This question was one of those. – Damien_The_Unbeliever Jun 26 '15 at 14:02

score 1 · Answer 2 · edited May 23 '17 at 12:02

Ok, if first yout take the [IncrementGuid] function from this answer, then you can do something like this,

Fiddle Here

INSERT [dbo].[FrustratingTable]
SELECT
      [dbo].[IncrementGuid](MAX([Id])),
      '01/01/01',
      '3'
  FROM
      [dbo].[FrustratingTable];

Caveat:

Once you reviewed the function in the other answer, you'll agree, there must be a better way.

Change the code that needs the GUIDs to be sequential.

Philip Devine · Answer 3 · 2015-06-26T13:24:34.043

0

ALTER TABLE FrustratingTable
ALTER COLUMN id uniqueidentifier not null default newsequentialid()

edited Jun 26 '15 at 13:24

answered Jun 26 '15 at 12:55

Philip Devine

1,169
5
11

You're right, good catch.. that's what I get for being multi platform :) – Philip Devine Jun 26 '15 at 12:56
I can't alter the table, as I indicated in my question. – Greg Viers Jun 26 '15 at 13:00
1

See here for how to add a default constraint. http://stackoverflow.com/questions/4307075/t-sql-command-for-adding-a-default-constraint – Jodrell Jun 26 '15 at 13:05
Ok then you can disregard this answer (and probably most others, because as you can see you need a default constraint to perform what you are looking for, otherwise you need a new solution) – Philip Devine Jun 26 '15 at 13:25

How to INSERT into a table that uses sequential GUIDs as a primary key?

3 Answers3

Caveat:

Linked