SQL Server 関数の概要、その利点と欠点
SQL 関数とは
SQL Server 関数は、T-SQL または CLR (共通言語ランタイム) ルーチンを使用して単一 (スケーラー) 値またはテーブルを返すために使用でき、多くの場合、一般的なコードで使用するよりも複雑な計算を実行します。
インライン コードではなく関数を使用することをお勧めするのはいつですか?
良い使用
関数を使用して、ビューを置き換えたり (テーブルを返す)、テーブルの計算列として使用したり、一貫したルックアップ アクションを実行したり、必要な変更を減らすのに役立つコードを単にモジュール化したりすることができます。
悪い使い方
よく見かけますが、大規模なデータセットを扱う場合、結合の代わりにルックアップ データを返すために関数を使用しないでください。各行は、すでにその値に遭遇した場合でも、同じ関数を呼び出します。このような場合は、結合を使用します。
スケーラー関数の例
スケーラー関数は、その性質上すべての行に対して呼び出されるため、行ベースの再フォーマットや計算などのロジックを実行するために使用するのが最適です。別のテーブルのデータを検索するために使用できますが、一般に、次を使用するとパフォーマンスが向上します。結合。これについては、次のリンクで get age 関数を確認できます。
フォームに記入した時点での年齢を保存しても意味がありません。後でデータを照会すると、データが古くなっているからです。より良いオプションは、生年月日を取得してその場で計算することです。私たちの関数では、until フィールドを追加しました。これは、計算をさかのぼって計算するために使用できます。また、より厳密には、死亡時の年齢を計算するために使用できます (この関数は 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]スケーラー関数の例
利点: 一貫性があり、モジュール式で、よりコンパクトで、潜在的に変更の数が減る
短所：コードを表示するには、関数を調べる必要があります
この機能は一般的に便利ですが、うるう年機能を利用しているため、非常に正確です。本質的に非決定論的であるため、永続化されたデータとして保存しないでください。
表の列の例
計算列は、永続化 (データが変更されたときに変更) または非永続化 (行が選択されるたびに計算) として追加できます。ここでは、コンテンツ管理システム内でそれらを使用した 2 つの方法を見ることができます。
注: 一連の制約を満たす必要があるため、永続化されたデータを実現するのは難しい場合があります。
非持続: 年齢
上記の 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
CSSに必要なスペースを最大30%削減する機能があります。これを定期的に呼び出すと、テーブルの選択速度が遅くなり、データがほとんど更新されないため、挿入/更新時に計算を実行することは理にかなっています。列を関数として作成することにより、これらの操作をトリガーとして実行する必要もありません。
Add to a Table
CREATE TABLE CSS(CSSID INT IDENTITY(1,1) NOT NULL,CSSText NVARCHAR(MAX),CSSMin AS (ous.CSSProcessor([CSSText])) PERSISTED)永続化: 縮小された CSS
通常の列と同じように選択でき、データはテーブルに格納されます。また、コードを肥大化させる大規模な replace ステートメントの使用を回避します。
利点: 一貫性のある、モジュール式の、より速い選択速度、トリガーの必要なし!
短所: テーブルに必要なスペースが増加し、挿入速度が遅くなります
ビューの置き換え
複数の場所で同じ結合を定期的に使用する場合を除いて、ビューを使用しない傾向があります。
このような場合でも、テーブル関数をより効果的に使用できない理由はありません。使用したテーブルは以下のリンクにあります。使用例が 2 つあります。1 つは関数を使用し、もう 1 つはビューを使用しています。
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 RETURNENDGOCreate 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.CalenderFunctionUsage
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ビューの置き換え
利点: 呼び出しがコンパクトで、主キーと共に返され (さらに結合するのに最適)、パラメーターをコードの早い段階で使用できます。
短所: ビルドするコードが多くなり、柔軟性が低下します
適用結合での使用
表関数は、データを行ごとに渡すことができるため、結合の適用で使用するのに最適です。 TextToRows 関数を使用して、SQL Server で文字列を分離します。以下の例では、二重適用を使用して、異なる区切り文字でデータを 2 回分割しています。
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さらに詳しく
私たちが書いた関数のいくつかを以下に示します。
