6

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?

Improve this question
14
  • 1
    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()? Commented Jun 26, 2015 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. Commented Jun 26, 2015 at 12:46
  • 1
    possibly related: stackoverflow.com/questions/5585307/sequential-guids - could the existing table rows have been created via such a method? Commented Jun 26, 2015 at 12:48
  • 3
    newsequentialid() can only be used in a DEFAULT constraint, as your error message and the MSDN documentation states. So no - without changing your table structure, you cannot fix this Commented Jun 26, 2015 at 12:49
  • 3
    @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) Commented Jun 26, 2015 at 13:04

3 Answers 3

Reset to default
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.

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

4 Comments

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?
@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.
ok the scalar function failed, but your solution will work within my stored procedure quite well in another way.
@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.
1

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.

Improve this answer

Comments

0 
ALTER TABLE FrustratingTable ALTER COLUMN id uniqueidentifier not null default newsequentialid()
Improve this answer

4 Comments

You're right, good catch.. that's what I get for being multi platform :)
I can't alter the table, as I indicated in my question.
See here for how to add a default constraint. stackoverflow.com/questions/4307075/…
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)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.