Default value is GUID in SQL Server table column

Question

I need to add a column to an already existing table and set it to be the primary key. I use other software to add new data. Why does the column GIANGGUID have the value 00000000-0000-0000-0000-000000000000 ?

ALTER TABLE dbo.Test 
   ADD [GIANGGUID] UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY;

Sipes

What is the question??? – Chloromycetin

Why the column GIANGGUID has value as 00000000-0000-0000-0000-000000000000? – Sipes

Most of the time is a bad pratice to use a GUID as a primary key in SQL Server see this answer #11938544 – Jelly

We'll need to know exactly how you insert the row that has the 00000000-0000-0000-0000-000000000000 GUID in it. And whether you inserted that before or after you added your default. That's not really clear from your question. (Also, if you're using a GUID as a primary key you need to be careful; there's a lot of variables to consider, but NEWSEQUENTIALID() may be a better choice than NEWID(). Even better would probably be a simple incrememnting integer, if you can manage it.) – Krueger

Are you using an ORM? If so which one? Any way it looks as though your insert is inserting that value explicitly so the default never comes into it. – Droit

I think that whether your existing values get new GUIDs depends on whether the column is declared NULLable or not. If that's not explicitly declared, as in the question here, I think whether it defaults to NULLable or not depends on a few things. Looks to me like the PRIMARY KEY declaration implies that the column should be NOT NULL. Certainly if I try your code on my local SQL Server, I do get expected GUID values in my column for existing rows... – Krueger

I see there are answers here which make general assertions which in many contexts just aren't true. A GUID is not necessarily a bad thing as a Primary Key. Yes it should be random, that is the point and yes that makes it a poor choice as a clustered key. However a sequential GUID is a nonsense. If a clustered index is important then add an INT field, make it a key, sequential and clustered. But if you need a unique unguessable key value, use a GUID (UUID for the rest of the universe). – Cloraclorinda

Sequential GUIDs aren't nonsense, they are what you get if you call NEWSEQUENTIALID. And they are still GUIDs, that is unique, not random. There's nothing random about most GUID algorithms (there are many). Random means duplicatable. In any case, this isn't an answer. At best it's a comment. SO is a Q&A site, not a discussion forum. Answers are supposed to actually answer the questions. There's a reason people with less than 50 rep can't post comments too. You need to understand how SO works first – Charleencharlemagne

Answer 1

Several things are happening here.

If you simply add a column with a default value using this query:

ALTER TABLE #Test ADD [GIANGGUID] uniqueidentifier DEFAULT NEWID();

You will get NULL value for existing columns because NULL are allowed and newid() for newly inserted rows:

id  name    GIANGGUID
0   A       NULL
1   B       NULL
2   C       NULL

Now, if you add a new column with NOT NULL:

ALTER TABLE #Test ADD [GIANGGUID] uniqueidentifier NOT NULL DEFAULT NEWID();

Since the column can't be NULL, the DEFAULT constraint is used and newid() is inserted for each existing row:

id  name    GIANGGUID
0   A       52C70279-B3A4-4DE6-A612-F1F32875743F
1   B       3005D9BE-5389-4870-BAA9-82E658552282
2   C       E50FDD26-A8FD-43BD-A8F0-3FDA83EFF5D9

The same thing happens when you add a Primary Key because the PK can't be NULL and newid() will be added as well with this ALTER:

ALTER TABLE #Test ADD [GIANGGUID] uniqueidentifier NOT NULL DEFAULT NEWID()-- PRIMARY KEY;

• With your query, newid() will be inserted for new and existing rows.
• With the other queries above, you will either get NULL or newid().

There is no reason to end up with 00000000-0000-0000-0000-000000000000 unless something that has not been mentionned do it or transform it.

Now if we put this problem aside, you should not consider using a UNIQUEIDENTIFIER as a Primary Key. GUID are:

• not narrow
• random although Sequential GUID could be used.

If you need something random and unique such as a GUID in your table for some reasons, you can keep this column without a PK and also add an extra unique and sequential ID column (bigint with identity) as you PK.

Sample Data:

CREATE TABLE #Test(id int, name varchar(10));
INSERT INTO #Test(id, name) values
    (0, 'A')
    , (1, 'B')
    , (2, 'C');

Edit to go around the software insertion issue... (see GUID of 00000000-0000-0000-0000-000000000000 causing merge index violation)

Rename the table:

EXEC sp_rename 'dbo.test', 'test_data'

Add the new column:

ALTER TABLE dbo.Test_data ADD [GIANGGUID] UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY;

or:

ALTER TABLE dbo.Test_data ADD [GIANGGUID] bigint identity(0, 1) PRIMARY KEY;

Create a view without GIANGGUID:

CREATE VIEW dbo.test AS
    SELECT col1, col2, ... FROM dbo.test_data

When the software will do its insert it won't see GIANGGUID and it won't try to insert something automaticaly.

Chloromycetin

I add a column is GIANGGUID and set a primary key for it, but when I add a few rows data, the first row value is 00000000-0000-0000-0000-000000000000 and the rows next will can not insert because it duplicate primary key, The problem is that when I set the default value or binding is newid () or newsequentialid (), the primary key have value is 00000000-0000-0000-0000-000000000000. default my table is no primary key – Sipes

how do you insert the new row ? your code probably replace it with 000000. – Chloromycetin

I do not have permission to edit code. I just be allowed to add a column is GUID to tracking table – Sipes

the code used to insert new rows probabably add the 0000. What software is used to insert rows in your table ? – Chloromycetin

I added a new field is GIANGGUID, it does not depend software to insert data because it auto generate by newid () or newsequentialid () – Sipes

yes it should but perhaps the software see this column and insert something else.... can you remove try it with ALTER TABLE #Test ADD [GIANGGUID] uniqueidentifier NOT NULL and see if you still get 00000 ? – Chloromycetin

i had try it with ALTER TABLE sde.BDTDS_28939 ADD GIANGGUID uniqueidentifier NOT NULL; and result is 000000. – Sipes

i need enable change tracking for table no primary key, there are other ways to solve this problem? – Sipes

is a bigint identy(0, 1) an option for your? – Chloromycetin

i had try for this, and error occur "Cannot insert explicit value for identity column in table 'BDTDS_28939'" when IDENTITY_INSERT is set to OFF". I've set it ON is still error. – Sipes

This is a problem with the sowftare try to insert everything. Perhaps you can create a new table where you will store the guid and add a trigger to the main table. Another option is to rename test to test_data, add the guid column with newid() to it and create a view called test with all test_data column except the guid. The software will not see the guid and will not try to insert it. see edit in answer – Chloromycetin

Answer 2

There is no way to get the default value in SQL server, Use the below conversion value to get the default guid

select CAST( cast(0 as binary) as uniqueidentifier)

Result

00000000-0000-0000-0000-000000000000

Spiritism

Answer 3

I spent a long time trying to figure this out. It appears you need the following format for a derived column to be a GUID. (DT_GUID) "{00000000-0000-0000-0000-000000000000}"

Trenna