همانطور که در قسمت اول و قسمت دوم این مقاله اشاره شد مقدار موجود در فیلدهایی از نوع 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 باز گردانیم.»
باید اشاره کنم که انجام اینکار به هیچ عنوان توصیه نمیشود. زیرا
- با انجام اینکار یک جدول جدید مشابه جدول شما ایجاد شده و فیلد مورد نظر از حالت Identity خارج میشود.
- انتقال رکوردها از جدول قدیم به جدول جدید
- جدول جدید جایگزین جدول قدیمی خواهد شد.
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ی است که این دستور را اجرا کرده است.
دانلود این مقاله آموزشی
2 دیدگاه
عاطفه حسن پور
خسته نباشید جناب مهندس ممنون بابت اموزش خوبتون
افسانه
بسیار عالی بود.
سپاس گزارم