T-SQLとは？SQL Server向けTransact-SQL入門ガイド

T-SQLの関数

T-SQLには、データ変換に用いる組み込み関数とユーザー定義関数（UDF）の両方が含まれます。

組み込み関数

これらの関数はSQL Serverにあらかじめ用意されており、クエリで直接使用できます。

文字列関数

文字列データ型を変換する関数です。例えば次のクエリは、Customersテーブルの名をすべて大文字に変換して取得します。

-- Converts text to uppercase
SELECT UPPER(FirstName)  
FROM Customers;

日付関数

日付を扱う関数です。次のクエリは、現在のシステム日付と時刻を返します。

-- Returns current date and time
SELECT GETDATE();  

集約関数

テーブル内の値を集約する関数です。例えば次のクエリは、Customersテーブルの行（顧客）数の合計を数えます。

-- Counts rows
SELECT COUNT(*) AS TotalCustomers
FROM Customers;  

ユーザー定義関数（UDF）

独自の関数を作成して、複数のクエリでロジックを再利用することもできます。

スカラー関数

スカラー関数は単一の値を返します。フォーマットや計算など、単一の算出値が必要なときに有用です。次のクエリでは、名と姓を入力として受け取り、1つのフルネームとして返します。

-- Create a function that combines the first and last name
CREATE FUNCTION GetFullName 
(@FirstName VARCHAR(50), @LastName VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName; -- Concatenates the two values
END;

テーブル値関数

テーブル値関数はテーブルを返します。例えば次の関数は、指定した国に属するすべての顧客のテーブルを返します。

-- Create a table-valued function named GetCustomersByCountry
CREATE FUNCTION GetCustomersByCountry (@Country VARCHAR(50))
RETURNS TABLE -- Specifies that the function returns a table
AS
RETURN
(
    -- Select all columns from the Customers table
    SELECT *
    FROM Customers
    WHERE Country = @Country -- Filter rows by the given country
);

ストアドプロシージャの作成・更新・実行に加え、集約関数、結合、挿入、削除なども身につく、完全版のSQL Server Developerキャリアトラックをぜひお試しください。

T-SQLのエラー処理

T-SQLはTRY...CATCHブロックを用いて、スクリプト全体を停止させずにエラー処理を行います。これにより、想定外のクラッシュを防ぎ、デバッグやログ用に意味のあるメッセージを返すことができます。

例えば次のクエリは、エラーを引き起こす計算を試み、エラーを捕捉し、クラッシュの代わりに読みやすいエラーメッセージを出力します。

BEGIN TRY
    -- Attempt to run this code
    SELECT 1 / 0;  -- This causes a divide-by-zero error
END TRY
BEGIN CATCH
    -- Runs if an error occurs in the TRY block
    PRINT 'An error occurred: ' + ERROR_MESSAGE(); -- Displays the error message
END CATCH;

T-SQLのトランザクション

トランザクションは、一連の処理がすべて成功するか、あるいはすべて失敗するかを保証します。これはACID特性に従っており、更新の途中で停電が起きてもデータベースの整合性が保たれることを意味します。

以下の例では、トランザクション内で一方の口座からもう一方へ資金を移動し、両方の変更をまとめて保存します。

BEGIN TRANSACTION; -- Start the transaction

-- Deduct money from Account 1
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

-- Add money to Account 2
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

COMMIT; -- Save all changes permanently

問題が発生した場合は、ROLLBACKでトランザクションを取り消し、データを以前の状態に戻せます。

-- Undo all changes made in the current transaction
ROLLBACK; 

T-SQLにおけるトランザクションは、特に金融アプリケーションのようなクリティカルなシステムで重要です。部分的な更新は不整合につながる可能性があるためです。

SQL Serverのトランザクションとエラー処理を扱うTransactions and Error Handling in SQL Serverキャリアトラックもお試しください。

データ分析で役立つT-SQLの一般的な機能

T-SQLには、データプロフェッショナルにとって特に有用なツールがいくつか含まれます。ここでは、データ分析での適用例を見ていきます。

ウィンドウ関数

ウィンドウ関数は、行をひとつにまとめずに横断的な計算を行います。ランキング、累計、先進的な分析で主に用いられます。

以下の例では、CustomerIDに基づいて各顧客に一意の行番号を割り当てます。

-- Assigns row numbers to customers
SELECT 
    FirstName,
    ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum -- Assigns row numbers
FROM Customers;

このクエリでは、

• ROW_NUMBER(): 一意の行番号を割り当て

• RANK(): 同順位にギャップのあるランクを割り当て

• OVER(): ウィンドウ（並べ替え／パーティション）を定義

ダウンロード可能なSQL Window Functions Cheat Sheetもぜひご覧ください。SQLのさまざまなウィンドウ関数を手早く参照できます。また、T-SQLを用いたデータ分析（集約に役立つウィンドウ関数を含む）を学ぶには、Intermediate SQL Serverコースもおすすめです。

共通テーブル式（CTE）

CTE（Common Table Expressions）は、クエリ内で再利用できる一時的な結果セットを作成します。次のクエリは、米国の顧客の一時リストを作成し、そのデータを取得します。

-- Define a CTE
WITH CustomerCTE AS (
    SELECT FirstName, Country
    FROM Customers
    WHERE Country = 'USA' -- Filter only USA customers
)

-- Query the CTE
SELECT *
FROM CustomerCTE;

一方、再帰CTEは定義内で自分自身を参照する特殊なCTEで、繰り返し処理を可能にします。これにより、組織図のような階層データを扱うのに最適です。

SQL ServerでCTEや再帰CTEを用いた高度なクエリを実例とともに学ぶには、Hierarchical and Recursive Queries in SQL Serverコースの受講をおすすめします。

一時テーブル

一時テーブルは、セッション中の短期間だけデータを保持します。データのステージングや、多段の変換の単純化、複雑なクエリでのパフォーマンス向上に役立ちます。

t-SQLでの一時テーブルの動作を示すため、以下の3つのクエリブロックを考えます。最初に一時テーブルを作成し、次に顧客データで埋め、同一セッション内でクエリします。

-- Create a temporary table
CREATE TABLE #TempCustomers (
    FirstName VARCHAR(50),
    Country VARCHAR(50)
);

-- Insert data into the temporary table
INSERT INTO #TempCustomers
SELECT FirstName, Country
FROM Customers;

-- Retrieve data from the temporary table
SELECT * FROM #TempCustomers;

一時テーブルは#で始まり、セッション終了時に自動で削除される点に注意してください。

T-SQLと他のSQL方言の比較

SQLの各方言は共通の基盤を共有しますが、各データベース製品が独自の構文や機能を追加しています。T-SQLはMicrosoft SQL Server向けに特化しており、その動作や最適な用途に影響しています。

T-SQL vs. PostgreSQL（PL/pgSQL）

PostgreSQL は手続き型ロジックにPL/pgSQLを用います。T-SQLと似ていますが、構文や強みが異なります。以下の表に相違点をまとめます。

リレーショナルデータベースの設計や、パフォーマンス向上のためのクエリ設計を学ぶには、SQL for Database Administratorsスキルトラックをご覧ください。

T-SQL vs. MySQL

MySQL も手続き型SQLをサポートしますが、よりシンプルで制限のあるアプローチです。以下の表は両者の構文と機能の違いを比較したものです。

T-SQLの実務での活用例

T-SQLは単なるデータクエリにとどまらず広く使われています。以下は、データプロフェッショナルとして有用だと感じた例です。

レポート用クエリ

T-SQLは、データベースから直接レポートを生成する用途で一般的に使われます。例えば、地域別に売上を集計したり、ウィンドウ関数を使って上位商品のランキングを作成したりできます。

ETLプロセス（抽出・変換・格納）

T-SQLはデータエンジニアによってデータパイプラインで使用され、データの準備や移送に用いられます。例えば、ステージングテーブルからの抽出、フォーマット変換、不整合のクレンジング、最終的なレポート用テーブルへのロードなどです。

データクレンジングスクリプト

住所表記が不統一な数百万行のデータベースがある場合、WHILEループやCTEを使ったT-SQLスクリプトで、重複の特定、欠損値の補完、表記の標準化を行えます。

アプリケーションのバックエンドロジック

多くのアプリケーションは、ビジネスロジックをデータベース内で処理するためにT-SQLに依存しています。CRUD操作にストアドプロシージャを用いたり、挿入前に検証を行ったり、重要処理ではトランザクション処理を用いるといった使い方が可能です。

よくあるミスとベストプラクティス

T-SQLは高度なデータベースクエリに有用ですが、いくつかの典型的な問題に遭遇しがちです。以下は、T-SQLを使う際によく見かけるミスです。

• 集合指向ロジックの代わりにループを多用する: WHILEのようなループで行ごとの処理を行うと、パフォーマンスが低下しがちです。SQLは一度に集合としてデータを扱うよう設計されています。

• インデックスへの理解不足: 適切にインデックスを使わないと、特に大規模テーブルでクエリが遅くなります。

• トランザクションの不適切な使用: 関連する複数の処理をトランザクションで囲まずに実行すると、途中で失敗した場合にデータが不整合のまま残る恐れがあります。

• エラー処理の軽視: TRY...CATCHブロックを省くと、デバッグが困難になり、本番環境でサイレントに、あるいは予期せず失敗する原因になります。

以下は、より効率的で信頼性の高いクエリを書くためのおすすめのベストプラクティスです。

• 集合指向のクエリを優先する: 可能な限りJOIN、GROUP BY、ウィンドウ関数を使い、ループを避けましょう。これはSQL Serverのデータ処理設計に合致します。

• 読みやすく保守しやすいコードを書く: 明確な命名規則を用い、クエリのフォーマットを一貫させ、必要に応じてコメントを追加しましょう。可読性が上がり、デバッグや共同作業が容易になります。

• プロシージャをテストする: さまざまな入力でストアドプロシージャやクエリを常にテストし、正しく動作し、端境ケースも扱えることを確認しましょう。

まとめ

T-SQLは標準SQLにプログラミング機能を追加し、自動化、データ処理、分析において柔軟で強力なツールとなります。変数、ストアドプロシージャ、エラー処理などの機能により、より動的で効率的なデータベースソリューションを構築できます。

次のステップとして、熟練のデータアナリストを目指せるAssociate Data Analyst in SQLキャリアトラックに挑戦してみてください。さらに、複雑なレポート作成に習熟するにはReporting in SQLコースが役立ちます。最後に、ビジネス課題をSQLで解決するスキルを証明して他のプロフェッショナルと差別化するために、SQL Associate Certificationの取得もおすすめします。