SQL Server - Identity Column

IDENTITY เป็นคุณสมบัติหนึ่งของคอลัมน์ ที่มีเก็บข้อมูลประเภทตัวเลข ได้แก่ประเภท int, tinyint, smallint, bigint, decimal และ numeric หากเราต้องการกำหนดค่าของคอลัมน์ Identity ให้เป็นคีย์ควรกำหนดข้อมูลเป็นตัวเลขจำนวนเต็ม ซึ่งเราจะนิยมใช้อยู่ 2 ประเภท คือ  int หรือ bigint  เมื่อกำหนดแล้ว โดยคุณสมบัติของคอลัมน์นี้จะเป็นการสร้างเลขลำดับแบบอัตโนมัติทันทีเมื่อมีการเพิ่มแถวของข้อมูลเข้าสู่ตาราง (อาจเพิ่มข้อมูลจะสำเร็จหรือไม่สำเร็จก็ได้)
ตัวอย่าง  การสร้างคอลัมน์ Identity ชื่อ row_id ให้กับตาราง product

CREATE TABLE product

            (row_id                       bigint NOT NULL IDENTITY(1,1) PRIMARY KEY , 

 product_code             nvarchar(20) NOT NULL ,

 product_name            nvarchar(100),

 product_type              nvarchar(10), 

 um                            nvarchar(20),

 sale_price                   money)

CREATE UNIQUE INDEX product_product_code ON product (product_code)

หลายท่านสงสัย ทำไมถึงต้องมี row_id ที่เป็น identity และทำเป็น PK ทั้ง ๆ ที่ มี product_code ที่เป็นรหัสสินค้าที่ไม่ซ้ำกันซึ่งสามารถทำเป็น primary key ได้อยู่แล้ว ผู้เขียนเลี่ยงจะไปทำเป็น unique ให้กับรหัส product_code แทน  ด้วยเหตุผลทางด้านความเร็ว

แล้วมันเอาไปทำอะไรได้บ้าง   

1. เอาไปทำลำดับของข้อมูลอันไหนเกิดก่อนหลัง 
2. เอาไปทำเป็นคีย์ ใช้เวลาปรับปรุงข้อมูล ลบข้อมูล "คงสงสัย อ้าวมันก็มี product_code อยู่แล้ว แล้วจะใช้ row_id ทำไม" มาดูตัวอย่าง เช่น 
          เราเลือกข้อมูลมารายการมา 1 รายการ เช่น product_code = '000001'
          DECLARE @ROW_ID int ;
          SELECT @ROW_ID = row_id , * FROM product WHERE product_code = '000001'
          เก็บ row_id ไว้ในตัวแปร @ROW_ID (ถ้าเขียนโปรแกรมก็เก็บและเรียกใช้ ก็แนว ๆ นี้แหละ)
          เวลาจะปรับปรุง หรือลบ ก็ใช้ row_id มาใช้ได้เลย ทำให้เราสามารถหลับหูหลับตาใช้เงื่อนไข where row_id เดิม ๆ ในการปรับปรุงได้กับทุก ๆ ตาราง
         UPDATE SET product_code = '000002' , product_name = 'KKKK' WHERE row_id = @ROW_ID
ไม่ว่าจะใช้ตารางใด ๆ ก็ตาม เราไม่ต้องไปสนใจกับรหัสต่าง ๆ ที่เราตั้งขึ้นมา ก็ใช้ row_id ที่เป็น identity ได้เลยสะดวกดี
3. เลื่อนข้อมูลทีละรายการ ก็ใช้ได้
         DECLARE @ROW_ID int ;
         SET @ROW_ID = 0;
        --- เริ่มอ่านจาก @ROW_ID แรก
         SELECT TOP 1 @ROW_ID = row_id , * FROM product WHERE row_id > @ROW_ID
         --  อ่านรายการต่อไป และต่อ ๆ ไป ก็เขียนเช่นเดิม ตามลำดับก่อนหลัง         SELECT TOP 1 @ROW_ID = row_id , * FROM product WHERE row_id > @ROW_ID
4. ช่วยให้ข้อมูลเป็นเอกลักษณ์ บางตาราง อาจจะมีข้อมูลรายการซ้ำกันทุกคอลัมน์ SQL SERVER จะงงทำไม่ได้เมื่อมีการปรับปรุงข้อมูล เราก็เอา Identity คอลัมน์มาช่วยได้
5. และอื่น ๆ ตามที่อยากทำและนำไปใช้

ข้อระวัง

สิ่งที่ต้องระวัง เคยเห็นบางระบบ เอา Identity คอลัมน์ไปทำ เป็น Key เพื่อเชื่อมโยงไปยังตารางอื่น  (PK -> FK) ไม่ควรทำเป็นอย่างยิ่ง ถ้าจะใช้ให้ตั้งเป็นรหัส เช่น product_code แล้วเอาไปเชื่อมโยงแทน เวลาเกิดปัญหาถ้าคนอื่นมาทำต่อ หรือมาจัดการข้อมูลโดยไม่ทราบว่ามีการเชื่อมโยง แล้วหากมีการ generate Identity ใหม่ก็บรรลัยเกิด 

สวัสดีช่วงหน้าร้อน
เนื้อหารายละเอียดสามารถอ่านได้ที่ หนังสือรอบรู้ประยุกต์ใช้ SQL Server สั่งซื้อออนไลน์ได้ที่ ศูนย์หนังสือจุฬา http://www.chulabook.com/description.asp?barcode=9789740335108