くりーむわーかー

プログラムとか。作ってて ・試しててハマった事など。誰かのお役に立てば幸いかと。 その他、いろいろエトセトラ。。。

SQLServer

SQLServer サロゲートペアがあるかどうか調べるクエリ


--//上位サロゲート:U+D800 ~ U+DBFF
--//下位サロゲート:U+DC00 ~ U+DFFF
--//C#での正規表現:"[\uD800-\uDBFF][\uDC00-\uDFFF]"

select * from TEST where 対象文字 collate japanese_bin like N'%[' + nchar(0xD800) +N'-' + nchar(0xDBFF) + N'][' + nchar(0xDC00)+'-'+ nchar(0xDFFF) + N']%'

Unicode的表現だと↓でもいけるはずなんだけど上手くいかなかった。

select * from TEST where 対象文字 like '%[' + nchar(0x10000) + '-' + nchar(0xFFFFF) + ']%' 

サロゲ以外の漢字も抜けてくる。。。

漢字だけだと0x20000~の範囲なので、↓でもいいと思うけど大丈夫かはわからない。

select * from TEST where 対象文字 like '%[' + nchar(0x20000) + '-' + nchar(0xFFFFF) + ']%'

うえの範囲だと普通の漢字は出てこなくなる。なんででしょね。

SQLServer バイト数で切り出し

ちょっとSQLServerのクエリで文字列をバイト数で切り出ししたくなった。 SQLServerにはバイト数で切り出す関数は無いらしい。ほへ。まー別に使いどころはあんまないしの。

で、SQLServerでやる場合は下の感じ。

declare @test as varchar(100)
set @test = 'あ1い2う3え4お'

--バイト数
select datalength(convert(VARCHAR(30), @test))
--バイト数でLEFT
select convert(VARCHAR(5), @test)
--バイト数でRIGHT
select reverse(convert(VARCHAR(5), reverse(@test)))

全角半角入ってもちゃんといく。ただし、全角を中途半端に切ると1バイト削られる。↓の感じ。

declare @test as varchar(100)
set @test = 'あ1い2う3え4お'

--バイト数でLEFT
select convert(VARCHAR(3), @test)--⇒"あ1"
select convert(VARCHAR(4), @test)--⇒"あ1"

文字列として扱いたい場合は、この仕様は割とありがたい。ただ、使い方によっては気を付けないとダメそーかな。

SQLServer 照合順序の変更

最近、DBの照合順序を変更する必要があった。SQLServer2012。一部のテーブルのみとか、部分的に照合順序を変える事は正直あまり考えられないので、インスタンス丸ごと変える。

で、色々見てるとだいたい↓の感じで書いてある。

ユーザー データベースに作成する新しいオブジェクトの照合順序は、 ALTER DATABASE ステートメントの COLLATE 句を使用して変更できます。 このステートメントを実行しても、既存のユーザー定義テーブルの列の照合順序は変わりません。 ALTER TABLEの COLLATE 句で変更することができます。

とか。⇒microsoftのdoc

master、model、msdb、および tempdb の各システム データベースを再構築する場合は、元の場所からデータベースを削除して再作成する必要があります。 再構築ステートメントに新しい照合順序を指定する場合は、その照合順序の設定でシステム データベースが作成されます。 これらのデータベースに対するユーザーの変更はすべて失われます。 たとえば、master データベースのユーザー定義オブジェクト、msdb にスケジュールされたジョブ、または model データベースの既定のデータベース設定に対する変更が対象になります。

とかとか。⇒TechNet

ユーザーDBは変えれなくはないけど、DBの照合順序変えても既存は変更されない。あと、システムデータベース(tempとか)はユーザDBとか存在してると変えられない。作り直しの必要あり。。。

それ何て再インストール?

正直、ユーザDBのインスタンス変えても、一時テーブルとか使うとtempテーブルにデータが出来るから、そっちと照合順序違くなって、Joinとか落ちるので、全DB変更する以外の事はあまし考えられない。。。

ググると色々出てくるんだけど、ぶっちゃけインスタンス再インストール(or別で作る)以外に無難な方法がありませんね。

あと、見てるとDBバックアップして変更後に復元するとか書いてあるサイトもあるけど、それやると、照合順序復元されてまるで意味なし。

ストアドとかの戻りとかはDBの照合順序変えれば適用されるらしいけど、テーブルの定義は変わらないようす。ついでにDBの照合順序は新しく作成されるテーブルに適用されるものみたいなので、既存は何も変わらない。

DBの照合順序変えてもsp_helpでテーブル見れば定義変わってないのが確認できる。

とゆーことで、以下の感じで全部作り直した。

  1. 既存をバックアップするか、リンクサーバ出来るようにしておく
  2. インスタンスを新しく作る(照合順序はインストール時に指定する)
  3. リンクサーバかバックアップを別名で復元
  4. 元のDBと同じものをCreateする
  5. 元のDBの全オブジェクトをスクリプト化しておいて、新しい方に全部作成
  6. 元DB復元したものかリンクサーバで元データを新しい方に入れ込む
めんどくさし。でもこれしか綺麗にいく方法が思いつかん。

ただ、↓のコマンドは通ったから、全列に対し実行するスクリプト作ればいけるかな?

alter table [テーブル名] alter column [列名] nvarchar(200) collate Japanese_XJIS_100_CI_AS_SC

見かけ上は上でも上手くいってるんだけど、本当にうまくいってるのか自信がありません。どうやって確認すればいいんだろうか。。。

ちなみに変更するのはサロゲートペア文字への対応用です。プログラム的な話はあれですが、SQLでいくと普通の照合順序使ってると、len()とかおかしくなる。サロゲの文字の場合はlen()が2でかえってくるので。なのでrightとかsubstringとか全部おかしくなる。ゆえに照合順序を変えて、サロゲでもlen()で1って帰ってきてくれるようにしないと色々あれなのです。

照合順序的には「japanese*****SC」みたいに「SC」つく照合順序が補助文字があーだこーだでサロゲ文字入ってきても上手くいく照合順序の様子。

あー、いちを個別のクエリ上でcollate句で照合順序指定すればテーブルとかDBの照合順序が違くても上手くいく。でも、んな事をやり続ける自信ありませんヨネ。。。

SQLServer 断片化と平均ページ密度を調べるクエリ

SQLServerで全DB・全テーブルの断片化と平均ページ密度の調査する時のクエリ。

断片化と平均ページ密度については↓のサイトでどうぞ。
https://blogs.msdn.microsoft.com/jpsql/2011/10/16/337/

SET NOCOUNT ON;

declare @CR_DATABASE_ID AS INT
declare @CR_DBNAME AS NVARCHAR(200)
declare @WKSTR AS VARCHAR(20)

--//DBとテーブルのリスト用
create table #WKTBLLIST
(
database_id int
,object_id int
,DB名 nvarchar(200)
,テーブル名 nvarchar(256)
)
--//結果保存用
create table #RESULT
(
database_id smallint
,object_id int
,table_object_id int
,index_id int
,DB名 nvarchar(256)
,テーブル名 nvarchar(256)
,[断片化率] float
,[平均ページ密度] float
,ページ数 bigint
,レコードカウント bigint
)

--//DB毎に繰り返し
declare CUR_1 cursor for
select top 1 database_id,name from sys.databases 
order by name

open CUR_1

FETCH NEXT FROM CUR_1
INTO    @CR_DATABASE_ID,@CR_DBNAME

while(@@FETCH_STATUS=0)
begin
	--//DB毎にテーブルの一覧を保存する
    set @WKSTR = cast(@CR_DATABASE_ID as varchar(10))
    exec(
    'use ' + @CR_DBNAME + ';' +
    'insert #WKTBLLIST '+
    'select '+ @WKSTR +',object_id,'''+@CR_DBNAME+''',name ' +
    'from sys.tables ' +
    );

    FETCH NEXT FROM CUR_1
    INTO    @CR_DATABASE_ID,@CR_DBNAME
end

Close CUR_1
DEALLOCATE CUR_1

declare @CR_OBJECT_ID AS INT
declare @CR_TABLENAME AS NVARCHAR(256)

--//上で保存したDB/テーブルのリストで繰り返し
declare CUR_1 cursor for
select database_id,object_id,DB名,テーブル名 from #WKTBLLIST
order by database_id,テーブル名

open CUR_1

FETCH NEXT FROM CUR_1
INTO    @CR_DATABASE_ID,@CR_OBJECT_ID,@CR_DBNAME,@CR_TABLENAME

while(@@FETCH_STATUS=0)
begin
	--//テーブル毎に断片化率を取得して結果テーブルに保存
    insert #RESULT
    select 
    database_id
    ,object_id
    ,@CR_OBJECT_ID
    ,index_id
    ,@CR_DBNAME as DB名
    ,@CR_TABLENAME as テーブル名
    ,avg_fragmentation_in_percent as [断片化率]
    ,avg_page_space_used_in_percent as [平均ページ密度]
    ,page_count as ページ数
    ,record_count as レコードカウント
    from sys.dm_db_index_physical_stats(@CR_DATABASE_ID,@CR_OBJECT_ID, NULL, NULL , 'DETAILED')

    FETCH NEXT FROM CUR_1
    INTO    @CR_DATABASE_ID,@CR_OBJECT_ID,@CR_DBNAME,@CR_TABLENAME
end

Close CUR_1
DEALLOCATE CUR_1

--//断片化してるインデックスを調査
select * from #RESULT
where 断片化率 > 30 --Microsoftの目安値
and ページ数 > 50 --ページ数が少ない奴は見てもしょうがないと思う

--//平均ページ密度が低下してるインデックスを調査
select * from #RESULT
where 平均ページ密度 < 60--60%はなんとなく
and ページ数 > 50 --ページ数が少ない奴は見てもしょうがないと思う

--//全部の結果
select * from #RESULT

SET NOCOUNT OFF;

あとは↓の感じで再構成か再構築する

--再構成はこっち
ALTER INDEX ALL ON [テーブル名] REORGANIZE WITH(LOB_COMPACTION=ON);
--再構築はこっち
ALTER INDEX ALL ON [テーブル名] REBUILD WITH(ONLINE=OFF);

再構成(REORGANIZE)はリーフレベル:一番下位のページしかやらないらしく、大本がそもそもイカれてる場合は意味無い。その場合は再構築(REBUILD)する。ただし、REBUILDは処理してるインデックス単位にロックするらしいので、実行してる最中はテーブルはさわっちゃだめ。

参考サイト

断片化について
https://blogs.msdn.microsoft.com/jpsql/2011/10/16/337/

インデックスの再構築 (rebuild) と再構成 (reorganize) の違い
https://blogs.msdn.microsoft.com/jpsql/2013/02/28/977/

Reorganize and Rebuild Indexes
https://msdn.microsoft.com/ja-jp/library/ms189858.aspx

ALTER INDEX (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms188388.aspx

sys.dm_db_index_physical_stats (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms188917.aspx

SQLServerでデータベース拡張が行われたか調べる

タイトル通り。どの程度の頻度で行われているかを調べたいとき。


declare @filename varchar(500) 

--//トレースファイルのパスを取得
select @filename = path from sys.traces
where is_default = 1 

--//EventClass
--92:Data File Auto Grow
--93:Log File Auto Grow
--94:Data File Auto Shrink
--95:Log File Auto Shrink

select
HostName
,EventClass
,DatabaseName
,Filename
,StartTime
,EndTime
,(Duration/1000.0) as かかった秒
,(IntegerData*8.0/1024) as 増えたMB
from sys.fn_trace_gettable(@filename, default) 
where EventClass between 92 and 95
and DatabaseName = DB_NAME() 
order by StartTime desc

データベース拡張だけではなくいろんなログが取れるから結構便利。EventClassの定義をとる関数とかないかと探してみたけど見つからなかった。誰か教えてください。定義自体はココに載ってる。

MSSQLServerからPostgreSQLへのデータ移行をC#で書く

タイトル通り。最近、SQLServerからPostgreSQLへの切り替えをやってる。CreateTableしてー、CSVにデータはいてー、Postgreにインポートしてー。みたいなのはググればいっぱい出てきますが、正直、
クッソだるい

何がだるいかとゆーと、ってか実際には上の感じの作業はしてないので、もしかしたらすんなり行くのかもしれませんが、十中八九、以下の点ではまるのが目に見えている。

  1. テキスト系で改行とかエスケープしないとダメなのあったらどーせ無理じゃない?
  2. 100を超えるテーブルのCreate文なんか流したくない
  3. 100を超えるテーブルのインポートなんかしたくない
  4. バイナリ型ってCSV吐いただけで行けるのかしら?
などなど。ハマルのが目に見えている。

とゆーわけで、PostgreSQLをC#でごにょごにょするのに慣れる意味も含めてプログラムでやった。

SQLServerへの接続とかはデフォでほぼ行けるからどーでもいいとして、PostgreSQLを扱う場合は「Npgsql」を使う。インストールはNugetから。

あと、一件づつインサートとかはありえないので、.netでいうBulkCopyもしたい。

プログラムの流れは↓の感じ。

①SQLServerで移行したいDBのテーブルの定義を丸ごと取得
②①の中でPostgre用のCreateTableを作っておく。
③Postgreに②のCreateTableを発行
④SQLServer側のテーブルをSelect *して、Postgreに順次BulkCopy
⑤Postgre側にIndexつける
⑥VACUUM実行

大したことはやってないけど、確実に手でやるより早い。

①のSQLServerの定義取得は↓の感じのクエリで丸ごととってくる

select 
X.object_id
,X.name as tablename
,cast(Y.column_id as int) as column_id
,Y.name as colname
,cast(Y.system_type_id as int) as system_type_id
,TYPE_NAME(Y.system_type_id) as typename
,cast(Y.max_length as int) as max_length
,cast(Y.precision as int) as precision
,cast(Y.scale as int) as scale
,cast(Y.is_nullable as int) as is_nullable
,cast(Y.is_identity as int) as is_identity 
,cast(isnull(Z.is_primary_key,0) as int) as is_primary_key
from (
	select * from sys.tables
	where type = 'U' 
	and name not like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
) as X
inner join (
	select * from sys.columns
) as Y
on X.object_id = Y.object_id
left join (
	select X.object_id,Y.column_id,Z.is_primary_key from sys.columns as X
	inner join sys.index_columns as Y
	on X.object_id = Y.object_id
	and X.column_id = Y.column_id
	inner join sys.indexes as Z
	on Y.object_id = Z.object_id
	and Y.index_id = Z.index_id
	and X.object_id = Z.object_id
	where Z.is_primary_key = 1
) as Z
on Y.object_id = Z.object_id
and Y.column_id = Z.column_id
order by X.object_id,Y.column_id

主キーの判定がちょっとメンドー。あと、テーブル名で西暦8ケタ付きのバックアップぽいテーブルは除くようにしてみたりしてる。あと、数値系の項目ってどれがbyte型か調べるのめんどーになったので、全部強制的にIntに変換。ランボー過ぎかな?

定義が取れれば、Create文はテキトーに作れるでしょう。

そしたら、Postgre側にテーブル作成を投げる。下の感じ。公式のままだ。

//EF使ってるけど、クエリを発行したいのでEFの接続文字列を無理やり取得
string constr= this.Database.Connection.ConnectionString;
using (var conn = new NpgsqlConnection(constr))
{
    conn.Open();
    using (var cmd = new NpgsqlCommand())
    {
        cmd.Connection = conn;
        try
        {
            //何回か流すようにテーブルドロップ。初回は落ちるのでtryしておく。これもランボー。
            cmd.CommandText = String.Format("DROP TABLE dbo.\"{0}\";",pDef.tablename);
            cmd.ExecuteNonQuery();
        }
        catch { }
        cmd.CommandText = pDef.createSql;
        cmd.ExecuteNonQuery();
    }
}

そしたら次はBulkCopy。ロジックは抜粋で。

using (SqlConnection cn_ = new SqlConnection(ConnectionString))
{
    cn_.Open();

    SqlCommand command = new SqlCommand();
    command.CommandTimeout = 3600;//タイムアウトの設定
    command.CommandText = "select * from [テーブル名]";
    command.Connection = cn_;
    //SQLServerにSelect投げる
    using (SqlDataReader sqlDr = command.ExecuteReader())
    {
        DataTable schemaDt = sqlDr.GetSchemaTable();
        //Postgreにつなぐ
        using (var conn = new NpgsqlConnection(pgConStr))
        {
            conn.Open();
            //Postgreのbulk
            using (var writer = conn.BeginBinaryImport(String.Format("COPY dbo.\"{0}\" ({1}) FROM STDIN (FORMAT BINARY)", "テーブル名","テーブルの列のリスト")))
            {
                while (sqlDr.Read())//SQLServerのSelect結果
                {
                    writer.StartRow();//コピーする行ごとに必要っぽい
                    for(int i=0;i< schemaDt.Columns.Count; i++)
                    {
                        //列毎にカキカキ
                        writer.Write(sqlDr[i],getColDef(pDef, i));
                    }
                }
            }
        }
    }
}
//SQLServerのデータ型に応じてPostgre側の型定義を返す
public NpgsqlDbType getColDef(MSDBTableDef pDef, int i)
{

    switch (pDef.coldeflist[i].system_type_id)
    {
        case 56://int
            return NpgsqlDbType.Integer;
        case 127://bigint
            return NpgsqlDbType.Bigint;
        case 167://varchar
        case 231://nvarchar
            if(pDef.coldeflist[i].max_length < 0)
                return NpgsqlDbType.Text;
            else
                return NpgsqlDbType.Varchar;
        case 106://decimal
            return NpgsqlDbType.Numeric;
        case 61://datetime
            return NpgsqlDbType.Timestamp;
        case 165://varbinary
            return NpgsqlDbType.Bytea;
    }
    return NpgsqlDbType.Varchar;
}

Npgsqlのバルクコピーは↓の感じで、Objectの配列として渡してもOKな気がする。

object[] dtArray = new object[schemaDt.Columns.Count];
sqlDr.GetValues(dtArray);
writer.WriteRow(dtArray);

ただ、公式のこの辺に「NpgsqlDbTypeでデータの型をちゃんと指定する事を激しくお勧めする」って書いてあるのでそーした。データ型の指定はいつの時代になってもとっても重要。

あと、インデックスはデータ入れ終わってから、まとめてやった方が多分よいと思う。速度的な意味で。

最後に、対象のDBに対して「VACUUM」ってコマンドを投げてあげる。不要領域の削除とかなんかもろもろやるらし。テーブルロックとかされる場合もあるらしいので、誰か触ってる時にはやらない方がいい。

でだ、NpgsqlのCopyはいいんだけど、Writeのタイミングで書きに行ってるわけじゃないよね?なんだかいつ実行してるのかロジック見る感じでよくわからない。多分、usingしてるし、Disposeされるときにやってるのかなと愚考。個人的に明示してくれる方がわかりやすい。

SQLServer 2000 ストアドとかの更新日

持ってないんだって・・・。

SQLServer2005以降なら、sys.objectsの「modify_date」で取れるんだけど、SQLServer2000は更新日が無いらしい。衝撃的である。

ストアド、関数、ビューのソース管理するツールをふんわり作ってるんだけど、古い環境だと、SQLServer2000をいまだに使ってやがる環境がまだあるもんで。。。。

んー、2000の場合はソースのバイナリ比較をするしか無いかしらね。

SQLServerで関数とかViewとかストアドのソースを取得

タイトル通り。ググれば出てくるんだけど、イマイチ欲しい情報がすべて乗ってる例がない。なので、特筆。

SELECT
Y.name
,Y.type
,Y.create_date
,Y.modify_date
,X.uses_ansi_nulls
,X.uses_quoted_identifier
,X.definition
FROM sys.sql_modules AS X
INNER JOIN sys.objects AS Y
ON X.object_id = Y.object_id
WHERE Y.is_ms_shipped = 0
ORDER BY Y.type,Y.name

単純に「sys.sql_modules」に「sys.objects」くっつけるだけ。typeとかmodify_dateが欲しいので。 「is_ms_shipped = 0」付けとかないと、SQLServerが勝手に作るやつも出てくる。

SQLServerで列一覧(主キーのフラグ付き)

SQLServerで列の一覧はすぐとれるんだけど、主キーかどうかわからない。なので主キーかどうかのフラグを一緒につけたい場合は↓の感じ。

select 
X.name as tablename
,Y.object_id
,Y.name
,Y.column_id
,Y.system_type_id
,Y.max_length
,Y.is_nullable
,Y.is_identity
,isnull(Z.is_primary_key,0) as is_primary_key
from (
	select * from sys.tables where type = 'U'
) as X
inner join (
	select * from sys.columns
) as Y
on X.object_id = Y.object_id
left join (
	select X.object_id,Y.column_id,Z.is_primary_key from sys.columns as X
	inner join sys.index_columns as Y
	on X.object_id = Y.object_id
	and X.column_id = Y.column_id
	inner join sys.indexes as Z
	on Y.object_id = Z.object_id
	and Y.index_id = Z.index_id
	where Z.is_primary_key = 1
) as Z
on Y.object_id = Z.object_id
and Y.column_id = Z.column_id

もっといい感じに書けると思うけど、ちょっとやっつけ。

SQLServerExpress2012でProfilerを使いたい

SQLServerのExpressはProfilerが使えない。なので、クエリを拾いたい場合は別のツールが必要。個人的にこのExpressProfilerがいい感じ。

↑のサイトでdownloadのリンクからインストールファイルをDLしてインストール。

実行すると↓の感じ。
01

そしたらサーバを指定して、Windows認証かSQLServer認証のどっちか選んで、SQLServer認証だったらID/PASSを指定しておく。

で、あとは実行クリック。
02

Webアプリ動かすと、下の感じでクエリが取れる。
03

フィルタのボタン押すと、フィルタがいろいろ出てくる。

いちを↑のTextDataのところに拾いたいSQLの中身の一部を書いておくとそのクエリだけ拾えたりする。ただ、一個しかつけれなさそう。まぁ、家とかでちんまりやる分には十分かと。。。

問合せ