whats the use of an autoincrement primary column?

Question

I would like to know when is it really needed to use an auto increment primary column, what is the value it adds?

my colleague suggests to have a separate auto increment column for a table as a primary key. It should be named ‘_id’. Primary key should not store any business data.

Another colleague is of different opinion that we can have a non-auto increment column to be a primary key as we plan not to have duplicates.

One approach without auto-increment primary column:

1) Create Tables in Database with the below fields

Table I
a.  SLOC_ID (Primary Key)
b.  Gerrit
c.  Parent Key
d.  Files Inserted
e.  Files Deleted
f.  Gerrit Last User (Non Service Accounts) update time 
g.  Current_Patch_Number 



Table II
a.  SLOC_ID (One to Many Relationship)
b.  File Path
c.  File Name

Another approach with auto-increment is below

PK,FK   Column Name
PK(autoincrement)   sloc_id
FK  change_list_id

PK,FK   Column Name
PK  sloc_details_id
FK  sloc_id
    linesmodified
    lines deleted
    filepath
    latestpatch
    modification_type

where can I find more details here?which of the best solution?

Answer 1

There are a few advantages to using an auto-increment column as a primary key.

• Unlikely to need to change: If your primary key is business data that's meaningful to human beings, you may be asked to change that at some point. Examples I have seen would include usernames based on a combination of company branch number and person's initials (want to change them if the person moves to a different branch); and company region names (may need to change if company reorganizes regions). Updating a user's username ultimately touched about 20 columns across about a dozen tables, and almost always caused blocking for long enough periods that users noticed.
• Less fragmentation on table: Assuming that your primary key is also the clustered index for your table, records will also be inserted at the end of the table. since you won't be inserting a new record in between existing records, you won't be splitting pages in the middle of your data, which leads to fragmentation.
• Smaller indexes: In some DBs, at least, the clustered index value is used in any other indexes to locate a row in the table. If your primary key is a varchar value that's 8 characters long on average, that will require twice as much space to store as a 4-byte integer value (4 times as much, if nvarchar).

For me, personally, the first reason was the strongest one, and the one that made me start including an autoincrement primary key in basically every table. Human-meaningful values will always be at risk of being changed by order of the business; putting those into a position where making that sort of changes could take hours and cause millions of other records to get changed is not acceptable, in my opinion.

UPDATE: As joanolo points out there are certain codes that may be meaningful to human beings, but which are part of established standards, and should (at least in theory) not change because of that. Examples would include an ISO 3166 country code, an ISO 4217 currency code, an ISO 639 language code, or a UN ISIC activity code. He notes these are usually a reasonable size (char(4) or less), so they wouldn't cause overly large indexes, and they are generally static, and thus fragmentation wouldn't be a concern. So, it would make sense to use these codes as primary keys in tables based on this sort of information. It's an excellent point, and I agree.

Answer 2

About 20 years ago, our database had a table that contained a single row whose purpose was to add an updated key code to any of several other data tables when adding a row. This may be still in our application, but it's not good practice now. I don't precisely understand your examples, and if that is what you are considering, don't do it.

As for primary key though - not every table needs a unique row key, but in case you may need a key later, it's simple enough to include a "row_id" with arbitrary value automatically set, such as 1, 2, 3, etc. In order to use this as a table key, such as by defining a "foreign key" relationship where the key value stored in a different table points to the corresponding row in this table, you should create a constraint which asserts that the key is unique. This can be either a primary key constraint or another uniqueness constraint. A professional database system will typically create an index on the key's columns to select each key value efficiently in the table.

Specifically in Microsoft SQL Server, which I know, and presumably also in any other useful SQL relational database system, a table can have one "clustered index" whose effect is to store data with sequential values of the key value together, so that they can be quickly read together. This is not necessarily the order in which data is added; for instance, you may want to organise data by customer ID and then by date, so that all records for one customer are bunched together. MS SQL will create a clustered index for the primary key unless you use the words "Primary Key Nonclustered",

MS SQL then bases any other index on the clustered index and includes the clustered index key, which means mainly that a short key (few bytes) for the clustered index is usually best. An auto-incrementing table column - you usually ignore it when inserting data and it is automatically set to the next number - is good for this, if it's acceptable to have table data physically sorted in the order of inserting the rows. That often is what you want. Also, the clustered index should be created before other indexes; this isn't compulsory, but when you create or change the clustered index, other indexes are re-created as well, adding to the time taken.

Semantically, "primary key" also should be a meaningful element in data, and not a random number that you arbitrarily associated with the meaningful data in a row - unless it becomes meaningful from then on. And @RDFozz made the point that using another value as the key (username) and then having to rewrite the data in all tables so that data previously held with username1 now has username2, is an inconvenient exercise. Better to have table column Users.arbitrary_row_id_number as the primary key, and username as another column in the table that can be easily updated in one place. You even could be caught out with "country code" if mainland China takes over Taiwan, or if Puerto Rico is promoted to a state of the U.S.

With or without that extra care, most of the time, you can simply declare an appropriate "primary key" for the table, and benefit from useful indexing as well. But take care. One of our tables has a file name as the primary key - people send us files, named with date, time, and account code. But it's over 100 bytes long, so it is not a good clustered index key; that's where an auto-incremented number (4 bytes) is used. The filename is primary key, but it is not the clustered index key.