خانه SQL Server Identity در SQL Server چیست؟ آشنایی با نحوه استفاده از فیلد Identity SQL Server دستورات SQL نوشته شده توسط: مسعود طاهری تاریخ انتشار: ۰۱ تیر ۱۳۹۴ آخرین بروزرسانی: 04 اردیبهشت 1403 زمان مطالعه: 12 دقیقه ۳.۳ (۱۰) 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 پس از درج به آن تخصیص مییابد. چنانچه بخواهید به این مقدار دسترسی پیدا کنید، چند روش زیر ممکن هستند: استفاده از متغیر سیستمی Identity@@ استفاده از تابع ()Scope_Identity استفاده از تابع 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 بهرهمند شوید. چه رتبه ای میدهید؟ میانگین ۳.۳ / ۵. از مجموع ۱۰ اولین نفر باش معرفی نویسنده مقالات 20 مقاله توسط این نویسنده محصولات 65 دوره توسط این نویسنده مسعود طاهری مسعود طاهری مدرس و مشاور ارشد SQL Server & BI ، مدیر فنی پروژههای هوش تجاری (بیمه سامان، اوقاف، جین وست، هلدینگ ماهان و...) ، مدرس دورههــای SQL Server و هوشتجاری در شرکت نیکآموز و نویسنده کتاب PolyBase در SQL Server معرفی محصول ایمان باقری آموزش کوئری نویسی در sql server 2.190.000 تومان مقالات مرتبط ۰۲ آبان SQL Server ابزار Database Engine Tuning Advisor؛ مزایا، کاربردها و روش استفاده تیم فنی نیک آموز ۱۵ مهر SQL Server معرفی Performance Monitor ابزار مانیتورینگ SQL Server تیم فنی نیک آموز ۱۱ مهر SQL Server راهنمای جامع مانیتورینگ بکاپ ها در SQL Server تیم فنی نیک آموز ۰۸ مهر SQL Server Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ