はじめに

SQL Serverで開発やデータ抽出をやっていますが、いつも使うクエリの定型文が思い出せず毎回調べているのでこのタイミングでまとめて見ようかなと（~~アドベントカレンダーに登録したけど書く内容に行き詰まったから~~）

作成・更新・削除系

テーブル作成

create table  テーブル名A(
    カラム1 INT IDENTITY(1,1) NOT NULL, --自動採番（始まりの値,インクリメントする値）
    カラム2 INT DEFAULT 5, --デフォルトを指定したいとき
    カラム3 INT NULL, --NULL許容
    カラム4 INT NOT NULL, --NOT NULL制約
    カラム5 DATETIME
)

テーブル作成でよく使う設定を記述しました。
デフォルトを指定したいときはそんなに使わないけど、忘れた頃にやってくる。。。

制約削除

ALTER TABLE テーブル名A DROP CONSTRAINT 制約名B

カラム削除しようとしたら、制約に依存しているから～というエラーが出る。制約付きカラムの削除のときによく使った。（上記のテーブルでいうと「カラム2」がそれに価する）

カラム削除

ALTER TABLE テーブル名A DROP COLUMN カラム2

自動採番設定済みカラムに対して任意の値をインサートする

set identity_insert テーブル名A on;
insert into テーブル名A(カラム1,カラム2,カラム3,カラム4) values (2,1,1,00000000000)
set identity_insert テーブル名A off;

identity_insert を上記のように指定してあげればインサートできます。
このときに気をつけなければならないのが、インサート文で全カラムを明示しないといけません。
（いつもそのことを忘れてしまう）

その他系

テーブルを全取得

SELECT
    *
FROM
   sys.objects
WHERE
   type = 'U';

シノニムを全取得

SELECT
    NAME
    ,BASE_OBJECT_NAME
FROM
    SYS.SYNONYMS
ORDER BY
    BASE_OBJECT_NAME

テーブルの情報を取得

SELECT
    TABLE_NAME
    ,COLUMN_NAME
    ,DATA_TYPE
    ,COLUMN_DEFAULT
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME='テーブル名A'

アンチパターン

NULLの扱い

NULLを許可しているカラムではスカラー式が任意の値にならないことに注意する。
SQLは3値論理であることを忘れない。
最近、これを忘れていて痛い目にあった。。。