Thêm thuộc tính Cột IDENTITY trong SQL Server

Thảo luận trong 'Kiến thức lập trình SQL & MySQL' bắt đầu bởi admin, 13/10/16.

  1. admin
    Tham gia ngày:
    22/5/13
    Bài viết:
    4,878
    Đã được thích:
    1,193
    Điểm thành tích:
    113
    Giới tính:
    Nam
    Thêm thuộc tính Cột IDENTITY trong SQL Server

    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
    Mã:
    CREATE TABLE dbo.MyTable(
    ID_Col INT IDENTITY(1,1),
    Col1 INT,
    Col2 VARCHAR(50)...
    )
    
    • Thêm cột vào bảng
    Mã:
    ALTER TABLE dbo.Tblyyy ADD ID_Col INT IDENTITY(1,1)
    
    2. Cú pháp tổng quát của IDENTITY
    • 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
    Mã:
    CREATE TABLE dbo.MyTable(
    ID_Col INT IDENTITY...
    )
    
    Giá trị khởi đầu = 1000 và mỗi lần tăng 5
    Mã:
    CREATE TABLE dbo.MyTable(
    ID_Col INT IDENTITY(1000,5)...
    )
    
    Giá trị khởi đầu = -100 và mỗi lần giảm 2
    Mã:
    CREATE TABLE dbo.MyTable(
    ID_Col INT IDENTITY(-100,-2)...
    )
    
    Lưu ý là chuỗi giá trị có thể giảm dần (increment < 0) như ở ví dụ trên, nhưng tôi thấy điều này ít có giá trị thực tế và thậm chí còn dễ gây nhầm lẫn. Do đó trong bài này tôi luôn giả định là chuỗi luôn tăng.

    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)
    Mã:
    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')
    
    • Khi bạn muốn ghi đè lên giá trị tự sinh của cột IDENTITY, bạn phải đưa cột này vào danh sách các cột đồng thời trước đó phải đặt lại lựa chọn IDENTITY_INSERT là ON (mặc định là OFF)
    Mã:
    SET IDENTITY_INSERT dbo.MyTable ON
    INSERT INTO dbo.MyTable(ID_Col,Col_1,Col_2) VALUES(100,42,'jkl')
    
    • Một tình huống thường xảy ra là bạn INSERT vào bảng từ một bảng khác có cùng cấu trúc. Một cách lười biếng bạn có thể viết thế này:
    Mã:
    INSERT INTO dbo.Table1 SELECT * FROM dbo.Table2
    
    • Khi bảng dbo.Table1 không có cột IDENTITY, câu lệnh trên thực hiện không có gì trở ngại. Nhưng nếu bảng dbo.Table1 có chứa cột IDENTITY, bạn sẽ nhận được thông báo lỗi sau:
    Mã:
    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:
    Mã:
    SET IDENTITY_INSERT dbo.Table1 ON
    INSERT INTO dbo.Table1(ID_Col, Col_1,...)
    SELECT ID_Col, Col_1,...
    FROM dbo.Table2
    
    8. Một bảng chỉ được phép có tối đa một cột IDENTITY. Bạn có thể tìm ra cột nào là IDENTITY bằng cách edit bảng trong Management Studio, hoặc bằng cách truy vấn view hệ thống SYS.IDENTITY_COLUMNS:
    Mã:
    SELECT *
    FROM SYS.IDENTITY_COLUMNS
    WHERE OBJECT_ID = OBJECT_ID('dbo.MyTable')
    
    9. Để lấy giá trị IDENTITY cuối cùng vừa được INSERT, bạn dùng hàm SCOPE_IDENTITY(), gọi hàm này ở ngay dưới câu lệnh INSERT.
    Mã:
    INSERT INTO dbo.MyTable(Col_1,Col_2) VALUES(15,'abc')
    SELECT SCOPE_IDENTITY()
    
    Ngoài ra còn một vài hàm khác có cùng mục đích, nhưng khi đi vào chi tiết chúng có những khác biệt đáng kể. Trong phần lớn trường hợp, SCOPE_IDENTITY() là hàm an toàn nhất tránh được những rắc rối không cần thiết.

    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.
     
    Cảm ơn đã xem bài:

    Thêm thuộc tính Cột IDENTITY trong SQL Server

    Chỉnh sửa cuối: 13/10/16
  2. ly hoang vu
    Tham gia ngày:
    19/4/17
    Bài viết:
    5
    Đã được thích:
    0
    Điểm thành tích:
    1
    cám ơn về bài viết của bạn
     
  3. ingiacucre
    Tham gia ngày:
    18/6/19
    Bài viết:
    21
    Đã được thích:
    0
    Điểm thành tích:
    6
    Giới tính:
    Nữ
    Rất nhiều ái ko biết. thank chủ thớt
     
  4. jan nguyen
    Tham gia ngày:
    19/7/19
    Bài viết:
    19
    Đã được thích:
    0
    Điểm thành tích:
    1
    Giới tính:
    Nữ
    cảm ơn ad nhiều về bài viết
     
  5. Bếp phú quý
    Tham gia ngày:
    7/7/19
    Bài viết:
    3
    Đã được thích:
    0
    Điểm thành tích:
    1
    Giới tính:
    Nam
    với identity thì khỏi lo bị trùng lặp dữ liệu, mỗi bản nghi luôn có 1 id là duy nhất.
     
  6. caycongtrinh
    Tham gia ngày:
    21/8/18
    Bài viết:
    15
    Đã được thích:
    0
    Điểm thành tích:
    6
    Giới tính:
    Nam
    Rất nhiều ái ko biết. thank chủ thớt
     
  7. minhtranseo
    Tham gia ngày:
    13/8/19
    Bài viết:
    20
    Đã được thích:
    0
    Điểm thành tích:
    1
    Giới tính:
    Nam
    vẫn còn mơ hồ nhưng vấn cảm ơn ad rất nhiều
     
  8. Hoàng Phương An
    Tham gia ngày:
    12/6/19
    Bài viết:
    26
    Đã được thích:
    0
    Điểm thành tích:
    1
    Giới tính:
    Nữ
    hay ghê, cảm ơn thớt nhiều nhé ạ
     
  9. sofadep999
    Tham gia ngày:
    25/8/19
    Bài viết:
    20
    Đã được thích:
    0
    Điểm thành tích:
    1
    Giới tính:
    Nữ
    Nhiều khi thêm báo lỗi may mà có bài hướng dẫn này, mình thực hiện thôi.
     
  10. thietbim5sdonggoi
    Tham gia ngày:
    21/10/19
    Bài viết:
    15
    Đã được thích:
    0
    Điểm thành tích:
    1
    Giới tính:
    Nam
    Cảm ơn ad vì bài viết rất hữu ích! Mình sẽ thực hiện ngay
     
  11. Thompson Clegg
    Tham gia ngày:
    22/11/19
    Bài viết:
    19
    Đã được thích:
    1
    Điểm thành tích:
    1
    Giới tính:
    Nam
    Cảm ơn bạn đã chia sẻ bài viết hữu ích
     
  12. tongtuana2
    Tham gia ngày:
    2/12/19
    Bài viết:
    21
    Đã được thích:
    0
    Điểm thành tích:
    6
    cảm ơn đã chia sẻ. bài viết của ban rất hay và có ích
     
  13. Hiếu PL
    Tham gia ngày:
    4/12/19
    Bài viết:
    16
    Đã được thích:
    0
    Điểm thành tích:
    1
    Giới tính:
    Nam
    Cảm ơn thớt nhìu nha
     
  14. vn.ttquynhh
    Tham gia ngày:
    26/7/15
    Bài viết:
    291
    Đã được thích:
    9
    Điểm thành tích:
    18
    Giới tính:
    Nam
    SQL Server là cái mà lúc trước mình học bên thiết kế hơi bị ngao ngán khi nó bị vấn đề
     


Chủ để tương tự : Thêm thuộc
Diễn đàn Tiêu đề Date
Kiến thức lập trình SQL & MySQL Thêm xóa sửa dữ liệu trong table mysql 9/1/16
Kiến thức lập trình SQL & MySQL Lệnh thêm, xóa, sửa(giá trị) một columns trong table sql server 2005, 2008 28/2/14
Kiến thức lập trình SQL & MySQL Select size Database kích thước database table trong SQL server như thế nào? 30/3/20