I found a t-sql question and its answer. It is too confusing. I could use a little help.
我找到了一个t-sql问题及其答案。它太令人困惑。我需要一点帮助。
The question is:
问题是:
You develop a database application. You create four tables. Each table stores different categories of products. You create a Primary Key field on each table.
开发一个数据库应用程序。你创建四个表。每个表存储不同种类的产品。在每个表上创建一个主键字段。
You need to ensure that the following requirements are met:
你需要确保符合以下要求:
- The fields must use the minimum amount of space.
- 字段必须使用最小的空间。
- The fields must be an incrementing series of values.
- 字段必须是递增的一系列值。
- The values must be unique among the four tables.
- 值必须在四个表中是唯一的。
What should you do?
你应该做什么呢?
- A. Create a
ROWVERSIONcolumn. - 创建行版本列。
- B. Create a
SEQUENCEobject that uses theINTEGERdata type. - 创建一个使用整数数据类型的序列对象。
- C. Use the
INTEGERdata type along withIDENTITY - C.使用整数数据类型和标识
- D. Use the
UNIQUEIDENTIFIERdata type along withNEWSEQUENTIALID() - D.使用惟一标识符数据类型和NEWSEQUENTIALID()
- E. Create a
TIMESTAMPcolumn. - 创建一个时间戳列。
The said answer is D. But, I think the more suitable answer is B. Because sequence will use less space than GUID and it satisfies all the requirements.
这个答案是d，但是我认为更合适的答案是b，因为序列会比GUID占用更少的空间，满足所有的要求。
3 个解决方案
#1
3
D is a wrong answer, because NEWSEQUENTIALID doesn't guarantee "an incrementing series of values" (second requirement).
D是错误的答案，因为NEWSEQUENTIALID不能保证“一个递增的值序列”(第二个需求)。
NEWSEQUENTIALID()NEWSEQUENTIALID()
Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique.
创建一个GUID，该GUID大于自Windows启动以来该函数在指定计算机上生成的任何GUID。在重新启动Windows之后，GUID可以从更低的范围重新启动，但仍然是全局惟一的。
I'd say that B (sequence) is the correct answer. At least, you can use a sequence to fulfil all three requirements, if you don't restart/recycle it manually. I think it is the easiest way to meet all three requirements.
我认为B(序列)是正确答案。至少，如果不手工重新启动/回收，您可以使用一个序列来满足这三个需求。我认为这是满足这三个要求的最简单的方法。
#2
1
Between the choices provided
D
B is the correct answer, since it meets all requirements:
D B为正确答案，满足所有要求:
ROWVERSION is a bad choice for a primary key, as stated in MSDN:
对于主键，ROWVERSION是一个糟糕的选择，如MSDN所述:
Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column. This property makes a rowversion column a poor candidate for keys, especially primary keys. Any update made to the row changes the rowversion value and, therefore, changes the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid.
每次修改或插入带有rowversion列的行时，都会在rowversion列中插入递增的数据库rowversion值。此属性使行版本列成为键(尤其是主键)的不佳候选项。对行进行的任何更新都会更改rowversion值，因此会更改键值。如果列在主键中，则旧键值不再有效，引用旧值的外键不再有效。
TIMESTAMP is deprecated, as stated in that same page:
不赞成使用时间戳，如同页所述:
The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
不赞成使用时间戳语法。这个特性将在未来的Microsoft SQL Server版本中被删除。避免在新的开发工作中使用该特性，并计划修改当前使用该特性的应用程序。
An IDENTITY column does not guarantee uniqueness, unless all it's values are only ever generated automatically (you can use SET IDENTITY_INSERT to insert values manually), nor does it guarantee uniqueness between tables for any value.
标识列不能保证惟一性，除非它的所有值都是自动生成的(您可以使用SET IDENTITY_INSERT来手动插入值)，也不能保证任何值的表之间的惟一性。
A GUID is practically guaranteed to be unique per system, so if a guid is the primary key for all 4 tables it ensures uniqueness for all tables. the one requirement it doesn't fulfill is storage size - It's storage size is quadruple that of int (16 bytes instead of 4).
一个GUID实际上保证每个系统都是唯一的，所以如果一个GUID是所有4个表的主键，那么它保证了所有表的唯一性。它不满足的一个需求是存储大小——它的存储大小是int的四倍(16字节而不是4字节)。
A SEQUENCE, when is not declared as recycle, guarantee uniqueness, and has the lowest storage size.
一个序列，当没有被声明为循环时，它保证惟一性，并且具有最低的存储大小。
The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted.
数值序列在一个定义的间隔内以升序或降序生成，并可配置为在耗尽时重新启动(循环)。
However, I would actually probably choose a different option all together - create a base table with a single identity column and link it with a 1:1 relationship with all other categories. then use an instead of insert trigger for all categories tables that will first insert a record to the base table and then use scope_identity() to get the value and insert it as the primary key for the category table. This will enforce uniqueness as well as make it possible to use a single foreign key reference between the categories and products.
然而，我实际上可能会选择一个不同的选项——创建一个具有单个标识列的基表，并将它与所有其他类别的1:1关系连接起来。然后对所有类别表使用替代插入触发器，这些表将首先向基表插入一条记录，然后使用scope_identity()获取值并将其作为类别表的主键。这将增强惟一性，并使在类别和产品之间使用单个外键引用成为可能。
#3
0
The issue has been discussed extensively in the past, in general:
这个问题在过去已经被广泛讨论，一般来说:
http://blog.codinghorror.com/primary-keys-ids-versus-guids/
http://blog.codinghorror.com/primary-keys-ids-versus-guids/
The constraint #3 is why a SEQUENCE could run into issues as there is a higher risk of collision/lowered number of possible rows in each table.
约束#3是为什么一个序列会遇到问题，因为在每个表中有更高的冲突风险/减少可能的行数。
#1
3
D is a wrong answer, because NEWSEQUENTIALID doesn't guarantee "an incrementing series of values" (second requirement).
D是错误的答案，因为NEWSEQUENTIALID不能保证“一个递增的值序列”(第二个需求)。
NEWSEQUENTIALID()NEWSEQUENTIALID()
Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique.
创建一个GUID，该GUID大于自Windows启动以来该函数在指定计算机上生成的任何GUID。在重新启动Windows之后，GUID可以从更低的范围重新启动，但仍然是全局惟一的。
I'd say that B (sequence) is the correct answer. At least, you can use a sequence to fulfil all three requirements, if you don't restart/recycle it manually. I think it is the easiest way to meet all three requirements.
我认为B(序列)是正确答案。至少，如果不手工重新启动/回收，您可以使用一个序列来满足这三个需求。我认为这是满足这三个要求的最简单的方法。
#2
1
Between the choices provided
D
B is the correct answer, since it meets all requirements:
D B为正确答案，满足所有要求:
ROWVERSION is a bad choice for a primary key, as stated in MSDN:
对于主键，ROWVERSION是一个糟糕的选择，如MSDN所述:
Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column. This property makes a rowversion column a poor candidate for keys, especially primary keys. Any update made to the row changes the rowversion value and, therefore, changes the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid.
每次修改或插入带有rowversion列的行时，都会在rowversion列中插入递增的数据库rowversion值。此属性使行版本列成为键(尤其是主键)的不佳候选项。对行进行的任何更新都会更改rowversion值，因此会更改键值。如果列在主键中，则旧键值不再有效，引用旧值的外键不再有效。
TIMESTAMP is deprecated, as stated in that same page:
不赞成使用时间戳，如同页所述:
The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
不赞成使用时间戳语法。这个特性将在未来的Microsoft SQL Server版本中被删除。避免在新的开发工作中使用该特性，并计划修改当前使用该特性的应用程序。
An IDENTITY column does not guarantee uniqueness, unless all it's values are only ever generated automatically (you can use SET IDENTITY_INSERT to insert values manually), nor does it guarantee uniqueness between tables for any value.
标识列不能保证惟一性，除非它的所有值都是自动生成的(您可以使用SET IDENTITY_INSERT来手动插入值)，也不能保证任何值的表之间的惟一性。
A GUID is practically guaranteed to be unique per system, so if a guid is the primary key for all 4 tables it ensures uniqueness for all tables. the one requirement it doesn't fulfill is storage size - It's storage size is quadruple that of int (16 bytes instead of 4).
一个GUID实际上保证每个系统都是唯一的，所以如果一个GUID是所有4个表的主键，那么它保证了所有表的唯一性。它不满足的一个需求是存储大小——它的存储大小是int的四倍(16字节而不是4字节)。
A SEQUENCE, when is not declared as recycle, guarantee uniqueness, and has the lowest storage size.
一个序列，当没有被声明为循环时，它保证惟一性，并且具有最低的存储大小。
The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted.
数值序列在一个定义的间隔内以升序或降序生成，并可配置为在耗尽时重新启动(循环)。
However, I would actually probably choose a different option all together - create a base table with a single identity column and link it with a 1:1 relationship with all other categories. then use an instead of insert trigger for all categories tables that will first insert a record to the base table and then use scope_identity() to get the value and insert it as the primary key for the category table. This will enforce uniqueness as well as make it possible to use a single foreign key reference between the categories and products.
然而，我实际上可能会选择一个不同的选项——创建一个具有单个标识列的基表，并将它与所有其他类别的1:1关系连接起来。然后对所有类别表使用替代插入触发器，这些表将首先向基表插入一条记录，然后使用scope_identity()获取值并将其作为类别表的主键。这将增强惟一性，并使在类别和产品之间使用单个外键引用成为可能。
#3
0
The issue has been discussed extensively in the past, in general:
这个问题在过去已经被广泛讨论，一般来说:
http://blog.codinghorror.com/primary-keys-ids-versus-guids/
http://blog.codinghorror.com/primary-keys-ids-versus-guids/
The constraint #3 is why a SEQUENCE could run into issues as there is a higher risk of collision/lowered number of possible rows in each table.
约束#3是为什么一个序列会遇到问题，因为在每个表中有更高的冲突风险/减少可能的行数。