SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server Express LocalDB の照合順序について

leave a comment

SQL Server 2012 から、Express エディションより軽量な SQL Server Express LocalDB (SQLLocalDB) の機能が追加されています。
SQL Server 2014 Express LocalDB

SQL Server Express LocalDB の照合順序について軽くまとめてみたいと思います。

LocalDB は 44MB 程度のインストーラーからインストールすることができ、以下のようなコマンドで DL からインストールまでを実施することができます。
# SQL Server 2014 の LocalDB では自動インスタンス名が、2012 の 「v11.0」から、「MSSQLLocalDB」 に変更されています。

$outfile = [System.IO.Path]::GetTempFileName()
Write-Output $outfile
wget http://download.microsoft.com/download/1/C/9/1C95EAB0-F98C-4039-8402-4D7A84D9B290/LocalDB%2064BIT/SqlLocalDB.msi -OutFile $outfile
Start-Process -FilePath msiexec -ArgumentList "/i $outfile", "/qn", "IACCEPTSQLLOCALDBLICENSETERMS=YES"  -Wait

$env:Path = [System.Environment]::GetEnvironmentVariable("Path","Machine")

$instance = sqllocaldb info

$ConString = New-Object System.Data.SqlClient.SqlConnectionStringBuilder

$ConString.psbase.DataSource = "(localdb)\$instance"
$ConString.psbase.IntegratedSecurity = $true

$Con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = $ConString
$con.Open()

$cmd = $Con.CreateCommand()
$cmd.CommandText = "SELECT @@VERSION"
$ret = $cmd.ExecuteReader()

$ret.Read()
$ret[0]

 

LocalDB の照合順序については、冒頭に紹介した技術情報に以下のように記載されています。

LocalDB のインスタンスの照合順序は SQL_Latin1_General_CP1_CI_AS に設定され、変更することはできません。データベース レベル、列レベル、および式レベルの照合順序は正常にサポートされます。包含データベースは、包含データベースの照合順序によって定義されたメタデータおよび tempdb の照合順序ルールに従います。

クエリの実行結果からも確認することができます。

select name,collation_name from sys.databases

image

システムの照合順序が、「SQL_Latin1_General_CP1_CI_AS」に設定されています。

それでは、以下のクエリを実行してみます。

USE [master]
GO
IF DB_ID('DB01') IS NOT NULL
BEGIN
	DROP DATABASE DB01
END

CREATE DATABASE DB01
GO


USE [DB01]
GO

DECLARE @tmp1 table(Col1 varchar(10))
DECLARE @tmp2 varchar(10) = 'あ'


IF OBJECT_ID('tempdb..#tmp3') IS NOT NULL
BEGIN
	DROP TABLE #tmp3
END

CREATE TABLE #tmp3 (Col1 varchar(10))

INSERT INTO @tmp1 VALUES('あ')
SELECT * FROM @tmp1
SELECT @tmp2
INSERT INTO #tmp3 VALUES('あ')
SELECT * FROM #tmp3

 

このクエリの実行結果は以下のようになります。

image

日本語に対応した照合順序を使用していないため、文字が想定通り格納されていないことが確認できますね。

それでは、日本語の照合順序を使用してクエリを再実行してみます。

USE [master]
GO
IF DB_ID('DB01') IS NOT NULL
BEGIN
	DROP DATABASE DB01
END

CREATE DATABASE DB01 COLLATE Japanese_XJIS_100_CI_AS
GO


USE [DB01]
GO

DECLARE @tmp1 table(Col1 varchar(10))
DECLARE @tmp2 varchar(10) = 'あ'


IF OBJECT_ID('tempdb..#tmp3') IS NOT NULL
BEGIN
	DROP TABLE #tmp3
END

CREATE TABLE #tmp3 (Col1 varchar(10))

INSERT INTO @tmp1 VALUES('あ')
SELECT * FROM @tmp1
SELECT @tmp2
INSERT INTO #tmp3 VALUES('あ')
SELECT * FROM #tmp3

tempdb に格納したデータ以外は正常に格納できていますね。

image

LocalDB は SQL Server 2012 以降に追加されたものなので、「包含データベース」を使用することができます。

照合順序の基本をユーザーデータベースに合わせて動作させるため、包含データベースの機能を有効にします。

USE [master]
GO
EXEC sp_configure 'contained database authentication', 1
RECONFIGURE

IF DB_ID('DB01') IS NOT NULL
BEGIN
	DROP DATABASE DB01
END

CREATE DATABASE DB01 COLLATE Japanese_XJIS_100_CI_AS
ALTER DATABASE [DB01] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

USE [DB01]
GO

DECLARE @tmp1 table(Col1 varchar(10))
DECLARE @tmp2 varchar(10) = 'あ'


IF OBJECT_ID('tempdb..#tmp3') IS NOT NULL
BEGIN
	DROP TABLE #tmp3
END

CREATE TABLE #tmp3 (Col1 varchar(10))

INSERT INTO @tmp1 VALUES('あ')
SELECT * FROM @tmp1
SELECT @tmp2
INSERT INTO #tmp3 VALUES('あ')
SELECT * FROM #tmp3

 

上記のクエリについては、これで日本語に対応させることができます。

image

システムデータベースの照合順序を変更することができない LocalDB で各種動作を日本語に対応させたい場合には、包含データベースの利用も検討してみるとよいかもしれないですね。

包含データベースを本番環境で有効にするかは検討が必要ですので、開発環境から移行する際の設定には気を付けた方がよさそうですが。

Written by masayuki.ozawa

9月 22nd, 2015 at 9:33 pm

Posted in SQL Server

Tagged with

Leave a Reply

*