Введение в функции SQL Server, их преимущества и недостатки
Что такое функция SQL?
Функции SQL Server могут использоваться для возврата либо одиночных (масштабирующих) значений, либо таблиц, используя процедуры T-SQL или CLR (среда CLR) и часто выполняя более сложные вычисления, чем вы хотели бы использовать в общем коде.
Когда лучше использовать функцию, а не встроенный код?
Хорошее использование
Функции могут использоваться для замены представлений (возврата таблицы), в качестве вычисляемого столбца в таблице, выполнения согласованных действий поиска или просто для модульного построения кода, что может помочь в сокращении необходимых изменений.
Плохое использование
Мы видим это все время, но не следует использовать функции для возврата данных поиска вместо соединения, когда вы имеете дело с большими наборами данных. Каждая строка будет вызывать одну и ту же функцию, даже если она уже встретила это значение. В этих случаях используйте соединение.
Примеры функций скалера
Функции масштабирования лучше всего использовать для выполнения логики, такой как переформатирование на основе строк или вычисления, поскольку по своей природе они вызываются для каждой строки, их можно использовать для поиска данных в другой таблице, но в целом вы получите лучшую производительность, используя соединение. Для этого мы можем посмотреть на нашу функцию получения возраста по следующей ссылке.
Сохранять возраст человека на момент заполнения формы не имеет смысла, поскольку при последующем запросе данных они будут устаревшими. Лучшим вариантом было бы зафиксировать дату рождения и вычислить ее на лету. В нашей функции мы добавили поле до, которое можно использовать для вычисления задним числом или, что более мрачно, для вычисления возраста и времени смерти (эта функция была расширена для контракта с 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]Примеры функций скалера
Преимущества : согласованный, модульный, более компактный, потенциально снижает количество изменений.
Недостатки : чтобы увидеть код нужно заглянуть в функцию
Хотя эта функция в целом полезна, она также чрезвычайно точна, поскольку использует функцию високосного года. Он недетерминирован по своей природе, поэтому никогда не должен храниться в виде постоянных данных.
Примеры столбцов таблицы
Вычисляемые столбцы могут быть добавлены либо как постоянные (изменяется при изменении данных), либо как непостоянные (вычисляются каждый раз при выборе строки). Мы можем рассмотреть два способа их использования здесь, в нашей системе управления контентом.
Примечание : постоянных данных может быть труднее достичь, поскольку для этого требуется набор ограничений.
Несохраняемый: возраст
Используя функцию возраста, как указано выше, мы можем добавить это в таблицу и передать значения из других столбцов. Затем мы просто выбираем его как столбец.
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
Его можно выбрать так же, как и обычный столбец, а данные хранятся в таблице. Это также позволяет избежать использования массовых операторов замены, раздувающих наш код.
Преимущества : последовательная, модульная, быстрая скорость выбора, отсутствие необходимости в спусковом крючке!
Недостатки : увеличивает пространство, необходимое для стола, снижает скорость вставки.
Замена представления
Мы, как правило, не используем представления, за исключением случаев, когда мы регулярно используем одни и те же объединения в нескольких местах.
Даже в этих случаях нет причин, по которым табличную функцию нельзя было бы использовать более эффективно. Таблицу, которую мы использовали, можно найти по ссылке ниже, и у нас есть два примера использования: один с помощью функции, а другой с использованием представления.
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Замена представления
Преимущества : Компактность для вызова, возврат с первичным ключом (идеально подходит для дальнейшего соединения), параметры можно использовать раньше в коде.
Недостатки : больше кода для сборки, менее гибкий.
Использование в Apply Joins
Табличные функции отлично подходят для использования в Apply Joins, поскольку данные могут передаваться построчно. Мы используем нашу функцию TextToRows для разделения строк в 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) s2Further detail
Some of the functions we have written can be found below.
