Atsushi2022の日記

データエンジニアリングに関連する記事を投稿してます

読書メモ ～ SQL Server Transact-SQL プログラミング 実践開発ガイド

SQL

概要

「SQL Server Transact-SQL プログラミング 実践開発ガイド」を読んで重要そうなところをメモしておく。

あと本書に記載ないけれど気になって調べたところもメモしておく。

インスタンス

ユーザ

2種類存在する。

インスタンス接続の認証方法

各ユーザの作成

※本書の記載事項ではありません。

下記のサイトを参考にすること。

ユーザ作成前にSQL Server 認証を許可する必要があるので要注意！

SQLServer認証のユーザーを作成してみた｜ITエンジニアとして経験・学習したこと

データベースのユーザーとデータベースへのログインユーザーの作成 : SQL Server Tips | iPentec

スキーマ

※本書の記載事項ではありません。

  • あるデータベース内でテーブルを分けて管理するための箱みたいなもの
  • 既定のスキーマはdbo
  • データベースユーザのプロパティ設定で「既定のスキーマ(Default schema)」を変更できる
  • テーブル作成時に[スキーマ名].[テーブル名]とすることで、既定のスキーマ以外を指定してテーブル作成できる

既定のスキーマを設定しているのにテーブルにアクセスできない[SQL Server] | zenmai software

スキーマの作成方法はこちら。SSMS上で「セキュリティ」→「スキーマ」→「新しいスキーマ」でスキーマを選択できる。

SQL Server 2017でスキーマを作成する方法 | .NETコラム

SQL Serverのツール

SSMS

スキップ

SQL Server Profiler

  • SQLServerに対して実行されたクエリをトレースし、トレース結果を閲覧することができるツール
  • どのクエリに時間がかかっているのかを調査できる

データベースオブジェクト

  • テーブル
  • ビュー
  • ストアドプロシージャ
  • ストアドファンクション
  • トリガー
  • システムオブジェクト

システムテーブル

代表的なシステムテーブルをピックアップ

  • どのようなテーブルやビュー、ストアドプロシージャがそのデータベースに存在するのか
    • SELECT * FROM sys.objects;
  • テーブルの一覧
    • SELECT * FROM sys.tables;
  • データベースの一覧
    • SELECT * FROM sys.databases;
  • データベースオブジェクトの従属関係
    • SELECT * FROM sys.sysdepends;

システムストアドプロシージャ

  • サーバーレベルのロール一覧取得
    • EXECUTE sp_helpsrvrole;
  • ロールに所属するユーザの一覧取得
    • EXECUTE sp_helpsrvrolemember;

データ型

文字列型

  • 文字列
    • CHAR, VARCHAR, TEXT
  • Unicode文字列
    • NCHAR, NVARCHAR, NTEXT
  • バイナリ文字列
    • BINARY, VARBINARY, IMAGE

文字リテラルはシングルクォーテーションで囲む。

数値型

  • BIGINT, INT, SMALLINT, TINYINT
  • BIT
  • DECIMAL, NUMERIC
  • MONEY, SMALLMONEY
  • FLOAT, REAL
    • これらのデータ型は、科学技術計算の分野で使われることが多いようです。会計システムなどの金額を扱う処理では、1円単位の誤差が発生するため使わないようにしましょう。

日付型

  • DATE, TIME, DATETIME, DATETIME2, SMALLDATETIME, DATETIMEOFFSET

キーと製薬

スキップ

サーバーの運用管理

  • SQL ServerをWindowsにインストールした直後だと、「sa」という管理ユーザーが最初から作成されている
  • .mdfがデータベースファイル、.ldfがトランザクションログファイル
    • SSMSで新しいデータベースを作成する際にファイルの保存先パスを変更できる

ロール

ロールの追加 - SQL Server | Microsoft Learn

  • サーバーレベルのロール
    • 固定サーバー ロール
    • ユーザー定義サーバー ロール
  • データベースレベルのロール
    • 固定データベース ロール
    • ユーザー定義データベース ロール
  • アプリケーションロール

固定サーバーロール

サーバー レベルのロール - SQL Server | Microsoft Learn

  • sysadmin
    • sysadmin 固定サーバー ロールのメンバーは、サーバーに対するすべての操作を実行できます。
  • serveradmin
    • serveradmin 固定サーバー ロールのメンバーは、サーバー全体の構成オプションを変更したり、サーバーをシャットダウンしたりできます。
  • securityadmin
    • securityadmin 固定サーバー ロールのメンバーは、ログインとログインのプロパティを管理します。 サーバー レベルのアクセス許可を GRANT、DENY、REVOKE することができます。 また、データベースにアクセスできる場合は、データベース レベルのアクセス許可も GRANT、DENY、REVOKE できます。 また、 SQL Server ログインのパスワードをリセットできます。
  • processadmin
    • processadmin 固定サーバー ロールのメンバーは、SQL Server のインスタンス内で実行中のプロセスを終了できます。
  • setupadmin
    • setupadmin 固定サーバー ロールのメンバーは、Transact-SQL ステートメントを使用してリンク サーバーを追加および削除できます。 (Management Studio を使用するときは sysadmin メンバーシップが必要になります)。
  • bulkadmin
    • bulkadmin 固定サーバー ロールのメンバーは、BULK INSERT ステートメントを実行できます。
  • diskadmin
    • diskadmin 固定サーバー ロールは、ディスク ファイルを管理するために使用します。
  • dbcreator
    • dbcreator 固定サーバー ロールのメンバーは、任意のデータベースを作成、変更、削除、および復元できます。
  • public
    • すべての SQL Server ログインは、public サーバー ロールに属しています。 サーバー プリンシパルがセキュリティ保護可能なオブジェクトに対する特定のアクセス許可を付与または拒否されていない場合、ユーザーはそのオブジェクトの public に付与されたアクセス許可を継承します。 すべてのユーザーがオブジェクトを使用できるようにする場合は、対象のオブジェクトに public 権限のみを割り当てます。 パブリックのメンバーシップを変更することはできません。

固定サーバーロールへのメンバー追加

ロールの追加 - SQL Server | Microsoft Learn

ユーザー定義データベースロールを作成して権限を付与

※本書の記載事項ではありません。

【SQL Server】ロール(データベースロール)を作成して権限を付与する | 現場で使える！ SQL Server実践ガイド

1．ロールの作成

CREATE ROLE (Transact-SQL) - SQL Server | Microsoft Learn

CREATE ROLE role_name

2．ロールへの権限割り当て

GRANT (Transact-SQL) - SQL Server | Microsoft Learn

GRANT SELECT,INSERT,UPDATE,DELETE,ALTER TO role_name

3．ロールへのメンバ追加

ALTER ROLE (Transact-SQL) - SQL Server | Microsoft Learn

ALTER ROLE role_name ADD MEMBER database_principal

4．ロールの権限確認

【SQL Server】ユーザーやロールに付与されている権限を確認する | 現場で使える！ SQL Server実践ガイド

SELECT * FROM sys.database_permissions WHERE grantee_principal_id = USER_ID('role0419');

5．ロールに含まれるメンバ一覧を取得

sp_helprolemember (Transact-SQL) - SQL Server | Microsoft Learn

EXEC sp_helprolemember;

固定データベースロール

データベース レベルのロール - SQL Server | Microsoft Learn

  • db_owner
    • db_owner 固定データベース ロールのメンバーは、データベースでのすべての構成とメンテナンス作業を実行でき、SQL Server でデータベースを drop することもできます。 (SQL Database と Azure Synapse では、一部のメンテナンス作業にサーバー レベルの権限が必要であり、db_owners では実行できません。)
  • db_securityadmin
    • db_securityadmin 固定データベース ロールのメンバーは、カスタム ロールのロール メンバーシップのみの変更、および権限の管理を実行できます。 このロールのメンバーは、特権を昇格させる可能性があり、そのアクションを監視する必要があります。
  • db_accessadmin
    • db_accessadmin 固定データベース ロールのメンバーは、Windows ログイン、Windows グループ、および SQL Server ログインのデータベースに対するアクセスを追加または削除できます。
  • db_backupoperator
    • db_backupoperator 固定データベース ロールのメンバーは、データベースをバックアップできます。
  • db_ddladmin
    • db_ddladmin 固定データベース ロールのメンバーは、すべての DDL (データ定義言語) コマンドをデータベースで実行できます。 このロールのメンバーは、高い特権で実行される可能性があり、アクションを監視する必要があるコードを操作することで、特権を昇格させることができます。
  • db_datawriter
    • db_datawriter 固定データベース ロールのメンバーは、すべてのユーザー テーブルのデータを追加、削除、または変更できます。 ほとんどのユース ケースでは、このロールを db_datareader メンバーシップと組み合わせて、変更するデータを読み取れるようにします。
  • db_datareader
    • db_datareader 固定データベース ロールのメンバーは、すべてのユーザー テーブルとビューからすべてのデータを読み取ることができます。 ユーザーオブジェクトは、sys と INFORMATION_SCHEMA を除くどのスキーマにも存在できます。
  • db_denydatawriter
    • db_denydatawriter 固定データベース ロールのメンバーは、データベース内のユーザー テーブルのデータを追加、変更、または削除することはできません。
  • db_denydatareader
    • db_denydatareader 固定データベース ロールのメンバーは、データベース内のユーザー テーブルとビューのデータを読み取ることはできません。

アプリケーションロール

アプリケーション ロール - SQL Server | Microsoft Learn

アプリケーション ロールは、ユーザーのような独自の権限でアプリケーションを実行できるようにするデータベース プリンシパルです。 アプリケーション ロールは sp_setapproleを使用して有効化され、これにはパスワードが必要です。

データベースのバックアップ

復旧モデル

復旧モデル (SQL Server) - SQL Server | Microsoft Learn

  • シンプル復旧モデル
    • バックアップファイルのみでの復元。最新のバックアップ以降の変更は保護されません
  • 完全復旧モデル
  • 一括ログ復旧モデル

バックアップの種類

完全復旧モデル、一括ログ復旧モデルでは、完全バックアップと差分バックアップ、さらにトランザクションログバックアップを使用して復元を行う。

エラーログの表示

SQL Server エラー ログの表示 (SSMS) - SQL Server | Microsoft Learn

SQLクエリの履歴

※本書の記載事項ではありません。

SQL Server で実行された SQL を SQL で取得する方法 - Project Group

SELECT st.text
      ,last_execution_time
FROM   sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE  last_execution_time >= '2023/01/01 00:00:00'
ORDER BY last_execution_time

データのインポート・エクスポート

SQL Server インポートおよびエクスポート ウィザードを起動する - Integration Services (SSIS) | Microsoft Learn

sqlcmd

スキップ

別のコンピュータからの接続

レッスン 2: 別のコンピューターからの接続 - SQL Server | Microsoft Learn

  1. 「SQL Server 構成マネージャー」でTCP/IPを有効化
  2. 特定のポートでリッスンさせる
  3. ファイアウォールでポートを開く

文字コード

SQLServer日本語コードの確認方法 - Qiita

SQLServer UTF8へ文字コード変換

テーブルのコピー

SELECT.INTO ステートメント (Microsoft Access SQL) | Microsoft Learn

SELECT * INTO new_table FROM source_table

※ キーや制約が一切引き継がれないことに注意！！！

テンポラリテーブル

  • ローカルセッションテンポラリテーブル
    • #table_name
  • グローバルセッションテンポラリテーブル
    • ##table_name

セッションID

  • セッションIDを確認できる　
    • PRINT @@spid;
  • クエリ画面ごとにセッションIDが異なる

トランザクション

※本書の記載事項ではありません。

BEGIN TRANSACTION (Transact-SQL) - SQL Server | Microsoft Learn

BEGIN TRANSACTION transaction_name

COMMIT TRANSACTION (Transact-SQL) - SQL Server | Microsoft Learn

COMMIT TRANSACTION transaction_name

ROLLBACK TRANSACTION (Transact-SQL) - SQL Server | Microsoft Learn

ROLLBACK TRANSACTION transaction_name

Transact-SQL

    BEGIN TRY
        SELECT 1/0
    END TRY
    BEGIN CATCH
    SELECT 
        ERROR_LINE() AS ERROR_LINE,
        ERROR_MESSAGE() AS ERROR_MESSAGE,
        ERROR_NUMBER() AS ERROR_NUMBER,
        ERROR_PROCEDURE() AS ERROR_PROCEDURE,
        ERROR_SEVERITY() AS ERROR_SEVERITY,
        ERROR_STATE() AS ERROR_STATE
    END CATCH;

  • エラー情報の取得

    Transact-SQL のエラー情報の取得 | Microsoft Learn

    • ERROR_LINE()：エラーが発生したクエリの行番号
    • ERROR_MESSAGE()：アプリケーションから返されるエラー メッセージのテキスト
    • ERROR_NUMBER()：エラー番号
    • ERROR_PROCEDURE()：エラーが発生したストアド プロシージャまたはトリガの名前。ストアド プロシージャまたはトリガ内部でエラーが発生しなかった場合は NULL
    • ERROR_SEVERITY()：重大度
    • ERROR_STATE()：状態

  • IF

    IF...ELSE (Transact-SQL) - SQL Server | Microsoft Learn

  IF DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday')
    BEGIN
      SELECT 'Weekend'
    END
  ELSE
    BEGIN
      SELECT 'Weekday'
    END

条件式に用いる演算子は以下を参照のこと。

比較演算子 (Transact-SQL) - SQL Server | Microsoft Learn

論理演算子 (Transact-SQL) - SQL Server | Microsoft Learn

  WHILE Boolean_expression 
  BEGIN
    statement_block
  END

ストアドプロシージャ

  • 命令の順次実行、分岐、繰り返しなどの一連のデータ処理命令をまとめたもの
  • ストアドプロシージャはデータベースに保存される
  • 繰り返し、SQL文を発行せずに済む

ストアドプロシージャの作成

ストアド プロシージャの作成 - SQL Server | Microsoft Learn

  • ストアドプロシージャに渡す引数を定義できる
  • 引数のデフォルト値を設定できる
  • 引数のデフォルト値を設定した場合は、ストアドプロシージャの実行時に引数の指定を省略可能
  • 省略可能なパラメータは最後尾から配置しなければならない
CREATE PROCEDURE sample_stored_proc
    @param1 INT,
    @param2 = 0 INT
AS
BEGIN
    SELECT * FROM test_table
    WHERE
        id = @param1
        OR
        id = @param2
    ORDER BY id
END

ストアドプロシージャの実行

  • 引数に値を渡してストアドプロシージャを実行する
EXEC sample_stored_proc 1, 2;

ストアドプロシージャの戻り値

  • 次のようにプロシージャを実行することで、プロシージャの戻り値を変数に格納できる
DECLARE @name varchar(20);
EXEC @name = sample_stored_proc 1, 2;
PRINT @name

OUTPUT キーワード

ストアド プロシージャからデータを返す - SQL Server | Microsoft Learn

  • プロシージャの定義でパラメーターに OUTPUT キーワードを指定すると、プロシージャの終了時に、そのパラメーターの現在値を呼び出し元のプログラムに返すことができます。
  • 呼び出し元のプログラムで使用できる変数にパラメーターの値を保存するには、呼び出し元のプログラムがプロシージャを実行する際に OUTPUT キーワードを使用する必要があります。
  • プロシージャを呼び出すときにパラメーターに OUTPUT を指定した場合は、そのパラメーターがプロシージャの定義で OUTPUT を使用して定義されていないと、エラー メッセージが表示されます。
  • ただし、プロシージャに出力パラメーターを定義しておき、OUTPUT を指定せずにこのプロシージャを実行することも可能です。 エラーは返されませんが、呼び出し側のプログラムで出力値を使用することはできません。

ストアドファンクション

CREATE FUNCTION (Transact-SQL) - SQL Server | Microsoft Learn

  • ストアドファンクションは必ず戻り値を返す
  • ストアドプロシージャは必ずしも戻り値を返す必要はない

スカラー値関数

  • 引数@金額に1.08を乗算した値を返す

  • 戻り値のデータ型はMONEY型

  • ストアドファンクションの定義

      CREATE FUNCTION fn_include_tax
  ( 
      @price MONEY 
  ) 
  RETURNS MONEY 
  AS 
  BEGIN 
  RETURN @price * 1.08;
  END

  • ストアドファンクションの実行

      DECLARE @price_with_tax MONEY;
  EXEC @price_with_tax = fn_include_tax 200;
  PRINT @price_with_tax;

テーブル値関数

方法: テーブル値のユーザー定義関数を使用する - ADO.NET | Microsoft Learn

テーブルデータを返す

  • ストアドファンクションの定義

      CREATE FUNCTION get_names
  (
      @id INT
  )  
  RETURNS TABLE  
  AS  
  RETURN  
      SELECT id  
      FROM dbo.test_table  
      WHERE id > @id

  • ストアドファンクションの実行

    • スカラー値関数と実行の仕方が違うので要注意！

      SELECT * FROM get_names(1);

カーソル

  • SELECT文の実行結果からレコードを1件ずつ取り出す
  • カーソルは必ず解放する！！！
-- カーソルの定義
-- カーソルをセットする対象のSELECT文を指定する
DECLARE cur CURSOR FOR 
SELECT id, name FROM test_table;

-- カーソルを開く
OPEN cur;

DECLARE @id INT;
DECLARE @name varchar(20);

-- カーソルから1件ずつデータを抽出する
FETCH NEXT FROM cur INTO
  @id,
  @name

PRINT @id
PRINT @name


-- @@FETCH_STATUSはレコードが取得できなくなったところで0以外の値を返す
WHILE(@@FETCH_STATUS = 0)
BEGIN
  FETCH NEXT FROM cur INTO
    @id,
    @name

  PRINT @id
  PRINT @name
END

-- カーソルを閉じ、開放する
CLOSE cur;
DEALLOCATE cur;

動的SQL

  • SQL文をTransact-SQLで作る
  • 端的にいうと、変数に文字リテラル（SQL文）を代入するだけ
  • IF文とかを使うと動的にSQL文の中身を変えて作成することができる（↓の例は動的になってません汗）
-- SQL文を格納する変数を宣言
DECLARE @sql VARCHAR(8000);

-- 初期化する
SET @sql = '';

-- SQL文を代入していく
SET @sql = @sql + 'SELECT * FROM test_table';
SET @sql = @sql + ' WHERE id > 1'; -- WHERE句の前に半角スペースが1つあります

-- SQLを実行する
-- クエリが格納されている変数の場合は括弧()が必要！！！
EXECUTE(@sql)

CTE

  • Common Table Expressionsの略
  • 要は名前付きサブクエリのこと

テーブル変数

table (Transact-SQL) - SQL Server | Microsoft Learn

DECLARE @table_variable_name TABLE (
    [列][データ型],
    [列][データ型],
    [列][データ型]
);