SQL Server Int 或 BigInt 数据库表 ID [英] SQL Server Int or BigInt database table Ids
问题描述
我正在编写一个新程序，它需要一个数据库(SQL Server 2008).我现在为系统运行的所有东西都是 64 位的，这让我想到了这个问题.对于各种表中的所有 Id 列，我应该将它们全部设为 INT 还是 BIGINT?我怀疑该系统是否会超过 INT 范围，但我认为在一些较大的财务表中是有可能的.虽然 INT 似乎是标准...
I am writing a new program and it will require a database (SQL Server 2008). Everything I am running now for the system is 64-bit, which brings me to this question. For all of the Id columns in various tables, should I make them all INT or BIGINT? I doubt the system will ever surpass the INT range but it is a possibility within some of the larger financial tables I suppose. It seems like INT is the standard though...
推荐答案
好的，让我们快速回顾一下:
OK, let's do a quick math recap:
INT 是 32 位的，基本上可以给你 40 亿个值——如果你只计算大于零的值，它仍然是 20 亿.你们有这么多员工吗?顾客?产品有库存吗?贵公司生命周期内的订单?真的吗?
INT is 32-bit and gives you basically 4 billion values - if you only count the values larger than zero, it's still 2 billion. Do you have this many employees? Customers? Products in stock? Orders in the lifetime of your company? REALLY?
BIGINT 远不止于此.你真的需要那个吗??真的??如果你是天文学家，或者是粒子物理学——也许吧.一个普通的业务线用户?我强烈怀疑
BIGINT goes way way way beyond that. Do you REALLY need that?? REALLY?? If you're an astronomer, or into particle physics - maybe. An average Line of Business user? I strongly doubt it
假设您有一个表，其中包含 - 比如说 - 1000 万行(贵公司的订单).假设您有一个 Orders 表，您创建 BIGINT 的 OrderID 被其他 5 个表引用，并在 Orders 表的 5 个非聚集索引中使用 - 我认为并不过分，对吧?
Imagine you have a table with - say - 10 million rows (orders for your company). Let's say, you have an Orders table, and that OrderID which you made a BIGINT is referenced by 5 other tables, and used in 5 non-clustered indices on your Orders table - not overdone, I think, right?
1000 万行，由 5 个表加上 5 个非聚集索引组成，即 1 亿个实例，其中每个实例使用 8 个字节而不是 4 个字节 - 4 亿字节 = 400 MB.完全浪费...您将需要更多数据和索引页，您的 SQL Server 将不得不从磁盘读取更多页面并缓存更多页面....这对您的性能没有好处 - 简单明了.
10 million rows, by 5 tables plus 5 non-clustered indices, that's 100 million instances where you are using 8 bytes each instead of 4 bytes - 400 million bytes = 400 MB. A total waste... you'll need more data and index pages, your SQL Server will have to read more pages from disk and cache more pages.... that's not beneficial for your performance - plain and simple.
PLUS:大多数程序员没有想到的:是的，磁盘空间非常便宜.但是，浪费的空间也与您的 SQL Server RAM 内存和数据库缓存相关 - 而且该空间并不便宜！
PLUS: What most programmer's don't think about: yes, disk space it dirt cheap. But that wasted space is also relevant in your SQL Server RAM memory and your database cache - and that space is not dirt cheap!
因此，要使一篇很长的文章简短:使用真正适合您需要的最小类型的 INT；如果您有 10-20 个不同的值要处理 - 使用 TINYINT.如果您需要订单表，我相信 INT 应该足够多 - BIGINT 只是浪费空间.
So to make a very long post short: use the smallest type of INT that really suits your need; if you have 10-20 distinct values to handle - use TINYINT. If you need an order table, I believe INT should be PLENTY ENOUGH - BIGINT is only a waste of space.
另外:如果你的任何表真的接近达到 2 或 40 亿行，你仍然有足够的时间将你的表升级到 BIGINT ID，如果真的需要的话......
Plus: should any of your tables really ever get close to reaching 2 or 4 billion rows, you'll still have plenty of time to upgrade your table to a BIGINT ID, if that's really needed.......
这篇关于SQL Server Int 或 BigInt 数据库表 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
