How to set automatic numbering in SQL Server

Setting the automatic numbering in SQL Server uses the IDENTITY attribute. The specific steps include: setting the IDENTITY(1, 1) attribute when creating the table; automatically setting the ID column when inserting data; and querying the inserted automatic numbering value using @@IDENTITY. In addition, the start value, step size, seed and loop automatic numbering can be set.

Setting automatic numbering in SQL Server

Answer:

You can use IDENTITY attribute to set the automatic numbering in SQL Server.

Detailed steps:

1. Create a table:

 <code class="sql">CREATE TABLE YourTable ( ID INT IDENTITY(1, 1) NOT NULL, Name VARCHAR(50) );</code>

• ID is the name of the automatically numbered column.
• IDENTITY(1, 1) specifies the start value and step size. 1 means the starting value is 1, and 1 means the step size is 1.

2. Insert data:

 <code class="sql">INSERT INTO YourTable (Name) VALUES ('John');</code>

At this point, ID column will be automatically set to 1.

3. Search for automatic numbering values:

 <code class="sql">SELECT @@IDENTITY;</code>

The query will return the auto-numbered value of the currently inserted record, i.e. 1.

Other options:

• Start value: Use IDENTITY(<start_value>, <increment>)</increment></start_value> to set the start value.
• Step size: Use IDENTITY(<start_value>, <increment>)</increment></start_value> to set the step size.
• Seed: Reset the autonumbered seed using DBCC CHECKIDENT('<table_name>', RESEED, <new_seed>)</new_seed></table_name> .
• Loop: Use IDENTITY(<start_value>, <increment>, <max_value>) RESEED</max_value></increment></start_value> to set the loop automatic number. When max_value is reached, the automatic number will be reset to start_value

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