SQL Server(RDB)に使うtransact SQLの私的チートシート。
逐次増やしていきます。
##テーブル操作関係
###テーブルを作る
CREATE TABLE test_table (
[id] int not null primary key,
[name] nvarchar(50) not null
);
###テーブルを作る（先頭列idを自動インクリメント行にする）
GUIで編集していると気づきにくいですが、自動インクリメント（identity insert）の設定ができるのは、テーブル作成時と行追加時のみです。
CREATE TABLE mw_log (
id int identity not null primary key ,
name nvarchar(50) not null
);
###テーブルに行を追加する
INSERT INTO mw_log (id,name)
values (2,'ボルシチ')
###テーブルに列を追加する
ALTER TABLE mw_log add [updatedate] datetime not null default getDate();
###テーブルを削除する
DROP TABLE mw_log
###テーブルに複数項目からなるの主キーを設定する
テーブル作成時には、複数の項目からなる主キー制約を設定できないので、テーブルを作った後に設定します。
主キーは自動的にクラスタ化インデックスになるので、例示のCLUSTEREDはなくても動きます。
CREATE table mw_log_detail
(id int not null,
UpdateDate datetime not null);
ALTER table mw_log_detail
ADD CONSTRAINT pk_mw_log_dtail
PRIMARY KEY CLUSTERED (id,UpdateDate);
##データベースの情報を取得する
###データベースのテーブル一覧を取得する
SELECT * FROM INFORMATION_SCHEMA.tables
###データベースのカラムの一覧を取得する
SELECT * FROM INFORMATION_SCHEMA.columns
データベースの容量を減らしたい時に
プレゼンやデモ、説明会用にデータベースの容量を少なくしたい時に。
インデックスを削除してDBの容量を節約する。
DROP INDEX index_hogehoge on table_name_hoge
プレゼンやデモ、説明会用にデータベースの容量を少なくしたい時に。
SELECT
o.name AS [tabele_name]
i.name as [index_name]
FROM sysindexes i
INNER JOIN sys.objects o
ON o.object_id = i.id
WHERE i.name like 'INDEX_%'
データベースのテーブルの中身を全削除するSQL文を作る。
注意：生成したSQLの実行時は外部キー制約を一時的に外すなどの工夫が必要です。
SELECT 'TRUNCATE TABLE ' + table_name FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE'
##データベースが遅くなった時の対処
###統計情報の更新(DB全体)
exec sp_updatestats
###統計情報の更新(指名)
UPDATE STATISTICS dbo.hogetable hogeindex
統計情報の削除（指名）
DROP STATISTICS duo.hogetable.hogeindex
###プロセスを確認・キルする
急なDBパフォーマンスダウンの時、ロックエスカレーションなどが考えられます。
SSMSの利用状況モニタでGUIでも作業できますが、急ぐ場合はコマンドの方が対処しやすいです。
特にパフォーマンス低下は対処優先になりやすいので、確認方法、注意点をまとめます。
####プロセスの一覧を取得
sp_who
####プロセスをkillする
一応ロールバックするらしい。
kill 621
##ストアドプロシージャの操作
###ストアドプロシージャを作成する
CREATE PROCEDURE select_today_update
--ここに引数
AS
SELECT * from mw_log A INNER JOIN
mw_log_detail B ON A.id=B.id
WHERE convert(nvarchar,UpdateDate,112)=convert(nvarchar,getDate(),112)
###ストアドプロシージャを変更する
ALTER PROCEDURE select_today_update
--ここに引数
AS
SELECT * from mw_log A INNER JOIN
mw_log_detail B ON A.id=B.id
WHERE convert(nvarchar,UpdateDate,112)=convert(nvarchar,getDate(),112)
###ストアドプロシージャを削除する
DROP PROCEDURE select_today_update
トリガー操作
トリガーの一覧を取得する
SELECT * FROM sys.triggers
日々のチューニング
あまり利用されていないインデックス順に表示
利用頻度が低いインデックスは削除。 ORDER BY以下は目的に応じて計算式に係数を入れるなど工夫します。
Use DBName
SELECT
o.name As table_name,
i.name As index_name,
s.user_seeks,
s.user_scans,
s.user_updates
FROM
sys.objects o INNER JOIN
sys.indexes i ON o.object_id = i.object_id INNER JOIN
sys.dm_db_index_usage_stats s ON i.index_id=s.index_id
WHERE
o.type='U' AND database_id = DB_ID() AND
s.object_id = OBJECT_ID('table_name')
ORDER BY
s.user_seeks+s.user_scans+s.user_updates
文字列操作
文字の長さを取得する
SELECT LEN (‘コンニチハ’)