SQLServerのComputed Column(計算列)についての備忘録

Computed Column(計算列)について調べました。

Computed Column(計算列)は、SQLServerにて利用できる、テーブルの他の列のデータを元に値を計算する仮想列です。
たとえば、下記のテーブル定義では、FullName列が計算列となります。

CREATE TABLE [dbo].[Accounts]
(
    [Id] INT NOT NULL IDENTITY,
    [FirstName] NVARCHAR (50) NOT NULL,
    [LastName] NVARCHAR (50) NULL,
    [LastNameComesFirst] BIT DEFAULT (0) NOT NULL,
    [FullName] AS (
        CASE WHEN [LastName] IS NULL THEN [FirstName]
             WHEN [LastNameComesFirst] = 0 THEN concat([FirstName], N' ', [LastName])
             ELSE concat([LastName], N' ', [FirstName])
        END), 
    CONSTRAINT [PK_Accounts_Id] PRIMARY KEY ([Id] ASC),
)

この例では、FullName列はSelectされるたびに計算されます。
今までSelect時にSelect文の中で明示的に計算していた苦労は全くの徒労だったのかもしれません。

Persisted Computed Column

また、計算列の中には、Persisted Computed Columnと呼ばれるものがあり、この場合は計算列の計算結果が物理的にテーブルに保存されます。
下記の例では、FullName列の最後に"PERSISTED"が指定されており、この場合はこの列はPersisted Computed Columnとなります。

CREATE TABLE [dbo].[AccountsPersisted]
(
    [Id] INT NOT NULL IDENTITY,
    [FirstName] NVARCHAR (50) NOT NULL,
    [LastName] NVARCHAR (50) NULL,
    [LastNameComesFirst] BIT DEFAULT (0) NOT NULL,
    [FullName] AS (
        CASE WHEN [LastName] IS NULL THEN [FirstName]
             WHEN [LastNameComesFirst]=0 THEN concat([FirstName], N' ', [LastName])
             ELSE concat([LastName], N' ',[FirstName])
        END) PERSISTED,
    CONSTRAINT [PK_AccountsPersisted_Id] PRIMARY KEY ([Id] ASC),
)

今までUpdate/Insert時にTriggerを使って計算結果をメモ化していた苦労は何だったのでしょうか。
FullNameが自動的に計算されるのは最初のAccountsテーブルの例と同じですが、Persisted Computed Columnの場合はSelect時に計算するのではなく、計算に必要なColumnのデータが変化した場合だとのことです。
そのため、Persisted Computed Columnを用いた方がSelectは高速になると思われます*1が、ストレージ容量は増えます。

EXEC sp_spaceused N'dbo.Accounts';
GO
EXEC sp_spaceused N'dbo.AccountsPersisted';
GO
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Accounts' AND COLUMN_NAME = 'FullName'
GO
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'AccountsPersisted' AND COLUMN_NAME = 'FullName'

計算にユーザー定義関数を使いたい

Computed Columnは使いどころによっては非常に便利になり得ますが、テーブル定義の中に計算式を記述するのは少し遠慮したいです。
よって、以下のようなユーザー定義関数を作成しました。

--この関数ではPersisted Computed Columnでは動作しない
CREATE FUNCTION [dbo].[ufnFullName]
(
    @firstName nvarchar(50),
    @lastName nvarchar(50),
    @lastNameComesFirst bit = NULL
)
RETURNS nvarchar(101)
AS
BEGIN
    RETURN (
        CASE WHEN @lastName IS NULL THEN @firstName
             WHEN @lastNameComesFirst = 0 THEN concat(@firstName, N' ', @lastName)
             ELSE concat(@lastName, N' ', @firstName)
        END);
END

この関数を利用することで、テーブル定義もかなりスッキリしました。

CREATE TABLE [dbo].[Accounts]
(
    [Id] INT NOT NULL IDENTITY,
    [FirstName] NVARCHAR (50) NOT NULL,
    [LastName] NVARCHAR (50) NULL,
    [LastNameComesFirst] BIT DEFAULT (0) NOT NULL,
    [FullName] AS (dbo.ufnFullName([FirstName], [LastName], [LastNameComesFirst])), 
    CONSTRAINT [PK_Accounts_Id] PRIMARY KEY ([Id] ASC),
)

が、この関数をPersisted Computed Columnに対して利用することはできないようです。

テーブル 'AccountsPersisted' の計算列 'FullName' を保存できません。この列は不明確です。

と出てエラーとなりました。イケズです。
調べたところ、ユーザー定義関数に"WITH SCHEMABINDING"を記述すると動作するようです。*2

CREATE FUNCTION [dbo].[ufnFullName]
(
    @firstName nvarchar(50),
    @lastName nvarchar(50),
    @lastNameComesFirst bit = NULL
)
RETURNS nvarchar(101)
WITH SCHEMABINDING
AS
BEGIN
    RETURN (
        CASE WHEN @lastName IS NULL THEN @firstName
             WHEN @lastNameComesFirst = 0 THEN concat(@firstName, N' ', @lastName)
             ELSE concat(@lastName, N' ', @firstName)
        END);
END

*1:未検証　→　確認しました。

mrgchr.hatenablog.com

*2:sql server 2008 - Schema Binding with Computed Column Function - Stack Overflow