SQL Server AlwaysOn でプライマリサーバーかどうかを確認するクエリ

動的管理ビュー (DMV) を使用して、AlwayOn でプライマリサーバーかどうかを確認するクエリ。

select name,replica_server_name,role_desc,dns_name from sys.availability_groups_cluster agc
right join sys.dm_hadr_availability_replica_cluster_states dharc
on agc.group_id = dharc.group_id
left join sys.dm_hadr_availability_replica_states dhars
on dharc.replica_id=dhars.replica_id
left join sys.availability_group_listeners agl
on agc.group_id = agl.group_id

そーすると、次の画像のように、サーバー一覧とSecondaryかPrimaryかが表示される。自身のサーバーのロールしか取れないので、SecondaryかPrimaryかしかわからない。複数サーバーあると、どれがPrimaryかはそれぞれのサーバーで実行してみないとわからない…。

image

SQL Server 2016 での JSON

SQL Server 2016 では、JSONへのサポートが追加されました。
どのような考え方でJSONサポートが追加されていて、どのようにユーザーはJSONをSQL Serverで使用できるのかを理解するのに、SQL Server チームが投稿した全4回の投稿が参考になるので、ざっくりと意訳してみました。

概要

SQL Server でJSON関数は、JSONデータを分析しクエリを実行し、JSONをリレーショナルフォーマットに変換し、SQLクエリ結果をJSONテキストに出力します。

 

 

組み込み関数(JSON_VALUE、JSON_QUERY)を使用して、JSONからデータを取得したり、JSONフォーマットが正しいかを検証できます。OPENJSON関数を使用するとJSONオブジェクト配列を行セットに変換できます。FOR JSONでJSONテキスト形式でクエリ結果を取得できます。

次のTransact-SQLコードは、JSONテキストの格納にテキスト変数を指定しています。

DECLARE @json NVARCHAR(4000)
SET @json =
N'{
    “info”:{ 
      “type”:1,
      “address”:{ 
        “town”:”Bristol”,
        “county”:”Avon”,
        “country”:”England”
      },
      “tags”:[“Sport”, “Water polo”]
   },
   “type”:”Basic”
}’

JSON_VALUE と JSON_QUERY関数を使用して、JSONテキストから値とオブジェクトを取得できます。

SELECT
  JSON_VALUE(@json, ‘$.type’) as type,
  JSON_VALUE(@json, ‘$.info.address.town’) as town,
  JSON_QUERY(@json, ‘$.info.tags’) as tags

このクエリは、「Basic」、「Bristol」、「[“Support”,”Water polo”]」を返します。JSON_VALUE関数は、セカンドパラメーターで指定したJSONパスでJSONテキスト(例えば、文字列、数字、true/false)から値を一つ返します。JSON組み込み関数はセカンドパラメーターで指定するJavaScriptのような文法でJSONの値やオブジェクトを参照します。

OPENJSON関数はJSONテキストの配列を参照でき、配列から項目を返します。

SELECT value
FROM OPENJSON(@json, ‘$.info.tags’)

この例では、タグ配列から文字列を返します。OPENJSON関数は複雑なオブジェクトを返します。

FOR JSONはSQLクエリによって返す結果セットをJSONテキスト形式で整形します。

SELECT object_id, name
FROM sys.tables
FOR JSON PATH

SQL ServerにJSONデータを格納する

SQL ServerではJSONはテキスト形式で格納されます。
JSONデータを格納するために標準的なNVARCHARカラムが使用されます。
次の例は、いくつかの情報をJSON形式で保存したシンプルなテーブルです。

CREATE TABLE Person (
Id int IDENTITY PRIMARY KEY NONCLUSTERED,
FirstName nvarchar(100) NOT NULL,
LastName nvarchar(100) NOT NULL,
InfoJson nvarchar(max) 
) WITH (MEMORY_OPTIMIZED=ON)

伝統的なリレーショナルのみのデータベースやドキュメントのみのシステムと、SQL Server 2016で提供するハイブリッドモデルとの大きな違いがこの例で示されています。SQL Server 2016では、通常の列(この例では、FirstNameやLastName)とJSONを格納した列(この例では、InfoJSON)を混在させることができます。

SQL Serverでは、必要に応じて通常の列やJSON列、Spatial、XML列と1つ以上混在させて構成することができます。同じテーブルで速くアクセスできる標準的なリレーショナル列と柔軟性を提供するJSON列により迅速なアプリケーション開発ができます。

JSONはテキスト列に格納されますが、平文ではありません。SQL Serverは、UNICODE圧縮のような最大50%の圧縮ができる圧縮メカニズムを使用してテキスト列のストレージを最適化する仕組みを組み込んでいます。またカラムストアテーブルにJSONテキストを格納できたり、またはGZipアルゴリズムを使う組み込みのCOMPRESS機能で明示的に圧縮することができます。

JSONはSQL ServerのコンポーネントやNVARCHARデータで動作するテクノロジーと完璧な互換性があります。上の例では、JSONは高パフォーマンスを提供するインメモリOLTP(Hekaton)テーブルに格納しています。JSONを標準的なテーブルや、カラムストアインデックス、FILESTREAMに格納できます。Polybaseを使用してHadoopから読み込んだり、ファイルシステムから読み込んだり、Azure SQLにストレッチデータベースを設定できます。

もし自由なテキストフォーマットでJSONを保持したくない場合は、標準的なCHECK制約とISJSON関数を使用して正しいフォーマットになっているかのJSON検証妥当性確認を追加できます。

ALTER TABLE Person
ADD CONSTRAINT [Content should be formatted as JSON]
 CHECK ( ISJSON( InfoJSON )> 0 )

こては標準的なチェック制約を使用して、正しいフォーマットのJSONカラムにテキストが格納されているかどうかの妥当性確認ができます。

JSONはテキストで表すので、クライアントアプリケーションを変更したり、新しいドライバーをまったり、プロトコルを変更する必要がありません。C#、Java、Node.jsアプリケーションでJSONドキュメントの読み書きができます。JSONはテキストフィールドでORMモデルで読み込んだり、Ajaxリクエストを経由して直接JavaScriptクライアントコードに送信できます。

組み込みのJSON処理用の関数

SQL Server 2016 では、JSONテキストをパースしたり処理する関数が提供されています。

  • ISJSON(json文):NVARCHARテキストがJSON仕様に沿った正しいフォーマットかを確認します。JSONを格納するNVARCHAR列に、この関数を使用してチェック制約を作成できます。
  • JSON_VALUE(json文,パス):JSONをパースして、JavaScriptライクなパスで指定された値を出力します。(JSONパス例は下を参照)
  • JSON_QUERY(json文,パス):JSONをパースして、JavaScriptライクなパスで指定されたオブジェクトまたは配列を出力します。(JSONパス例は下を参照)

これらの関数はJSONパスを使用してJSONテキストで値やオブジェクトを参照します。JSONパスはJSONテキストのプロパティの参照をJavaScriptライクな文法を使用します。いくつかの例を提示します。

  • $ – 入力されたテキストのJSONオブジェクト全体を参照します
  • $.property1 – JSONオブジェクトのproperty1を参照します
  • $[4] – JSON配列の5番目の項目を参照します(インデックスはJavaScriptのように0から数えます)
  • $.property1.property2.array1[5].property3.array2[15].property4 – JSONオブジェクトで複雑にネストしたプロパティを参照します
  • $.info.”first name” – infoオブジェクトの”first name”プロパティを参照します。スペースやダラーなどの特別な記号がキーに含まれている場合、ダブルコーテーションマークで囲む必要があります。

$記号は、入力したJSONオブジェクト(XPATH言語ではルート”/”に該当します)を示します。JSONオブジェクトでプロパティを参照するために、$の後にJavaScriptライクのプロパティか配列を追加できます。次の例は、組み込み関数を使用してシンプルなクエリ例の1つです。

SELECT Id, FirstName, LastName,
     JSON_VALUE(InfoJSON, ‘$.info.”social security number”’) as SSN,

     JSON_QUERY(InfoJSON, ‘$.skills’) as Skills
FROM Person AS t
WHERE ISJSON( InfoJSON ) > 0
AND JSON_VALUE(InfoJSON, ‘$.Type’) = ‘Student’

このクエリは、FirstNameとLastNameを標準的なテーブル列から返し、JSON列からスキルの配列と社会保障番号を返します。結果は、InfoJSON列に正しいJSONが格納されていて、Type値にStudentを含んでる行を結果として返します。クエリの一部でJSONの値を使用できます。order byやgroup byなど。

JSONテキストをリレーショナルテーブルへ変換する – OPENJSON

OPENJSONは、JSONテキストの中を見たり、JSONオブジェクトの配列を示したり、主力結果に各エレメントを1行で戻したりするテーブル値関数(TVF)です。

上の例では、JSON配列のどこを開くか(例では、$.Ordrrs)、どの列を結果で返し、JSONオブジェクトの値をセルのどこに返すかを指定します。

次の例では、@orders変数のJSON配列を行セットにし、標準的なテーブルに挿入します。

INSERT INTO Orders(Number, Date, Customer, Quantity)
SELECT Number, Date, Customer, Quantity
 OPENJSON (@orders)
 WITH (
        Number varchar(200),
        Date datetime,
        Customer varchar(200),
        Quantity int
 ) AS OrdersArray

OPENJSONによって返される結果セットの4列は、WITHで定義されます。OPENJSONはNumber、Date、Customer、Quantityプロパティを探索し、それらの値を結果セットの列に変換します。既定では、プロパティが無ければNullを返します。上のクエリではm、@orders変数に次のJSON配列が含まてれいると仮定しています。

‘[
   {“Number”:1, “Date”: “8/10/2012”, “Customer”: “Adventure works”, “Quantity”: 1200},
   {“Number”:4, “Date”: “5/11/2012”, “Customer”: “Adventure works”, “Quantity”: 100},
   {“Number”:6, “Date”: “1/3/2012”, “Customer”: “Adventure works”, “Quantity”: 250},
   {“Number”:8, “Date”: “12/7/2012”, “Customer”: “Adventure works”, “Quantity”: 2200}
]’

JSONテキストからリレーショナル形式に変換するのはとても簡単なのがわかるかと思います。列名とデータ型と、JSONテキストでマッチしたプロパティを見つけたOPENJSONの列を指定する必要があります。

OPENJSONは、リレーショナルとJSONデータを同じクエリで組み合わせて使用することができます。前掲のJSONテキストがOrder列に格納されていると仮定して、次のクエリはJSONと列を組み合わせたクエリです。

SELECT Id, FirstName, LastName, Number, Date, Customer, Quantity
 FROM Person
    CROSS APPLY OPENJSON (OrdersJson)
                            WITH (
                                        Number varchar(200),
                                        Date datetime,
                                        Customer varchar(200),
                                        Quantity int ) AS OrdersArray

OPENJSONは、各セルに配列を開き、JSONオブジェクトごとに1行を配列で返します。CROSS APPLY OPENJSON文法は、JSONセル内のJSON配列からマテリアライズされる子内部表と表の行を結合するのに使われます。

JSONデータのインデックス

JSON値はテキストでフォーマットされますが、テーブル列にほかの値と同じ方法でインデックスすることができます。標準的な非クラスタ化インデックスまたはフルテキスト検索インデックスを使用できます。

クエリでよく使用されるいくつかのJSONプロパティにインデックスを作成したい場合、値を参照する非永続型の計算列を作成し、その列に標準的なインデックスを作成します。次の例は、InfoJSON列で$.Companyプロパティを使用して行をフィルタリングするクエリを最適化しています。

ALTER TABLE Person
ADD vCompany AS JSON_VALUE(InfoJSON, $.Company’)

CREATE INDEX idx_Person_1
    ON Person(vCompany)

SQL Serverはハイブリッドモデルを提供し、JSONからの値をキーや列に含めることができ、JSON値と標準的な列を同じインデックスで使用できます。

JSONは通常のテキストなので、標準的なフルテキスト検索を使用できます。フルテキストインデックスは、値の配列上で作成されます。フルテキストインデックスをJSON配列を含んだ列上で作成したり、既存の列でいくつかの配列を参照する計算列を作成しそのカラム上でフルテキスト検索インデックスを作成できます。

ALTER TABLE Person
ADD vEmailAddresses AS JSON_QUERY(InfoJSON, ‘$.Contact.Emails’)

CREATE FULLTEXT INDEX ON Person(vEmailAddresses)
    KEY INDEX PK_Person_ID ON jsonFullTextCatalog;

フルテキスト検索インデックスはJSONはいてるに含まれているいくつかの値を含んでいる行を探そうとするクエリを最適化する必要があるときに便利です。

SELECT PersonID, FirstName,LastName,vEmailAddresses
FROM Person
WHERE CONTAINS(vEmailAddresses, ‘john@mail.microsoft.com’)

このクエリは、email配列に「john@mail.microsoft.com」を含んでいるPerson行を返します。フルテキストインデックスは、JSONのパースに関して特別なルールを用意していません。セパレーター(ダブルクォートやコンマ、括弧)を使用して、JSON配列を分割し、配列に値をインデックスします。フルテキストインデックスは、適切に数字やシンプルな文字列値に配列します。JSON配列でより複雑なオブジェクトを持っている場合、システムはKeyとValueの違いを認識できないのでフルテキストインデックスは直接適用できません。

ここまでで、同じインデックスで、JSON値とリレーショナル列両方を使用できることが分かりました。

データをJSONとして出力する – FOR JSON

SQL  SELECT クエリの終わりに、FOR JSONを追加すると、SQL Serverは、JSONにフォーマットした結果をクライアントに返します。全ての行は1つのJSONオブジェクトにフォーマットされ、結果セットのセル内の値はJSONオブジェクトのvalueとして生成され、カラム名またはエイリアス名じゃKye名として生成されます。FOR JSONには2種類あります。

  • FOR JSON PATH:カラム名/エイリアス名を使用してJSONアウトプットの構造を定義できます。列エイリアスでドットで区切られた文字列を指定すると、JSONプロパティはネーミング規則に従います。この機能はスラッシュを使用してパスを分割するFOR XML PATHと似ています。
  • FOR JSON AUTO:クエリで使用したテーブル仮想をもとに、ネストしたJSONサブ配列を自動的に生成します。

FOR JSONで生成されたJSONテキストは、OPENJSONを使用してリレーショナル形式に戻す返還ができます。

まとめ

SQL ServerのJSON関数はJSONをリレーショナルに変換、リレーショナルデータをJSONに返還するのと同様のJSON分析とクエリ発行を可能にします。アプリケーションレイヤーで追加の変更なしにJSONデータを使ったり発行できるようになりました。

SQL ServerはリレーショナルデータとJSONを組み合わせたハイブリッドストレージモデルを提供します。高速データアクセスと柔軟で迅速な開発とのトレードオフを可能にしました。標準的な列とJSONテキストの値の両方で同じインデックス技術を使用できます。

ハイブリッドモデルは、SQL Serverのメリットである、フルパワーのクエリ言語とACIDトランザクションの恩恵を受けられます。SQL Serverと互換性のあるエコシステムであるツールを使用でき、既知の管理モデル、セキュリティモデルが、JSON機能でもそのまま使用できます。

参照記事

SQL Server のクエリヒントによるロックへの影響(SELECT)

SQL Serverで、クエリヒントを使用するとロックにどのような影響が出るのかを調べてみました。

結論

image

標準クエリの場合、ISを取得するので、DDL文とXロック(SELECTだと、XLOCKクエリヒントを使ったクエリ)と競合する。UPDLOCKとは競合しない。

UPDLOCKは、キーにUを取得するので、DDL文と同一キーへのUとXと競合する。ページにIUを取得するので、ページへのUとXと競合する。キーへのUはトランザクション終了時まで保持される。

 

詳細は、ロックの互換性参照

調査方法

今回は単純なテーブルとクエリでの調査です。
4列あるテーブルで、100000行のデータを格納して、Where無しのクエリで処理をしています。

テーブル例

image

クエリ例

SELECT   [Uid]
      ,[AddTime]
      ,[EnemyId]
      ,[StrongType]
FROM [dev_next_dev].[dbo].[AAA]

クエリヒント無し

データベースにSロックを取得・
テーブルにISロックを取得。
PageにISロックを取得しますね。(検証中、まれにSの時もあり)

image

UPDLOCK

データベースにSロック
テーブルにIXロック
PAGEにIUロック
KEYにUロックを取得します。Keyは全レコード(Top1000なので1000行)のロックを保持します。

image

image

これは、トランザクションが終了するまで保持されていて、トランザクションが終了すると、KEYのUロックとPAGEのIUロック、テーブルのIXロックが解除されます。

image

NOLOCK

データベースにSロックを取得します。
テーブルにSCH-Sロックを取得します。

image

SCH-Cロックは、DDL文発行時に取得されるSCH-Mロックとのみ競合します。

TABLOCK

データベースにSロック
テーブルに大量のSロック

image

XLOCK

データベースにSロック
テーブルにISロック
テーブルにIXロック
ページにIXロック
キーごとにXロック

image

SQL Server 2016 CTP 2.0 : SQL Server Managed Backup to Azureのまとめ

SQL Server 2016のデータベースエンジンの新機能の1つに「SQL Server Managed Backup to Azure(単にManaged Backupと表記されることもある)」があります。

機能概要

Microsoft Azure Blobストレージに自動的にSQL Serverのバックアップをとったり、管理するための機能です。

バックアップタイミングは次の2パターンを選ぶことができます。

  • データベースのワークロードをベースにした自動バックアップ
  • オプションで設定したバックアップのスケジュール(時間)定義に基づいたバックアップ

Microsoft Azure Blobストレージにバックアップファイルを保存する期間を設定できます。

メリット

これまでは、時間指定のバックアップを設定するか、カスタムコードを書かないと自動バックアップを設定することができなかったのです。

しかし、SQL Server Managed Backup to Windows Azureを使用すると、バックアップファイルの保持期間と、保存場所を指定すれば自動的にバックアップされます。オプションでスケジュールを指定することもできますが不要です。

設定対象

次の範囲で、Managed Backupを設定できます。

  • インスタンスレベル
  • データベースレベル(インスタンスレベルの設定を上書き可能)

インスタンスレベルで定義をしておくと、新しいデータベースを作成したときにも自動的にバックアップされます。

前提条件

次のサービス、アカウント、認証情報が必要です。

  • Microsoft Azure アカウント
  • Azure ストレージアカウント
  • Blobコンテナー
  • Shared Access Signature:SAS(共有アクセス署名)
  • SQL Server Agent サービス

SQL Server 2014 と SQL Server 2016のManaged Backupの機能差異

項目SQL Server 2014SQL Server 2016
名前空間smart_adminmanaged_backup
ストアドsp_set_db_backUP
sp_set_instance_backup
sp_backup_config_basic
sp_backup_config_advance
セキュリティストレージアカウント
アクセスキー
共有アクセス署名
ストレージページBlobブロックBlob

SQL Server 2016では次のような機能特徴がある

  • コストの安いブロックBlobを使用する
  • ストライピングを使用し、最大12TB(ページBlobだと1TB)
  • ストライピングにより、リストア時間の短縮
  • システムデータベースに対応
  • 単純復旧モードのデータベースも利用可能

フルバックアップが取得されるタイミング

  • 機能を有効にしたタイミング
  • 最後のフルバックアップからログが1GB以上拡張したとき
  • 最後のフルバックアップから1週間経過したとき
  • ログチェインが壊れたとき(トランザクションログが消されたとき)

トランザクションログバックアップが取得されるタイミング

  • ログバックアップ履歴が無いとき(通常は機能を有効にしたとき)
  • トランザクションログ要領を5MB以上使用したとき
  • 最後のトランザクションログバックアップから2時間経過したとき
  • フルバックアップが遅延しているとき

バックアップファイルの保存期間

  • 保存期間は最少1日、最大30日を指定できる
  • 保存期間内であれば、指定した時間にポイントタイムリカバリができる

注意事項

  • ブロックBlobの最大サイズは200GB
  • ストライピングを使用することで、最大12TBまで保存可能
  • それ以上の場合は、圧縮を使用する
  • CTP 2.1段階では、GUI(SQL Server Management Studio)での操作はできない
  • ストアドかPowerShellで設定する

機能を有効にする手順

具体的な手順はドキュメントに記載されているので、簡単な流れのみ記載。

  1. Microsoft Azureにサインアップ
  2. ストレージアカウントの作成
  3. Blobコンテナーの作成
  4. 共有アクセス署名(SAS)の作成
  5. SQL証明書の作成
  6. SQL Server Agentサービスの開始
  7. SQL Server Managed Backup to Windows Azureの設定と有効化
  8. ヘルスステータス(エラーや警告)のメール通知の設定と有効化

Azure PowerShellでの操作

Azure PowerShellを使用して、バックアップファイルを格納するコンテナーのSASを取得します。以下の例は、既存のコンテナーのSASを取得する例。

Get-AzurePublishSettingsFile
Import-AzurePublishSettingsFile
Select-AzureSubscription
$context = New-AzureStorageContext -StorageAccountName ***atoya -StorageAccountKey (Get-AzureStorageKey -Storage
New-AzureStorageContainerSASToken -Name pub -Permission rwdl -FullUri -Context $context

 

image

SQL 証明書の作成

次に、SQL証明書を作成します。

CREATE CREDENTIAL [https://***atoya.blob.core.windows.net/pub]
WITH IDENTITY = ‘Shared Access Signature’,
SECRET = ‘sv=2014-02-14&sr=c&sig=********wUKKjd2d8%3D&se=2015-07-08T01%3A55%3A03Z&sp=rwdl’

image

SQL Server Managed Backup to Windows Azureの設定と有効化

Managed Backupを設定します。

Use msdb;
GO
EXEC msdb.managed_backup.sp_backup_config_basic
@enable_backup = 1,
@database_name = ‘test’,
@container_url = ‘https://y****a.blob.core.windows.net/pub’,
@retention_days = 30
GO

ストアドプロシージャーを設定すると、次のように設定しましたと出力されます。

image

裏側では、フルバックアップが実行されます。

Database backed up. Database: test, creation date(time): 2015/05/28(23:11:16), pages dumped: 369, first LSN: 36:376:37, last LSN: 36:395:1, number of dump devices: 1, device information: (FILE=1, TYPE=URL: {‘https://*****.blob.core.windows.net/pub/test_9806c8082c754fdcbca50ae99e2b1763_20150708100900+09.bak’}). This is an informational message only. No user action is required.

SQL Server Agentサービスを使用すると書いてあるので、てっきりジョブ登録されるのかと思ったのですが、ジョブには登録されていません。

image

ヘルスステータス(エラーや警告)のメール通知の設定と有効化

SQLデータベースメールを設定します。

image

SQL Server Agentメールの設定をします。

image

監視を追加します。

EXEC msdb.managed_backup.sp_set_parameter
@parameter_name = ‘SSMBackup2WANotificationEmailIds’,
@parameter_value = ‘<t.yam***ya@***ops.com>’

メール通知はこんな感じのが届きます。

image

バックアップファイルを確認します

バックアップ状況を確認します。

SELECT *
FROM managed_backup.fn_available_backups (‘test’)

image

取得できる情報は次のようなもの。

backup_path
backup_type
expiration_date
database_guid
first_lsn
last_lsn
backup_start_date
backup_finish_date
machine_name
last_recovery_fork_id
first_recovery_fork_id
fork_point_lsn
availability_group_guid

SQL Server 2016でJSONに対応します

MSDNブログで投稿されていた「JSON support in SQL Server 2016」が元ネタ。

SQL ServerでのJSONサポートは、Microsoft connect siteで、1000以上の投票が集まるリクエストが多いもののひとつです。マイクロソフトは、SQL Server 2016で、デフォルト機能でJSONサポートをすることを発表しました。

まず最初に、nativeのJSONサポートは、nativeのJSONデータ型を提供するわけではありません。SQL Server 2016では、JSONは、NVARCHARデータ型で表現される予定です。

  • 移行
    すでに多くの人がテキストとしてJSONを格納しており、JSONデータ型を提供した場合、新しい機能を使用するには、データベーススキーマーの変更とデータの再ロードが必要になってしまいます。開発者がデータベースの変更をすることなく新しい機能を利用できるように実装します。
  • 機能間の互換性
    NVARCHARは全てのSQL Serverのコンポーネントでサポートしているので、JSONがどこでもサポートされます。Hekaton、カラムストアテーブル、ローレベルセキュリティを含む標準セキュリティポリシー、標準のB-Treeインデックス、FTSインデックス、ストアドプロシージャーでのJSON利用などができます。Hekatonとカラムストアはは、LOB値に対応していないので、少ないデータ量のJSONドキュメントのみ対応します。
  • クライアントサイドのサポート
    C#では、多くの開発者がJSON.NETを使用してビルトインのJObjectやJArray型を使用しています。しかし、まだ標準ではありません。JSONデータ型を追加したとしても、クライアントアプリでは文字列として扱われ、必要があればカスタムパーサーが使われるでしょう。

JSONデータのエクスポート

最初の機能提供は、SQL Server 2016 CTP2で提供されており、「FOR JSON」を使用してJSON形式で結果を取得できるようになっています。

SELECT column, expression, column as alias FROM table1, table2, table3 FOR JSON [AUTO | PATH]

T-SQLクエリの最後に、「FOR JSON」を追加すると、JSONテキスト形式の結果をクライアントに返します。各行は全て、JSONオブジェクトの一部として整形され、セルの値はJSONオブジェクトの値として生成されます。

JSONテキストをリレーショナルテーブルに変換する:OPENJSON

OPENJSONは、JSONテキストを探すテーブル値機能(TVF)です。

SELECT Number, Customer, Date, Quantity
 FROM OPENJSON (@JSalestOrderDetails, '$.OrdersArray')
 WITH (
        Number varchar(200),
        Date datetime,
        Customer varchar(200),
        Quantity int
 ) AS OrdersArray

@JSalesOrderDetailsは、次のような例のOrdersArrayプロパティでJSONオブジェクトの配列を含むテキストです。

‘{"OrdersArray": [

   {"Number":1, "Date": "8/10/2012", "Customer": "Adventure works", "Quantity": 1200},

   {"Number":4, "Date": "5/11/2012", "Customer": "Adventure works", "Quantity": 100},

   {"Number":6, "Date": "1/3/2012", "Customer": "Adventure works", "Quantity": 250},

   {"Number":8, "Date": "12/7/2012", "Customer": "Adventure works", "Quantity": 2200}

]}’

OPENJSONは、プロパティで配列を見つけ、JSONオブジェクトごとに行を返します。結果セットには、4列含まれ、定義は、「WITH」で指定します。OPENJSONは、Number、Date、Customer、Quantityプロパティを見つけ、結果セットのカラムに値を変換します。デフォルトでは、プロパティが見つからない場合はNULLを返します。

JSONを処理するビルトイン関数

  • ISJSON

    NVARCHARテキストにJSONと互換性があるか確認します。
  • JSON_VALUE

    JsonTextをパースし、JSONパスで指定した値を取得します。
  • JSON_QUERY

    JsonTextをパースし、JSONフラグメントを取得します。
SELECT t.Id, t.OrderNumber, t.OrderDate,
 JSON_VALUE(t.JOrderDetails, '$.Order.ShipDate'),
 JSON_QUERY(t.JOrderDetails, '$.Order')
FROM SalesOrderRecord AS t
WHERE ISJSON(t.JOrderDetails) > 0
 AND JSON_VALUE(t.JOrderDetails, '$.Order.ProductType') = 'TV'

JSON インデックス

今のところJSON用のカスタムインデックスを追加する予定はありません。

JSONはプレーンなテキストで、既存のB-Treeやフルテキスト検索インデックスを使用できます。

たとえば次のように、JSON_VALUE関数を使用して、計算列を作成し、その列に標準のインデックスを作成します。

CREATE TABLE SalesOrderRecord (
 Id int PRIMARY KEY IDENTITY,
 OrderNumber NVARCHAR(25) NOT NULL,
 OrderDate DATETIME NOT NULL,
 JOrderDetails NVARCHAR(4000),
 Quantity AS CAST(JSON_VALUE(JOrderDetails, '$.Order.Qty') AS int),
 Price AS JSON_VALUE(JOrderDetails, '$.Order.Price'),
 )
 GO
 CREATE INDEX idxJson
 ON SalesOrderRecord(Quantity)
 INCLUDE (Price);