Default value is GUID in SQL Server table column


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;

Answers:


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.