How to implement automatic numbering in SQL Server 2008?

How to automatically number sql server 2008

Note: The identity attribute can only be set for columns that do not allow nulls and whose data type is decimal, int, numeric, smallint, bigint, or tinyint. Additionally, identity properties cannot be set on primary key columns.

one. Modify the identity attribute of the column through SQL management tools

1. In Object Explorer, right-click the table containing the column whose data type you want to change, and then click Modify. The table will be opened in the table designer.

2. Clear the "Allow Nulls" checkbox for the column you want to change.

3. In the "Column Properties" tab, expand the "Identification Specification" property.

4. Click the grid cell of the "Yes Identity" sub-property and select "Yes" from the drop-down list.

5. Enter a value in the "Identification Seed" cell. This value will be assigned to the first row of the table. By default, this value will be 1.

6. Type a value in the "Identification Increment" cell. This value is a row-by-row increment based on the "identification seed". By default, the increment is set to 1.

two. SQL statement to create

Specify automatic numbering fields when creating a table

CREATE TABLE [dbo].[UserInfor](

[UserID] [int] IDENTITY(100,2) NOT NULL, --The initial value and increment step can be specified here

[UserName] [nchar](10) NOT NULL, )

About sql server automatic numbering issue

You can create a table and set the "Identity Column" property of one of the columns (such as "ID") to "Yes", and then set its seed (initial value) and increment. For example, if you set the seed to 1 and the increment to 1 (the default setting), the generated ID sequence will be: 1, 2, 3, 4,...

When the number of records is not particularly large (more than 10 million), this value is unique, that is, the two will not be the same.

But there will be a problem. For example, if you generate 10 records in sequence:

1, 2, 3, 4, 5, 6, 7, 8, 9, 10

Then you delete record No. 3, so the remaining records are:

1, 2, 4, 5, 6, 7, 8, 9, 10

If you want the number to gradually increase from 1 without interruption in the middle, you can set another non-identifying column, such as "number". When adding a new record, you can use the following methods:

In the query statement, you can use the following statement to select the maximum value of the number column in the table: SELECT @number = MAX(number) FROM tablename

insert into tablename(number) values(@number 1)

---------------------

The above is a demonstration using SQL statements, but it is assumed that you use stored procedures to operate the database. If you use ASP language, you can do it as follows:

psql = "SELECT MAX(number) AS maxnumber FROM tablename"

rs.open psql,conn,3,3

maxnumber=rs(＂maxnumber＂)

Read the maximum number value, then add 1, and then use it in your data addition statement.

---------------------

In this way, you can manually generate the number, and then write the program like this when deleting the record:

' Assume that the record number to be deleted is @n

delete tablename where number=@n

Update the record named tablename, decrement the value of the number field by 1, but update only when number is greater than the value of parameter @n.

After doing this, we can rearrange the numbering order, delete the records and renumber them. Similarly, I can also show you a SQL statement demonstration, but this time the code using ASP SQL is no longer provided. Please refer to and understand it by yourself.

The above is the detailed content of How to implement automatic numbering in SQL Server 2008?. For more information, please follow other related articles on the PHP Chinese website!