すぐに忘れるSQLServerのクエリ

create database

照合順序を指定する。

CREATE DATABASE create_database_name collate  Japanese_CS_AS_KS_WS

SQLServerでSELECTした結果をCREATE TABLEする

SELECT * INTO CRATE_TABLE FROM SELECT_table

UPDATE,DELETEでjoin

UPDATE

UPDATE a 
    SET a.CODE = b.CODE
    FROM TABLEA a 
    INNER JOIN TABLEB b ON
    a.ID = b.ID
    WHERE CODE = '01'

DELETE

DELETE a FROM TABLEA a 
    INNER JOIN TABLEB b ON
    a.ID = b.ID
    WHERE CODE = '01'

文字列分割

普通のSUBSTRと違い０番目から始まらないので注意

SELECT SUBSTRING('TEST',2,2)

> ES

文字列を日付型にして経過月数を算出

select datediff(month, try_convert(datetime, "19980101"),try_convert(datetime, "20080910")) / 12.0

文字列のエスケープ文字

シングルクォート二つ

select 'cat''s eye'

> cat's eye

backup stripe

バックアップファイルを分割。すごく簡単。
ファイルサイズが大きいと単一ディスクに対しても速度があがるようです。

BACKUP DATABASE MY_DATABASE
TO DISK='c:\database\1.bak', 
DISK='c:\database\2.bak', 
DISK='c:\database\3.bak',
DISK='c:\database\4.bak'
WITH COMPRESSION -- 圧縮
GO

restore

RESTORE DATABASE MY_DATABASE FROM  
DISK = N'C:\database\backup1.bak',  
DISK = N'C:\database\backup2.bak',  
DISK = N'C:\database\backup3.bak',  
DISK = N'C:\database\backup4.bak'
WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Default with nolock hint

SQLServerはSelectで行ロックしてしまうのでnolock hintを書かないとウェイトしてしまう。
そんな時Defaultでnolockしたいときのquery。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

※ トランザクション分離レベルが変わるので注意

全カラムを取得

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'datetime2'

大文字、小文字変換

-- 小文字→大文字
  SELECT UPPER('abc');
-- 大文字→小文字
  SELECT LOWER('ABC');

Triggerでのinsert delete update判定方法

if exists(select * from insertd) and
   exists(select * from deleted)
   --Update
else if exists (select * from inserted)
   --Insert
else if exists (select * from deleted)
   --Delete
else
   --どれでもない。

FKのWITH NOCHECKを調べる方法とCHECKにするクエリを出力するクエリ

スタックオーバーフローの回答にありました。
https://stackoverflow.com/questions/529941/with-check-add-constraint-followed-by-check-constraint-vs-add-constraint

;WITH Untrusted (ConstraintType, ConstraintName, ConstraintTable, ParentTable, IsDisabled, IsNotForReplication, IsNotTrusted, RowIndex) AS
(
    SELECT 
        'Untrusted FOREIGN KEY' AS FKType
        , fk.name AS FKName
        , OBJECT_NAME( fk.parent_object_id) AS FKTableName
        , OBJECT_NAME( fk.referenced_object_id) AS PKTableName 
        , fk.is_disabled
        , fk.is_not_for_replication
        , fk.is_not_trusted
        , ROW_NUMBER() OVER (ORDER BY OBJECT_NAME( fk.parent_object_id), OBJECT_NAME( fk.referenced_object_id), fk.name) AS RowIndex
    FROM 
        sys.foreign_keys fk 
    WHERE 
        is_ms_shipped = 0 
        AND fk.is_not_trusted = 1       

    UNION ALL

    SELECT 
        'Untrusted CHECK' AS KType
        , cc.name AS CKName
        , OBJECT_NAME( cc.parent_object_id) AS CKTableName
        , NULL AS ParentTable
        , cc.is_disabled
        , cc.is_not_for_replication
        , cc.is_not_trusted
        , ROW_NUMBER() OVER (ORDER BY OBJECT_NAME( cc.parent_object_id), cc.name) AS RowIndex
    FROM 
        sys.check_constraints cc 
    WHERE 
        cc.is_ms_shipped = 0
        AND cc.is_not_trusted = 1

)
SELECT 
    u.ConstraintType
    , u.ConstraintName
    , u.ConstraintTable
    , u.ParentTable
    , u.IsDisabled
    , u.IsNotForReplication
    , u.IsNotTrusted
    , u.RowIndex
    , 'RAISERROR( ''Now CHECKing {%i of %i)--> %s ON TABLE %s'', 0, 1' 
        + ', ' + CAST( u.RowIndex AS VARCHAR(64))
        + ', ' + CAST( x.CommandCount AS VARCHAR(64))
        + ', ' + '''' + QUOTENAME( u.ConstraintName) + '''' 
        + ', ' + '''' + QUOTENAME( u.ConstraintTable) + '''' 
        + ') WITH NOWAIT;'
    + 'ALTER TABLE ' + QUOTENAME( u.ConstraintTable) + ' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME( u.ConstraintName) + ';' AS FIX_SQL
FROM Untrusted u
CROSS APPLY (SELECT COUNT(*) AS CommandCount FROM Untrusted WHERE ConstraintType = u.ConstraintType) x
ORDER BY ConstraintType, ConstraintTable, ParentTable;

カラムのデフォルト値を変更する方法

すでに存在すると以下のエラーが出力される。
Column already has a DEFAULT bound to it.

一度デフォルト値を削除して再設定する必要があります。

ALTER TABLE [dbo].[table] DROP CONSTRAINT [DF_table_column];

ALTER TABLE [dbo].[table]
    ADD  CONSTRAINT [DF_table_column] DEFAULT ((1)) FOR [isTest];

default名を指定していない場合消せないので、以下のクエリで取得できます。

EXECUTE [dbo].[sp_help] 'table'
GO
EXECUTE [dbo].[sp_helpconstraint] 'table'
GO