تکامل SQL Server امکانات قدیمی و رویکردهای جدید

تکامل SQL Server: بررسی امکانات قدیمی و رویکردهای نوین در مدیریت پایگاه داده

اکنون که سال‌ها از معرفی SQL Server می‌گذرد، به یادآوری برخی از جنبه‌های قدیمی و تغییراتی که در این سال‌ها رخ داده‌اند، خالی از لطف نیست. برخی از ویژگی‌ها کاملاً حذف شده‌اند، برخی جایگزین‌های مدرن و بهتری پیدا کرده‌اند و تعداد کمی نیز همچنان در پلتفرم باقی مانده‌اند. هدف این مقاله مرور این قابلیت‌ها، تاریخچه آن‌ها و اشاره به رویکردهای کنونی است تا نگاهی به تکامل SQL Server بیندازیم.

تنظیمات `sp_configure` و تغییرات آن

در گذشته، تنظیمات SQL Server عمدتاً از طریق رویه ذخیره‌شده `sp_configure` انجام می‌شد. برای تغییر گزینه‌های پیشرفته، ابتدا باید آن‌ها را فعال می‌کردید.

EXEC sp_configure ‘show advanced options’, 1;

RECONFIGURE;

پس از فعال‌سازی، می‌توانستید گزینه‌های مختلفی مانند حداکثر درجه موازی‌سازی (max degree of parallelism) را تنظیم کنید.

EXEC sp_configure ‘max degree of parallelism’, 4;

RECONFIGURE;

امروزه، بسیاری از این تنظیمات را می‌توان به راحتی از طریق رابط کاربری گرافیکی SQL Server Management Studio (SSMS) یا Azure Data Studio مدیریت کرد، که تجربه کاربری را به طور قابل توجهی بهبود بخشیده است. با این حال، `sp_configure` همچنان ابزاری قدرتمند برای اتوماسیون و اسکریپت‌نویسی باقی مانده است.

وداع با `sp_dboption`

رویه ذخیره‌شده `sp_dboption` برای تنظیم گزینه‌های سطح پایگاه داده مانند وضعیت فقط خواندنی (READ_ONLY) استفاده می‌شد.

EXEC sp_dboption ‘AdventureWorks’, ‘READ_ONLY’, ‘TRUE’;

این رویه در نسخه‌های جدیدتر SQL Server منسوخ شده است. جایگزین مدرن و استاندارد SQL برای این کار، دستور `ALTER DATABASE` است که وضوح و کنترل بیشتری را فراهم می‌کند.

ALTER DATABASE AdventureWorks SET READ_ONLY ON;

`DBCC CHECKTABLE` و گزینه `WITH TABLOCK`

دستورات `DBCC` (Database Console Commands) همیشه برای نگهداری و عیب‌یابی پایگاه داده حیاتی بوده‌اند. یکی از استفاده‌های رایج، `DBCC CHECKTABLE` برای بررسی یکپارچگی جدول بود. در نسخه‌های قدیمی‌تر، گزینه `WITH TABLOCK` اغلب برای بهبود عملکرد در جداول بزرگ استفاده می‌شد، زیرا به SQL Server اجازه می‌داد به جای قفل‌های ردیفی یا صفحه‌ای، یک قفل انحصاری بر روی کل جدول اعمال کند.

DBCC CHECKTABLE (‘AdventureWorks.Person.Address’) WITH TABLOCK;

امروزه، با پیشرفت‌های انجام شده در موتور ذخیره‌سازی و قابلیت‌های پردازش موازی، نیاز به `WITH TABLOCK` در بسیاری از سناریوها کاهش یافته است. همچنین، می‌توان از گزینه‌هایی مانند `WITH NO_INFOMSGS` برای کاهش خروجی و تمرکز بر خطاها استفاده کرد.

DBCC CHECKTABLE (AdventureWorks.Person.Address) WITH NO_INFOMSGS;

`dbcc opentran` – همچنان مفید

`DBCC OPENTRAN` یک دستور مفید برای شناسایی تراکنش‌های باز (open transactions) در پایگاه داده فعلی است. تراکنش‌های باز طولانی‌مدت می‌توانند باعث مسدود شدن (blocking) و افزایش حجم فایل لاگ شوند. این دستور هنوز هم در عیب‌یابی مشکلات عملکردی و نگهداری پایگاه داده بسیار ارزشمند است.

DBCC OPENTRAN;

تفاوت `PRINT` و `SELECT`

در T-SQL، هر دو `PRINT` و `SELECT` برای نمایش اطلاعات به کاربر استفاده می‌شوند، اما با اهداف متفاوتی. `PRINT` عمدتاً برای نمایش پیام‌ها، هشدارها یا اطلاعات وضعیت در پنجره پیام‌ها در SSMS یا خروجی کنسول به کار می‌رود.

PRINT ‘Hello, World!’;

در مقابل، `SELECT` برای بازگرداندن مجموعه‌ نتایج (result sets) داده‌ای استفاده می‌شود و نتایج آن در پنجره نتایج (results pane) نمایش داده می‌شوند.

SELECT ‘Hello, World!’;

در حالی که هر دو می‌توانند یک رشته را نمایش دهند، `PRINT` برای اشکال‌زدایی (debugging) و پیام‌های اطلاع‌رسانی و `SELECT` برای بازیابی داده‌های واقعی مناسب‌تر است.

انقراض انواع داده `TEXT`, `NTEXT`, `IMAGE`

یکی از بزرگترین تغییرات در مدیریت داده‌ها، حذف انواع داده قدیمی `TEXT`, `NTEXT`, و `IMAGE` است. این انواع داده محدودیت‌ها و مشکلات عملکردی خاصی داشتند. جایگزین‌های مدرن و توصیه شده برای آن‌ها به ترتیب `VARCHAR(MAX)`, `NVARCHAR(MAX)`, و `VARBINARY(MAX)` هستند. این انواع جدید نه تنها انعطاف‌پذیری بیشتری در ذخیره‌سازی داده‌های بزرگ (Large Object – LOB) فراهم می‌کنند، بلکه عملکرد بهتری نیز دارند و با بقیه موتور SQL Server سازگاری بیشتری دارند.

`SET ROWCOUNT` در مقابل `TOP`

دستور `SET ROWCOUNT` در گذشته برای محدود کردن تعداد ردیف‌های بازگردانده شده توسط یک عبارت `SELECT` یا تحت تأثیر قرار گرفته توسط `INSERT`, `UPDATE`, `DELETE` استفاده می‌شد.

SET ROWCOUNT 10;

SELECT * FROM MyTable ORDER BY SomeColumn;

SET ROWCOUNT 0; — برای غیرفعال کردن محدودیت

اگرچه هنوز در SQL Server موجود است، اما استفاده از `TOP` (یا `OFFSET-FETCH` در SQL Server 2012 به بعد) به شدت توصیه می‌شود. `TOP` به طور صریح‌تر و قابل کنترل‌تری تعداد ردیف‌ها را محدود می‌کند و به راحتی با دستور `ORDER BY` ترکیب می‌شود.

SELECT TOP 10 * FROM MyTable ORDER BY SomeColumn;

`RAISERROR` و جایگزین `THROW`

`RAISERROR` یک دستور قدیمی اما همچنان مفید برای گزارش خطاها و هشدارها در T-SQL است. این دستور به شما امکان می‌دهد شماره خطا، شدت (severity) و وضعیت (state) خطا را مشخص کنید.

RAISERROR (‘An error occurred’, 16, 1);

از SQL Server 2012 به بعد، دستور `THROW` معرفی شد که رویکرد مدرن‌تری برای مدیریت خطاها فراهم می‌کند و با مدیریت خطای ساختاریافته (structured error handling) در سایر زبان‌های برنامه‌نویسی سازگارتر است. `THROW` معمولاً همراه با بلوک‌های `TRY…CATCH` استفاده می‌شود.

THROW 50001, ‘An error occurred’, 1;

تکامل انواع داده تاریخ و زمان (`DATETIME`)

در گذشته، `DATETIME` و `SMALLDATETIME` تنها گزینه‌های موجود برای ذخیره داده‌های تاریخ و زمان بودند. `DATETIME` دقت میلی‌ثانیه را تا سه رقم اعشار (0.000، 0.003، 0.007 ثانیه) و محدوده سال 1753 تا 9999 را فراهم می‌کرد، در حالی که `SMALLDATETIME` فقط تا دقیقه دقت داشت و محدوده کوچکتری را پوشش می‌داد.

با نسخه‌های جدیدتر، انواع داده جدیدی مانند `DATETIME2`, `DATE`, `TIME`, و `DATETIMEOFFSET` معرفی شدند. `DATETIME2` دقت بسیار بالاتری (تا 7 رقم اعشار میلی‌ثانیه) و محدوده وسیع‌تری (سال 0001 تا 9999) را ارائه می‌دهد. `DATE` فقط تاریخ و `TIME` فقط زمان را ذخیره می‌کند، و `DATETIMEOFFSET` شامل اطلاعات منطقه زمانی (timezone offset) است که برای برنامه‌های توزیع‌شده جهانی بسیار مفید است.

ستون `IDENTITY` و توابع مرتبط

ستون `IDENTITY` برای تولید خودکار مقادیر عددی منحصر به فرد (معمولاً کلید اصلی) در یک جدول استفاده می‌شود. برای کار با این ستون‌ها، چندین تابع مفید وجود دارد:

`IDENT_SEED` : مقدار اولیه (seed) ستون `IDENTITY` را برمی‌گرداند.

IDENT_SEED(‘TableName’)

`IDENT_INCR` : مقدار افزایش (increment) ستون `IDENTITY` را برمی‌گرداند.

IDENT_INCR(‘TableName’)

`IDENT_CURRENT` : آخرین مقدار `IDENTITY` تولید شده برای یک جدول خاص را برمی‌گرداند، صرف نظر از اسکوپ (scope) یا جلسه (session).

IDENT_CURRENT(‘TableName’)

علاوه بر این، توابع `SCOPE_IDENTITY()` و `@@IDENTITY` برای بازیابی آخرین مقدار `IDENTITY` تولید شده در اسکوپ فعلی (یا کل سرور برای `@@IDENTITY`) وجود دارند. `SCOPE_IDENTITY()` به طور کلی ترجیح داده می‌شود زیرا فقط مقادیر تولید شده در اسکوپ جاری را برمی‌گرداند و از اثرات جانبی تریگرها جلوگیری می‌کند.

SELECT SCOPE_IDENTITY();

SELECT @@IDENTITY;

`COMPUTE BY` – منسوخ و جایگزین شده

دستور `COMPUTE BY` برای ایجاد سطرهای خلاصه در مجموعه‌ نتایج در گزارش‌گیری‌های ساده استفاده می‌شد. این دستور قادر بود عملیات تجمیعی مانند `SUM`، `AVG`، `MIN`، `MAX` و `COUNT` را برای گروه‌های خاصی از داده‌ها انجام دهد.

SELECT SalesTotal, CustomerID FROM Orders COMPUTE SUM(SalesTotal) BY CustomerID;

`COMPUTE BY` در نسخه‌های جدیدتر SQL Server منسوخ شده و استفاده از آن توصیه نمی‌شود. جایگزین‌های مدرن و قدرتمندتر شامل توابع پنجره‌ای (window functions) و گزینه‌های `GROUP BY` مانند `ROLLUP`, `CUBE`, و `GROUPING SETS` هستند که انعطاف‌پذیری و قابلیت‌های گزارش‌گیری بسیار بیشتری را فراهم می‌کنند.

`fn_listextendedproperty` – هنوز هم مفید برای فراداده

تابع `sys.fn_listextendedproperty` برای بازیابی خصوصیات توسعه‌یافته (extended properties) یک شیء پایگاه داده استفاده می‌شود. این خصوصیات می‌توانند برای ذخیره فراداده‌های اضافی (metadata) در مورد جداول، ستون‌ها، روال‌ها و سایر اشیاء SQL Server مفید باشند، مثلاً برای نگهداری مستندات یا اطلاعات تجاری.

SELECT * FROM sys.fn_listextendedproperty(NULL, ‘schema’, ‘dbo’, ‘table’, ‘MyTable’, NULL, NULL);

این تابع هنوز هم ابزاری ارزشمند برای مدیریت و دسترسی به فراداده‌های سفارشی در SQL Server است.

DMV های مدرن: `sys.dm_exec_sql_text` و `sys.dm_exec_query_plan`

با تکامل SQL Server، قابلیت‌های نظارت و تشخیص عملکرد نیز به طور چشمگیری بهبود یافته‌اند. توابع مدیریت دینامیک (Dynamic Management Functions – DMFs) و نماهای مدیریت دینامیک (Dynamic Management Views – DMVs) مانند `sys.dm_exec_sql_text` و `sys.dm_exec_query_plan` جایگزین ابزارهای قدیمی‌تر شده‌اند.

`sys.dm_exec_sql_text` متن کامل یک بچ SQL یا عبارت T-SQL را برمی‌گرداند که توسط handle متن (SQL handle) آن مشخص می‌شود.

SELECT * FROM sys.dm_exec_sql_text(0x…);

`sys.dm_exec_query_plan` برنامه اجرایی کش شده (cached execution plan) یک کوئری را بر اساس handle برنامه (plan handle) آن برمی‌گرداند. این DMVs برای تحلیل عملکرد کوئری‌ها، شناسایی گلوگاه‌ها و بهینه‌سازی بسیار مهم هستند.

SELECT * FROM sys.dm_exec_query_plan(0x…);

`fn_trace_geteventinfo` و نقش ردیابی

توابع ردیابی (trace functions) مانند `sys.fn_trace_geteventinfo` و `sys.fn_trace_gettable` برای خواندن فایل‌های ردیابی (trace files) SQL Server استفاده می‌شوند. این فایل‌ها شامل اطلاعات دقیقی در مورد رویدادهای رخ داده در سرور هستند و برای ممیزی (auditing)، تشخیص مشکلات عملکرد و عیب‌یابی استفاده می‌شوند. `sys.fn_trace_geteventinfo` اطلاعات رویدادهای ردیابی را برمی‌گرداند.

SELECT * FROM sys.fn_trace_geteventinfo(1);

اگرچه ابزارهایی مانند Extended Events به عنوان جایگزین مدرن و کارآمدتر برای ردیابی معرفی شده‌اند، اما درک و استفاده از توابع ردیابی قدیمی هنوز در برخی سناریوها کاربرد دارد، به خصوص هنگام کار با سیستم‌های قدیمی‌تر یا تحلیل فایل‌های ردیابی موجود.

جمع‌بندی

نگاهی به گذشته SQL Server نشان می‌دهد که این پلتفرم چگونه برای پاسخگویی به نیازهای در حال تغییر توسعه‌دهندگان و مدیران پایگاه داده تکامل یافته است. در حالی که برخی از ویژگی‌ها برای همیشه از بین رفته‌اند، بسیاری از اصول اساسی مدیریت پایگاه داده ثابت مانده‌اند. آشنایی با این تغییرات و درک جایگزین‌های مدرن برای ساختن و نگهداری سیستم‌های قوی، کارآمد و مقیاس‌پذیر در SQL Server امروز ضروری است.