تحلیل فنی و معماری
ALTER روش استاندارد انتشار نسخه تازه یک رویه موجود است، چون object_id و مجوزهای متصل به شیء را حفظ میکند. این مزیت به معنای بیخطر بودن تغییر نیست؛ افزودن پارامتر اجباری، تغییر نوع ستون خروجی یا حذف Result Set میتواند برنامههای مصرفکننده را بلافاصله بشکند.
پیش از تغییر، وابستگیهای مستقیم و غیرمستقیم را بررسی کنید. کاتالوگ SQL Server بخشی از مراجع را نشان میدهد، اما فراخوانهای Dynamic SQL، کد برنامه و Jobها ممکن است پنهان بمانند. جستوجوی مخزن کد، Query Store و تست یکپارچه مکمل تحلیل کاتالوگ هستند.
نسخه جدید باید در اسکریپت کامل و قابل تکرار نگهداری شود. ویرایش دستی از طریق رابط گرافیکی، تاریخچه قابل اعتماد ایجاد نمیکند. فایل مهاجرت باید پیششرط نسخه، متن ALTER، آزمون پس از استقرار و در صورت لزوم اسکریپت بازگشت را کنار هم داشته باشد.
تغییر Query داخلی ممکن است Plan Cache را دگرگون کند. پس از انتشار فقط مدت یک اجرای آزمایشی را نبینید؛ توزیع پارامتر، تخمین Cardinality، Logical Reads، Spill، Memory Grant و مدت انتظار قفل را مقایسه کنید. Query Store برای مقایسه Plan پیش و پس از ALTER بسیار ارزشمند است.
ALTER داخل تراکنش قابل انجام است، ولی قفل Schema Modification میتواند اجرای همزمان را متوقف کند. زمان انتشار، اندازه تراکنش و وابستگی DDL را مدیریت کنید. در سامانه پرترافیک، استقرار کوتاه همراه Health Check و قابلیت Rollback امنتر از Batch بزرگ و مبهم است.
برای سازگاری عقبرو، پارامتر جدید را در صورت امکان اختیاری اضافه کنید و نام و نوع ستونهای خروجی را ثابت نگه دارید. اگر Breaking Change اجتنابناپذیر است، نسخه جدیدی از رویه با نام مستقل بسازید، مصرفکنندگان را مرحلهای منتقل و نسخه قدیمی را پس از دوره مشاهده حذف کنید.
ده مثال عملی و قابل اجرا
مثال 1: افزودن ستون خروجی
ابتدا Stub رویه را در صورت نبودن میسازیم و سپس با ALTER خروجی نسخه جدید را منتشر میکنیم.
IF OBJECT_ID(N'dbo.usp_AlterDemo01', N'P') IS NULL
EXEC(N'CREATE PROCEDURE dbo.usp_AlterDemo01 AS SELECT 1 AS Stub;');
GO
ALTER PROCEDURE dbo.usp_AlterDemo01
AS
BEGIN
SET NOCOUNT ON;
SELECT N'نسخه دوم' AS VersionName, SYSDATETIME() AS ChangedAt;
END;
GO
EXEC dbo.usp_AlterDemo01;
| خروجی یا وضعیت | مقدار مورد انتظار |
|---|
| نتیجه | VersionName = نسخه دوم |
نکته کاربردی: ALTER شناسه شیء و مجوزهای مستقیم آن را حفظ میکند.
مثال 2: افزودن پارامتر اختیاری
برای سازگاری با فراخوانهای موجود، پارامتر جدید را با مقدار پیشفرض اضافه میکنیم.
IF OBJECT_ID(N'dbo.usp_AlterDemo02', N'P') IS NULL
EXEC(N'CREATE PROCEDURE dbo.usp_AlterDemo02 @Amount decimal(18,2) AS SELECT @Amount AS Net;');
GO
ALTER PROCEDURE dbo.usp_AlterDemo02
@Amount decimal(18,2), @TaxRate decimal(5,2) = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT @Amount * (1 + @TaxRate / 100.0) AS GrossAmount;
END;
GO
EXEC dbo.usp_AlterDemo02 100000, 10;
| خروجی یا وضعیت | مقدار مورد انتظار |
|---|
| نتیجه | GrossAmount = 110000.00 |
نکته کاربردی: حذف یا تغییر نوع پارامتر یک Breaking Change است؛ افزودن اختیاری ریسک کمتری دارد.
مثال 3: اصلاح رفتار NULL
نسخه اولیه ممکن است NULL برگرداند؛ در نسخه اصلاحی قرارداد خروجی را با COALESCE پایدار میکنیم.
IF OBJECT_ID(N'dbo.usp_AlterDemo03', N'P') IS NULL
EXEC(N'CREATE PROCEDURE dbo.usp_AlterDemo03 @Value int AS SELECT @Value AS ResultValue;');
GO
ALTER PROCEDURE dbo.usp_AlterDemo03 @Value int = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT COALESCE(@Value, 0) AS ResultValue;
END;
GO
EXEC dbo.usp_AlterDemo03 NULL;
| خروجی یا وضعیت | مقدار مورد انتظار |
|---|
| نتیجه | ResultValue = 0 |
نکته کاربردی: رفتار NULL را در قرارداد API پایگاه داده و تستهای رگرسیون ثبت کنید.
مثال 4: افزودن OUTPUT
رویه موجود را طوری تغییر میدهیم که مقدار محاسبهشده از راه پارامتر خروجی نیز قابل دریافت باشد.
IF OBJECT_ID(N'dbo.usp_AlterDemo04', N'P') IS NULL
EXEC(N'CREATE PROCEDURE dbo.usp_AlterDemo04 AS SELECT 1 AS Stub;');
GO
ALTER PROCEDURE dbo.usp_AlterDemo04
@Input int, @Squared int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @Squared = @Input * @Input;
END;
GO
DECLARE @Answer int;
EXEC dbo.usp_AlterDemo04 9, @Answer OUTPUT;
SELECT @Answer AS SquaredValue;
| خروجی یا وضعیت | مقدار مورد انتظار |
|---|
| نتیجه | SquaredValue = 81 |
نکته کاربردی: مصرفکنندگان قدیمی باید پیش از تغییر امضای اجباری شناسایی شوند.
مثال 5: افزودن اعتبارسنجی و THROW
قانون تجاری را در ابتدای رویه بررسی میکنیم تا داده نامعتبر پیش از عملیات سنگین رد شود.
IF OBJECT_ID(N'dbo.usp_AlterDemo05', N'P') IS NULL
EXEC(N'CREATE PROCEDURE dbo.usp_AlterDemo05 @Qty int AS SELECT @Qty AS Qty;');
GO
ALTER PROCEDURE dbo.usp_AlterDemo05 @Qty int
AS
BEGIN
SET NOCOUNT ON;
IF @Qty <= 0 THROW 51005, N'تعداد باید مثبت باشد.', 1;
SELECT @Qty AS AcceptedQuantity;
END;
GO
EXEC dbo.usp_AlterDemo05 4;
| خروجی یا وضعیت | مقدار مورد انتظار |
|---|
| نتیجه | AcceptedQuantity = 4 |
نکته کاربردی: شماره خطای سفارشی بالاتر از 50000 انتخاب شود تا پایش آن ساده باشد.
مثال 6: تغییر نتیجه به شکل پایدار
نام و نوع ستونهای Result Set را صریح نگه میداریم تا نگاشت ORM پس از انتشار نشکند.
IF OBJECT_ID(N'dbo.usp_AlterDemo06', N'P') IS NULL
EXEC(N'CREATE PROCEDURE dbo.usp_AlterDemo06 AS SELECT 1 AS ItemId;');
GO
ALTER PROCEDURE dbo.usp_AlterDemo06
AS
BEGIN
SET NOCOUNT ON;
SELECT CAST(1 AS int) AS ItemId,
CAST(N'نمونه' AS nvarchar(50)) AS ItemTitle;
END;
GO
EXEC dbo.usp_AlterDemo06;
| خروجی یا وضعیت | مقدار مورد انتظار |
|---|
| نتیجه | ItemId = 1 و ItemTitle = نمونه |
نکته کاربردی: تغییر ترتیب، نام یا نوع ستون خروجی میتواند قرارداد مصرفکننده را بشکند.
مثال 7: حفظ مجوز پس از ALTER
مجوزی به Role میدهیم، رویه را ALTER میکنیم و سپس مجوز باقیمانده را کنترل میکنیم.
IF DATABASE_PRINCIPAL_ID(N'AlterReaders') IS NULL CREATE ROLE AlterReaders;
IF OBJECT_ID(N'dbo.usp_AlterDemo07', N'P') IS NULL
EXEC(N'CREATE PROCEDURE dbo.usp_AlterDemo07 AS SELECT 1 AS Value;');
GRANT EXECUTE ON dbo.usp_AlterDemo07 TO AlterReaders;
GO
ALTER PROCEDURE dbo.usp_AlterDemo07 AS SELECT 2 AS Value;
GO
SELECT permission_name
FROM sys.database_permissions
WHERE major_id = OBJECT_ID(N'dbo.usp_AlterDemo07');
| خروجی یا وضعیت | مقدار مورد انتظار |
|---|
| نتیجه | permission_name شامل EXECUTE است |
نکته کاربردی: DROP و CREATE ممکن است مجوز مستقیم را از بین ببرد؛ ALTER برای انتشار تغییر مناسبتر است.
مثال 8: مشاهده تاریخ تغییر
پس از ALTER، متادیتای sys.procedures را برای ممیزی زمان تغییر مشاهده میکنیم.
IF OBJECT_ID(N'dbo.usp_AlterDemo08', N'P') IS NULL
EXEC(N'CREATE PROCEDURE dbo.usp_AlterDemo08 AS SELECT 1 AS V;');
GO
ALTER PROCEDURE dbo.usp_AlterDemo08 AS SELECT 8 AS V;
GO
SELECT name, modify_date
FROM sys.procedures
WHERE object_id = OBJECT_ID(N'dbo.usp_AlterDemo08');
| خروجی یا وضعیت | مقدار مورد انتظار |
|---|
| نتیجه | یک ردیف با نام رویه و modify_date جدید |
نکته کاربردی: modify_date جایگزین کنترل نسخه نیست، اما برای بررسی سریع عملیات مفید است.
مثال 9: اصلاح الگوی غیر SARGable
نسخه جدید بهجای اعمال تابع روی ستون، مرز زمانی را روی پارامتر محاسبه میکند.
IF OBJECT_ID(N'dbo.usp_AlterDemo09', N'P') IS NULL
EXEC(N'CREATE PROCEDURE dbo.usp_AlterDemo09 @D date AS SELECT @D AS D;');
GO
ALTER PROCEDURE dbo.usp_AlterDemo09 @ReportDate date
AS
BEGIN
SET NOCOUNT ON;
DECLARE @NextDate date = DATEADD(day, 1, @ReportDate);
SELECT @ReportDate AS RangeStart, @NextDate AS RangeEnd;
-- الگوی بهینه: WHERE CreatedAt >= @ReportDate AND CreatedAt < @NextDate
END;
GO
EXEC dbo.usp_AlterDemo09 '2026-07-20';
| خروجی یا وضعیت | مقدار مورد انتظار |
|---|
| نتیجه | RangeStart = 2026-07-20 و RangeEnd = 2026-07-21 |
نکته کاربردی: پس از ALTER، Actual Plan و Logical Reads را با نسخه قبلی مقایسه کنید.
مثال 10: استقرار اتمی با تراکنش
چند تغییر DDL مرتبط را در تراکنش قرار میدهیم تا در صورت خطا نسخه نیمهکاره باقی نماند.
IF OBJECT_ID(N'dbo.usp_AlterDemo10', N'P') IS NULL
EXEC(N'CREATE PROCEDURE dbo.usp_AlterDemo10 AS SELECT 1 AS V;');
GO
BEGIN TRY
BEGIN TRANSACTION;
EXEC(N'ALTER PROCEDURE dbo.usp_AlterDemo10 AS BEGIN SET NOCOUNT ON; SELECT 10 AS V; END;');
COMMIT;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK;
THROW;
END CATCH;
GO
EXEC dbo.usp_AlterDemo10;
| خروجی یا وضعیت | مقدار مورد انتظار |
|---|
| نتیجه | V = 10 |
نکته کاربردی: در انتشار واقعی، قفلهای Schema و مدت تراکنش DDL را کوتاه نگه دارید.
سؤالات متداول
۱. ALTER PROCEDURE در SQL Server دقیقاً چه کاری انجام میدهد؟
این دستور برای تغییر تعریف یک رویه موجود بدون حذف هویت شیء بهکار میرود. اثر آن در سطح پایگاه داده ثبت میشود و باید با نامگذاری روشن، کنترل نسخه و آزمون قابل تکرار همراه باشد تا رفتار محیط توسعه و تولید یکسان بماند.
۲. از چه نسخهای میتوان از قابلیتهای جدید ALTER PROCEDURE استفاده کرد؟
هسته دستور در نسخههای قدیمی SQL Server نیز وجود دارد، اما گزینههایی مانند CREATE OR ALTER یا DROP IF EXISTS به نسخه وابستهاند. پیش از استقرار، Compatibility Level و مستندات همان نسخه را بررسی کنید.
۳. آیا استفاده از ALTER PROCEDURE هزینه نگهداری سامانه را کاهش میدهد؟
اگر استاندارد کدنویسی، ثبت تغییر و پایش اجرا رعایت شود، بله؛ منطق متمرکز و قابل ممیزی هزینه خطای انسانی را کم میکند. برای سامانههای حساس، بازبینی تخصصی و آزمون کارایی پیش از انتشار ارزش تجاری مستقیمی دارد.
۴. چه زمانی برای طراحی مبتنی بر ALTER PROCEDURE به مشاوره نیاز داریم؟
وقتی زنجیره وابستگی، مجوزها، حجم تراکنش یا حساسیت امنیتی زیاد است، ارزیابی معماری مفید خواهد بود. مشاوره SQL Server میتواند قرارداد ورودی و خروجی، طرح بازگشت و شاخصهای پایش را پیش از پیادهسازی تثبیت کند.
۵. تفاوت کاربرد ALTER PROCEDURE با اجرای مستقیم Query چیست؟
ALTER PROCEDURE یک واحد نامدار و قابل کنترل در چرخه استقرار ایجاد میکند، در حالی که Query مستقیم معمولاً پراکندهتر است. انتخاب صحیح به نیاز استفاده مجدد، امنیت، پارامتردهی، کش طرح اجرا و مسئولیت تیمها بستگی دارد.
۶. چگونه میتوان پیادهسازی ALTER PROCEDURE را برای پروژه سفارش داد؟
ابتدا ورودیها، خروجیها، SLA، سطح دسترسی و سناریوهای خطا مستند میشوند؛ سپس نمونه قابل آزمون، اسکریپت استقرار و معیار پذیرش تهیه میشود. این روش تحویل پروژه را قابل سنجش و پشتیبانی را سادهتر میکند.
۷. رایجترین خطای مرتبط با ALTER PROCEDURE چیست؟
رایجترین خطا نادیدهگرفتن زمینه اجرایی، Schema یا وابستگیهاست. بهویژه کنترل نوع پارامتر، Schema، مجوز و وابستگی ضروری است. ثبت متن کامل خطا، شماره خط و نام رویه در CATCH، یافتن علت را بسیار سریعتر میکند.
۸. ALTER PROCEDURE چه اثری بر Performance دارد؟
خود دستور فقط بخشی از مسئله است؛ کیفیت Queryهای داخل رویه، نوع پارامتر، آمار، ایندکس و همزمانی تعیینکنندهاند. Query Store، Actual Execution Plan و Extended Events ابزارهای مناسب سنجش پیش و پس از تغییر هستند.
۹. بهترین روش استفاده از ALTER PROCEDURE چیست؟
اسکریپت idempotent، نام Schema-qualified، کمترین سطح مجوز، قرارداد پایدار، SET NOCOUNT ON، مدیریت خطا و آزمون بازگشت را همزمان رعایت کنید. هر تغییر باید در کنترل نسخه و فرایند انتشار ثبت شود.
۱۰. آیا ALTER PROCEDURE با Azure SQL و نسخههای جدید سازگار است؟
بخش اصلی در SQL Server و Azure SQL Database قابل استفاده است، ولی برخی گزینههای امنیتی یا اجرای راهدور میان محصولات تفاوت دارند. اسکریپت را روی موتور و سطح سازگاری مقصد آزمایش کنید و به فرض سازگاری کامل اکتفا نکنید.