SQL Server İşlevlerine giriş, yararları ve sakıncaları

İyi yazılmış herhangi bir veritabanının bir dizi işlevi olacaktır, çoğu zaman yararlıdırlar, ancak yanlış bağlamda kullanıldığında performansa zarar verebilirler.

SQL İşlevi nedir?

SQL Server işlevleri, T-SQL veya CLR (ortak dil çalışma zamanı) rutinlerini kullanarak ve genellikle genel kodda kullanmak isteyeceğinizden daha karmaşık hesaplamalar gerçekleştirerek tek (ölçekleyici) değerleri veya tabloları döndürmek için kullanılabilir.

Satır içi kod yerine bir işlev kullanmak ne zaman iyi bir fikirdir?

İyi kullanım

İşlevler, görünümleri değiştirmek (bir tablo döndürmek), bir tabloda hesaplanmış bir sütun olarak, tutarlı arama eylemleri gerçekleştirmek veya yalnızca gerekli değişiklikleri azaltmaya yardımcı olabilecek kodunuzu modülerleştirmek için kullanılabilir.

Kötü Kullanım

Bunu her zaman görüyoruz, ancak büyük veri kümeleriyle uğraşırken birleştirme yerine arama verilerini döndürmek için işlevler kullanılmamalıdır. Her satır, o değerle daha önce karşılaşmış olsa bile aynı işlevi çağıracaktır. Bu durumlarda, bir birleştirme kullanın.

Ölçekleyici işlevi örnekleri

Ölçekleyici işlevleri, doğası gereği her satır için çağrıldıkları için satır tabanlı yeniden biçimlendirme veya hesaplamalar gibi mantık yapmak için en iyi şekilde kullanılır, başka bir tabloda veri aramak için kullanılabilirler, ancak genel olarak, kullanarak daha iyi performans elde edersiniz. bir katılmak. Bunun için aşağıdaki linkten get age fonksiyonumuza bakabiliriz.

Birinin formu doldurduğu andaki yaşını saklamak hiçbir anlam ifade etmeyecektir, çünkü veriler daha sonra sorgulandığında güncelliğini yitirecektir. Daha iyi bir seçenek, bir doğum tarihini yakalamak ve anında hesaplamak olacaktır. Fonksiyonumuza, bir hesaplamayı geri tarihlemek için veya belki daha ciddi bir şekilde, yaş ve ölüm zamanını hesaplamak için kullanılabilecek bir bitiş alanı ekledik (bu fonksiyon bir NHS sözleşmesi için genişletildi).

Daha fazla: Yaş Al

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

Ölçekleyici işlevi örnekleri

Bunu kurgusal bir tablodan kullanmak için, ya şimdiki yaşı ya da ölümdeki yaşı sağlayacak olan bunu kullanırdık.

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]

Ölçekleyici işlevi örnekleri

Avantajları : Tutarlı, modüler, daha kompakt, potansiyel olarak değişiklik sayısını azaltır

Dezavantajları : Kodu görmek için fonksiyona bakmanız gerekir.

Bu işlev genellikle yararlı olmakla birlikte, artık yıl işlevini kullandığından son derece doğrudur. Doğası gereği deterministik değildir, bu nedenle asla kalıcı veri olarak saklanmamalıdır.

Tablo sütunu örnekleri

Hesaplanan sütunlar, kalıcı (veriler değiştiğinde değişir) veya kalıcı olmayan (satır her seçildiğinde hesaplanır) olarak eklenebilir. Bunları burada İçerik Yönetim Sistemimizde kullandığımız iki şekilde görebiliriz.

Not : Bir dizi kısıtlamanın karşılanması gerektiğinden, kalıcı verilerin elde edilmesi daha zor olabilir

Kalıcı: Yaş

Age fonksiyonunu yukarıdaki gibi kullanarak bunu bir tabloya ekleyebilir ve diğer sütunlardan değerler aktarabiliriz. Daha sonra onu bir sütun olarak seçiyoruz.

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]

Kalıcı: Yaş

Avantajları : Tutarlı, modüler

Dezavantajları : Gerekmiyorsa sorgu hızını yavaşlatır.

Kalıcı: Küçültülmüş CSS

CSS için gereken alanı %30'a kadar azaltan bir fonksiyonumuz var. Bunu düzenli olarak çağırmak, tablonun seçim hızını yavaşlatır ve veriler nadiren güncellendiğinden, ekleme/güncelleme zamanında hesaplamalar yapmak mantıklıydı. Sütunu bir fonksiyon olarak oluşturduğumuzda, bu işlemleri bir tetikleyici olarak da gerçekleştirmemize gerek kalmaz.

Daha fazla: SQL'de CSS Ön İşlemcisi

Add to a Table

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

Kalıcı: Küçültülmüş CSS

Normal bir sütun gibi seçilebilir ve veriler tabloda saklanır. Ayrıca, kodumuzu şişiren büyük bir değiştirme ifadesinin kullanılmasını da önler.

Avantajlar : Tutarlı, modüler, daha hızlı seçim hızı, tetiklemeye gerek yok!

Dezavantajları : Tabla için gerekli alanı artırır, ekleme hızını yavaşlatır

Bir görünümü değiştirme

Aynı birleştirmeleri birden çok yerde düzenli olarak kullandığımız durumlar dışında, görünümleri kullanma eğilimindeyiz.

Bu durumlarda bile, bir tablo işlevinin daha etkin kullanılmaması için hiçbir neden yoktur. Kullanmış olduğumuz tablo aşağıdaki linkte mevcut olup, biri fonksiyon üzerinden, diğeri ise görünüm üzerinden olmak üzere iki kullanım örneğimiz bulunmaktadır.

daha fazla: SQL Server Tarihlerini Kullanma

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

Bir görünümü değiştirme

Yararları : Aramak için kompakt, Birincil Anahtar ile döndürülen (daha fazla katılım için mükemmel), parametreler kodda daha önce kullanılabilir.

Dezavantajları : Oluşturulacak daha fazla kod, daha az esnek

Birleştirmeleri Uygula'da kullanın

Veriler satır satır aktarılabildiğinden, Apply Joins'te tablo işlevleri harikadır. SQL Server'da dizeleri ayırmak için TextToRows işlevimizi kullanıyoruz. Aşağıdaki örnekte, verileri farklı sınırlayıcılarla iki kez bölmek için çift başvuru kullanıyoruz.

daha fazlası: Metni veri satırlarına ayıran SQL Server işlevi

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.