사용자 정의 함수

-- (가) 한 줄로
CREATE OR ALTER FUNCTION Board.F_LEVEL_SIMPLE (@depth smallint)
RETURNS nvarchar(20)
AS
BEGIN
    RETURN CASE @depth WHEN 0 THEN N'원글' WHEN 1 THEN N'답글' ELSE N'답답글' END;
END
GO
-- (나) 같은 결과인데 반복문으로
CREATE OR ALTER FUNCTION Board.F_LEVEL_LOOP (@depth smallint)
RETURNS nvarchar(20)
AS
BEGIN
    DECLARE @r nvarchar(20) = N'답답글', @i int = 0;
    WHILE @i <= 1
    BEGIN
        IF @i = @depth
        BEGIN
            SET @r = CASE @i WHEN 0 THEN N'원글' ELSE N'답글' END;
            BREAK;
        END
        SET @i += 1;
    END
    RETURN @r;
END
GO
-- 엔진이 이 함수를 문장에 녹여 넣을 수 있는지 봅니다.
SELECT o.name AS 함수, m.is_inlineable AS 인라인가능
FROM sys.sql_modules m JOIN sys.objects o ON o.object_id = m.object_id
WHERE o.type = 'FN' AND o.is_ms_shipped = 0;

-- depth 만 담은 10만 행짜리 표를 만들어 셋을 견줍니다.
DECLARE @s datetime2(7), @r nvarchar(20);

SET @s = SYSDATETIME();
SELECT @r = CASE depth WHEN 0 THEN N'원글' WHEN 1 THEN N'답글' ELSE N'답답글' END
FROM Board.T_F;
SELECT DATEDIFF(millisecond, @s, SYSDATETIME()) AS 밀리초;

SET @s = SYSDATETIME();
SELECT @r = Board.F_LEVEL_SIMPLE(depth) FROM Board.T_F;
SELECT DATEDIFF(millisecond, @s, SYSDATETIME()) AS 밀리초;

SET @s = SYSDATETIME();
SELECT @r = Board.F_LEVEL_LOOP(depth) FROM Board.T_F;
SELECT DATEDIFF(millisecond, @s, SYSDATETIME()) AS 밀리초;

-- (가) 인라인: RETURN 뒤에 SELECT 하나뿐입니다. BEGIN 도 없습니다.
CREATE OR ALTER FUNCTION Board.FT_POSTS_INLINE (@board_num int)
RETURNS TABLE
AS
RETURN (
    SELECT num, title, group_num, sort_no, hit_count
    FROM Board.POSTS
    WHERE board_num = @board_num
);
GO
-- (나) 다중 문: 표 변수를 선언하고 채워서 돌려줍니다.
CREATE OR ALTER FUNCTION Board.FT_POSTS_MULTI (@board_num int)
RETURNS @t TABLE (num int, title nvarchar(200), group_num int, sort_no int, hit_count int)
AS
BEGIN
    INSERT INTO @t
    SELECT num, title, group_num, sort_no, hit_count
    FROM Board.POSTS
    WHERE board_num = @board_num;
    RETURN;
END
GO
-- 같은 방법으로 부릅니다.
SELECT TOP (20) num, title FROM Board.FT_POSTS_INLINE(2) ORDER BY group_num DESC, sort_no;
SELECT TOP (20) num, title FROM Board.FT_POSTS_MULTI(2) ORDER BY group_num DESC, sort_no;

-- 같은 조회를 100번씩 합니다.

-- 표를 고치려 하면
CREATE OR ALTER FUNCTION Board.F_BAD1 (@num int)
RETURNS int
AS
BEGIN
    UPDATE Board.POSTS SET hit_count = hit_count + 1 WHERE num = @num;
    RETURN 1;
END
GO
-- 오류를 던지려 하면
CREATE OR ALTER FUNCTION Board.F_BAD4 (@n int)
RETURNS int
AS
BEGIN
    IF @n < 0 THROW 50001, N'음수는 안 됩니다', 1;
    RETURN @n;
END

-- 같은 함수에 WITH SCHEMABINDING 만 더합니다.
CREATE OR ALTER FUNCTION Board.F_LEVEL_SB (@depth smallint)
RETURNS nvarchar(20)
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE @depth WHEN 0 THEN N'원글' WHEN 1 THEN N'답글' ELSE N'답답글' END;
END
GO
SELECT o.name AS 함수, m.is_inlineable AS 인라인가능,
       OBJECTPROPERTY(o.object_id, 'IsDeterministic') AS 결정적,
       OBJECTPROPERTY(o.object_id, 'IsPrecise') AS 정확
FROM sys.sql_modules m JOIN sys.objects o ON o.object_id = m.object_id
WHERE o.name IN ('F_LEVEL_SIMPLE', 'F_LEVEL_SB');

-- 결정적이므로 계산 열에 사용하고 인덱스까지 걸 수 있습니다(3.6).
ALTER TABLE Board.POSTS ADD level_name AS Board.F_LEVEL_SB(depth);
CREATE NONCLUSTERED INDEX IX_POSTS_level ON Board.POSTS (level_name);

SELECT TOP (3) num, depth, level_name FROM Board.POSTS WHERE group_num = 6 ORDER BY sort_no;