SQL Server "ID column" Problems

Let's take a look at a table: how can we easily add data to this table in the database SQLServer?

It is not difficult to observe this table carefully. The addition of the column data in the ID field is regular, and it is an ascending sequence of proportional auto-increment, is there a simple way to add such data to a data table?

This introduces the problem of SQLServer ID columns. I have summarized some materials and shared my experiences and exchanges with you.

(1) Definition of an identity column

An ID column (also known as an "Id field" or an "auto-increment field") is an identifier for a row of records to distinguish other records, so that the row record can be retrieved uniquely.

Note: A table can only have one ID column (Id field). Generally, the "primary key" is set as the ID column.

(2) how to set a field as an ID column?

The following uses SQLServer2008 as an example to describe how to set it:

For example, right-click -- table Person3, left-click -- "design" attribute, and select the field to be set as the ID column. In "Identity specification, set "yes" to "yes" for the field, close this window, and save it.

From the "identification specification", we can see that there is an "identification seed" and "id increment" in the identification column, mainly to avoid duplicate values and facilitate query.

1. Identify the Starting number of seeds. The default value is 1.

2. Identify the increment value (also known as the growth seed) of each added data. The default value is 1.

3. The value cannot be inserted in the ID column. It is automatically assigned and an error is reported if it is specified.

Identity () indicates that the seed of this column is 1 and the seed of growth is also 1. The first parameter is the standard seed, and the second parameter is the growth seed.

Note: Before setting the ID column, in the-'tool' menu, locate-'options'-in options-Remove "√" of "change to block table creation required for saving, otherwise, the ID column of the Table to be modified by SQLServer cannot be modified.

(3) Advantages of setting an ID column

The ID column increases automatically, starting from the seed and increasing sequentially. For example, if the seed is 1 and the growth volume is 1, the values of the column are: 1, 2, 3, 4, 5 ...... And so on. Therefore, when you write an Insert statement, you do not need to write the value of the column ID.

That is to say, the ID column achieves Field Auto-increment, which solves the concurrency problem. If you do not need to control the auto-increment, SQLServer will automatically allocate the value of the ID column, if two people insert data to the database at the same time, a unique ID is automatically added.