Identity چیست – قسمت سوم

همانطور که در قسمت اول و قسمت دوم این مقاله اشاره شد مقدار موجود در فیلدهایی از نوع Identity توسط SQL Server اعمال می‌گردد. در صورتیکه بنا به دلایلی خودتان بخواهید به این فیلدها مقدار Assign کنید چه راهی برای انجام اینکار وجود دارد؟

شاید اولین چیزی که به ذهن برخی دوستان برسد این است که با استفاده از دستور Insert رکوردی را در جدول درج کرده و به فیلد Identity مقدار دلخواه خود را تخصیص دهیم.باید خدمت این گروه از دوستان عرض کنم که در صورت تلاش برای درج مقدار در یک فیلد Identity منجر به ایجاد خطای زیر خواهد شد.

INSERT Employees(ID,FirstName,LastName) VALUES (100 , N'خدیجه' , N'افروزنیا')
GO

Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Employees' when IDENTITY_INSERT is set to OFF.

راه حل دیگری که به ذهن برخی دیگر از دوستان می‌رسد این است که : «یک لحظه این فیلد را از حالت Identity خارج کرده و پس از درج رکوردهای مورد نظر دوباره آن را به Identity باز گردانیم.»

 باید اشاره کنم که انجام این‌کار به هیچ عنوان توصیه نمی‌شود. زیرا

1.  با انجام اینکار یک جدول جدید مشابه جدول شما ایجاد شده و فیلد مورد نظر از حالت Identity خارج می‌شود.
2.  انتقال رکوردها از جدول قدیم به جدول جدید
3.  جدول جدید جایگزین جدول قدیمی خواهد شد.

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Employees
(
ID int NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL
)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Employees SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.Employees)
EXEC('INSERT INTO dbo.Tmp_Employees (ID, FirstName, LastName)
SELECT ID, FirstName, LastName FROM dbo.Employees WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Employees
GO
EXECUTE sp_rename N'dbo.Tmp_Employees', N'Employees', 'OBJECT'
GO
COMMIT

با کلیک کردن بر روی دکمه Saveکدهای زیر اجرا شده که این کدها دقیقاً شامل مراحل 1 تا 3 می‌باشد.

کلیه این فعالیت‌ها توسط SQL Serverانجام می‌شود. و در طی اجرای این فعالیت‌ها جدول  Lockمی‌باشد. اما نکته مهم این است که اگر حجم رکوردهای موجود در جدول زیاد باشد مرحله 2 (انتقال رکوردها) به شدت زمان‌بر است.

خوب پس راه حل چیست؟

(سوال رو که یادتون هست؟!  قرار است در فیلد Identity خودمان مقدار درج کنیم.)
برای انجام اینکار با استفاده از دستور SET IDENTITY_INSERT یک لحظه مقدار دهی اتوماتیک به ازای فیلد Identity را قطع کرده و خودمان به آن مقدار تخصیص می‌دهیم.

مثال زیر نحوه انجام این‌کار را نمایش می‌دهد.

SET IDENTITY_INSERT Employees ON
GO
INSERT Employees(ID,FirstName,LastName) VALUES (100 , N'خدیجه' , N'افروزنیا')
GO

پس از اتمام کار با استفاده از دستور زیر مجدداً مقدار دهی اتوماتیک به ازای Identityجدول مورد نظر اعمال خواهد شد.

SET IDENTITY_INSERT Employees OFF

ممکن است این سوال به ذهنتان برسد که آیا با اجرای دستور SETIDENTITY_INSERT Employees ON کاربران دیگر امکان تخصیص مقدار به ازای فیلد Identityرا خواهند داشت و یا خیر؟

در جواب باید گفت : خیر، چون که تاثیر اجرا دستور SETIDENTITY_INSERT Employees ON روی Sessionی است که این دستور را اجرا کرده است.

دانلود این مقاله آموزشی