معرفی توابع SQL Server ، مزایا و معایب آنها

هر پایگاه داده ای که به خوبی نوشته شده باشد ، دارای توابع مختلفی خواهد بود ، بیشتر اوقات مفید هستند اما در صورت استفاده نادرست ، در واقع به عملکرد آسیب می رسانند.

عملکرد SQL چیست؟

از توابع SQL Server می توان برای بازگرداندن مقادیر منفرد (مقیاس کننده) یا جداول ، با استفاده از روال های T-SQL یا CLR (زمان اجرای زبان مشترک) و غالباً محاسبات پیچیده تری را از آنچه در کد عمومی استفاده می کنید ، انجام داد.

چه زمانی استفاده از یک تابع به جای کد درون خطی ایده خوبی است؟

استفاده مناسب

از توابع می توان برای جایگزینی نمایش ها (برگرداندن یک جدول) استفاده کرد ، به عنوان یک ستون محاسبه شده در یک جدول ، انجام اقدامات جستجوی سازگار یا فقط برای مدولاسیون کد شما که می تواند در کاهش تغییرات مورد نیاز کمک کند.

استفاده بد

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

نمونه های عملکرد مقیاس پذیر

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

ذخیره سن افراد در زمان پر کردن فرم ، معنایی نخواهد داشت ، زیرا وقتی داده ها بعداً مورد پرسش قرار می گیرند ، منسوخ می شوند. گزینه بهتر ثبت تاریخ تولد و محاسبه آن در حین پرواز است. در عملکرد ما یک فیلد اضافه کردیم تا ، که می تواند برای به روز کردن محاسبه استفاده شود ، یا شاید محرمانه تر ، محاسبه سن زمان مرگ (این تابع برای قرارداد NHS تمدید شد).

بیشتر: سن بگیرید

Example

CREATE FUNCTION [Dates].[GetAge](@Date DATETIME2,@Until DATETIME2) RETURNS INT AS BEGINIF @Until IS NULL SET @Until=CONVERT(DATE,GETDATE())DECLARE @Age INT=DATEDIFF(YEAR,@Date,@Until)+(CASE WHEN DATEPART(DAYOFYEAR,@Date)>(DATEPART(DAYOFYEAR,@Until)+(CASE WHEN dbo.GetLeapYear(@Date)=1 AND DATEPART(DAYOFYEAR,@Date)>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END)RETURN @AgeEND

نمونه های عملکرد مقیاس پذیر

برای استفاده از این جدول داستانی ، ما به سادگی از این جدول استفاده می کنیم که سن فعلی یا سن مرگ را فراهم می کند.

Use in a select statement

SELECT [PersonID],[DateOfBirth],[dbo].[GetAge]([DateOfBirth],[DeceasedDate]) AgeAsFunction,--Simpler code to understand!DATEDIFF(YEAR,[DateOfBirth],[DeceasedDate])+(CASE WHEN DATEPART(DAYOFYEAR,[DateOfBirth])>(DATEPART(DAYOFYEAR,[DeceasedDate])+(CASE WHEN dbo.GetLeapYear([DateOfBirth])=1 AND DATEPART(DAYOFYEAR,[DateOfBirth])>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END) AgeAsScript,[DeceasedDate]FROM [Person]

نمونه های عملکرد مقیاس پذیر

مزایا : سازگار ، مدولار ، جمع و جورتر ، به طور بالقوه تعداد تغییرات را کاهش می دهد

معایب : برای دیدن کدی که باید در عملکرد جستجو کنید

این عملکرد در عین مفید بودن ، بسیار دقیق نیز هست ، زیرا از یک عملکرد سال کبیسه استفاده می کند. ماهیت آن غیر قطعی است ، بنابراین هرگز نباید به عنوان داده های ماندگار ذخیره شود.

نمونه ستون جدول

ستون های محاسبه شده را می توان به صورت ماندگار اضافه کرد (وقتی داده ها تغییر می کند) یا غیر ماندگار (هر بار انتخاب ردیف محاسبه می شود) ما می توانیم در سیستم مدیریت محتوای خود به دو روش استفاده کنیم.

توجه : دستیابی به داده های پایدار ممکن است دشوارتر باشد زیرا نیاز به مجموعه ای از محدودیت ها دارد

بدون دوام: سن

با استفاده از تابع age به بالا ، می توانیم این را به جدول اضافه کنیم و از ستونهای دیگر مقادیر را منتقل کنیم. سپس به سادگی آن را به عنوان ستون انتخاب می کنیم.

Add to a table

CREATE TABLE [Person]([PersonID] [int] IDENTITY(1,1) NOT NULL,[DateOfBirth] [datetime] NULL,[Age] AS ([dbo].[GetAge]([DateOfBirth],[DeceasedDate])),[DeceasedDate] [datetime] NULL)

Select Statement

SELECT [PersonID],[DateOfBirth],[Age] AgeAsColumn,--Even simpler code to understand!DATEDIFF(YEAR,[DateOfBirth],[DeceasedDate])+(CASE WHEN DATEPART(DAYOFYEAR,[DateOfBirth])>(DATEPART(DAYOFYEAR,[DeceasedDate])+(CASE WHEN dbo.GetLeapYear([DateOfBirth])=1 AND DATEPART(DAYOFYEAR,[DateOfBirth])>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END) AgeAsScript,[DeceasedDate]FROM [Person]

بدون دوام: سن

مزایا : سازگار ، مدولار

معایب : در صورت عدم نیاز سرعت پرس و جو را کاهش می دهد.

مداوم: CSS Minified

ما تابعی داریم که فضای مورد نیاز CSS را تا 30٪ کاهش می دهد. تماس منظم با این کار سرعت انتخاب جدول را کاهش می دهد ، و چون داده ها بندرت به روز می شوند انجام محاسبات در زمان درج / به روزرسانی منطقی است. با ایجاد ستون به عنوان یک تابع ، دیگر نیازی به انجام این عملیات به عنوان ماشه نیستیم.

بیشتر: پیش پردازنده CSS در SQL

Add to a Table

CREATE TABLE CSS(CSSID INT IDENTITY(1,1) NOT NULL,CSSText NVARCHAR(MAX),CSSMin AS (ous.CSSProcessor([CSSText])) PERSISTED)

مداوم: CSS Minified

این می تواند دقیقاً مانند یک ستون عادی انتخاب شود و داده ها در جدول ذخیره می شوند. همچنین از استفاده از یک عبارت جایگزین عظیم که کد ما را نفخ می کند ، جلوگیری می کند.

مزایا : سازگار ، مدولار ، سرعت انتخاب سریعتر ، بدون نیاز به ماشه!

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

جایگزینی یک نمای

ما تمایل داریم که از نماها استفاده نکنیم ، جدا از زمانی که به طور منظم از اتصالات مشابه در چندین مکان استفاده می کنیم.

حتی در این موارد هیچ دلیلی وجود ندارد که از یک عملکرد جدول نمی توان به طور موثرتری استفاده کرد. جدولی که ما استفاده کرده ایم را می توانید در لینک زیر بیابید ، و ما دو مثال استفاده داریم ، یکی از طریق یک تابع و دیگری با استفاده از یک نمای.

بیشتر: استفاده از تاریخ های SQL Server

Create a function

CREATE FUNCTION Dates.GetCalender(@DateFrom DATETIME2,@DateTo DATETIME2,@Function INT) RETURNS @D TABLE (CalenderDate DATETIME2(7) NOT NULL PRIMARY KEY,CalenderCA INT NULL,CalenderCD INT NULL,WeekDayID INT NULL,WeekDayName VARCHAR(9) NULL,HolidayType NVARCHAR(100)) AS BEGININSERT INTO @DSELECT c.*,HolidayTypeFROM [Utilities].[Dates].[Calender] cLEFT JOIN Utilities.Dates.CalenderHolidays h ON h.CalenderDate=c.CalenderDate AND CalenderFunction=@FunctionWHERE c.CalenderDate BETWEEN @DateFrom AND @DateTo RETURNENDGO

Create a view

CREATE VIEW Dates.GetCalenderView ASSELECT c.CalenderDate, CalenderCA, CalenderCD, WeekDayID, WeekDayName,h.HolidayType,c.CalenderFunctionFROM (SELECT CalenderDate, CalenderCA, CalenderCD, WeekDayID, WeekDayName, CalenderFunction FROM [Utilities].[Dates].[Calender],(SELECT DISTINCT CalenderFunction FROM Dates.CalenderHolidays) x) cLEFT JOIN Utilities.Dates.CalenderHolidays h ON h.CalenderDate=c.CalenderDate AND h.CalenderFunction=c.CalenderFunction

Usage

SELECT * FROM Dates.GetCalender('2018-01-01','2018-12-31',0) --England & WalesSELECT * FROM Dates.GetCalender('2018-01-01','2018-12-31',1) --Scotland----OR----SELECT * FROM Dates.GetCalenderView WHERE CalenderDate BETWEEN '2018-01-01' AND '2018-12-31' AND CalenderFunction=0

جایگزینی یک نمای

مزایا : برای تماس فشرده ، با کلید اصلی برگردانده می شود (مناسب برای پیوستن بیشتر) ، پارامترها می توانند زودتر در کد استفاده شوند

معایب : کد ساخت بیشتر ، انعطاف پذیری کمتری دارد

در Apply Joins استفاده کنید

توابع جدول برای استفاده در Apply Joins عالی هستند ، زیرا داده ها می توانند به صورت ردیف به ردیف منتقل شوند. ما از تابع TextToRows خود برای جدا کردن رشته ها در SQL Server استفاده می کنیم. در مثال زیر ما از تقسیم دوگانه برای تقسیم داده ها دو بار با جداکننده های مختلف استفاده می کنیم.

بیشتر: عملکرد SQL Server متن را به سطرهای داده تقسیم می کند

SQL Code

DECLARE @TestText TABLE(TextToSplit NVARCHAR(100))INSERT INTO @TestText SELECT 'Text,To,Tows:Split,One'INSERT INTO @TestText SELECT 'Text,To,Tows:Split,Two'SELECT t.TextToSplit,s1.WordInt,s1.WordStr,s2.WordInt,s2.WordStrFROM @TestText tOUTER APPLY dbo.TextToRows(':',TextToSplit) s1OUTER APPLY dbo.TextToRows(',',WordStr) s2

Further detail

Some of the functions we have written can be found below.