在SQL SERVER 2000中使用IDENTITY属性作为PK有什么缺点和优点？ [英] What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000?

问题描述

大家好！


我们正在为SQL Server 2000进行新的开发，并且从

SQL 7.0转移到SQL Server 2000.


在SQL SERVER中使用IDENTITY属性作为PK有什么缺点和优点

2000？

请分享你使用IDENTITY作为PK的经验。

SCOPE_IDENTITY在SQL 2000中是否让生活更轻松？


从一台服务器移动数据库时是否存在DENTITY属性问题

到另一个？ （相同版本的SQL Server）


提前谢谢，

Andy

解决方案

>在SQL SERVER

2000中使用IDENTITY属性作为PK有什么缺点和优点？


优点：

- 小（4字节）

- 自动

- 相对可预测（与GUID不同）

- 更有用（在调试问题时尝试WHERE guidColumn = {AECB ...}）


缺点：

- 无意义的标识符（这也可能是一件好事）

- 可以有间隙（删除或回滚后）

- 不能在某些类型中使用复制

- 插入的热点如果它也是聚集索引

- 不便携


我们在这里使用它因为我们的天然密钥远大于4个字节，并且这将是低效的（特别是在索引的外键约束中）。

SCOPE_IDENTITY在SQL 2000中是否使生活更轻松？


是的，它比@@ IDENTITY更可靠...但我不知道它会如何实现更好的生活。

让生活更轻松。

将DB从一台服务器移动到另一台服务器时，是否存在DENTITY属性问题？ （相同版本的SQL Server）

取决于您如何定义移动，以及这是一次性还是

连续。


-

Aaron Bertrand

SQL Server MVP
http://www.aspfaq.com/

Aaron Bertrand [MVP]写道：

在SQL SERVER中使用IDENTITY属性作为PK有什么缺点和优点
2000？

优点：
- 小（4字节）
- 自动
- 相对可预测（与GUID不同）
- 更有用（在调试问题时尝试WHERE guidColumn = {AECB ...}）

缺点：
- 无意义的标识符（这也可能是一件好事）
- 可以有间隙（删除或回滚后）
- 不能用于某些类型的复制
- 插入的热点如果它也是聚簇索引
- 不可移植
我们在这里使用它是因为我们的自然键远大于4个字节，而
就是这样低效率（特别是在索引的外键约束中）。

4个字节与它有什么关系？如果你说过60或100我会理解为什么4 b $ b了解为什么4？


请考虑Joe Celko关于这个问题的大量评论

人工或代理键，在回复时。


谢谢。


-

Daniel Morgan
http：/ /www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da ****** @ x.washington.edu

（用'a'替换''x'' '你'回复）

> 4个字节与它有什么关系？如果你说过60或100我会

了解但为什么4？


呃，因为INT（IDENTITY最常见的数据类型）是4个字节？

请考虑Joe Celko关于这个主题的大量评论
人工或代理键，在回复时。

乔是个大男孩，他可以为自己说话。我也可以。我们都没有和b $ b达成一致意见。这就是为什么它被称为意见。


-

Aaron Bertrand

SQL Server MVP
http://www.aspfaq.com/

Hi All!

We are doing new development for SQL Server 2000 and also moving from
SQL 7.0 to SQL Server 2000.

What are cons and pros for using IDENTITY property as PK in SQL SERVER
2000?
Please, share your experience in using IDENTITY as PK .
Does SCOPE_IDENTITY makes life easier in SQL 2000?

Is there issues with DENTITY property when moving DB from one server
to another? (the same version of SQL Server)

Thank you in advance,
Andy

解决方案

> What are cons and pros for using IDENTITY property as PK in SQL SERVER

2000?
Pros:
- small (4 bytes)
- automatic
- relatively predictable (unlike GUID)
- more usable (try WHERE guidColumn = {AECB...} when debugging a problem)

Cons:
- meaningless identifier (this can also be a good thing)
- can have gaps (after delete or rollback)
- can''t be used in some types of replication
- hotspot for insert if it is also clustered index
- not portable

We use it here because our natual keys are much larger than 4 bytes, and
this would be inefficient (especially in indexed foreign key constraints).
Does SCOPE_IDENTITY makes life easier in SQL 2000?
Yes, it is more reliable than @@IDENTITY... but I don''t know how it would
make life easier.
Is there issues with DENTITY property when moving DB from one server
to another? (the same version of SQL Server)

Depends on how you define "move," and whether this is one-time or
continuous.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Aaron Bertrand [MVP] wrote:

What are cons and pros for using IDENTITY property as PK in SQL SERVER
2000?

Pros:
- small (4 bytes)
- automatic
- relatively predictable (unlike GUID)
- more usable (try WHERE guidColumn = {AECB...} when debugging a problem)

Cons:
- meaningless identifier (this can also be a good thing)
- can have gaps (after delete or rollback)
- can''t be used in some types of replication
- hotspot for insert if it is also clustered index
- not portable

We use it here because our natual keys are much larger than 4 bytes, and
this would be inefficient (especially in indexed foreign key constraints).

What does 4 bytes have to do with it? If you had said 60 or 100 I''d
understand but why 4?

And please consider Joe Celko''s voluminous comments on the subject of
artificial, or surrogate keys, when responding.

Thanks.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace ''x'' with a ''u'' to reply)

> What does 4 bytes have to do with it? If you had said 60 or 100 I''d

understand but why 4?
Uh, because INT (the most common datatype for IDENTITY) is 4 bytes?
And please consider Joe Celko''s voluminous comments on the subject of
artificial, or surrogate keys, when responding.

Joe''s a big boy, and he can speak for himself. So can I. We don''t all have
to agree on everything. This is why it''s called an opinion.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

这篇关于在SQL SERVER 2000中使用IDENTITY属性作为PK有什么缺点和优点？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！