🔹 SQL（基本の文法・仕様）
1. データベース切り替え
USE dbname;
2. テーブル作成（CREATE TABLE）
CREATE TABLE schemaname.YourTableName (
ID INT PRIMARY KEY,
Name NVARCHAR(100),
CreatedDate DATETIME DEFAULT GETDATE()
);
3. スキーマ配下のテーブル一覧
SELECT
s.name AS SchemaName,
t.name AS TableName
FROM sys.tables t
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE s.name LIKE 'schemaname%'
ORDER BY t.name;
4. カラム一覧（データ型・NULL 確認）
SELECT
c.COLUMN_NAME,
c.DATA_TYPE,
c.CHARACTER_MAXIMUM_LENGTH,
c.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'schemaname'
AND c.TABLE_NAME = 'YourTableName'
ORDER BY c.ORDINAL_POSITION;
5. インデックス一覧
SELECT
i.name AS IndexName,
i.type_desc AS IndexType,
c.name AS ColumnName,
ic.key_ordinal AS KeyOrder,
i.is_primary_key,
i.is_unique
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
INNER JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('schemaname.YourTableName')
ORDER BY i.name, ic.key_ordinal;
6. ストアド一覧
▼ 全ストアド
SELECT
s.name AS SchemaName,
p.name AS ProcedureName,
m.definition AS ProcedureDefinition
FROM sys.procedures p
INNER JOIN sys.schemas s
ON p.schema_id = s.schema_id
LEFT JOIN sys.sql_modules m
ON p.object_id = m.object_id
ORDER BY s.name, p.name;
▼ 特定スキーマ
SELECT
p.name AS ProcedureName,
m.definition AS ProcedureDefinition
FROM sys.procedures p
LEFT JOIN sys.sql_modules m
ON p.object_id = m.object_id
WHERE p.schema_id = SCHEMA_ID('schemaname')
ORDER BY p.name;
7. テーブル削除（DROP TABLE）
DROP TABLE schemaname.YourTableName;
8. 集約関数（MAX / SUM…）は WHERE に書けない
❌ NG
UPDATE table1
SET column3 = @arg3
WHERE MAX(column1); -- エラー
🔍 理由
- WHERE は「行ごとに評価」
- 集約関数は「全体を見て1つの値を返す」
→ 仕組みがそもそも合わない
9. 集約を使ったUPDATEの正しい書き方
✔ 方法①：サブクエリで集約を求める
UPDATE t
SET column3 = @arg3
FROM table1 t
WHERE t.column = (SELECT MAX(column) FROM table1);
✔ 方法②：CTE（WITH句）で集約を先に計算
WITH MaxVal AS (
SELECT MAX(column) AS MaxCol
FROM table1
)
UPDATE t
SET column3 = @arg3
FROM table1 t
CROSS JOIN MaxVal m
WHERE t.column = m.MaxCol;
10. サブクエリ / CTE の使いどころ
● サブクエリ
→ 1箇所だけ集約値やフィルタ値を使いたいとき
● CTE
→ 複数回使う、JOIN したい、読みやすくしたいとき
11. スカラー関数とテーブル値関数
● スカラー関数（1値を返す）
SELECT dbo.GetName(@id);
● テーブル値関数（テーブルを返す）
パラメータなし
SELECT * FROM dbo.GetItems();
パラメータあり（CROSS APPLY 必須）
SELECT t.*, f.*
FROM table1 t
CROSS APPLY dbo.GetItemsById(t.ID) f;
→ TVF をパラメータ付きで使うときは APPLY を使うのが基本。
12. APPLY の使いどころ
- テーブル値関数
- サブクエリを行単位で評価したいとき
- JOIN ではできない柔軟な関連付けが必要なとき
SELECT t.*, x.*
FROM table1 t
CROSS APPLY (SELECT TOP 1 * FROM logs l WHERE l.key = t.key) x;
🔹 ストアドプロシージャ
1. パラメータの受け取り
CREATE PROCEDURE dbo.TestProc
@Id INT,
@Name NVARCHAR(50)
AS
BEGIN
SELECT @Id, @Name;
END
2. トランザクション（TRY / CATCH）
BEGIN TRY
BEGIN TRAN
-- SQL処理
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
3. RETURN と OUTPUT パラメータ
● RETURN（整数だけ返せる）
RETURN 0;
● OUTPUT（値を返したいとき）
CREATE PROCEDURE dbo.Sample
@Num INT OUTPUT
AS
BEGIN
SET @Num = 10;
END
4. ストアド内での関数の呼び方
● スカラー関数
→ そのまま SELECT に書く
SELECT dbo.GetName(@Id);
● テーブル値関数
→ APPLY か JOIN が必要
SELECT f.*
FROM Users u
CROSS APPLY dbo.GetHistory(u.Id) f;
5. UPDATE + 集約のハマりポイント
-
WHERE に MAX は書けない
-
UPDATE は「行単位の評価」
-
集約値を使うときは
- サブクエリ
- CTE
- APPLY
のどれかを使う
6. UPDATE の基本パターン（JOIN あり）
UPDATE t
SET t.Name = s.NewName
FROM table1 t
INNER JOIN source s
ON t.ID = s.ID;
7. ストアド内で注意するポイントまとめ
- 集約は先に計算する
- 関数はスカラーかテーブルか見分ける
- APPLY はパラメータ付き TVF で特に有効
- トランザクションを使うかどうか判断
- 実行順序や評価タイミングに注意
- UPDATE の書き方は FROM を使う