SQL Server 2005 会因为我使用 nvarchar(50) 而不是整数作为主键而惩罚我吗? [英] Will SQL Server 2005 penalize me for using an nvarchar(50) as a primary key, instead of an integer?
问题描述
我正在考虑更改一些表以使用 nvarchar(50) 作为主键而不是 int 主键.对键使用 int ID 确实是不相关的数据，它是我感兴趣的字符串.会发生什么样的性能损失，或者你在哪里研究这个?除了剪切和尝试.
I'm considering altering some tables to use nvarchar(50) as primary key instead of an int primary key. Using an int ID for a key really is irrelevant data, it's the string I'm interested in. What sort of performance hit will occur, or where do you research this? Other than cut and try that is.
推荐答案
您遇到了数据库设计的主要圣战"之一.您所指的辩论是代理与自然键"的争论，只要有 RDBMS(据我所知)，这种争论就一直在肆虐.
You have hit upon one of the major "holy wars" of database design. The debate you're referring to is the "surrogate vs. natural key" argument that's been raging for as long as there have been RDBMSs (as nearly as I can tell).
争论本质上归结为是否应该使用代表性键(代理，例如 IDENTITY 列)与使用唯一描述记录的实际数据(自然键).
The debate essentially boils down to whether a representative key (surrogate, for example an IDENTITY column) should be used versus using the actual data that uniquely describes a record (natural key).
我会说没有正确"的答案.性能指标是平台的产物，应通过实验进行评估，但性能不太可能成为主要关注点.
I will say that there is no "right" answer. Performance measures are an artifact of the platform, and should be assessed by experimentation, but performance is not likely to be the major concern.
我认为代理键的主要论点是主键的不变性.如果您选择使用自然密钥，则您放弃在其建立后更改该密钥的选项.你也放弃了它在未来某个时候变得不唯一的可能性.出于这些原因，我通常(并非总是)为我的大多数表使用代理键.
What I consider to be the primary argument for surrogate keys is the immutability of primary keys. If you choose to use a natural key, you give up the option of altering that key after it is established. You also give up the possibility that it may become non-unique at some point in the future. For those reasons, I typically (not always) use surrogate keys for most of my tables.
但是，正如我所提到的，如果您愿意，可以阅读有关索引策略和规范格式依从性的讨论，这是一个长期存在的争论.
However, as I mentioned, there is a very long-standing debate filled with discussions of indexing strategies and normal-form adherance to be read if you are so inclined.
我会谷歌代理与自然键".以下是一些可帮助您入门的链接:
I would Google "surrogate vs. natural keys". Here are a few links to get you started:
希望这会有所帮助.
这篇关于SQL Server 2005 会因为我使用 nvarchar(50) 而不是整数作为主键而惩罚我吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
