为什么数据库设计人员不让标识列从最小值开始而不是从1开始?
As we know, In Sql Server, The IDENTITY (n,m) means that the values will start from n, and the increment value is m, but I noticed that all database designers make Identity columns as IDENTITY(1,1) , without taking advantage of all values of int data type which are from (-2,147,483,648) to (2,147,483,647),
正如我们所知,在Sql Server中,身份(n,m)意味着将开始从n的值,和增量值m,但我注意到,所有数据库设计师身份列标识(1,1),没有利用int数据类型的所有值(-2147483648)(2147483647),
I am planning to make all Identity columns as IDENTITY (-2,147,483,648, 1), (the identity columns are hidden from the application user).
我正计划将所有标识列作为标识(-2,147,483,648,1)，(标识列对应用程序用户隐藏)。
Is that a good idea ?
这是个好主意吗?
6 个解决方案
#1
55
If you find that 2billion values isn't enough, you're going to find out that 4billion isn't enough either (needing more than twice as many of anything over the lifetime of a project, than it was first designed for, is hardly rare*), so you need to take a different approach entirely (possibly long values, possibly something totally different).
如果你发现20亿个值是不够的,你会发现40亿是不够的(需要两倍多的东西在一个项目的生命周期,比第一次设计,并不罕见的*),所以你需要一个完全不同的方法(可能长值,可能是完全不同的东西)。
Otherwise you're just being strange and unreadable for no gain.
否则你就会变得很奇怪，无法读懂，毫无收获。
Also, who doesn't have a database where they know that e.g. item 312 is the one with some nice characteristics for testing particular things? I know I have some arbitrary ids burned in my head. They may call it "so good they named it twice", but I'll always know New York as "city 657, covers most of our test cases". It's only a shorthand, but -2147482991 wouldn't be as handy.
此外，谁没有一个数据库，在那里他们知道，例如，项目312是一个有一些好的特征的测试特定的东西?我知道我脑子里有一些任意的id。他们可能会称它为“太好了，他们给它起了两次名字”，但我永远都知道纽约是“城市657，涵盖了我们大部分的测试用例”。这只是一个简写，但是-2147482991就没这么方便了。
*To add a bit to that. With some things you might say "ah about 100" and find it's actually 110, okay. With others you'll find actually it's actually 100,000 - you were out by orders of magnitude. The higher the number, the more often the mistake is of this sort due to the sort of problems that end up with estimates in the billions being different to those that end up with answers in the dozens. If you estimate 200 is your max in a given case, you should probably leave room for maybe a few hundred more. If you estimate 2billion in a given case, you should probably leave room for a few quadrillion more. That said, the only time I saw someone actually start an id at minus 2billion they ended up having about 3,000 rows.
再补充一点。你可能会说"啊大约100"然后发现实际上是110。对于其他的，你会发现实际上是10万，你被数量级的淘汰了。数字越高，出现这种错误的可能性就越大，因为这类问题最终导致数十亿人的估计与那些最终得到几十个答案的问题不同。如果你估计在一个给定的情况下，你的最大值是200，那么你应该留出更多的空间。如果你估计在一个给定的情况下有20亿人，你可能应该多留几个千万亿的空间。也就是说，我唯一一次看到有人以- 20亿的速度开始id他们最终得到了3000行。