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; 

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.