How to set up AutoNumber fields in SQL Server 2005 _mssql2005

If you want to redefine the serial number that is automatically generated and stored in the column when you add a new record to the table, you can change the identity property of the column. You can set the identity property of only one column in each table.
A column with identity attributes contains system-generated contiguous values that uniquely identify each row in the table (for example, employee identification numbers). When you insert a value in a table that contains an identity column, Microsoft SQL Server automatically generates the next identifier based on the last identity value used (identifying the seed attribute) and the increment value (identity increment attribute) specified when the column was created.

Attention:

You can set identity properties only for columns that do not allow null values and whose data type is decimal, int, numeric, smallint, bigint, or tinyint. In addition, you cannot set identity properties for the primary key column.

to modify the identity property of a column

1. In Object Explorer, right-click the table that contains the column whose data type you want to change, and then click Modify. This opens the table in Table Designer.
2. Clear the Allow Nulls check box for the column you want to change.
3. In the Column Properties tab, expand the Identity specification property.
4. Click the grid cell that identifies the child property, and then select Yes from the Drop-down list.
5. Type a value in the Identify seed cell. This value is assigned to the first row in the table. By default, a value of 1 is assigned.
6. Type a value in the identity increment cell. This value is the increment that is incremented for each subsequent row, based on the identity seed sequence. By default, a value of 1 is assigned.

For example, suppose you want to automatically generate a 5-bit order ID for each row added to the Orders table, starting at 10000 and incrementing 10 at a time. To do this, type 10000 in identity seed, type 10 in identity increment.
If you change any of the identity properties of the table, the existing identity values are preserved. The new setting value applies only to new rows that are added to the table.

Attention:

If an identity column exists in a table that performs a delete operation frequently, there is an inconsistency between the identity values. To avoid this inconsistency, do not use identity attributes.

When you click outside the grid cell or use the Tab key to move to another grid cell, the new value of the Identity specification property is assigned to the column in Table Designer. When you save changes in Table Designer, these values take effect in the database.

1. Specify AutoNumber fields when creating a table

CREATE TABLE [dbo]. [Userinfor] (
 [UserID] [int] IDENTITY (100,2) not NULL,
 [UserName] [nchar] (a) NOT NULL,

Description: Creates a user information table userinfor and specifies UserID as an AutoNumber field.
Where: IDENTITY (100,2) indicates that the initial value of the field is 100, each increment is 2, such as the first time after entering data is 100, the second is 102.

2. Get AutoNumber field values

Here you can use the @ @IDENTITY to get the value of automatically increasing the primary key of the field

(3) Reset the starting value of the AutoNumber field seed

If the user empties the contents of the table with the automatic increment field, the starting value of the field will increase from the original value, and the start value can be reset by the DBCC command.
DBCC checkident (userinfor,reseed,0)
Set the Userinfor table's Auto increment field seed start value to 0, and after inserting one data, the value of the automatic field is 0+ increment, for example, the increment is 1, the value is 1

----------------------------------------------
Right click on your table--> design Table--> Find your ID field (class int type)--> identity--> is--> identity seed (initial value)--> identity Increment-->ok
---------------------------------

CREATE TABLE MyTest 
( 
ID int primary key identity (1,1),--primary key, automatic +1 
name varchar unique NOT NULL,--does not allow duplication, Not allowed for empty age 
tinyint, 
notetime smalldatetime default getdate () 
) 

inserts into mytest values (' John ', 20, GETDATE ())