触って動く、検証済みの正解集。

-- ✅カーソル基本テンプレ
DECLARE @table_name SYSNAME;
DECLARE @sql NVARCHAR(MAX);

DECLARE meta_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR

-- ❌重いデフォルト（GLOBAL / KEYSET /更新可能）
DECLARE c CURSOR FOR SELECT id FROM big_table;

-- ✅軽量カーソル（LOCAL / FAST_FORWARD / READ_ONLY）
DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT id FROM big_table;

SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM dbo.tmp_orders WHERE customer_id = 1;
GO
SET SHOWPLAN_TEXT OFF;

--準備:通常テーブル+インデックス+ 1万件投入+統計更新
IF OBJECT_ID('dbo.tmp_orders','U')IS NOT NULL DROP TABLE dbo.tmp_orders;
CREATE TABLE dbo.tmp_orders (id INT IDENTITY PRIMARY KEY, customer_id INT, amount INT);
CREATE INDEX IX_customer ON dbo.tmp_orders(customer_id);

|--Nested Loops(Inner Join, OUTER REFERENCES:([id]))
     |--Index Seek(OBJECT:([IX_customer]), SEEK:([customer_id]=(1)))
     |--Clustered Index Seek(OBJECT:([PK__tmp_orders]), SEEK:([id]=[id])LOOKUP)

IF OBJECT_ID('dbo.tmp_products','U')IS NOT NULL DROP TABLE dbo.tmp_products;
CREATE TABLE dbo.tmp_products (id INT IDENTITY PRIMARY KEY, name NVARCHAR(50), price INT);
INSERT INTO dbo.tmp_products (name, price)VALUES
  (N'apple', 100),(N'banana', 200),(N'cherry', 300),(N'durian', 400);

SET STATISTICS PROFILE ON;
SELECT * FROM dbo.tmp_orders WHERE customer_id = 1;
SET STATISTICS PROFILE OFF;

-- ✗ Index効かない
WHERE CONVERT(VARCHAR, created_at, 23)= '2026-05-15'

-- ✓ Index効く
WHERE created_at >= '2026-05-15' AND created_at < '2026-05-16'

-- 全 INDEX の断片化を一発で返す (SAMPLED モード)
SELECT
    OBJECT_NAME(ps.object_id) AS table_name,
    si.name                   AS index_name,
    si.type_desc              AS index_type,

-- 現在のロック保持状況 (Sch-M / Sch-S を含む)
SELECT
    request_session_id      AS session_id,
    resource_type,
    resource_associated_entity_id,

-- 全 INDEX の fill factor 現状
SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name                   AS index_name,
    i.type_desc              AS index_type,

-- 更新多い INDEX を REBUILD + fill factor 85% で再構築
ALTER INDEX [IX_Orders_OrderDate] ON [dbo].[Orders]
REBUILD
WITH (
    FILLFACTOR = 85,

-- これは罠
SELECT * FROM Sessions WHERE SessionId = @sid OPTION (RECOMPILE);
-- ↑ 1秒に100回叩かれる SELECT に付けると、コンパイル時間が積み重なって CPU が爆上がり

SELECT OrderId, CustomerId, TotalAmount
  FROM Orders
 WHERE OrderId = @orderId;

SELECT OrderId, CustomerId, OrderDate
  FROM Orders
 WHERE Status = @status
 OPTION (RECOMPILE);

SELECT SessionId, UserId, LastAccess
  FROM Sessions
 WHERE SessionId = @sid
 OPTION (RECOMPILE);

-- 0.5/1.5/2.5 を四捨五入。リテラル 0.5 は桁が足りず overflow するので CAST で桁を確保
SELECT ROUND(CAST(0.5 AS decimal(2,1)), 0)   -- 1.0
     , ROUND(CAST(1.5 AS decimal(2,1)), 0)   -- 2.0
     , ROUND(CAST(2.5 AS decimal(2,1)), 0);  -- 3.0

-- float は 2.675 を正確に表現できないことがある
SELECT ROUND(CAST(2.675 AS float), 2);          -- 2.67（誤差で 2.675 が 2.6749… 扱い）
-- decimal なら正確
SELECT ROUND(CAST(2.675 AS decimal(10,3)), 2);  -- 2.680（正しく切り上げ）

-- 金額は decimal で。消費税8%を計算
SELECT CAST(1000 AS decimal(12,2)) * 0.08;                         -- 80.0000
-- 表示桁を金額の2桁に揃えるなら、結果も明示的に丸める
SELECT CAST(CAST(1000 AS decimal(12,2)) * 0.08 AS decimal(12,2));  -- 80.00

-- NG: 同点の時に順位がブレる
SELECT customer_id, ROW_NUMBER() OVER (ORDER BY total_sales DESC) AS rn
FROM monthly_sales;

-- OK: customer_id を tiebreaker に

-- 統計情報を見るためのセッション設定
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- ROW_NUMBER + 大量データで Sort Operator を確認

SELECT TOP 20
       qs.execution_count,
       qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
       qs.total_elapsed_time / qs.execution_count / 1000 AS avg_elapsed_ms,
       SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,

SELECT
    OBJECT_NAME(s.object_id) AS table_name,
    s.name                   AS stats_name,
    STATS_DATE(s.object_id, s.stats_id) AS last_updated,
    s.auto_created,

SELECT
    OBJECT_NAME(si.id) AS table_name,
    si.name            AS index_name,
    si.rowcnt,
    si.rowmodctr,

SET STATISTICS PROFILE ON;
SELECT * FROM Orders WHERE Status = 'エラー' AND OrderDate > '2026-05-01';
SET STATISTICS PROFILE OFF;

-- 今この瞬間メモリ grant を持ってる / 待ってるクエリ
SELECT
    mg.session_id,
    mg.request_time,
    mg.grant_time,

-- Resource Semaphore のキュー俯瞰
SELECT
    resource_semaphore_id,
    target_memory_kb     / 1024 AS target_mb,
    max_target_memory_kb / 1024 AS max_target_mb,

-- 過去 grant が大きかった TOP 10 クエリ (2012 SP3 / 2014 SP2 / 2016+)
SELECT TOP 10
    qs.execution_count,
    qs.max_grant_kb     / 1024 AS max_grant_mb,
    qs.min_grant_kb     / 1024 AS min_grant_mb,

-- tempdb のファイル構成と autogrowth 設定
SELECT
    name,
    physical_name,
    type_desc,

-- products テーブルの category_name を categories マスタの最新名で更新
UPDATE p
SET p.category_name = c.name
FROM products p
INNER JOIN categories c ON p.category_id = c.id