ClownWiki

この文書は読取専用です。文書のソースを閲覧することは可能ですが、変更はできません。もし変更したい場合は管理者に連絡してください。
====== SQL Server  ======

====== Main  ======

===== ストアドのサンプル  =====

<code>
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
</code>

===== ストアドの編集  =====

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

===== 複数クエリを一括実行  =====

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

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

<code>
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
</code>

または、

<code>
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'テーブル名'
</code>

===== ランダムSELECT  =====

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

<code>
SELECT TOP n * FROM Table ORDER BY NEWID()
</code>

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

<code>
SELECT TOP n * FROM Table ORDER BY RAND()
</code>

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

<code>
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 
</code>

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

==== 状態取得  ====

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

<code>
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;
</code>

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

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

<code>
ALTER INDEX インデックス名 ON テーブル名 REORGANIZE
</code>

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

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

<code>
ALTER INDEX インデックス名 ON テーブル名 REBUILD
</code>

<code>
ALTER INDEX ALL ON テーブル名 REBUILD
</code>