我应该设计一个主键是 varchar 还是 int 的表? [英] Should I design a table with a primary key of varchar or int?

问题描述

我知道这是主观的，但我想知道人们的意见，并希望在设计 sql server 表结构时可以应用一些最佳实践.

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.

我个人认为在固定(最大)长度 varchar 上键入表是禁忌，因为这意味着还必须在使用它作为外键的任何其他表中传播相同的固定长度.使用 int，将避免必须全面应用相同的长度，这必然会导致人为错误，即 1 个表具有 varchar(10)，并且其他 varchar (20).

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).

对于最初的设置来说，这听起来像是一场噩梦，另外也意味着表格的未来维护也很麻烦.例如，假设键控 varchar 列突然变成 12 个字符而不是 10 个字符.您现在必须去更新所有其他表，这可能是一项艰巨的任务.

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.

我错了吗?我在这里错过了什么吗?我想知道其他人对此有何看法，以及坚持使用 int 作为主键是否是避免维护噩梦的最佳方法.

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.

推荐答案

我绝对推荐在每个表中使用 INT NOT NULL IDENTITY(1,1) 字段作为主键.

I would definitely recommend using an INT NOT NULL IDENTITY(1,1) field in each table as the primary key.

使用 IDENTITY 字段，您可以让数据库处理所有细节以确保它确实是唯一的，并且 INT 数据类型只有 4 个字节，并且是固定的，因此它更容易也更适合用于主(和聚类)键在您的表中.

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.

你是对的 - INT 是一个 INT 是一个 INT - 它不会改变任何东西的大小，所以你永远不必重新创建和/或更新你的外键关系.

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.

使用 VARCHAR(10) 或 (20) 只会占用太多空间 - 10 或 20 个字节而不是 4 个，而且很多人不知道 - 每个索引上都会重复聚集键值表上每个非聚集索引的条目，因此可能会浪费大量空间(不仅在磁盘上 - 这很便宜 - 而且在 SQL Server 的主内存中).此外，由于它是可变的(可能是 4 个字符，也可能是 20 个字符)，SQL 服务器很难正确维护良好的索引结构.

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.

马克

这篇关于我应该设计一个主键是 varchar 还是 int 的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！