我应该设计一个主键为varchar或int的表吗？ [英] Should I design a table with a primary key of varchar or int?
问题描述
我知道这是主观的，但是我想知道人们的意见，并希望在设计sql server表结构时可以应用一些最佳实践。
I个人觉得在一个固定（最大）长度的varchar上键入一个表是一个no-no，因为它意味着还必须在使用它作为外键的任何其他表之间传播相同的固定长度。使用 int 将避免必须在整个板上使用相同的长度，这势必导致人为错误，即1个表具有 varchar（ 10），另一个 varchar（20）。
这听起来像是初始设置的噩梦，加上表格的未来维护也很麻烦。例如，键入的varchar列突然变成12个字符而不是10.你现在必须去更新所有其他表，这可能是一个巨大的任务年底。
$ b $我错了吗？我错过了这里的东西吗？我想知道别人怎么看待这个问题，如果使用主键的int是避免维护恶梦的最佳方法。
我一定会建议在
主键中使用每个表格中的 INT NOT NULL IDENTITY（1,1）字段。
使用IDENTITY字段，您可以让数据库处理所有的细节，确保它是真正独特的，所有的INT数据类型只有4个字节，并且是固定的，所以它更容易和更适合用于表中的主（和聚类）键。
你是对的 - INT是INT是一个INT - 它不会改变它的大小任何东西，所以你不必去重新创建和/或更新你的外键关系。
使用VARCHAR（10）或（20）太多的空间 - 10或20个字节而不是4个字节，以及很多人不知道 - 聚集键值将在表上每个单个非聚集索引的每个索引条目上重复，所以潜在的，你浪费了很多空间（不仅仅是在磁盘上 - 这很便宜，而且在SQL Server的主内存中）。另外，由于它是可变的（可能是4个字符，可能是20个字符），因此SQL Server难以适当地维护良好的索引结构。
Marc
I know this is subjective, but I'd like to know peoples opinions and hopefully some best practices that I can apply when designing sql server table structures.
I personally feel that keying a table on a fixed (max) length varchar is a no-no, because it means having to also propogate the same fixed length across any other tables that use this as a foreign key. Using an int, would avoid having to apply the same length across the board, which is bound to lead to human error, i.e. 1 table has varchar (10), and the other varchar (20).
This sounds like a nightmare to initially setup, plus means future maintaining of the tables is cumbersome too. For example, say the keyed varchar column suddenly becomes 12 chars instead of 10. You now have to go and update all the other tables, which could be a huge task years down the line.
Am I wrong? Have I missed something here? I'd like to know what others think of this and if sticking with int for primary keys is the best way to avoid maintainace nightmares.
I would definitely recommend using an INT NOT NULL IDENTITY(1,1) field in each table as the
primary key.
With an IDENTITY field, you can let the database handle all the details of making sure it's really unique and all, and the INT datatype is just 4 bytes, and fixed, so it's easier and more suited to be used for the primary (and clustering) key in your table.
And you're right - INT is an INT is an INT - it will not change its size of anything, so you won't have to ever go recreate and/or update your foreign key relations.
Using a VARCHAR(10) or (20) just uses up too much space - 10 or 20 bytes instead of 4, and what a lot of folks don't know - the clustering key value will be repeated on every single index entry on every single non-clustered index on the table, so potentially, you're wasting a lot of space (not just on disk - that's cheap - but also in SQL Server's main memory). Also, since it's variable (might be 4, might be 20 chars) it's harder to SQL server to properly maintain a good index structure.
Marc
这篇关于我应该设计一个主键为varchar或int的表吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
