SQL Server表列中的默认值为GUID

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 ?

我需要在已存在的表中添加一列，并将其设置为主键。我使用其他软件添加新数据。为什么列GIANGGUID的值为00000000-0000-0000-0000-000000000000？

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

3 个解决方案

#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:

您将获得现有列的NULL值，因为允许NULL并且newid（）用于新插入的行：

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

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

现在，如果添加一个带有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:

由于列不能为NULL，因此使用DEFAULT约束并为每个现有行插入newid（）：

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:

添加主键时会发生同样的情况，因为PK不能为NULL，并且在此ALTER中也会添加newid（）：

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

With your query, newid() will be inserted for new and existing rows.
使用您的查询，将为新行和现有行插入newid（）。
With the other queries above, you will either get NULL or newid().
使用上面的其他查询，您将获得NULL或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.

没有理由以00000000-0000-0000-0000-000000000000结束，除非未提及的内容或转换它。

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

现在，如果我们不考虑这个问题，你不应该考虑使用UNIQUEIDENTIFIER作为主键。 GUID是：

not narrow
不窄
random although Sequential GUID could be used.
随机虽然可以使用顺序GUID。

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.

如果由于某些原因需要随机且独特的内容（例如表中的GUID），则可以在没有PK的情况下保留此列，并在PK时添加额外的唯一和顺序ID列（bigint with identity）。

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)

编辑以解决软件插入问题...（请参阅导致合并索引违规的00000000-0000-0000-0000-000000000000的GUID）

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:

创建一个没有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.

当软件进行插入时，它不会看到GIANGGUID，也不会尝试自动插入内容。

#2

Before using a GUId as a primary you should have a look at this post :

在使用GUId作为主要内容之前，您应该看一下这篇文章：

What are the best practices for using a GUID as a primary key, specifically regarding performance?
使用GUID作为主键的最佳做法是什么，特别是在性能方面？

Most of the time is a bad pratice to use a GUID as a primary key in SQL Server.

大多数时候使用GUID作为SQL Server的主键是一个不好的实践。

#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}"

我花了很长时间试图解决这个问题。您似乎需要以下格式才能将派生列作为GUID。（DT_GUID）“{00000000-0000-0000-0000-000000000000}”

#1