Tìm hiểu thuộc tính IDENTITY trong SQL server 2008
Tìm hiểu thuộc tính IDENTITY trong SQL server 2008
1. Cột IDENTITY dùng để tạo một dãy số liên tục tăng cho mỗi bản ghi được INSERT, nó rất thích hợp khi bạn cần có một cột mà mỗi lần INSERT giá trị của nó được tự động tăng lên cao hơn các giá trị trước đó, trong khi bạn không quan tâm nhiều lắm đến bản thân các giá trị trong cột này. Trong rất nhiều trường hợp, cột IDENTITY cũng được dùng luôn làm khóa chính cho bảng.
Bạn khai báo thuộc tính IDENTITY cho cột như sau:--Tạo bảng CREATE TABLE dbo.Tblxxx( ID_Col INT IDENTITY(1,1), Col1 INT, Col2 VARCHAR(50)... ) --Thêm cột vào bảng ALTER TABLE dbo.Tblyyy ADD ID_Col INT IDENTITY(1,1)
IDENTITY(seed, increment)trong đó seed là giá trị khởi tạo và increment là giá trị tăng cho mỗi lần. Khi không chỉ định seed và increment thì các giá trị mặc định của chúng được dùng là seed = 1 và increment = 1. Ví dụ:
--giá trị khởi đầu = 1 và mỗi lần tăng 1 CREATE TABLE dbo.Tblxxx( ID_Col INT IDENTITY... ) --giá trị khởi đầu = 1000 và mỗi lần tăng 5 CREATE TABLE dbo.Tblxxx( ID_Col INT IDENTITY(1000,5)... ) -- giá trị khởi đầu = -100 và mỗi lần giảm 2 CREATE TABLE dbo.Tblxxx( ID_Col INT IDENTITY(-100,-2)... )
3. Bạn không thể thay đổi một cột có sẵn để nó trở thành cột IDENTITY, hoặc bỏ thuộc tính IDENTITY của nó. Khi bạn edit bảng trong Management Studio và sửa một cột thành IDENTITY hoặc ngược lại, ở phía sau thực ra SQL Server xóa bảng và tạo một bảng mới.
4. SQL Server không duy trì tính liên tục của dãy số IDENTITY, khi bạn DELETE bản ghi thì khoảng trống sẽ xuất hiện. Ở lần INSERT sau đó một giá trị mới sẽ được dùng, thay vì giá trị bị xóa được dùng lại.
5. Bản thân thuộc tính IDENTITY không đảm bảo các giá trị trong cột là duy nhất. Ví dụ, bạn có đặt lại seed với một giá trị tùy ý (kể cả giá trị đã được dùng trước đó). Khi cần đảm bảo tính duy nhất, bạn cần thêm ràng buộc khóa chính hoặc khóa duy nhất cho cột.
6. Để khởi tạo lại giá trị seed, bạn dùng lệnh DBCC CHECKIDENT
7. Khi cần INSERT vào bảng, bạn có thể bỏ qua cột IDENTITY trong danh sách các cột, và giá trị của cột IDENTITY sẽ được tự động sinh ra (đó là mục đích chính khi tạo cột này)
CREATE TABLE dbo.MyTable( ID_Col INT IDENTITY, Col_1 INT, Col_2 VARCHAR(50) ) GO INSERT INTO dbo.MyTable(Col_1,Col_2) VALUES(15,'abc') INSERT INTO dbo.MyTable(Col_1,Col_2) VALUES(62,'def') INSERT INTO dbo.MyTable(Col_1,Col_2) VALUES(37,'ghi')
SET IDENTITY_INSERT dbo.MyTable ON INSERT INTO dbo.MyTable(ID_Col,Col_1,Col_2) VALUES(100,42,'jkl')
INSERT INTO dbo.Table1 SELECT * FROM dbo.Table2
An explicit value for the identity column in table 'dbo.Table1'
can only be specified when a column list is used and IDENTITY_INSERT is
ON.Như vậy theo nội dung của thông báo lỗi, để thực hiện được câu lệnh INSERT trên bạn cần thực hiện hai việc: đặt IDENTITY_INSERT là ON cho bảng dbo.Table1, và liệt kê ra các cột:
SET IDENTITY_INSERT dbo.Table1 ON INSERT INTO dbo.Table1(ID_Col, Col_1,...) SELECT ID_Col, Col_1,... FROM dbo.Table2
SELECT * FROM SYS.IDENTITY_COLUMNS WHERE OBJECT_ID = OBJECT_ID('dbo.Tblxxx')
INSERT INTO dbo.Tblxxx(Col_1,Col_2) VALUES(15,'abc') SELECT SCOPE_IDENTITY()
10. Với các tính năng như trên, cột IDENTITY sẽ gây ra khó khăn trong một số trường hợp. Nếu bạn muốn duy trì tính liên tục của dãy số (ví dụ, dùng lại các giá trị bị xóa trước đó), bạn phải tự thực hiện việc kiểm tra này trước mỗi lần INSERT. Việc xử lý cũng phức tạp hơn khi bảng nằm trong một replication với nhiều publisher cùng update dữ liệu về một subscriber. Đặc biệt với replication hai chiều (hai database update qua lại cho nhau) thì nó hoàn toàn bó tay. Lý do là vì giá trị IDENTITY kế tiếp chỉ được lưu cục bộ ở mỗi database, bảng này không biết giá trị kế tiếp của bảng ở bên database kia là bao nhiêu, cho nên khi replication xảy ra thì các giá trị INSERT vào mỗi bảng sẽ bị xung đột với nhau.
Post a Comment