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
ALTER TABLE dbo.Test ADD [GIANGGUID] UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY;
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.
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;
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.