Identity در SQL Server چیست؟ آشنایی با نحوه استفاده از فیلد Identity

Identity در SQL Server چیست؟ شاید این سؤال در ابتدای ورود به مسیر یادگیری زبان T-SQL و کار با RDBMS ها در ذهن شما شکل گرفته باشد. به‌صورت کلی، ویژگی Identity مشابه یک ابزار قدرتمند برای مدیریت شناسه‌های منحصربه‌فرد در SQL Server به کار می‌رود. در مسیر شغلی مدیر پایگاه داده (DBA)، استفاده از ویژگی Identity کمک می‌کند تا فرآیندهای مربوط به مدیریت داده‌ها تسهیل یابند و از یکپارچگی و کارایی داده‌ها اطمینان حاصل شود. بنابراین، صرف نظر از اینکه شما درحال طراحی یک پایگاه داده جدید باشید یا بخواهید دیتابیس موجود را بهبود دهید، بهتر است بدانید که چگونه ویژگی Identity در SQL Server می‌تواند استراتژی‌های مربوط به مدیریت دیتای شما را توسعه دهد. شما می‌توانید علاوه‌بر مطالعه این مطلب، با رجوع به مقاله آموزش SQL Server ، سایر مباحث مهم مربوطه را به زبان ساده و با جزئیات بیاموزید.

Identity در SQL Server چیست؟

ویژگی Identity در SQL Server ، یک خاصیت ستون است که به منظور تولید خودکار اعداد منحصربه‌فرد و با افزایش ترتیبی استفاده می‌شود. معمولاً این ویژگی برای ستون های کلید اصلی (PK | Primary Key) اعمال می‌شود و به حفظ شناسه‌های منحصربه‌فرد (بدون نیاز به ورودی دستی) کمک می‌کند. این موضوع برای حفظ یکپارچگی داده‌ها و مدیریت ارتباطی ضروری محسوب می‌شود. به بیان ساده، هنگامی که این Attribute به ازای یک فیلد عددی تنظیم گردد و چنانچه رکوردی در جدول مربوط به Identity درج شود، فیلد Identity به‌طور اتوماتیک مقداری را دریافت خواهد کرد. نحوه دریافت مقدار به ازای فیلد Identity ، با توجه به آخرین مقدار آن و گام افزایش است. برای ایجاد یک فیلد از نوع Identity در SQL Server ، می‌توانید در زمان ایجاد جدول، این Attribute را به فیلد موردنظر خود تخصیص دهید.

مزایای استفاده از Identity در SQL Server

 مزایای استفاده از Identity در SQL Server عبارتند از:

• تولید خودکار عددی
• مدیریت داده‌ها به‌صورت تسهیل‌یافته
• کارایی و عملکرد بهتر
• سهولت استفاده در تکثیر (Replication) و همگام سازی داده ها (Data Synchronization)
• پشتیبانی از قابلیت ارتقا (Scalability)
• کاهش پیچیدگی منطق برنامه
• مفید برای دریافت Log و پیگیری دیتای تاریخی
• انعطاف‌پذیری در پیکربندی

کاربرد Identity در SQL Server چیست؟

ویژگی Identity در SQL Server دارای چندین کاربرد عملی در طراحی و مدیریت پایگاه داده است. برخی از سناریوهای رایج آن در ادامه شرح داده می‌شوند:

• ایجاد کلید اصلی (Primary Key Generation): به‌طور خودکار Identity در SQL Server شناسه‌های منحصربه‌فرد برای رکوردهای جدید ایجاد می‌کند. این موضوع برای کلیدهای اصلی در جداول پایگاه داده ضروری است. برای درک بهتر، مقاله انواع کلید در پایگاه داده و نحوه ایجاد جدول را به‌عنوان آموزش‌های تکمیلی مطالعه کنید.
• پیگیری سفارش (Order Tracking): در سیستم‌های پیگیری سفارش باید هر سفارش یک شناسه منحصربه‌فرد به‌منظور تمایز از سایر سفارشات داشته باشد.
• عددگذاری افزایشی: در سناریوهایی که نیاز به شماره‌گذاری ترتیبی دارید، مانند زمانی که شماره فاکتور، شماره بلیط یا هر سری دیگر از اقلام مرتب‌شده عددی نیاز است، می‌توان Identity را به کار برد.
• همگام‌سازی داده‌ها: با اطمینان از اینکه رکوردها در چندین سیستم یا مکان دارای شناسه‌های منحصربه‌فرد و سازگار هستند، به تسهیل همگام‌سازی پایگاه‌های داده می‌پردازد.
• Logging و حسابرسی: Identity با ارائه یک شناسه منحصربه‌فرد برای هر تراکنش یا ورودی Log، اصطلاحاً به لاگ‌گیری و حسابرسی تغییرات درون پایگاه داده کمک خواهد کرد.
• تسهیل عملیات Import/Export: در حین Import یا Export دیتا، داشتن یک شناسه منحصربه‌فرد در هر ردیف، فرآیند آپدیت رکوردهای موجود و افزودن موارد جدید را ساده‌سازی می‌کند. یک مقاله آموزشی تحت عنوان دستور Update ارائه شده است که شما می‌توانید با مطالعه آن، با ساختار این Command آشنا شوید.
• تکثیر (Replication): در رپلیکیشن کردن پایگاه داده، Identity در SQL Server با اطمینان از اینکه رکوردها در سیستم‌های توزیع‌شده به‌طور منحصر قابل شناسایی هستند، به حفظ انسجام داده در پایگاه‌های داده رپلیکیت‌شده کمک می‌کند.

بررسی نحوه ایجاد Identity

در این بخش از مطلب، به بررسی چند مثال می‌پردازیم تا به کمک آن‌ها بتوانید Identity در SQL Server را به‌خوبی درک کنید.

نحوه ایجاد یک فیلد از نوع Identity

مثال ۱: این مثال، نحوه ایجاد یک فیلد از نوع Identity را نمایش می‌دهد.

USE tempdb
GO
CREATE TABLE Customers1
(
ID INT IDENTITY,-- ID INT IDENTITY(1,1)
Name NVARCHAR(100),
[Address] NVARCHAR(200)
)
GO

در مثال فوق، فیلد ID از نوع Identity تعریف شده است. در این حالت (ID int IDENTITY) مقدار شروع و گام افزایش به ازای این فیلد ۱ درنظر گرفته خواهد شد. در چنین شرایطی، اگر چند رکورد زیر را به ازای این جدول درج کنید، مقدار Identity مشابه زیر خواهد بود:

INSERT INTO Customers1 (Name,[Address]) VALUES
(N'مسعود',N'میانه'),
(N'فرید',N'میانه'),
(N'احمد',N'میانه')
GO
SELECT * FROM Customers1

نحوه ایجاد یک فیلد از نوع Identity همراه با مقدار شروع و گام افزایشی

مثال ۲: این مثال، نحوه ایجاد یک فیلد از نوع Identity در SQL Server به‌همراه مقدار شروع و گام افزایش را مشخص می‌کند.

USE tempdb
GO
CREATE TABLE Customers2
(
ID INT IDENTITY(100,2),
Name NVARCHAR(100),
[Address] NVARCHAR(200)
)
GO

در کوئری فوق، فیلد ID از نوع Identity تعریف شده و مقدار شروع آن از ۱۰۰ و همچنین گام افزایش ۲ برای آن درنظر گرفته شده است. در این صورت، اگر چند رکورد زیر را به ازای این جدول درج کنید. مقدار Identity مشابه زیر خواهد بود:

INSERT INTO Customers2 (Name,[Address]) VALUES
(N'مسعود',N'میانه'),
(N'فرید',N'میانه'),
(N'احمد',N'میانه')
GO
SELECT * FROM Customers2
GO

به‌واسطه دو مثال مذکور، یک دید کلی از نحوه استفاده از Identity در SQL Server به‌دست آوردید.

نحوه دسترسی به مقدار Identity

تا این بخش از مقاله، متوجه شدید که مقدار Identity در SQL Server پس از درج به آن تخصیص می‌یابد. چنانچه بخواهید به این مقدار دسترسی پیدا کنید، چند روش زیر ممکن هستند:

1. استفاده از متغیر سیستمی Identity@@
2. استفاده از تابع ()Scope_Identity
3. استفاده از تابع Ident_Current

هر سه این توابع، مقدار Identity ایجادشده برای جداول را نمایش می‌دهند، اما تفاوت‌هایی با یکدیگر دارند که در ادامه، این تفاوت‌ها بررسی می‌شوند.

۱- متغییر سیستمی Identity@@

این متغییر سیستمی حاوی آخرین Identity ایجادشده به ازای Session جاری شما است. اگر به‌واسطه دستور Insert شما، Identity دیگری در یک حوزه دیگر (مانند یک Trigger) ایجاد شود، مقدار موجود در این متغیر حاوی آخرین Identity ایجادشده، یعنی Identity ایجادشده توسط آن تریگر و نه خود جدول، است.

۲- استفاده از تابع ()Scope_Identity

با استفاده از تابع ()Scope_Identity می‌توان آخرین Identify ایجادشده به ازای Session جاری را به‌دست آورد. توجه کنید که مقادیر Identity ایجادشده توسط سایر حوزه‌ها، تأثیری در مقدار بازگشتی حاصل از این تابع ندارد.

۳- استفاده از تابع ()ident_Current

تابع ()ident_Current آخرین مقدار Identity موجود در یک جدول را نمایش می‌دهد. ذکر این نکته ضروری است که Identity ایجادشده توسط سایر Session ها نیز روی خروجی این تابع مؤثر است؛ چراکه این تابع، آخرین Identity موجود در جدول را به شما نمایش می‌دهد و Identity ایجادشده به ازای یک Session را نشان نخواهد داد. 

برای به‌دست آوردن یک Identity در SQL Server ، باید پس از درج رکورد در جدول موردنظر، متغییر سیستمی @@Identity یا توابع Scope_Identity یا Ident_Current را همانند مثال زیر Select کنید:

USE TEMPDB
GO
IF OBJECT_ID(N'Employees', N'U') IS NOT NULL
DROP TABLE Employees1;
GO
CREATE TABLE Employees
(
ID int IDENTITY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50)
)
GO
INSERT INTO Employees (FirstName,LastName) VALUES (N'مسعود',N'طاهری')
GO
SELECT @@IDENTITY AS [@@IDENTITY]
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY()]
SELECT IDENT_CURRENT('Employees1') AS [IDENT_CURRENT('Employees1')]
GO

شایان ذکر است که یکی از مباحث مهم در زمینه Identity در SQL Server ، تأثیر Scope بر مقدار Identity است.

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

نحوه مقداردهی Identity در SQL Server

زمانی که حرف از مقداردهی Identity در SQL Server می‌شود، احتمالاً بسیاری از افراد استفاده از دستور 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 ، کدها اجرا شده که این کدها دقیقاً شامل مراحل ۱ تا ۳ است.

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

راه حل پیشنهادی برای درج مقدار در فیلد Identity 

برای آن که بتوانید فیلد Identity در SQL Server را مقداردهی کنید، لازم است از دستور SET IDENTITY_INSERT استفاده کنید. با کمک دستور 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 ای بوده که این دستور را اجرا کرده است.

نحوه استفاده از دستور DBCC CheckIdent

برخی از مواقع، کاربران خواستار این هستند که مقدار Identity را Reset کنند. برای انجام این کار می‌توانید از دستور DBCC CheckIdent استفاده نمایید.

شکل کلی دستور DBCC CheckIdent به صورت زیر است:

DBCC CHECKIDENT
)
table_name
[ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]
(

پارامترهای مربوط به این دستور عبارتند از:

• Table_Name: این پارامتر نام جدولی را مشخص می‌کند که می‌خواهید مقدار آن را دستکاری کنید.
• NORESEED: در صورتی که از این کلمه کلیدی به‌عنوان پارامتر دوم استفاده شود، باعث می‌شود که تغییری در مقدار Identity به‌وجود نیاید.
• RESEED: در صورتی که از این کلمه کلیدی به‌عنوان پارامتر سوم استفاده شود، باعث می‌شود که تغییری در مقدار Identity با توجه به پارامتر چهارم به‌وجود آید.
• new_reseed_value: این پارامتر مشخص می‌کند که Identity در SQL Server باید به چه مقداری Reset شود.

نکته: توجه کنید که ورود پارامتر دوم و سوم اختیاری بوده و اجباری در استفاده از آن‌ها وجود ندارد.

قبل از بررسی مثال‌ها، Script های زیر را برای ایجاد جدول و رکوردهای مربوط به آن اجرا کنید:

USE tempdb
GO
CREATE TABLE Customers1
)
ID INT IDENTITY,
Name NVARCHAR(100),
[Address] NVARCHAR(200)
(
GO
INSERT INTO Customers1 (Name,[Address]) VALUES
(N'مسعود',N'میانه'),
(N'فرید',N'میانه'),
(N'احمد',N'میانه'),
GO
SELECT * FROM Customers1

مثال هایی از دستور DBCC CHECKIDENT

مثال‌های زیر، حالت‌های مختلف مربوط به دستور DBCC CHECKIDENT را به شما نشان می‌دهد.

مثال ۱: در صورت ورود پارامتر اول، صرفاً مقدار جاری Identity به شما نمایش داده می‌شود:

USE tempdb
GO
DBCC CHECKIDENT (Customers1);
GO

مثال ۲: در صورت استفاده از NORESEED به‌عنوان پارامتر دوم، صرفاً مقدار جاری Identity به شما نمایش داده می‌شود. البته ممکن است هنگام استفاده از این حالت، در برخی از شرایط، خروجی مقدار صحیحی نباشد.

USE tempdb
GO
;DBCC CHECKIDENT (Customers1,NORESEED)
GO

مثال ۳: درصورت استفاده از RESEED به‌عنوان پارامتر دوم، صرفاً مقدار جاری Identity به شما نمایش داده می‌شود.

USE tempdb
GO
DBCC CHECKIDENT (Customers1,RESEED);
GO

مثال ۴: در صورت استفاده از RESEED به‌عنوان پارامتر دوم و تعیین مقدار جدید برای Identity در SQL Server ، از این به بعد مقدار تولیدشده برای Identity برابر با new_reseed_value+1 خواهد بود.

USE tempdb
GO
;DBCC CHECKIDENT (Customers1,RESEED,300)
GO

در این حالت، چنانچه رکوردی در جدول درج شود، مقدار Identity برابر با new_reseed_value+1 خواهد بود.

INSERT INTO Customers1 (Name,[Address]) VALUES (N'خدیجه',N'میانه')
GO
SELECT * FROM Customers1

جمع بندی: Identity Property چیست؟

در این مقاله به بررسی چیستی Identity در SQL Server پرداخته و علاوه‌بر مزایا و کاربردهای آن، چگونگی ایجاد این نوع از فیلد، نحوه دسترسی به مقدار آن و درج Value جدید به آن را مورد بررسی قرار دادیم. ضمن اینکه در بخش پایانی آموزش، به دستور DBCC CheckIdent به‌همراه مثال اشاره شد. اگر می‌خواهید مهارت‌هایتان را در زمینه کار با SQL Server و کوئری‌نویسی گسترش دهید، می‌توانید از دو مطلب پرکاربردترین دستورات SQL Server و اسکریپت های SQL بهره‌مند شوید.