Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why database designers do not make IDENTITY columns start from the min value rather than 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),

I am planning to make all Identity columns as IDENTITY (-2,147,483,648, 1), (the identity columns are hidden from the application user).

Is that a good idea ?

like image 590
houssam Avatar asked Aug 29 '12 11:08

houssam


People also ask

Why does identity column skip numbers?

Usually, it occurs when the SQL Server instance is being forced to restart. This skipped gap is particularly depending on the data type of the column, and most of the time, it is possible that column type can be INT, BIGINT, or Numeric.

How many columns can be defined as identity column?

Any column in a table can be an identity column, but there can only be one identity column per table.

What is identity column in database?

An identity column is a column (also known as a field) in a database table that is made up of values generated by the database. This is much like an AutoNumber field in Microsoft Access or a sequence in Oracle.


1 Answers

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

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.

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

like image 138
Jon Hanna Avatar answered Oct 06 '22 03:10

Jon Hanna