Cột Identity trong SQL Server

Identity trong SQL server là thuộc tính nhận dạng, thường dùng để tạo cột nhận dạng, chứa các giá trị tự động phát sinh tuần tự. Thông thường thì mỗi bảng chỉ có duy nhất 1 thuộc tính nhận dạng. Vậy thì Identity in sql server có cú pháp như thế nào, cách dùng ra sao. Bài viết này Comdy sẽ chia sẻ đến bạn.

Giới thiệu về cột IDENTITY trong SQL Server

Identity SQL là gì?

Trong SQL, IDENTITY là một thuộc tính được sử dụng để tạo ra các cột có giá trị duy nhất và tự động tăng dần (auto-increment).

Cột có thuộc tính identity in sql thường được sử dụng để lưu trữ các giá trị khóa chính (primary key) duy nhất và tăng dần một cách tự động cho mỗi dòng dữ liệu được thêm vào bảng.

Cú pháp identity trong SQL là gì?

Để tạo cột định danh cho bảng, bạn sử dụng thuộc tính identity sql server như sau:

IDENTITY[(seed,increment)]

Trong cú pháp sql identity này:

• seed là giá trị của hàng đầu tiên insert vào bảng.
• increment là giá trị gia tăng được thêm vào giá trị định danh của hàng trước đó.

Giá trị mặc định của seed và increment là (1,1). Có nghĩa là hàng đầu tiên được tải vào bảng sẽ có giá trị là một, hàng thứ hai sẽ có giá trị là 2, tiếp theo là 3, 4, v.v.

Giả sử, bạn muốn giá trị của cột nhận dạng của hàng đầu tiên là 10 và giá trị gia tăng là 10, bạn sử dụng cú pháp sau:

IDENTITY (10,10)

Lưu ý: SQL Server chỉ cho phép bạn có một cột nhận dạng trên mỗi bảng.

Int Identity(1 1) trong SQL server là gì?  (identity(1 1) in SQL server)

Khi sử dụng identity(1,1) cho một cột, đó là cách bạn định nghĩa thuộc tính IDENTITY cho cột đó. Cụ thể:

• 1: Là giá trị bắt đầu cho cột IDENTITY. Điều này chỉ định giá trị của cột cho dòng đầu tiên sẽ là 1.
• 1: Là bước nhảy giữa các giá trị của cột IDENTITY. Điều này chỉ định rằng mỗi lần một dòng mới được thêm vào bảng, giá trị của cột IDENTITY sẽ tăng thêm 1.

Ví dụ về cột IDENTITY trong SQL Server

Hãy tạo một lược đồ mới có tên là hr để thực hành:

CREATE SCHEMA hr;

Câu lệnh sau tạo một bảng mới bằng cách sử dụng thuộc tính IDENTITY cho cột person_id:

CREATE TABLE hr.person (
    person_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender CHAR(1) NOT NULL
);

Đầu tiên, hãy insert một hàng mới vào bảng person:

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('John','Doe', 'M');

Như có thể thấy rõ ràng từ đầu ra, hàng đầu tiên đã được insert thành công và trả về giá trị của cột person_id là 1.

Tiếp theo, insert một hàng khác vào bảng person:

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('Jane','Doe','F');

Như bạn có thể thấy rõ từ đầu ra, hàng thứ hai đã được insert thành công và trả về giá trị của cột person_id là 2.

Tái sử dụng các giá trị định danh

SQL Server không sử dụng lại các giá trị định danh. Nếu bạn insert một hàng vào cột định danh và câu lệnh insert không thành công hoặc bị rollback, thì giá trị nhận dạng sẽ bị mất và sẽ không được tạo lại. Điều này dẫn đến khoảng trống trong cột danh tính.

Hãy xem xét ví dụ sau.

Đầu tiên, hãy tạo thêm hai bảng trong lược đồ hr có tên là position và person_position:

CREATE TABLE hr. POSITION (
	position_id INT IDENTITY (1, 1) PRIMARY KEY,
	position_name VARCHAR (255) NOT NULL,

);

CREATE TABLE hr.person_position (
	person_id INT,
	position_id INT,
	PRIMARY KEY (person_id, position_id),
	FOREIGN KEY (person_id) REFERENCES hr.person (person_id),
	FOREIGN KEY (position_id) REFERENCES hr. POSITION (position_id)
);

Tiếp theo, insert một người mới và chỉ định vị trí cho người mới này bằng cách insert một hàng mới vào bảng person_position:

BEGIN TRANSACTION
    BEGIN TRY
        -- insert a new person
        INSERT INTO hr.person(first_name,last_name, gender)
        VALUES('Joan','Smith','F');

        -- assign the person a position
        INSERT INTO hr.person_position(person_id, position_id)
        VALUES(@@IDENTITY, 1);
    END TRY
    BEGIN CATCH
         IF @@TRANCOUNT > 0  
            ROLLBACK TRANSACTION;  
    END CATCH

    IF @@TRANCOUNT > 0  
        COMMIT TRANSACTION;
GO

Trong ví dụ này, câu lệnh insert đầu tiên đã được thực thi thành công. Tuy nhiên, câu lệnh thứ hai không thành công do không có id bằng 1 trong bảng position. Do xảy ra lỗi, toàn bộ giao dịch đã bị rollback.

Bởi vì câu lệnh INSERT đầu tiên sử dụng giá trị định danh là 3 và giao dịch đã được khôi phục lại, giá trị nhận dạng tiếp theo sẽ là 4 như được hiển thị trong câu lệnh sau:

INSERT INTO hr.person(first_name,last_name,gender)
OUTPUT inserted.person_id
VALUES('Peter','Drucker','F');