تغییرات T-SQL در SQL Server 2022 | بررسی توابع جدید و بهبودها

SQL Server

نوشته شده توسط: تیم فنی نیک آموز

تاریخ انتشار: ۱۳ دی ۱۴۰۱

آخرین بروزرسانی: 12 اسفند 1403

زمان مطالعه: 16 دقیقه

در این مقاله قصد داریم تغییرات دیگر T-SQL را که در ورژن ۲۰۲۲ در پلتفرم پایگاه داده ایجاد شده را پوشش دهیم. در مقاله قبلی موضوعاتی از جمله DISTINCT FROM ،DATE_BUCKET GENERATE_SERIES ،GREATEST/LEAST ،STRING_SPLIT و DATETRUNC مورد بررسی قرار گرفت. در این مقاله APPROX_PERCENTILE_CONT،APPROX_PERCENTILE_DISC و توابع bit manipulation مورد بررسی قرار خواهد گرفت. همچنین تغییراتی که روی FIRST_VALUE ،LAST_VALUE و LTRIM/RTRIM/TRIM اتفاق افتاده بررسی می‌شود.
این مقاله یک نگاه ساده به این ویژگی‌های جدید و ارزیابی از SQL Server 2022 از دیدگاه ما است تا در نهایت تصمیم بگیرد که آیا ارتقا دادن به ورژن ۲۰۲۲ ارزش دارد یا خیر؟ بنابراین در حال بررسی این نکته هستم که تغییرات جدید کجاها ممکن است به درد بخورد و باعث تسهیل در کدنویسی شود. ما برای آزمایش‌های خود با SQL Server 2022 RC0 کار می‌کنیم.

بررسی توابع جدید و توابع بهبود یافته

در این بخش توابع که به تغییرات زبان T-SQL در SQL Server 2022 خواهیم کرد با ما همراه باشید.

بررسی APPROX_PERCENTILE_CONT

در حال حاضر تابع PERCENTILE_CONT در T-SQL وجود دارد. این تابع برای برگرداندن مقدار از مجموعه‌ای از داده‌ها است که براساس درصد محاسبه شده است. تابع تحلیلی PERCENTILE_CONT، محاسبه percentile (درصد) را براساس توزیع پیوسته‌ای از مقدار ستون انجام می‌دهد. این کار شبیه به پیدا کردن میانه با مقدار درصد است. این تابع یک تابع window است که به عبارت ()OVER نیاز دارد. یک کوئری نمونه از Docs در پایگاه داده AdventureWorks اجرا می‌کنم که خروجی به صورت زیر است:

تابع APPROX_PERCENTILE_CONT مقدار تقریبی همان تابع PERCENTILE_CONT را به دست می‌آورد و نیاز به خواندن همه داده‌ها ندارد. Microsoft Docs درمورد عملکرد این تابع به این صورت توضیح می‌دهد: این تابع یک مقدار تقریبی را از مجموعه‌ای از مقادیر که براساس مقدار درصدی مشخص شده و براساس یک ویژگی خاص مرتب‌سازی شده، برمی‌گرداند. از آنجایی که این یک تابع تقریبی است، خروجی در محدوده‌ای از خطا قرار دارد. مقدار برگردانده شده توسط این تابع براساس توزیع پیوسته مقادیر ستون است و نتیجه interpolate می‌شود. به همین دلیل، خروجی ممکن است یکی از مقادیر موجود در مجموعه داده نباشد. یکی از موارد استفاده رایج برای این تابع، اجتناب از داده‌های پرت است.

این تابع می‌تواند به عنوان جایگزینی برای تابع PERCENTILE_CONT برای مجموعه داده‌های بزرگ استفاده شود. چون در مقیاس بزرگ، خطای ناچیز قابل چشم‌پوشی است در مقایسه با دریافت مقدار دقیق، درحالی که زمان طولانی برای دریافت مقدار، سیستم باید معطل بماند. اساساً، اگر بتوانید یک مقدار تقریبی را بپذیرید و بخواهید منابع مورد نیاز برای محاسبات را به حداقل برسانید، این تابع مناسب است. این تابع یک تابع window نیست و از عبارت ORDER استفاده نمی‌کند. یک عبارت grouping وجود دارد و می‌توان یک دستور را در آنجا مشخص نمود. کد زیر را روی مجموعه داده Adventure Works اجرا می‌کنم، همان نتایج قبلی مشاهده می‌شود.

اگر به execution plan ها برای دو کوئری بالا نگاهی بیاندازیم، می‌بینیم که نسخه تقریبی از طرح بسیار ساده‌تری استفاده می‌کند. طرح بالایی تابع PERCENTILE_CONT و طرح پایینی تابع APPROX_PERCENTILE_CONT است. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.

بررسی APPROX_PERCENTILE_DISC

تفاوت توابع PERCENTILE_DISC و APPROX_PERCENTILE_DISC نیز همانند روال تابع APPROX_PERCENTILE_CONT است که در بخش قبل توضیح داده شد. یعنی تابع APPROX_PERCENTILE_DISC مقداری را به صورت تقریبی برمی‌گرداند اما چون سریع‌تر از تابع PERCENTILE_DISC خروجی را مشخص می‌کند، منابع کمتری مصرف می‌کند و سیستم کمتر درگیر محاسبات خواهد بود.

البته این مزیت به قیمت دقیق نبودن خروجی است که در بخش قبل هم اشاره شد، برای مجموعه داده‌های بزرگ، قابل پذیرش است. همانند توابع بخش قبل، در Adventure Works به ازای توابع دقیق و هم برای توابع تقریبی مقادیر یکسانی دریافت می‌کنم. شکل‌های طرح اجرا نیز یکسان به نظر می‌رسند.

عملیات بیتی

تعدادی توابع بیتی در تغییرات زبان T-SQL در SQL Server 2022 تازه اضافه شده که به شما امکان می‌دهد مقادیر تغییر بیت را انجام دهید. رمزگذاری و دستکاری بیت به عنوان مجموعه‌ای از toggle ها استفاده می‌شود که تنظیم آن سریع‌تر و آسان‌تر از استفاده از مقادیر ستون جداگانه است. به عنوان مثال، @@options در واقع یکسری بیت است که تنظیم شده است.

برای توضیح ساده این موضوع، یک مثال می‌آورم. به عنوان مثال، اگر من ۶۴ را در نظر بگیرم، این عدد به صورت مقدار باینری، به صورت مجموعه‌ای از ۷ بیت ذخیره می‌شود. این مقدار به صورت ۱۰۰۰۰۰۰ است. می‌توانم با استفاده از تابع AND (&) آن در دستور SELECT به صورت زیر اضافه کنم. اجازه دهید به یک عدد نگاه کنیم. به عنوان مثال، اگر من ۶۴ را در نظر بگیرم، این عدد در یک مقدار باینری به عنوان مجموعه‌ای از ۷ بیت ذخیره می‌شود. مقدار باینری برابر ۱۰۰۰۰۰۰ است. به خروجی دستور SELECT زیر نگاه کنید، متوجه می‌شوید که هر بیت براساس توان ۲ است.

توابع بعدی که در این مقاله در مورد آن صحبت می‌کنیم، با عملیات‌های مختلف بر این بیت‌ها تأثیر می‌گذارند.

معرفی ()LEFT_SHIFT

همان‌طور که ممکن است حدس زده باشید، تابع ()LEFT_SHIFT برای جابجایی بیت‌ها به سمت چپ طراحی شده است. در این صورت، اگر این تابع را روی مقدار ۶۴ اجرا کنم، نتیجه زیر ایجاد می‌شود:

SELET LEFT_SHIFT(64,1) as NewValue

NewValue
--------
۱۲۸

فرمت تابع به دو پارامتر نیاز دارد.

یک عدد صحیح یا یک مقدار باینری
تعداد بیت برای جابجایی به سمت چپ

این تابع مقدار باینری یا عدد صحیح (integer) جدید را برمی‌گرداند. شیفت می‌تواند روی یک مقدار باینری اعمال شود، بنابراین مقدار خروجی BIGINT خواهد بود. اگر من این تابع را روی عدد ۳۲ با شیفت ۱ اجرا کنم، در نهایت به عدد ۶۴ می‌رسم. این همان عددی است که انتظار داریم. اگر بخواهید به این دو مجموعه بیت با هم نگاه کنید به مثال زیر دقت کنید. کوئری زیر را اجرا می‌کنم:

DECLARE @number INT = 32, @new int;
SELECT left_shift (@number, 1)
SELECT @new = left_shift (@number, 1)
SELECT @number,
       CAST (@number & 128 AS BIT) AS bit8,
       CAST (@number & 64 AS BIT) AS bit7,
       CAST (@number & 32 AS BIT) AS bit6,
       CAST (@number & 16 AS BIT) AS bit5,
       CAST (@number & 8 AS BIT) AS bit4,
       CAST (@number & 4 AS BIT) AS bit3,
       CAST (@number & 2 AS BIT) AS bit2,
       CAST (@number & 1 AS BIT) AS bit1
UNION 
SELECT @new,
       CAST (@new & 128 AS BIT) AS bit8,
       CAST (@new & 64 AS BIT) AS bit7,
       CAST (@new & 32 AS BIT) AS bit6,
       CAST (@new & 16 AS BIT) AS bit5,
       CAST (@new & 8 AS BIT) AS bit4,
       CAST (@new & 4 AS BIT) AS bit3,
       CAST (@new & 2 AS BIT) AS bit2,
       CAST (@new & 1 AS BIT) AS bit1;

نتایج به صورت زیر است. همان‌طور که می‌بینید، عدد ۱ به اندازه یک بیت به سمت چپ منتقل می‌شود. شیفت از عدد صفر برای پر کردن از سمت راست استفاده می‌کند.

اگر این روال را با عدد ۲۷ تکرار کنم، متوجه می‌شوید که خروجی دو برابر، یعنی ۵۴ می‌شود. ()LEFT_SHIFT راهی برای ضرب در ۲ است. به عبارت دیگر، مقدار صحیح برای هر تغییر بیت، دو برابر می‌شود.

معرفی ()RIGHT_SHIFT

همان طور که ممکن است حدس زده باشید، تابع () RIGHT_SHIFT برعکس () LEFT_SHIFT عمل می‌کند. این تابع بیت‌ها را به سمت راست حرکت می‌دهد و برای هر بیت جابجا شده، تقسیم بر ۲ برای عدد صحیح خواهد بود.

همان کد بالا استفاده کنیم، تابع ()RIGHT_SHIFT را جایگزین می‌کنم. همان‌طور که مشاهده می‌کنید، اگر با ۶۴ شروع کنم و ۲ بار به راست شیفت کنم، ۱۶ دریافت می‌کنم. این یعنی ۶۴/۲ = 32 و ۳۲/۲ که برابر ۱۶ خواهد بود. کوئری به صورت زیر است:

DECLARE @number INT = 64, @new INT, @shifts INT = 2;
SELECT right_shift (@number, @shifts)
SELECT @new = right_shift (@number, @shifts)
SELECT @number,
       CAST (@number & 128 AS BIT) AS bit8,
       CAST (@number & 64 AS BIT) AS bit7,
       CAST (@number & 32 AS BIT) AS bit6,
       CAST (@number & 16 AS BIT) AS bit5,
       CAST (@number & 8 AS BIT) AS bit4,
       CAST (@number & 4 AS BIT) AS bit3,
       CAST (@number & 2 AS BIT) AS bit2,
       CAST (@number & 1 AS BIT) AS bit1
UNION 
SELECT @new,
       CAST (@new & 128 AS BIT) AS bit8,
       CAST (@new & 64 AS BIT) AS bit7,
       CAST (@new & 32 AS BIT) AS bit6,
       CAST (@new & 16 AS BIT) AS bit5,
       CAST (@new & 8 AS BIT) AS bit4,
       CAST (@new & 4 AS BIT) AS bit3,
       CAST (@new & 2 AS BIT) AS bit2,
       CAST (@new & 1 AS BIT) AS bit1;

و نتایج به صورت تصویری به صورت زیر خواهد بود. توجه کنید عدد ۱ در بیت ۷ قرار داشته به اندازه ۲ مکان به سمت راست جابه‌جا شده است.

با دو شیفت، عدد ۵۴ به چه عددی تبدیل می‌شود؟ عدد ۲۷ به طور مساوی بر ۲ تقسیم نمی‌شود. ممکن است حدس زده باشید که بخش عدد صحیح نتیجه یعنی (۲۶) را دریافت می‌کنیم و اعشار حذف می‌شود. در اینجا نتایج bit shift آمده است.

یک مثال دیگر برای دیدن شیفت‌ها، عدد ۱۰۰ را دو بار به سمت راست شیفت می‌دهیم. همه ۱ ها به سمت راست حرکت می‌کنند و ۰ ها از سمت چپ وارد می‌شوند.

معرفی ()BIT_COUNT

تابع () BIT_COUNT برای شمارش تعداد بیت‌های تنظیم شده روی ۱ در یک عبارت عددی یا باینری طراحی شده است. دقیقاً نمی‌دانم کاربرد این تابع کجا می‌تواند باشد. شاید اگر چیزی شبیه حضور در کلاسی داشته باشیم که هر دانش آموز را در مکانی خاص داشته باشیم، این تابع استفاده شود. به این مثال دقت کنید، این تابع به صورت زیر خروجی می‌دهد:

بررسی () GET_BIT:

همان طور که انتظار می‌رود، () GET_BIT مقدار یک عبارت عددی (یا باینری) را برمی‌گرداند. مانند () LEFT_SHIFT، به پارامتر دومی نیاز داریم که افست بیت خواهد بود. به عنوان مثال اگر بخواهم بیت ۴ را از عدد ۱۰۰ دریافت کنم، انتظار دارم عدد صفر را برگرداند. البته دقت داشته باشید مکان بیت‌ها از صفر شروع می‌شود یعنی جایگاه اول، در واقع بیت صفرم است و جایگاه دوم بیت یکم است و به همین ترتیب.

بررسی () SET_BIT

تابع () SET_BIT دقیقاً نقطه مقابل () GET_BIT است. این تابع به شما امکان می‌دهد مقدار بیت را در عبارت تنظیم کنید. در کد زیر بیت ۳ در عدد ۱۰۰ تنظیم می‌شود. مقدار بیت ۳ برابر صفر است و اگر تابع ()SET_BIT پارامتر سوم نداشته باشد، روی عدد ۱ تنظیم می‌شود. این کار باعث می‌شود عدد ۸ (۲ به توان ۳) به عدد اولیه یعنی ۱۰۰ اضافه شود.

DECLARE @number INT = 100, @new INT, @bit INT = 3;
SELECT @new = SET_BIT(@number, @bit)
SELECT @number AS Expression, 
       CAST(@number & 128 AS BIT) AS bit7,
       CAST(@number & 64 AS BIT) AS bit6,
       CAST(@number & 32 AS BIT) AS bit5,
       CAST(@number & 16 AS BIT) AS bit4,
       CAST(@number & 8 AS BIT) AS bit3,
       CAST(@number & 4 AS BIT) AS bit2,
       CAST(@number & 2 AS BIT) AS bit1,
       CAST(@number & 1 AS BIT) AS bit0
UNION 
SELECT @new,
       CAST(@new & 128 AS BIT) AS bit7,
       CAST(@new & 64 AS BIT) AS bit6,
       CAST(@new & 32 AS BIT) AS bit5,
       CAST(@new & 16 AS BIT) AS bit4,
       CAST(@new & 8 AS BIT) AS bit3,
       CAST(@new & 4 AS BIT) AS bit2,
       CAST(@new & 2 AS BIT) AS bit1,
       CAST(@new & 1 AS BIT) AS bit0
   ;
GO

نتیجه به صورت زیر خواهد بود:

این تابع در واقع راهی برای اضافه یا کم کردن یک مقدار از یک عدد صحیح است.

بررسی () FIRST_VALUE () / LAST_VALUE

البته تغییرات زبان T-SQL در نرم افزار SQL Server 2022 بوده است. در واقع دو عبارت جدید اضافه شده است. توضیحات MS Docs در مورد این دو تابع به این صورت است:

IGNORE NULLS: هنگام محاسبه اولین مقدار روی یک پارتیشن، مقادیر null در مجموعه داده نادیده گرفته شود.
RESPECT NULLS: هنگام محاسبه اولین مقدار روی یک پارتیشن، به مقادیر null در مجموعه داده توجه شود.

این بدان معناست که اگر مقادیر NULL در یک پارتیشن (مجموعه داده) وجود داشته باشد، می‌توانیم آنها را در نظر بگیریم و یا در نظر نگیریم. به عنوان مثال، من یک مجموعه داده در یک جدول مجازی دارم که دارای چند bucket از داده‌ها است. به مثال زیر دقت کنید:

SELECT bucket, 
FIRST_VALUE(val) OVER (PARTITION BY A.bucket ORDER BY A.bucketorder ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS firstvalue,
LAST_VALUE(val) OVER (PARTITION BY A.bucket ORDER BY A.bucketorder ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lastvalue
 FROM ( VALUES
         ('a', 1,  'first'),
         ('a', 2,  NULL),
         ('a', 3,  'LAST'),
         ('b', 1,  NULL),
         ('b', 2,  'mid'),
         ('b', 3,  'last'),
         ('c', 1,  'first'),
         ('c', 2,  'mid1'),
         ('c', 3,  'mid2'),
         ('c', 4,  NULL)
 ) A(bucket, bucketorder, val)

اگر کد بالا را در ورژن ۲۰۱۹ اجرا کنم، خروجی به صورت زیر خواهد بود:

من می‌توانم در ورژن ۲۰۲۲ عبارت IGNORE NULLS را اضافه کنم. این عبارت بعد از تابع و قبل از عبارت ()OVER اضافه شده است. کد به صورت زیر خواهد بود:

SELECT bucket, 
FIRST_VALUE(val) IGNORE NULLS OVER (PARTITION BY A.bucket ORDER BY A.bucketorder ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS firstvalue,
LAST_VALUE(val) IGNORE NULLS  OVER (PARTITION BY A.bucket ORDER BY A.bucketorder ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lastvalue
 FROM ( VALUES
         ('a', 1,  'first'),
         ('a', 2,  NULL),
         ('a', 3,  'LAST'),
         ('b', 1,  NULL),
         ('b', 2,  'mid'),
         ('b', 3,  'last'),
         ('c', 1,  'first'),
         ('c', 2,  'mid1'),
         ('c', 3,  'mid2'),
         ('c', 4,  NULL)
 ) A(bucket, bucketorder, val)

خروجی به صورت زیر خواهد بود. دقت داشته باشید روی همان مجموعه داده اجرا شده است.

اگر RESPECT NULLS را اضافه کنم، همان نتایج سال ۲۰۱۹ را دریافت می‌کنم. یعنی عبارت RESPECT NULLS به صورت پیش فرض اعمال شده است.

بررسی () LTRIM

تغییری در تابع () LTRIM در SQL Server 2022 وجود دارد که به شما امکان می‌دهد کاراکترهای خاصی را برش (TRIM) دهید. پیش فرض یک space است، اما می‌توانید آن را تغییر دهید. به مثال‌های زیر دقت کنید:

SELECT STRINGVALUE,
  LEN(StringValue) AS OriginalLength,
  LTRIM(STRINGVALUE,' '),
  LEN(LTRIM(StringValue,' ')) AS TrimLength
FROM (
 VALUES
  ('   String1'),
  (' String2'),
  ('   String3   '),
  ('aaaString4bbbb'),
  ('   String5bbbb'),
  ('bbbString4bbbb')
 ) A(StringValue)
GO

در این کد، من space ها را از سمت چپ رشته‌ها حذف می‌کنم. اگر به رشته نگاه کنید، ۴ مقدار وجود دارد که در سمت چپ فاصله دارند (string1, string2, string2, and string5). وقتی این کد را اجرا می‌کنم، نتایج به صورت زیر خواهد بود:

همان‌طور که می‌بینید space ها حذف شدند. در مثال زیر کاراکتر دیگری را حذف می‌کنم. کاراکتر a را با کد زیر حذف می‌کنم:

SELECT STRINGVALUE,
  LEN(StringValue) AS OriginalLength,
  LTRIM(STRINGVALUE,'a'),
  LEN(LTRIM(StringValue,'a')) AS TrimLength
FROM (
 VALUES
  ('   String1'),
  (' String2'),
  ('   String3   '),
  ('aaaString4bbbb'),
  ('   String5bbbb'),
  ('bbbString4bbbb')
 ) A(StringValue)
GO

در نتایج، فقط string4 در ابتدا دارای a است و هر ۳ مورد حذف می‌شوند.

کد زیر چندین کاراکتر را حذف می‌کند:

SELECT STRINGVALUE,
  LEN(StringValue) AS OriginalLength,
  LTRIM(STRINGVALUE,'The'),
  LEN(LTRIM(StringValue,'The')) AS TrimLength
FROM (
 VALUES
  ('The quick brown fox'),
  ('The end of the story'),
  ('TheThe Boat')
 ) A(StringValue)
GO

نتایج به صورت زیر خواهد بود:

توجه داشته باشید، این کد کلمه “The” را حذف نمی‌کند، بلکه به دنبال هر یک از کاراکترهای ابتدای رشته می‌گردد و آن‌ها را حذف می‌کند. بنابراین، اگر یک “t”، “h” یا “”e در ابتدا باشد، حذف می‌شوند. این بدان معنی است که SELECT LTRIM (‘tehhts’, ‘the’) فقط یک ”s را برمی‌گرداند.

بررسی () RTRIM

مانند () RTRIM () ،LTRIM نیز تغییر کرده است تا اجازه دهد یک کاراکتر برای حذف مشخص شود. در اینجا چند مثال ارائه می‌کنم:

SELECT STRINGVALUE,
  LEN(StringValue) AS OriginalLength,
  RTRIM(STRINGVALUE,'b'),
  LEN(RTRIM(StringValue,'b')) AS TrimLength
FROM (
 VALUES
  ('   String1'),
  (' String2'),
  ('   String3   '),
  ('aaaString4bbbb'),
  ('   String5bbbb'),
  ('String6bbbb')
 ) A(StringValue)
GO

نتایج شبیه به ()LTRIM هستند، اما از سمت راست رشته‌ها، کاراکترهایی حذف شده است.

این تابع یک عملکرد مفید ارائه داده است زیرا گاهی اوقات کاراکترهای اضافی را دریافت می‌کنیم که توسط مشتریان وارد می‌شوند و می‌خواهیم کاراکترهایی غیر از کاراکتر space را حذف کنیم.

بررسی () TRIM

تابع () TRIM نیز به صورت مشابه دو تابع قبلی بهبود یافته است، اما با سینتکسی متفاوت. سینتکس جدید برای ورژن ۲۰۲۲ در Docs به صورت زیر نشان داده شده است:

TRIM ( [ LEADING | TRAILING | BOTH ] [characters FROM ] string )

در سینتکس بالا از LEADING برای حالت LTRIM، از TRAILING برای حالت RTRIM و از BOTH برای حالت TRIM استفاده می‌شود. فرض کنید من یک رشته دارم که شامل کاراکترهایی است که می‌خواهم حذف کنم. به مثال زیر دقت کنید:

SELECT STRINGVALUE,
  LEN(StringValue) AS OriginalLength,
  TRIM (BOTH 'b' FROM STRINGVALUE),
  LEN (TRIM(BOTH 'b' FROM STRINGVALUE)) AS TrimLength
FROM (
 VALUES
  ('  b String1b'),
  (' String2b'),
  ('b   String3   '),
  ('aaaString4bbbb'),
  ('   String5bbbb'),
  ('bString6bbbb')
 ) A(StringValue)
GO

اگر به رشته‌ها نگاه کنید، متوجه می‌شوید که در ابتدا یا انتهای آن‌ها کاراکتر ‘b’ وجود دارد. می‌خواهیم b ها حذف شوند. نتایج به صورت زیر است:

در رشته اول، اولین b را حذف نمی‌کند؛ زیرا ابتدای آن با کاراکتر space شروع شده و ما در پارامتر جستجوی خود space را اضافه نکرده بودیم. اما در رشته سوم b حذف شده است. در رشته ششم هر دو حالت leading و trailing در نظر گرفته شده است و همه b ها را حذف کرده است. نکته مهم در این تابع آن است که تصمیم صحیح بگیرید که با هر پارامتر جستجو از کدام حالت‌های leading، trailing و یا both استفاده کنید تا نتیجه دلخواهتان به دست آید.

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

تغییرات زبان T-SQL در SQL Server 2022 در DISTINCT FROM

یکی از این تغییرات زبان T-SQL در SQL Server 2022 بر طبق مستندات ارائه شده برای IS [NOT] DISTINCT FROM این تابع، برابری دو عبارت را مقایسه و درست یا نادرست بودن را بررسی می‌کند؛ حتی اگر یکی از پارامترهای مورد بررسی NULL باشد.

به مثال زیر دقت کنید:

DECLARE @a INT, @b INT
SELECT @a = 1, @b = 1
IF @a = @b
 SELECT 'equal', @a, @b
ELSE 
 SELECT 'unequal', @a, @b
SELECT @a = 1, @b = 2
IF @a = @b
 SELECT 'equal', @a, @b
ELSE 
 SELECT 'unequal', @a, @b
 SELECT @a = 1, @b = null
IF @a = @b
 SELECT 'equal', @a, @b
ELSE 
 SELECT 'unequal', @a, @b

نتایج زیر را خواهیم داشت:

برای مثال دوم از کد زیر استفاده می‌کنم:

DECLARE
  @a INT
, @b INT;
SELECT @a = 1, @b = 1;
IF @a = @b
  SELECT 'equal', @a, @b;
ELSE IF @a IS NULL OR @b IS NULL
  SELECT 'unknown', @a, @b;
ELSE
  SELECT 'unequal', @a, @b;
SELECT @a = 1, @b = 2;
IF @a = @b
  SELECT 'equal', @a, @b;
ELSE IF @a IS NULL OR @b IS NULL
  SELECT 'unknown', @a, @b;
ELSE
  SELECT 'unequal', @a, @b;
SELECT @a = 1, @b = NULL;
IF @a = @b
  SELECT 'equal', @a, @b;
ELSE IF @a IS NULL OR @b IS NULL
  SELECT 'unknown', @a, @b;
ELSE
  SELECT 'unequal', @a, @b;

این کد نتایج مناسب را به صورت “equal”،”unequal” و “unknown” می‌دهد.

بدون این تابع جدید، وقتی موارد را مقایسه می‌کنیم، می‌توانیم سه حالت true، false یا NULL داشته باشیم. با وجود این تابع جدید، می‌توانیم روال را به صورت زیر انجام دهیم:

DECLARE
  @a INT
, @b INT;
SELECT @a = 1, @b = NULL;
IF @a IS DISTINCT FROM @b
  SELECT 'unequal', @a, @b;
ELSE
  SELECT 'equal', @a, @b;

نتیجه در اینجا “unequal” است حتی اگر یکی از مقادیر NULL باشد.

این تابع وقتی کاربردی می‌شود که نمی‌خواهم در داخل کد ابتدا NULL بودن یک عبارت را چک کنم و سپس کار مقایسه را انجام دهم. بااستفاده از تابع DISTINCT FROM بدون کنترل NULL بودن، می‌توان مقایسه را انجام داد. به مثال زیر دقت کنید:

DECLARE @cc INT = 5618;
SELECT soh.SalesOrderID
     , soh.OrderDate
     , soh.SalesOrderNumber
     , soh.CustomerID
     , soh.CreditCardID
     , soh.CreditCardApprovalCode
 FROM sales.SalesOrderHeader AS soh
 WHERE soh.CreditCardID = @cc

این کد چند سفارش را به من برمی‌گرداند. با این حال، اگر ورودی را به NULL تغییر دهم، به صورت زیر خواهد بود:

در SQL Server 2022 می‌توانم تغییر زیر را اعمال کنم. در این مورد، از IS NOT DISTINCT FROM استفاده می‌کنم. این کد باعث می‌شود ردیف‌های NULL مشخص شوند. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.

بدون این تابع، من باید ابتدا یک عبارت OR اضافه می‌کردم تا ISNULL را بررسی کند، هم برای پارامتر ورودی و هم برای مقداری که بررسی می‌شود. در حالی که با تابع IS NOT DISTINCT FROM نیاز به این روال‌های اضافی نیست.

مشاهده کامل‌ترین و بروزترین آموزش sql server در نیک آموز

تابع DATE_BUCKET

تابع DATE_BUCKET تاریخ شروع یک دوره زمانی را بر اساس window یا bucket ای که شما مشخص کرده‌اید به شما می‌دهد. این تابع برای موقعیت‌هایی که دوره‌های زمانی را گروه‌بندی یا محاسبه می‌کنید مفید است. در این قسمت با ارائه چند مثال ساده، کاربرد این تابع را ارائه می‌دهم.

فرض کنید من می‌خواهم سال را به bucket های ۴ هفته‌ای تقسیم کنم. در تقویم ۲۰۲۲، این bucket های ۴ هفته به صورت زیر درنظر گرفته شد:

1Jan to 28 Jan
۲۹ 25Jan to Feb
۲۶ 25Feb to Mar

به همین ترتیب دوره‌های زمانی در نظر گرفته می‌شود.

کوئری نویسی، اگر تاریخ شروع خود را ۱ ژانویه تنظیم کنم و تاریخ را در یک بازه زمانی انتخاب کنم، شروع هر bucket برگردانده می‌شود. من ۴ تاریخ را در قسمت‌های مختلف برای چند ماه اول، برای نشان دادن این موضوع انتخاب کرده‌ام. کد به صورت زیر است:

DECLARE @origin DATE = '2022/01/01';
DECLARE @bucketsize INT = 4
SELECT 
 date_bucket(week, @bucketsize, CAST('2022/01/15' as date), @origin),
 date_bucket(week, @bucketsize, CAST('2022/01/30' as date), @origin),
 date_bucket(week, @bucketsize, CAST('2022/02/25' as date), @origin),
 date_bucket(week, @bucketsize, CAST('2022/03/04' as date), @origin)

نتایج را همان‎طور که در تصویر زیر می‌بینید، اولین روز از هر bucket چهار هفته‌ای برگردانده می‌شود:

اگر نوع bucket را به ماه تغییر دهم و interval را به ۱ کاهش دهم، اولین روز هرماه را دریافت می‌کنم.

تابع GENERATE_SERIES

تابع GENERATE_SERIES جدولی از اعداد را به من برمی‌گرداند که می‌توان از آن در جای مناسب استفاده نمود. سینتکس بسیار ساده‌ای دارد و توسعه خوبی روی آن انجام گرفته است. سینتکس به صورت زیر است:

GENERATE_SERIES ( start, stop [, step])

اگر start < stop باشد به صورت پیش‌فرض مقدار step برابر ۱ خواهد بود و در غیراینصورت مقدار step برابر ۱- تنظیم می‌شود. دقت داشته باشید مقدار step هرگز نمی‌تواند برابر صفر باشد.

اگر لیستی از اعداد ۱ تا ۵ را بخواهم، کد زیر را می توانم اجرا کنم:

نکته جالب در مورد این تابع این است که می‌توانید از اعداد اعشاری نیز استفاده کنید. فرض کنید می‌خواهم کاری را با اعداد اعشاری برای درصد انجام دهم. کد زیر لیستی از درصدهای ۱ تا ۱۰۰ را برمی‌گرداند.

نام ستون value است و می‌توانید از آن به همان شیوه جدول tally استفاده کنید.

تابع GREATEST/LEAST

این دو تابع برای من توابع جالبی هستند که باعث می‌شوند کد بسیار تمیزتر باشد. خوشحالم که GREATEST() و LEAST() به پلتفرم اضافه شدند. این توابع تعدادی از پارامترها را باهم مقایسه کرده و بزرگترین یا کوچکترین آن‌ها را برمی‌گرداند.

چند مثال برای کاربردهای این دو تابع ارائه می‌کنم:

مشاهده می‌کنید که بزرگترین و کوچکترین اعداد توسط این توابع برگردانده می‌شوند. اما یک تبدیل datatype نیز اتفاق افتاده است. این روال براساس اولویت datatype است. این توابع برای string ها هم کار می‌کند:

مقادیر موجود در تابع LEAST() به صورت string هستند نه date. ما می‌توانیم از date نیز در این توابع استفاده کنیم، اما باید پارامترهای ارسالی از نوع date باشند. همه مقادیر ارسال شده باید ازطریق یک implicit conversion ارسال شوند.

نکته کاربردی این توابع برای date این است که NULL ها را نادیده می‌گیرند. بنابراین اگر تعدادی تاریخ برای سفارش داشته باشم، مانند تاریخ‌های سفارش، بسته‌بندی، ارسال و تحویل، می‌توانم آخرین تاریخ را به راحتی دریافت کنم:

در اینجا من تاریخ‌های تابع را طوری تنظیم کرده‌ام که معمولاً به‌روزرسانی می‌شوند اما نیازی نیست نگران مقادیر NULL برای سفارش برگشتی و تاریخ‌های تحویل باشم. این تابع به من آخرین تاریخ را می‌دهد.

تابع STRING_SPLIT

تابع STRING_SPLIT در چند ورژن قبلی SQL Server بوده است، اما یکی از دردسرها این بود که وقتی یک رشته را تقسیم می‌کنید، هیچ امکانی برای مرتب‌سازی ندارید. این روال در SQL Server 2022 تغییر کرده است. یک پارامتر سوم اختیاری، اضافه شده که می‌توانید آن را با عدد ۱ تنظیم کنید تا مقدار ترتیبی را تابع برگرداند.

ستون “value” برای رشته‌های تقسیم شده و ستون “ordinal” برای ترتیب قرارگیری است.

تابع DATETRUNC

من اغلب در بسیاری از مواقع به تابع DATETRUNC نیاز ندارم، اما به هرحال یک تابع کاربردی است. این تابع هنگامی مورد نیاز است که می‌خواهید از مقدار datetime تنها عدد مربوط به ماه را دریافت کنید و یا عدد مربوط به روز را دریافت کنید و برای بقیه مقادیر هم به همین ترتیب. یعنی قسمت‌های مختلف یک datetime را جداگانه کنترل کنید. به عنوان مثال می‌توانم روز را از تاریخ حذف کنم با مقدار ۰۱ جایگزین کنم و سال و ماه را نگه دارم. یا حتی روز و ماه را از تاریخ حذف کنم و با مقدار ۰۱ جایگزین کنم و تنها سال را نگه دارم. در مورد دقیقه، ثانیه و غیره نیز می‌توانید این کنترل را داشته باشید.

مثال زیر حالت‌های مختلف را نشان می‌دهد.

سخن پایانی

این‌ها تعدادی از تغییرات T-SQL برای SQL Server 2022 بودند. از GENERATE_SERIES استقبال می‌کنم؛ زیرا من از جداول tally استفاده می‌کنم و این تابع می‌تواند جایگزین خوبی باشد. من مطمئن هستم که استفاده از STRING_SPLIT نیز برای مرتب‌سازی رشته‌های خروجی کاربرد دارد. DATE_BUCKET جالب به نظر می‌رسد، به خصوص برای محاسبات دوره‌های زمانی مختلف مفید خواهد بود. توانایی تعیین دوره‌های زمانی مختلف به گزارش‌های مالی کمک می‌کند که گاهی اوقات از BUCKET های غیرمعمول استفاده کنند. من در مورد DATETRUNC مطمئن نیستم خیلی به دردم بخورد.

DISTINCT FROM و GREATEST/LEAST احتمالاً توابعی هستند که من بیشتر از بقیه توابع ارائه شده در این مقاله استفاده خواهم کرد. من دورنمای خوبی را با این تغییرات جدید می‌بینم که با این پیشرفت‌ها کدنویسی ساده‌تر و تمیزتر شود.

تغییرات زبان T-SQL در SQL Server 2022، برخی از این توابع که به تازگی در ورژن ۲۰۲۲ اضافه شدند، عملکردهای جالبی دارند. توابع تقریبی، کابردی هستند و می‌توانند منابع را ذخیره کنند. موارد تغییر بیت باعث می‌شود به این فکر کنم که کجا ممکن است در رمزگذاری برخی سوئیچ‌ها استفاده کنم. عملکردهای TRIM پیشرفت‌های خوبی داشته‌اند و به نظر می‌رسد در تسهیل کدنویسی کمک کنند.

ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.

مشاهده کامل‌ترین و بروزترین آموزش sql server در نیک آموز