FILESTREAMとFILETABLEでファイルデータをスマートに管理：SQL Serverの最新機能

2024-04-06

SQL Serverの最大行サイズ

バージョンごとの最大行サイズ

バージョン	最大行サイズ	LOBデータを含む最大行サイズ
SQL Server 2016 以降	8,060 バイト	2 GB
SQL Server 2012 および 2014	8,060 バイト	1 GB
SQL Server 2008 および 2008 R2	8,060 バイト	512 MB

注:

上記のサイズは、行に含まれるすべての列の合計サイズを表します。
LOBデータ (Large Object Data) は、テキスト、画像、バイナリデータなどの大容量データを格納するために使用されるデータ型です。LOBデータは、行サイズ制限とは別に格納されます。
SQL Server 2016 以降では、行圧縮機能を使用して、行サイズを最大 40% 削減できます。

最大行サイズを超えるデータを格納するには、以下の方法があります。

LOBデータ型を使用する: テキスト、画像、バイナリデータなどの大容量データを格納するには、LOBデータ型 (VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX)、TEXT、NTEXT、IMAGE) を使用します。
複数の行に分割する: データを複数の行に分割して格納することができます。
別のストレージソリューションを使用する: Azure Blob Storage などの別のストレージソリューションを使用して、大容量データを格納することができます。

最大行サイズを設定する

SQL Server Management Studio (SSMS) を使用して、テーブルの最大行サイズを設定することができます。

SSMS を開き、データベースに接続します。
オブジェクトエクスプローラーで、テーブルを右クリックし、「プロパティ」を選択します。
「ストレージ」ページで、「最大行サイズ」フィールドに新しいサイズを入力します。
「OK」をクリックして変更を保存します。

最大行サイズを変更する前に、テーブルにデータがないことを確認してください。
最大行サイズを小さくすると、既存のデータが切り捨てられる可能性があります。

SQL Serverの最大行サイズは、データベースのバージョンと使用しているデータ型によって異なります。最大行サイズを超えるデータを格納するには、LOBデータ型を使用したり、データを複数の行に分割したり、別のストレージソリューションを使用することができます。

データベース設計時には、最大行サイズを考慮する必要があります。
データベースのパフォーマンスを向上させるためには、行サイズをできるだけ小さくすることをお勧めします。

最大行サイズを確認する

SELECT 
    name, 
    object_id, 
    index_id, 
    max_length 
FROM 
    sys.indexes 
WHERE 
    object_id = OBJECT_ID('TableName') 
    AND index_id IN (0, 1)

上記のコードは、テーブル TableName の最大行サイズと、その行サイズに貢献している列を確認します。

最大行サイズを設定する

ALTER TABLE TableName
ALTER COLUMN ColumnName VARCHAR(MAX)

上記のコードは、テーブル TableName の列 ColumnName の最大行サイズを VARCHAR(MAX) に設定します。

LOBデータ型を使用する

CREATE TABLE TableName
(
    ID INT NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Description TEXT NOT NULL
)

上記のコードは、Description 列にテキストデータを格納するために TEXT LOBデータ型を使用するテーブルを作成します。

補足

上記のサンプルコードは、SQL Server 2016 以降で使用できます。

SQL Serverの最大行サイズを超えるデータを格納する他の方法

SQL Server 2008 R2 以降では、ファイルストリーム機能を使用して、行サイズ制限を超えるデータを格納することができます。ファイルストリームは、BLOBデータ (バイナリの大容量オブジェクト) を NTFS ファイルシステムに直接格納する機能です。

メリット:

行サイズ制限を超えるデータを格納できる
データベースのパフォーマンスを向上させることができる
ストレージコストを削減できる

SQL Server 2008 R2 以降が必要
NTFS ファイルシステムが必要
すべてのデータ型で利用できるわけではない

ファイルストリームを使用する例:

CREATE TABLE TableName
(
    ID INT NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Document VARBINARY(MAX) FILESTREAM
)

FILESTREAM と FILETABLE を使用する

FILESTREAM と FILETABLE を組み合わせることで、より柔軟な方法でファイルデータを格納することができます。

ファイルストリームのすべてのメリットに加えて、以下のメリットがある
ファイルをデータベース内のテーブルのように管理できる
ファイルメタデータをクエリできる
ファイルアクセス権限を制御できる

CREATE TABLE TableName
(
    ID INT NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Document FILESTREAM
)

CREATE FILETABLE
    DIRECTORY = N'\\MyData\\Documents'
    FOR TABLE TableName

外部データソースを使用する

SQL Server 2016 以降では、外部データソース機能を使用して、Hadoop Hive や Azure Blob Storage などの外部データソースにあるデータを格納することができます。

SQL Server のデータベースサイズを制限することなく、大量のデータを格納できる
データの場所を意識せずに、データ分析を行うことができる

外部データソースへのアクセス権限が必要

CREATE EXTERNAL DATA SOURCE MyDataSource
    TYPE HadoopHive
    LOCATION 'hdfs://localhost:8020/hive'
    CREDENTIAL = MyCredential

CREATE EXTERNAL TABLE TableName
(
    ID INT NOT NULL,
    Name VARCHAR(50) NOT NULL,
    Document VARCHAR(MAX)
)
WITH (
    LOCATION = '/MyData/Documents',
    DATA_SOURCE = MyDataSource
)