SQL Server

Main

ストアドのサンプル

CREATE procedure sp_sample @MngID int as

--変数リストの宣言
DECLARE @FirstName nvarchar(50)
DECLARE @LastName nvarchar(50)
DECLARE @Title nvarchar(50)

--カーソルの宣言
--この時点では箱ができるだけ
DECLARE EmpCur cursor FOR
    SELECT cnt.FirstName
          ,cnt.LastName
          ,emp.Title
    FROM HumanResources.Employee as emp 
    INNER JOIN Person.Contact AS cnt
        ON emp.ContactID = cnt.ContactID  
    WHERE emp.ManagerID = @MngID

--カーソルを開く
--箱を使えるようにする
OPEN EmpCur

--FETCH（行の取り出し）
FETCH NEXT FROM EmpCur INTO @FirstName,@LastName,@Title

--LOOP
--fecth（行の取出し）が正常に行えている間
WHILE (@@fetch_status = 0)
BEGIN
    --変数リストの値を出力
    PRINT @FirstName + ' ' + @LastName + ' ' + @Title
    --FETCH（行の取り出し）
    FETCH NEXT FROM EmpCur INTO @FirstName,@LastName,@Title
end

--カーソルを閉じる
CLOSE EmpCur
DEALLOCATE EmpCur

RETURN VALUE

ストアドの編集

―Microsoft SQL Server Management Studio
　―データベース
　　―対象DB
　　　―プログラミング
　　　　―ストアドプロシージャ

複数クエリを一括実行

BEGIN TRAN
update テーブル set カラム１ = 値１ WHERE 検索条件１
update テーブル set カラム１ = 値２ WHERE 検索条件２
update テーブル set カラム１ = 値３ WHERE 検索条件３
COMMIT

特定テーブルのカラム情報取得

DECLARE @ObjectName Varchar(50);
SET @ObjectName = 'テーブル名';

SELECT 
	sys.columns.column_id,
    sys.columns.name as Column_Name,
    sys.types.name as Type,
    sys.columns.max_length,
    (CASE sys.columns.is_nullable
         WHEN 0 THEN 'NO'
         WHEN 1 THEN 'YES'
         ELSE 'nothing'
     END) as IsNULL
FROM
    sys.objects,
    sys.types,
    sys.columns
WHERE
    sys.objects.name like @ObjectName
AND sys.objects.object_id = sys.columns.object_id
AND sys.types.system_type_id = sys.columns.system_type_id
-- AND 絞り込み条件
ORDER BY
    sys.columns.column_idORDER BY sys.columns.column_id

または、

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'テーブル名'

ランダムSELECT

NEWID() は実行のたびにランダムに抽出される。

SELECT TOP n * FROM Table ORDER BY NEWID()

RAND() は一度の実行ではランダムに抽出されるが、2度目以降は同じものが返ってくる。

SELECT TOP n * FROM Table ORDER BY RAND()

グループ毎の先頭(最後)ｎ件だけ取り出す

SELECT 
    Feild_1 
   ,Feild_2 
   ,Feild_3 
   ,Feild_X 
FROM 
    ( 
        SELECT 
            *
           ,ROW_NUMBER() OVER (PARTITION BY Feild_1 ORDER BY NEWID()) as Feild_X 
            -- Feild_1 でグループ化して、NEWID()でランダムに一意の値を設定
        FROM 
            Table
        WHERE 
            抽出条件 
    ) A
WHERE 
    A.Feild_X <= 3 -- 各グループのTOP3件を抽出
ORDER BY 
    A.Feild_X

（SQL Server）インデックス関連

状態取得

avg_fragmentation_in_percentの値が断片化率(％)。
断片化率が100に近いほど、断片化が発生していることになる。

SELECT	 DB_NAME(database_id)    as db_name
		,OBJECT_NAME(object_id)  as table_name
		,(select	min(name) from	sys.indexes 
		  where sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id 
		  and  sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id)as index_name
		,index_type_desc   
		,avg_fragmentation_in_percent  --断片化の割合
		,fragment_count                --インデックス内の断片化の数
		,avg_fragment_size_in_pages    --インデックス内の1つ断片化内の平均ページ数
FROM sys.dm_db_index_physical_stats
(DB_ID(),NULL, NULL, NULL , 'LIMITED')
order by avg_fragmentation_in_percent desc;

インデックスの再構成(REORGANIZE)

既に割り当てられている領域を利用して断片化の解消を行う

ALTER INDEX インデックス名 ON テーブル名 REORGANIZE

インデックスの再構築(REBUILD)

新規のページを使用して断片化の解消が行う（再構成より効果大）

ALTER INDEX インデックス名 ON テーブル名 REBUILD

ALTER INDEX ALL ON テーブル名 REBUILD

目次