بهینه سازی اشیاء SQL Server با CREATE OR ALTER

بهینه‌سازی مدیریت اشیاء پایگاه داده با CREATE OR ALTER در SQL Server

با استقرار یا اعمال تغییرات بر روی اشیاء پایگاه داده SQL Server مانند رویه‌های ذخیره شده (Stored Procedures)، ویوها (Views)، توابع (Functions) یا تریگرها (Triggers)، دو روش اصلی وجود دارد. روش اول، حذف (Drop) و سپس ایجاد مجدد (Recreate) شیء است. روش دوم، تغییر (Alter) شیء موجود است. در سناریوی حذف و ایجاد مجدد، نیاز به کدنویسی اضافی برای بررسی وجود شیء و سپس حذف آن دارید. همچنین در روش تغییر شیء، باید وجود آن را بررسی کنید. این مشکل اکنون با دستور **CREATE OR ALTER** که در SQL Server 2016 برای رویه‌های ذخیره شده، ویوها، توابع و تریگرها معرفی شد، حل شده است. این دستور، فرآیند توسعه و استقرار را به طور قابل توجهی ساده می‌کند.

دستور **CREATE OR ALTER** در SQL Server 2016 و Azure SQL Database برای رویه‌های ذخیره شده، ویوها، توابع و تریگرها معرفی شده است. این قابلیت پیشرفته، مزایای مهمی را ارائه می‌دهد:

* **ساده‌سازی اسکریپت‌های استقرار:** دیگر نیازی به نوشتن کد برای بررسی وجود شیء و سپس ایجاد یا تغییر آن نیست. این امر به کاهش پیچیدگی اسکریپت‌های استقرار پایگاه داده کمک می‌کند.
* **حفظ مجوزها و وابستگی‌ها:** مجوزها و وابستگی‌های مرتبط با شیء، حتی پس از تغییرات، حفظ می‌شوند. این ویژگی امنیت و یکپارچگی پایگاه داده را تضمین می‌کند.
* **همگام‌سازی با کنترل نسخه (Source Control):** اگر از سیستم‌های کنترل نسخه استفاده می‌کنید، شیء همیشه با آخرین تعریف خود ایجاد می‌شود و هماهنگی بین توسعه‌دهندگان را بهبود می‌بخشد.

ساختار کلی دستور **CREATE OR ALTER** به شرح زیر است:

CREATE OR ALTER { PROCEDURE | FUNCTION | VIEW | TRIGGER } procedure_name [ ( parameter_list ) ] [ RETURNS data_type ] AS { SQL_statement | EXTERNAL NAME assembly_name.class_name.method_name }

این دستور به شما اجازه می‌دهد تا بسته به نوع شیء (رویه‌ی ذخیره شده، تابع، ویو یا تریگر)، نام آن، لیست پارامترها، نوع بازگشتی (در صورت لزوم)، و بدنه اصلی شیء (`SQL_statement`) را مشخص کنید. این یک راهکار موثر برای مدیریت اشیاء دیتابیس SQL Server است.

در ادامه مثالی از یک رویه‌ی ذخیره شده را مشاهده می‌کنید که با استفاده از **CREATE OR ALTER** آن را ایجاد یا تغییر می‌دهیم:

CREATE OR ALTER PROCEDURE GetCustomers
AS
SELECT CustomerID, CustomerName
FROM Customers;

اینجا مثالی از یک ویو آورده شده است که با استفاده از **CREATE OR ALTER** آن را ایجاد یا تغییر می‌دهیم:

CREATE OR ALTER VIEW vw_ActiveCustomers
AS
SELECT CustomerID, CustomerName
FROM Customers
WHERE IsActive = 1;

مثالی از یک تابع که با استفاده از **CREATE OR ALTER** آن را ایجاد یا تغییر می‌دهیم، در اینجا نمایش داده شده است:

CREATE OR ALTER FUNCTION fn_GetCustomerName (@CustomerID INT)
RETURNS NVARCHAR(255)
AS
BEGIN
    DECLARE @CustomerName NVARCHAR(255);
    SELECT @CustomerName = CustomerName
    FROM Customers
    WHERE CustomerID = @CustomerID;
    RETURN @CustomerName;
END;

این مثالی از یک تریگر است که با استفاده از **CREATE OR ALTER** آن را ایجاد یا تغییر می‌دهیم:

CREATE OR ALTER TRIGGER tr_LogCustomerChanges
ON Customers
AFTER INSERT, UPDATE
AS
BEGIN
    INSERT INTO CustomerLog (CustomerID, ChangeType, ChangeDate)
    SELECT CustomerID, 'INSERT', GETDATE() FROM INSERTED WHERE NOT EXISTS (SELECT 1 FROM DELETED WHERE CustomerID = INSERTED.CustomerID);

    INSERT INTO CustomerLog (CustomerID, ChangeType, ChangeDate)
    SELECT CustomerID, 'UPDATE', GETDATE() FROM INSERTED WHERE EXISTS (SELECT 1 FROM DELETED WHERE CustomerID = INSERTED.CustomerID);
END;

برای درک بهتر نحوه عملکرد دستور **CREATE OR ALTER**، مراحل زیر را با یک مثال عملی دنبال می‌کنیم:

ابتدا یک پایگاه داده نمونه به نام `TestDB` ایجاد کرده و از آن استفاده می‌کنیم:

USE master;
GO
IF DB_ID('TestDB') IS NOT NULL
    DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB;
GO
USE TestDB;
GO

سپس جدول `Customers` و `CustomerLog` را ایجاد می‌کنیم تا داده‌های مشتریان و لاگ تغییرات را نگهداری کنند:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    City NVARCHAR(50)
);
GO

CREATE TABLE CustomerLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    ChangeType NVARCHAR(10),
    ChangeDate DATETIME
);
GO

چند رکورد نمونه به جدول `Customers` اضافه می‌کنیم:

INSERT INTO Customers (CustomerID, CustomerName, City) VALUES (1, 'Ali', 'Tehran');
INSERT INTO Customers (CustomerID, CustomerName, City) VALUES (2, 'Sara', 'Isfahan');
INSERT INTO Customers (CustomerID, CustomerName, City) VALUES (3, 'Reza', 'Shiraz');
GO

**Stored Procedure (رویه‌ی ذخیره شده):**

رویه‌ی ذخیره شده `GetCustomers` را با استفاده از **CREATE OR ALTER** ایجاد می‌کنیم تا تمام مشتریان را برگرداند:

CREATE OR ALTER PROCEDURE GetCustomers
AS
BEGIN
    SELECT CustomerID, CustomerName, City
    FROM Customers;
END;
GO

رویه‌ی ذخیره شده را اجرا می‌کنیم:

EXEC GetCustomers;
GO

اکنون، رویه‌ی ذخیره شده `GetCustomers` را برای فیلتر کردن مشتریان بر اساس شهر (City) با استفاده از **CREATE OR ALTER** تغییر می‌دهیم. این کار بدون نیاز به حذف و ایجاد مجدد رویه انجام می‌شود:

CREATE OR ALTER PROCEDURE GetCustomers
    @City NVARCHAR(50) = NULL
AS
BEGIN
    SELECT CustomerID, CustomerName, City
    FROM Customers
    WHERE City = ISNULL(@City, City);
END;
GO

رویه‌ی ذخیره شده را با پارامتر جدید اجرا می‌کنیم:

EXEC GetCustomers @City = 'Tehran';
GO

EXEC GetCustomers; -- برای مشاهده همه مشتریان
GO

**View (ویو):**

ویو `vw_CustomersInTehran` را با استفاده از **CREATE OR ALTER** ایجاد می‌کنیم تا مشتریان ساکن تهران را نمایش دهد:

CREATE OR ALTER VIEW vw_CustomersInTehran
AS
SELECT CustomerID, CustomerName
FROM Customers
WHERE City = 'Tehran';
GO

داده‌های ویو را انتخاب می‌کنیم:

SELECT * FROM vw_CustomersInTehran;
GO

ویو را برای نمایش مشتریان ساکن اصفهان با استفاده از **CREATE OR ALTER** تغییر می‌دهیم:

CREATE OR ALTER VIEW vw_CustomersInTehran -- نام ویو یکسان باقی می‌ماند، تعریف تغییر می‌کند
AS
SELECT CustomerID, CustomerName
FROM Customers
WHERE City = 'Isfahan'; -- تغییر به اصفهان
GO

مجدداً داده‌های ویو را انتخاب می‌کنیم تا تغییر را مشاهده کنیم:

SELECT * FROM vw_CustomersInTehran;
GO

**Function (تابع):**

تابع `fn_GetCustomerName` را با استفاده از **CREATE OR ALTER** ایجاد می‌کنیم که نام مشتری را بر اساس `CustomerID` برمی‌گرداند:

CREATE OR ALTER FUNCTION fn_GetCustomerName (@CustomerID INT)
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @CustomerName NVARCHAR(100);
    SELECT @CustomerName = CustomerName
    FROM Customers
    WHERE CustomerID = @CustomerID;
    RETURN @CustomerName;
END;
GO

تابع را فراخوانی می‌کنیم:

SELECT dbo.fn_GetCustomerName(1);
GO

تابع `fn_GetCustomerName` را برای افزودن شهر به نام مشتری (به عنوان مثال `Ali (Tehran)`) با استفاده از **CREATE OR ALTER** تغییر می‌دهیم:

CREATE OR ALTER FUNCTION fn_GetCustomerName (@CustomerID INT)
RETURNS NVARCHAR(200)
AS
BEGIN
    DECLARE @CustomerInfo NVARCHAR(200);
    SELECT @CustomerInfo = CustomerName + ' (' + City + ')'
    FROM Customers
    WHERE CustomerID = @CustomerID;
    RETURN @CustomerInfo;
END;
GO

مجدداً تابع را فراخوانی می‌کنیم تا تغییر را مشاهده کنیم:

SELECT dbo.fn_GetCustomerName(1);
GO

**Trigger (تریگر):**

تریگر `tr_LogCustomerChanges` را با استفاده از **CREATE OR ALTER** ایجاد می‌کنیم تا هر گونه `INSERT` یا `UPDATE` در جدول `Customers` را در جدول `CustomerLog` ثبت کند:

CREATE OR ALTER TRIGGER tr_LogCustomerChanges
ON Customers
AFTER INSERT, UPDATE
AS
BEGIN
    -- برای عملیات INSERT
    INSERT INTO CustomerLog (CustomerID, ChangeType, ChangeDate)
    SELECT i.CustomerID, 'INSERT', GETDATE() FROM INSERTED i LEFT JOIN DELETED d ON i.CustomerID = d.CustomerID WHERE d.CustomerID IS NULL;

    -- برای عملیات UPDATE
    INSERT INTO CustomerLog (CustomerID, ChangeType, ChangeDate)
    SELECT i.CustomerID, 'UPDATE', GETDATE() FROM INSERTED i INNER JOIN DELETED d ON i.CustomerID = d.CustomerID;
END;
GO

یک مشتری جدید اضافه می‌کنیم و یک مشتری موجود را به‌روزرسانی می‌کنیم تا تریگر فعال شود:

INSERT INTO Customers (CustomerID, CustomerName, City) VALUES (4, 'Mina', 'Yazd');
UPDATE Customers SET City = 'Kerman' WHERE CustomerID = 2;
GO

لاگ تغییرات را بررسی می‌کنیم:

SELECT * FROM CustomerLog;
GO

تریگر `tr_LogCustomerChanges` را تغییر می‌دهیم تا علاوه بر `INSERT` و `UPDATE`، عملیات `DELETE` را نیز ثبت کند:

CREATE OR ALTER TRIGGER tr_LogCustomerChanges
ON Customers
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- برای عملیات INSERT
    INSERT INTO CustomerLog (CustomerID, ChangeType, ChangeDate)
    SELECT i.CustomerID, 'INSERT', GETDATE()
    FROM INSERTED i
    LEFT JOIN DELETED d ON i.CustomerID = d.CustomerID
    WHERE d.CustomerID IS NULL;

    -- برای عملیات UPDATE
    INSERT INTO CustomerLog (CustomerID, ChangeType, ChangeDate)
    SELECT i.CustomerID, 'UPDATE', GETDATE()
    FROM INSERTED i
    INNER JOIN DELETED d ON i.CustomerID = d.CustomerID;

    -- برای عملیات DELETE
    INSERT INTO CustomerLog (CustomerID, ChangeType, ChangeDate)
    SELECT d.CustomerID, 'DELETE', GETDATE()
    FROM DELETED d
    LEFT JOIN INSERTED i ON d.CustomerID = i.CustomerID
    WHERE i.CustomerID IS NULL;
END;
GO

یک مشتری را حذف و یک مشتری دیگر را به‌روزرسانی می‌کنیم تا تریگر جدید فعال شود:

DELETE FROM Customers WHERE CustomerID = 3;
UPDATE Customers SET CustomerName = 'Reza Khan' WHERE CustomerID = 1;
GO

مجدداً لاگ تغییرات را بررسی می‌کنیم تا تغییرات ثبت شده توسط تریگر جدید را مشاهده کنیم:

SELECT * FROM CustomerLog;
GO