SQL Serverチートシート

SQL Server(RDB)に使うtransact SQLの私的チートシート。
逐次増やしていきます。

##テーブル操作関係

###テーブルを作る

```sql
CREATE TABLE test_table (
   [id] int not null primary key,
   [name] nvarchar(50) not null
);
```

###テーブルを作る（先頭列idを自動インクリメント行にする）
GUIで編集していると気づきにくいですが、自動インクリメント（identity insert）の設定ができるのは、テーブル作成時と行追加時のみです。

```sql
CREATE TABLE mw_log (
   id int identity not null primary key ,
   name nvarchar(50) not null
);
```

###テーブルに行を追加する

```sql
INSERT INTO mw_log (id,name)
values (2,'ボルシチ')
```

###テーブルに列を追加する
```sql
ALTER TABLE mw_log add [updatedate] datetime not null default getDate();
```

###テーブルを削除する

```sql
DROP TABLE mw_log
```

###テーブルに複数項目からなるの主キーを設定する
テーブル作成時には、複数の項目からなる主キー制約を設定できないので、テーブルを作った後に設定します。
主キーは自動的にクラスタ化インデックスになるので、例示のCLUSTEREDはなくても動きます。

```sql
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);
```

##データベースの情報を取得する

###データベースのテーブル一覧を取得する

```sql
SELECT * FROM INFORMATION_SCHEMA.tables
```

###データベースのカラムの一覧を取得する

```sql
SELECT * FROM INFORMATION_SCHEMA.columns
```

## データベースの容量を減らしたい時に

プレゼンやデモ、説明会用にデータベースの容量を少なくしたい時に。

### インデックスを削除してDBの容量を節約する。

```sql
DROP INDEX index_hogehoge on table_name_hoge
```

プレゼンやデモ、説明会用にデータベースの容量を少なくしたい時に。

```sql
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の実行時は外部キー制約を一時的に外すなどの工夫が必要です。

```sql
SELECT 'TRUNCATE TABLE ' + table_name FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE'
```

##データベースが遅くなった時の対処

###統計情報の更新(DB全体)

```sql
exec sp_updatestats
```

###統計情報の更新(指名)

```sql
UPDATE STATISTICS dbo.hogetable hogeindex
```

### 統計情報の削除（指名）

```sql
DROP STATISTICS duo.hogetable.hogeindex
```

###プロセスを確認・キルする
急なDBパフォーマンスダウンの時、ロックエスカレーションなどが考えられます。
SSMSの利用状況モニタでGUIでも作業できますが、急ぐ場合はコマンドの方が対処しやすいです。
特にパフォーマンス低下は対処優先になりやすいので、確認方法、注意点をまとめます。

####プロセスの一覧を取得
```sql
sp_who
```

####プロセスをkillする
一応ロールバックするらしい。

```sql
kill 621
```

##ストアドプロシージャの操作

###ストアドプロシージャを作成する

```sql
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)
```

###ストアドプロシージャを変更する

```sql

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)
```

###ストアドプロシージャを削除する

```sql
DROP PROCEDURE select_today_update 
```

## トリガー操作

### トリガーの一覧を取得する

```sql
SELECT * FROM sys.triggers
```

## 日々のチューニング

### あまり利用されていないインデックス順に表示

利用頻度が低いインデックスは削除。 ORDER BY以下は目的に応じて計算式に係数を入れるなど工夫します。

```sql
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
```

## 文字列操作

#### 文字の長さを取得する

```sql
SELECT LEN (‘コンニチハ’)
```