システム開発備忘録 SQL Server その他

OPENDATASOURCE
SQLの中でOPENDATASOURCEを使用することで、
OracleのDBLinkのように、別のデータベースのデータを結合することができる。
SQLの中に直接、接続情報を記述する。
使用するためには、SQLを実行する側のDBの、
sp_configureのAd Hoc Distributed Queriesのrun_valueが1になっている必要がある。

--SQL記述方法（別のSQLサーバーへの接続）

select *
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=サーバー名;User ID=ユーザー;Password=パスワード'
).DB名.スキーマ.テーブル名

--Ad Hoc Distributed Queriesの確認用SQL
select name
,convert(int, value_in_use) as run_value
from sys.configurations
where name='Ad Hoc Distributed Queries'

トランザクションログファイルを縮小する
肥大化したログファイルのサイズを1KBにする
Management Studio のDB選択＞タスク＞圧縮でも縮小できないファイルも縮小できる

/*データベースDB1のログの領域解放*/
backup log DB1 with truncate_only;

/*ファイルサイズ縮小*/
use DB1;
go
dbcc shrinkfile(2,truncateonly);
go

照合順序を設定する
メッセージ 468
「equal to 操作の "Japanese_BIN" と "Japanese_CI_AS" 間での照合順序の競合を解決できません。」
のエラーのとき、照合順序をSQL中で指定してやるとエラー回避できる。
DBの設定を変更するのが本来の対処方だが、そのような権限がなくてもなんとかしたい場合に有効。
次の例は、列”Col1”が”a1”となっているデータを検索したときにこのエラーが出た場合の書き方。

/*照合順序を指定した検索*/
select * from Table1
where Col1= 'a1' collate Japanese_CI_AS;

Japanese_CI_ASはアルファベットの大文字と小文字を区別しない。
一方Japanese_BIN は大文字と小文字を区別する。
検索列に'a1'と'A1'という値が存在していて検索条件を='a1'とした場合、
collateが"Japanese_CI_AS"の場合は、a1とA1の両方が抽出対象となる。
collateが" Japanese_BIN "の場合は、a1のみが抽出対象となる。
" Japanese_BIN "指定の検索結果は次のSQLと同じである。

/*バイナリに変換しての検索*/
select * from Table1
where convert(binary, Col1) = convert(binary, 'a1')

--日付形式の変換 YYYYMMDD形式の文字列をDatetime型にする
cast(Col1 as DATETIME)
--日付形式の変換システム日時をYYYY/MM/DD形式にする
SELECT CONVERT(NVARCHAR(10), getdate(), 111)
--日付形式の変換システム日時をYYYYMMDD形式にする
SELECT CONVERT(NVARCHAR(8), getdate(), 112)
--日付形式の変換システム日時をhh:mi:ss:mmm(24h)形式にする
SELECT CONVERT(NVARCHAR(12), getdate(), 114)
--日付形式の変換システム日時をhhmissmmm(24h)形式にする
SELECT replace(CONVERT(NVARCHAR(12), getdate(), 114),':','')

--トリム　ＳＱＬサーバーにはTRIM関数が無いのでLTRIMとRTRIMを組み合わせる
select LTRIM(RTRIM(' トリムされる文字 '))