ID seed Column

I. Definition and features of an identity column the identity column in SQLServer, also known as an identifier column, is also called an auto-increment column. This type of column has the following three features: 1. The column data type is a numeric type without decimal digits. 2. During the Insert operation, the value of this column is generated by the system according to certain rules. null values are not allowed. 3. The column values are unique and are identified.

I. Definition and features of an identity column the identity column in SQLServer is also called an identifier column. It is also called an auto-incrementing column. This type of column has the following three features: 1. The column data type is a numeric type without decimal digits. 2. During the Insert operation, the value of this column is generated by the system according to certain rules. null values are not allowed. 3. The column values are unique and are identified.

I,IdentifierColumn definition and features

In SQL ServerIdentifierThe column is also calledIdentifierCharacter column, also known as auto-incrementing column.
This type of column has the following three features:

1. The column data type is Numeric without decimal places.
2. During the Insert operation, the value of this column is generated by the system according to certain rules, and null values are not allowed.
3. The column value is unique and hasIdentifierEach row in a table can have only oneIdentifierColumn.

Because of the above featuresIdentifierColumn inDatabaseIs widely used in the design.

II,IdentifierColumn Composition
CreateIdentifierColumn. You must specify three columns:
1. type)
On SQL Server 2000,IdentifierThe column type must be numeric, as shown below:
Decimal, int, numeric, smallint, bigint, tinyint
Note that when decimal and numeric are selected, the number of decimal places must be zero.
In addition, pay attention to the value ranges of all values in each data type.

2,Seed(Seed)
Is the value assigned to the first row in the table. The default value is 1.

3. incremental (increment)
Two AdjacentIdentifierIncrement between values. The default value is 1.

III,IdentifierColumn creation and modification
IdentifierColumn creation and modification are usually implemented in the Enterprise Manager and using the Transact-SQL statement. It is relatively simple to use the enterprise management manager. For more information, see the SQL Server online help, here we only discuss how to use Transact-SQL.

1. SpecifyIdentifierColumn
IdentifierThe column can be created using the IDENTITY attribute. Therefore, in SQL Server, it is also calledIdentifierColumn is a column with the IDENTITY attribute or an IDENTITY column.
The following example creates an object with the name ID and the type is int,Seed1.IdentifierColumn
Create table T_test
(ID int IDENTITY (1, 1 ),
Name varchar (50)
)

2. Add an existing tableIdentifierColumn
The following example adds an ID to the T_test table, whose type is int,Seed1.IdentifierColumn
-- Create a table
Create table T_test
(Name varchar (50)
)

-- Insert data
INSERT T_test (Name) VALUES ('zhang san ')

-- AddIdentifierColumn
Alter table T_test
Add id int IDENTITY (1, 1)

3. Determine whether a table hasIdentifierColumn

You can use the OBJECTPROPERTY function to determine whether a table has an IDENTITY (Identifier) Column. Usage:
Select OBJECTPROPERTY (OBJECT_ID ('table name'), 'tablehasauth ')
If yes, 1 is returned; otherwise, 0 is returned.

4. Determine whether a column isIdentifierColumn

You can use the COLUMNPROPERTY function to determine whether a column has the IDENTITY attribute.
Select columnproperty (OBJECT_ID ('table name'), 'column name', 'isidentity ')
If this column isIdentifierColumn, 1 is returned; otherwise, 0 is returned.

5. query a tableIdentifierColumn name
SQL Server does not have ready-made functions to implement this function. The SQL statements implemented are as follows:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'table name' and columnproperty (
OBJECT_ID ('table name'), COLUMN_NAME, 'isidentity ') = 1

6,IdentifierColumn reference

If you referenceIdentifierColumn, which can be replaced by the keyword IDENTITYCOL
For example, to query the rows in the previous example with the ID equal to 1,
The following two query statements are equivalent.
SELECT * FROM T_test where identitycol = 1
SELECT * FROM T_test where id = 1

7. ObtainIdentifierColumnSeedValue

You can use the IDENT_SEED function. Usage:
SELECT IDENT_SEED ('table name ')

8. ObtainIdentifierColumn Increment

You can use the IDENT_INCR function. Usage:
SELECT IDENT_INCR ('table name ')

9. Obtain the last generatedIdentifierValue

You can use the IDENT_CURRENT function. Usage:
SELECT IDENT_CURRENT ('table name ')
Note: WhenIdentifierThe column table is just created. The value obtained by using the IDENT_CURRENT function isIdentifierColumnSeedValue.DatabasePay special attention to the application.

To sum upIdentifierHow to copy a column

1. Copying snapshots
In snapshot copying, you do not need to considerIdentifierColumn attributes.

2. Transaction Replication
Example:
ReleaseDatabaseA. subscribeDatabaseB. The publication is T_test_A, And the subscription table is T_test_ B.
Create table T_test_A
(ID int IDENTITY (1, 1 ),
Name varchar (50)
)
Create table T_test_ B
(ID int IDENTITY (1, 1 ),
Name varchar (50)
)

In this case, the replication agent cannot copy the new row to database B because the column ID isIdentifierColumn.IdentifierThe provided values are displayed in the column. Copying failed.
In this caseIdentifierSet the not for replication option FOR the column. In this way, when the replication agent connects to the T_test table on Database B with any login, all NOT
If the for replication option is activated, you can insert the ID column explicitly.

There are two situations:
1. The T_test table of database B will not be updated by the user (or Application)
The simplest case is: if the T_test of database B is not updated by the user (or application), we recommend that you remove the ID columnIdentifierAttribute, only the simple int type can be used.

2. The T_test table of database B will be updated by other users (or applications ).

In this case, the ID columns of the two T_test tables conflict. For example:
Run the following statement in database:
INSERT T_test_A (Name) VALUES ('Tom ') (assuming the ID column is 1)
Run the following statement in database B:
INSERT T_test_ B (Name) VALUES ('pip ') (assuming the ID column is 1)
In this way, A record will be inserted in the two tables of database A and database B respectively. Obviously, there are two different records.
However, the process is not over yet. When the pre-set replication time is reached, the replication proxy tries to insert the record "1 TOM" into the T_test table in database B, but the T_test_ B table in database B already exists.

If the column ID is 1, the insertion will not succeed. By copying the monitor, we will find that the replication fails.
The solutions to these problems include:
(1) For publishers and subscribersIdentifierColumns specify values in different ranges. In the preceding example, the values can be changed:
-- Make sure that the table records do not exceed 10000000.
Create table T_test_A
(ID int IDENTITY (1, 1 ),
Name varchar (50)
)
Create table T_test_ B
(ID int IDENTITY (0000000,1 ),
Name varchar (50)
)
(2) Enable the publisher and subscriberIdentifierColumn values are not repeated, such
-- Use odd values
Create table T_test_A
(ID int IDENTITY (1, 2 ),
Name varchar (50)
)
-- Use an even value
Create table T_test_ B
(ID int IDENTITY (2, 2 ),
Name varchar (50)
)
This method can be promoted. When the subscriber and publisher have around,IdentifierThe column attributes are defined as follows:
)

3. Merge and copy
Use the solution in transaction replication, as long as you make the publishing table and subscription tableIdentifierThe column value can be unique.

Related blogs:

• Programmer, do you want to improve programming efficiency?
• Summary of common features of. net Control Development
• DatabaseDesign Specifications (III)
• SQLDatabaseOptimization

OECP official blog