1

I am trying to add a column to an existing SQL table of uniqueidentifier type. That column must not be null and of course unique. I have attempted this code:

ALTER TABLE ScheduleJobs ADD CurrentInstanceID uniqueidentifier not null

followed by:

ALTER TABLE ScheduleJobs ADD CONSTRAINT DF_CurrentInstanceID DEFAULT newsequentialid() FOR CurrentInstanceID

However, when I create a new record (from C#), the uniqueidentifier is always all zeros (presumably null.) I can create the GUID in C# and pass it to sql upon creating a new record which works fine, but I am concerned that a duplicate GUID could be created. Based on my readings, it appears that would be an extremely rare case, but it always seems bad practice to have any sort of potential error floating around. Note that the field will not be a PK for the table. Suggestions and opinions welcome for the sake of education.

I am using C# 4.0 framework with MS SQL SERVER 2008

flyNflip
  • 451
  • 1
  • 5
  • 14
  • Have u considered using GUID? – David Jul 05 '13 at 14:59
  • try to use NEWID() on sql side – Zaki Jul 05 '13 at 15:03
  • 1
    Which exactly technology you use to access db? I know that entity framework doesn't take default values setting from db by default so no default values are set. You can pretty much generate a new guid in code, it would be unique, but not sequential. – ElDog Jul 05 '13 at 15:05
  • Chances that guid is not unique in the table are extremly low. And even if you hit it, there would be the same error thrown by the SQL Server if you already have the constraint in. I think you are over-engineering. I'd say don't fix what is not broken. – trailmax Jul 05 '13 at 15:07
  • Shame about it being 2008, you could use sequences in 2012. – Hogan Jul 05 '13 at 15:10
  • ElDog, I am using entity framework. If I generate the GUID in code, isn't there still potential that a duplicate could be created despite being statistically negligible? I suppose I could do a check against the DB table to make sure that newly created GUID doesn't exist. – flyNflip Jul 05 '13 at 15:14
  • 1
    I can't reproduce this in T-SQL only. Inserting a row without the ID in the table will create a new GUID. Are you perhaps sending a zero GUID to the server instead of NULL? GUID is a structure which means it can't be null. Unless you define it as Guid? in your code, passing an empty value will result in a Guid with zeros – Panagiotis Kanavos Jul 05 '13 at 15:15
  • I am never assigning a value to the GUID. I am using GUID and not GUID? I am using Entity Framework and I am assuming there is some magic happening that's causing my all zeros. I haven't tried inserting a record directly using SQL Server but I suspect that it would probably insert a valid GUID. I will try it. – flyNflip Jul 05 '13 at 15:18

4 Answers4

4

Sorry for the delay, but I am glad to say that I have this issue resolved. Thanks everyone for your overwhelming support. While no one quite hit the nail on the head (and there were some really good suggestions btw), Eldog brought up Entity Framework not playing nice in his comment. Thanks to him, I simply Googled Entity Framework + GUID and found the solution.

This article steps through the issue and gives a great explanation on the problem, solution, and steps to resolve it. I will note that I decided to step through and test one step at a time and that I didn't have to do the last step. That leads me to believe that part of the issue may have been resolved in later versions of the Entity Framework.

I simply pulled up the edmx file in design view (not xml) and set the StoreGeneratedPattern property to "Identity."

Thanks again for the help and suggestions. You're an awesome bunch.

James Skemp
  • 8,018
  • 9
  • 64
  • 107
flyNflip
  • 451
  • 1
  • 5
  • 14
  • 1
    That link is no more available. It is always suggested to provide some answers here on StackOverflow to avoid this situation. – Mahesh Sep 18 '15 at 15:47
  • Please reread my answer and note: I simply pulled up the edmx file in design view (not xml) and set the StoreGeneratedPattern property to "Identity" – flyNflip Sep 21 '15 at 01:32
2

Does your C# code attempt to pass in a CurrentInstanceID when creating the record? If so, can you drop that column from the INSERT statement?

We do this with numeric primary keys. Our C# code calls a stored procedure for CRUD operations on our records. The C# code generates a negative key on the client side for its own use. When it is ready to create the record, it passes this key to the stored procedure.

The proc ignores this key and inserts the rest of the data. The output of the proc is the actual key that SQL assigned to the record. Finally, the C# code merges the new key into the existing data.

Paul Williams
  • 16,585
  • 5
  • 47
  • 82
0

I wouldn't use a GUID for this. GUIDs are used in quite a lot of operations in windows, so this won't be an identifier that will be only unique in your application, it will be a unique identifier in your operating system. Unless this makes sens in your case, I wouldn't use it.

You could use an incremental value, like a simple uint. If your table already has some data, you could write a script that fills existing rows with incremental values for your new column, and add the unique contraint to your column after executing that script.

ppetrov
  • 3,077
  • 2
  • 15
  • 27
  • 2
    This is a little misleading, the fact that GUIDs are used as unique ids in other operations is meaningless, the chances of collision are so small. http://stackoverflow.com/a/2977648/1246040 – Mataniko Jul 05 '13 at 15:11
  • I am using this basically for versioning. To be honest, it wasn't my idea to use GUID (heck I didn't know anything about them.) However, I have been specifically instructed to use a GUID by my boss. My point is that the GUID doesn't need to be unique to the world just to the table. – flyNflip Jul 05 '13 at 15:11
  • newsequentialid is unique for a Windows installation. http://msdn.microsoft.com/library/ms189786.aspx – Andre Calil Jul 05 '13 at 15:11
  • @Mataniko yep you're right, the chances to hit the same GUID are really small. But to me if something must be unique only in one context, it doesn't need to be unique in a bigger one. It's just about separating concerns. Also it can be done in a simple way without using GUIDs. It's an opinion and a way of doing things, there are others ;) – ppetrov Jul 05 '13 at 15:13
  • To better illustrate what I mean: if you're making a client management application (in a national context), you won't use their national ID number as an identifier, you'll use an incremental value. Even if their national ID is a bigger number and you're sure there won't be a collision, it just doesn't make much sens. – ppetrov Jul 05 '13 at 15:23
0

in your original Create table or alter table use something like the following

create table ScheduleJobs (keyval int, id2 uniqueidentifier not null default newsequentialid())

then don't reference the column in your insert and a new GUID value will be added

SteveB
  • 1,474
  • 1
  • 13
  • 21
  • That is the same as what he already wrote. In fact, if you script the table from SSMS the syntax will be the same as the OP's – Panagiotis Kanavos Jul 05 '13 at 15:11
  • Good spot. Missed it. So if the OP doesn't reference the second column then it will have a default value ? Am I missing something else ? – SteveB Jul 05 '13 at 15:18
  • Just a note here... I read an article that there was a bug with SQL Server. The post stated to separate out the column generation and the newsequentialid() declaration like I did in my OP. I didn't test and can't confirm but just in case others have an issue. I'm not sure about creating a new table as the example and my scenario was to alter an existing table. Thanks for the comment. – flyNflip Jul 05 '13 at 22:04