How to create, check and change the Identity column on Microsoft SQL Server


Microsoft SQL Server has built-in Identity column, which is used to create important values. It can refer to an AutoNumber field in Microsoft Access or a range in Oracle. The following article will guide you how to create, test and change the Indentity column on Microsoft SQL Server.


An attribute Identity in a column will create new values ​​based on current values ​​and increments when a new record is inserted into the table. Each new value generated for a particular transaction will be different from other concurrent transactions across the board. This Identity column can be used as the only column when it is specified as the primary key.

Read the details of how to create, test and change the Indentity column on Microsoft SQL Server in the following article of Taimienphi.vn.

How to create, check and change the Identity column on Microsoft SQL Server

Step 1: Create a table with the Identity attribute

In this tutorial, Taimienphi.vn will create a new table named TechJourney, including 3 columns: TechJourneyID, TechJourneyCode and Description.

– SQL syntax

Create Table TableName
(ID int IDENTITY (1,1) NOT NULL,
Column1 datatype,
Column2 datatype,
Column3 datatype,
PRIMARY KEY (ID))

Run the following SQL command in SQL Management Studio:

CREATE TABLE TechJourney (
TechJourneyID int IDENTITY (1,1) NOT NULL,
TechJourneyCode [nvarchar](10) NULL,
description [nvarchar](100) NULL,
PRIMARY KEY (TechJourneyID))

how to check and change identity identity on microsoft sql server 2

Next insert the 2 records below into the TechJourney table to check the Identity value:

Insert into TechJourney (TechJourneyCode, Description)

Values ​​(‘TJ1’, ‘Tech Journey 1’)

And:

Insert into TechJourney (TechJourneyCode, Description)
Values ​​(‘TJ2’, ‘Tech Journey 2’)

how to check and change identity identity on microsoft sql server 3

Step 2: Check the current Identity value

– SQL syntax

DBCC CHECKIDENT (table_name, NORESEED)

Run the following SQL command in SQL Management Studio:

Note: NORESEED is the current Identity value should not be changed.

The next step now is to check the current value after inserting 2 records.

Run the following SQL command in SQL Management Studio:

DBCC CHECKIDENT (TechJourney, NORESEED)

It brings up the message:

“Checking identity information: current identity value ‘2’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator “.

Next, select the records in the TechJourney table to see the TechJourneyID value as shown below:

how to check and change identity identity on microsoft sql server 4

Step 3: Force the current Identity value to new value

– SQL syntax

DBCC CHECKIDENT (table_name, RESEED, new_reseed_value)

Suppose you want to change the current value from 2 to 20. Run the following SQL command in SQL Management Studio:

DBCC CHECKIDENT (TechJourney, RESEED, 20)

It brings up the message:

“Checking identity information: current identity value ‘2’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator “.

Check the current Identity price again by running the following SQL command in SQL Management Studio:

DBCC CHECKIDENT (TechJourney, NORESEED)

It brings up the message:

“Checking identity information: current identity value ’20’, current column value ‘2’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator “.

In the above step we have inserted 2 records into the table and the Identity value is now 2.

Insert 2 records into the TechJourney table again by running the following SQL:

Insert into TechJourney (TechJourneyCode, Description)

Values ​​(‘TJ3’, ‘Tech Journey 3’)

And

Insert into TechJourney (TechJourneyCode, Description)

Values ​​(‘TJ4’, ‘Tech Journey 4’)

how to check and change identity identity on microsoft sql server 5

Select the records in the TechJourney table to see the TechJourneyID value as below:

how to check and change identity identity on microsoft sql server 6

TechjourneyID for T3 is 21 = 20 (new identity value) + 1 (increased value).

https://thuthuat.taimienphi.vn/cach-tao-kiem-tra-va-thay-doi-cot-identity-tren-microsoft-sql-server-31618n.aspx
The article on Taimienphi.vn has shown you how to create, test and change the Identity column on Microsoft SQL Server. With these tutorial posts on SQL Server the way SA password reset is lost on SQL Server will help you master the knowledge of this system. If you have any questions, you can leave your comments in the comment section below the article.

.

Add a Comment

Your email address will not be published. Required fields are marked *