Route-n

[T-SQL]１から１０まで足す

Route（佐久間　竜也） — Tue, 06 Oct 2009 03:12:00 GMT

ご無沙汰しております。
そろそろ出尽くしたかな？と思われる「1から10までの総和をループを使わずに書く」ですが、
せっかくなのでT-SQLでカーソルを使わずに表現してみました。

基本的に既出の計算方法だけれど、こういう時になんとなくいつも蚊帳の外に追いやられてる気がする

＞SQLもたまにはまぜて～

ということで。

--まずはデータ準備
DECLARE @DATA TABLE(value int)
INSERT INTO @DATA(value) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

--列挙w
SELECT
	l1.value + l2.value + l3.value + l4.value + l5.value 
	+ l6.value + l7.value + l8.value + l9.value + l10.value
FROM @DATA l1 
INNER JOIN @DATA l2  ON l1.value =  l2.value - 1
INNER JOIN @DATA l3  ON l2.value =  l3.value - 1 
INNER JOIN @DATA l4  ON l3.value =  l4.value - 1
INNER JOIN @DATA l5  ON l4.value =  l5.value - 1
INNER JOIN @DATA l6  ON l5.value =  l6.value - 1
INNER JOIN @DATA l7  ON l6.value =  l7.value - 1
INNER JOIN @DATA l8  ON l7.value =  l8.value - 1
INNER JOIN @DATA l9  ON l8.value =  l9.value - 1
INNER JOIN @DATA l10 ON l9.value = l10.value - 1;

--再帰クエリ
WITH Calc(value, sumValue) AS
(
  SELECT value, value FROM @DATA WHERE value = (SELECT MIN(value) FROM @DATA)
  UNION ALL
  SELECT L.value, C.sumValue + L.value
  FROM @DATA L JOIN Calc C ON L.value - 1 = C.value  
)
SELECT sumValue FROM Calc WHERE value = (SELECT MAX(value) FROM @DATA)
--SELECT MAX(sumValue) FROM Calc でもOK

--最後は素直に
SELECT (MAX(value) + MIN(value)) * COUNT(value) / 2 FROM @DATA

いじょ～

[SQL Server]照合順序と識別子

Route（佐久間　竜也） — Thu, 18 Sep 2008 08:12:00 GMT

来週には遅い夏休みを取れる状況になったので更新☆
もう２ヶ月も経ってしまいましたが、７月のLTで触れた照合順序と識別子について文字にします～（今更？）

今回の環境

Windows Vista Ultimate 32bit
SQL Server 2008 Developer Edition

※今回は右記の４つのインスタンスを１つのサーバーグループに登録し、
　それぞれのmasterデータベースに対してクエリを実行しています。

インスタンス名	照合順序
SQL2008CIAI	Japanese_CI_AI
SQL2008CIAS	Japanese_CI_AS
SQL2008CSAI	Japanese_CS_AI
SQL2008CSAS	Japanese_CS_AS

照合順序によって結果が変わるクエリ

７月に紹介したクエリとその実行結果は次のような感じでした。
まずは①と②を見てください。

[クエリ①]

DECLARE @つづく NVARCHAR(2) = 'ほげ'
SELECT @づつく AS HOGE

[結果]

VISTA-NOTE\SQL2008CIAS(VISTA-NOTE\Route): メッセージ 137、レベル 15、状態 2、行 2
スカラ変数 "@づつく" を宣言してください。

VISTA-NOTE\SQL2008CSAS(VISTA-NOTE\Route): メッセージ 137、レベル 15、状態 2、行 2
スカラ変数 "@づつく" を宣言してください。

サーバー名               HOGE
------------------------ -----
VISTA-NOTE\SQL2008CIAI   ほげ

VISTA-NOTE\SQL2008CIAI(VISTA-NOTE\Route): (1 行処理されました)

サーバー名               HOGE
------------------------ -----
VISTA-NOTE\SQL2008CSAI   ほげ

VISTA-NOTE\SQL2008CSAI(VISTA-NOTE\Route): (1 行処理されました)

[クエリ②]

DECLARE @つつつつつつ BIT = 0
SELECT	@つつつつつつ AS A, @づづづづづづ AS B, @つづつづつづ AS C, 
	@っっっっっっ AS D, @つっつっつっ AS E

SET	@っっっっっっ = 1

SELECT	@つつつつつつ AS A, @づづづづづづ AS B, @つづつづつづ AS C, 
	@っっっっっっ AS D, @つっつっつっ AS E

[結果]

VISTA-NOTE\SQL2008CIAS(VISTA-NOTE\Route): メッセージ 137、レベル 15、状態 2、行 2
スカラ変数 "@づづづづづづ" を宣言してください。
VISTA-NOTE\SQL2008CIAS(VISTA-NOTE\Route): メッセージ 137、レベル 15、状態 1、行 5
スカラ変数 "@っっっっっっ" を宣言してください。
VISTA-NOTE\SQL2008CIAS(VISTA-NOTE\Route): メッセージ 137、レベル 15、状態 2、行 7
スカラ変数 "@づづづづづづ" を宣言してください。

VISTA-NOTE\SQL2008CSAS(VISTA-NOTE\Route): メッセージ 137、レベル 15、状態 2、行 2
スカラ変数 "@づづづづづづ" を宣言してください。
VISTA-NOTE\SQL2008CSAS(VISTA-NOTE\Route): メッセージ 137、レベル 15、状態 1、行 5
スカラ変数 "@っっっっっっ" を宣言してください。
VISTA-NOTE\SQL2008CSAS(VISTA-NOTE\Route): メッセージ 137、レベル 15、状態 2、行 7
スカラ変数 "@づづづづづづ" を宣言してください。

サーバー名               A     B     C     D     E
------------------------ ----- ----- ----- ----- -----
VISTA-NOTE\SQL2008CIAI   0     0     0     0     0

VISTA-NOTE\SQL2008CIAI(VISTA-NOTE\Route): (1 行処理されました)

サーバー名               A     B     C     D     E
------------------------ ----- ----- ----- ----- -----
VISTA-NOTE\SQL2008CIAI   1     1     1     1     1

VISTA-NOTE\SQL2008CIAI(VISTA-NOTE\Route): (1 行処理されました)

サーバー名               A     B     C     D     E
------------------------ ----- ----- ----- ----- -----
VISTA-NOTE\SQL2008CSAI   0     0     0     0     0

VISTA-NOTE\SQL2008CSAI(VISTA-NOTE\Route): (1 行処理されました)

サーバー名               A     B     C     D     E
------------------------ ----- ----- ----- ----- -----
VISTA-NOTE\SQL2008CSAI   1     1     1     1     1

VISTA-NOTE\SQL2008CSAI(VISTA-NOTE\Route): (1 行処理されました)

クエリ①もクエリ②も宣言されていない変数が使われているので一見エラーが返ってきそうですが、
実際に走らせてみるとSQL2008CIAIとSQL2008CSAIで正常に処理されています。

さて、なぜでしょう？

上記では４つのインスタンスのmasterデータベースに対して同じクエリを実行しました。
異なるのはそれぞれのインスタンスの照合順序です。

照合順序というと一般に並べ替えや文字列比較時に参考にされる条件というイメージですが、
それに加えてデータベース名やテーブル名・ストアド・変数などの各オブジェクトの名称（識別子）の
名前付けルールにも影響しています。これが今回の挙動の違いの原因です。

識別子の照合順序

識別子の照合順序はその識別子が定義されているレベルによって異なります。
ログイン名やデータベース名などのインスタンスで定義されているオブジェクトとテーブル変数や一時テーブルなどの
データベースに依存しないオブジェクトの識別子にはインスタンスの既定の照合順序が適用され、
テーブル名やビューなどのデータベース内で定義されているオブジェクトの識別子については
データベースの既定の照合順序が適用されます。
※あくまで識別子のお話です。特にテーブル変数の中身は違うので要注意～！！　（←これは後日）

クエリ①を再び例に挙げると、

DECLARE @つづく NVARCHAR(2) = 'ほげ'

この部分で「@つづく」という識別子の変数を宣言し、'ほげ'を入れました。
変数はインスタンスレベルのオブジェクトなので、この識別子にはインスタンスの既定の照合順序が適用されます。

次に

SELECT @づつく AS HOGE

で「@づつく」という識別子を使います。
ここでの問題は識別子がインスタンスから受け継いだ照合順序において「つ」と「づ」が区別されるかどうかです。
それではインスタンスと同じ照合順序のmasterデータベースで確認してみましょう。

[クエリ③]

SELECT CASE WHEN 'つづ' = 'づつ' THEN '○' ELSE '×' END AS 'つづ = づつ'
     , CASE  WHEN 'つ' = 'づ' THEN '○' ELSE '×' END AS 'つ = づ'

[結果]

サーバー名               つづ = づつ  つ = づ
------------------------ ------------ --------
VISTA-NOTE\SQL2008CIAS   ×           ×

VISTA-NOTE\SQL2008CIAS(VISTA-NOTE\Route): (1 行処理されました)

サーバー名               つづ = づつ  つ = づ
------------------------ ------------ --------
VISTA-NOTE\SQL2008CSAS   ×           ×

VISTA-NOTE\SQL2008CSAS(VISTA-NOTE\Route): (1 行処理されました)

サーバー名               つづ = づつ  つ = づ
------------------------ ------------ --------
VISTA-NOTE\SQL2008CIAI   ○           ○

VISTA-NOTE\SQL2008CIAI(VISTA-NOTE\Route): (1 行処理されました)

サーバー名               つづ = づつ  つ = づ
------------------------ ------------ --------
VISTA-NOTE\SQL2008CSAI   ○           ○

VISTA-NOTE\SQL2008CSAI(VISTA-NOTE\Route): (1 行処理されました)

アクセントを区別するJapanese_CI_ASとJapanese_CS_ASでは「×」になり、
アクセントを区別しないJapanese_CI_AIとJapanese_CS_AIで「○」になりました。
この結果の違いは「つ」と「づ」のアクセントが異なるためですね。

つまり、クエリ①がSQL2008CIAIとSQL2008CSAIで実行できるのはアクセントを区別しない、
「@つづく」と「@づつく」が区別されない照合順序だからということです。

次は、上記を踏まえて識別子が大文字か小文字かで結果が変わる例。

[クエリ④]

SELECT name, modify_date, 'servers' AS 識別子 FROM sys.servers
SELECT name, modify_date, 'Servers' AS 識別子 FROM sys.Servers --頭のSが大文字

[結果]

サーバー名               name                     modify_date             識別子
------------------------ ------------------------ ----------------------- -------
VISTA-NOTE\SQL2008CSAS   VISTA-NOTE\SQL2008CSAS   2008-08-16 22:25:05.373 servers

VISTA-NOTE\SQL2008CSAS(VISTA-NOTE\Route): (1 行処理されました)

VISTA-NOTE\SQL2008CSAS(VISTA-NOTE\Route): メッセージ 208、レベル 16、状態 1、行 2
オブジェクト名 'sys.Servers' が無効です。


サーバー名               name                     modify_date             識別子
------------------------ ------------------------ ----------------------- -------
VISTA-NOTE\SQL2008CSAI   VISTA-NOTE\SQL2008CSAI   2008-09-12 15:51:47.300 servers

VISTA-NOTE\SQL2008CSAI(VISTA-NOTE\Route): (1 行処理されました)

VISTA-NOTE\SQL2008CSAI(VISTA-NOTE\Route): メッセージ 208、レベル 16、状態 1、行 2
オブジェクト名 'sys.Servers' が無効です。


サーバー名               name                     modify_date             識別子
------------------------ ------------------------ ----------------------- -------
VISTA-NOTE\SQL2008CIAS   VISTA-NOTE\SQL2008CIAS   2008-08-13 04:07:31.863 servers

VISTA-NOTE\SQL2008CIAS(VISTA-NOTE\Route): (1 行処理されました)

サーバー名               name                     modify_date             識別子
------------------------ ------------------------ ----------------------- -------
VISTA-NOTE\SQL2008CIAS   VISTA-NOTE\SQL2008CIAS   2008-08-13 04:07:31.863 Servers

VISTA-NOTE\SQL2008CIAS(VISTA-NOTE\Route): (1 行処理されました)


サーバー名               name                     modify_date             識別子
------------------------ ------------------------ ----------------------- -------
VISTA-NOTE\SQL2008CIAI   VISTA-NOTE\SQL2008CIAI   2008-08-16 21:12:59.790 servers

VISTA-NOTE\SQL2008CIAI(VISTA-NOTE\Route): (1 行処理されました)

サーバー名               name                     modify_date             識別子
------------------------ ------------------------ ----------------------- -------
VISTA-NOTE\SQL2008CIAI   VISTA-NOTE\SQL2008CIAI   2008-08-16 21:12:59.790 Servers

VISTA-NOTE\SQL2008CIAI(VISTA-NOTE\Route): (1 行処理されました)

大文字小文字を区別するとJapanese_CS_ASとJapanese_CS_AIではServersの頭のSが大文字の文で
「オブジェクト名 'sys.Servers' が無効です。」というエラーになり、
大文字小文字を区別しないJapanese_CI_ASとJapanese_CI_AIではどちらも実行されました。

SQL Server 2008のManagement Studioにはインテリセンスの機能が追加されたので
識別子の扱いを意識することなくただ表示された候補の中から選択すればよい状態にもなりえますが、
照合順序次第でクエリが動くか動かないかも変わるんだよってことをできれば頭の片隅に置いといてくだされ～。

この照合順序と識別子の関係は黄色いご本とかにも一応載っていますが、
ほんの数行さらっと触れられてるだけなので印象に残らなそうですし。

Japanese_CI_ASのインスタンス及びDBを使用していてクエリをプログラム中でゴリゴリ書いているシステムを
バイナリ照合順序のサーバーに移して動かしてみると…思いの外、コケたりします。
ストアドを使っていたとしてもパラメータ名の大文字小文字が違うって怒られたり。。。

そもそも照合順序の異なるサーバーで稼働させることは通常想定されてないでしょうから問題はないと思いますが。
（というよりそのシステムに最適な照合順序を選択されている。と願いたい）
試してみると面白いですよ。

今日書いたことのオフィシャルかつより詳細なことは下記を参照してください。

SQL Server 2008 オンラインブック

[SQL Server]認証モードの設定と変更方法いろいろ

Route（佐久間　竜也） — Thu, 12 Jun 2008 07:50:00 GMT

先日、Windows認証モードに設定された既定のインスタンスが入っている
ActiveDirectryなしの環境で使用するサーバーと出会いました。

そのままではちょっと困るので、認証モードを変更しようとManagement Studioを探してみるも入ってないw
いつもはManagement Studioで変更するけど、ない時どうすんべ？
そういや他の方法知らないや。。。

と思い、ちょいと調べてみたのでメモをアップしますー

※SQL Server 2008についてはSQL Server 2008 RC 0を使用しています。

認証モードの設定（インストール時）

認証モードを最初に設定するのはインストールのタイミングです。

１.GUIから

ウィザードに従ってWindows認証モードか混合モードかを選択します。

操作は見たまんまですね。
詳細は下記を参照してください。

SQL Server 2005 Books Online

SQL Server 2005 をインストールする方法 (セットアップ)

SQL Server 2008 オンラインブック

SQL Server 2008 をインストールする方法 (セットアップ)

２.コマンドプロンプトから

iniファイルを使用してコマンドプロンプトからインストールを実行する場合は、次のパラメータを使用します。

認証モード	パラメータ
Windows認証モード	なし
混合モード	SECURITYMODE=SQL

コマンドプロンプトからのインストールを実際に試したことはないので以上。
詳細は下記に丸投げ。（気が向いたらそのうちやってみ…るかもw）

SQL Server 2005 Books Online

コマンドプロンプトから SQL Server 2005 をインストールする方法

SQL Server 2008 オンラインブック

コマンドプロンプトから SQL Server 2008 をインストールする方法

認証モードの変更（インストール後）

１～４のいずれかの操作を行った後にインスタンスの再起動が必要です。

１.Management Studioからサーバーのプロパティを操作

一番簡単なのはManagement Studio上からサーバーのプロパティを開いて変更する方法です。

これは2005でも2008でも画面レイアウトまで含めてまったく同じ。
2008のドキュメントでは2005のものと比べてセキュリティメモの部分がちょこっと加筆された模様です。

SQL Server 2005 Books Online

サーバーの認証モードを変更する方法

SQL Server 2008 オンラインブック

サーバーの認証モードを変更する方法

２.SMOを利用して変更

SMOを利用する場合はServerクラスが持っているSettingsプロパティ（Settingsクラス）から
LoginModeを変更します。

認証モードを変更するサーバーがSQLServer2005のインスタンスであれば

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum

への参照を追加してください。
また、2008のインスタンスの場合は上記３つに加えて

Microsoft.SqlServer.Management.Sdk.Sfc

が必要です。

注）2005のSMOアセンブリを使用して2008のインスタンスに接続することはできません。
　　2008のインスタンスに接続する場合は、2005ではなく2008のアセンブリを参照するようにしてください。

using System;
using Microsoft.SqlServer.Management.Smo;
namespace ChangeLoginMode
{
    class Program
    {
        static void Main(string[] args)
        {
            Server Svr = new Server();
            //インスタンスを指定（ここではVM-XPProの既定のインスタンスを使用）
            Svr.ConnectionContext.ServerInstance = "VM-XPPro";
            try
            {
                //接続（既定ではWindows認証）
                Svr.ConnectionContext.Connect();
                if (Svr.Settings.LoginMode == ServerLoginMode.Integrated)
                {
                    //混合モードに変更
                    Svr.Settings.LoginMode = ServerLoginMode.Mixed;
                }
                else
                {
                    //Windows認証モードに変更
                    Svr.Settings.LoginMode = ServerLoginMode.Integrated;
                }
                //LoginModeの変更を反映
                Svr.Alter();
            }
            catch (Exception ex)
            {
                //例外処理は省略
            }
            finally
            {
                //開けたら閉める
                if (Svr.ConnectionContext.IsOpen)Svr.ConnectionContext.Disconnect();
            }
        }
    }
}

LoginModeに設定するServerLoginMode列挙体には次の４つのメンバが含まれます。
（※レジストリのパスについては４にて後述）

メンバ名	認証モード	レジストリの値
Integrated	Windows認証モード	1
Mixed	混合モード	2
Normal	(SQLServer認証)	0
Unknown	(不明)	-

BOLによるとNormalはSQLServer認証を示すとのこと。

SQLServerにはNormalとUnknownにあたる認証モードはないけれど、
コード上はLoginModeにNormalとUnknownを設定できるので、
どうなるのか試しにやってみた。

結果

LoginModeにNormalを設定するとレジストリの値が0になって
挙動としてはWindow認証モードのような感じになり、（内部で正しく動いているのかは不明…）
Unknownを設定するとSvr.Alter()の時点で例外（FailedOperationException）が発生しました。

コード上は設定できてもするなってお話ですね。

SQL Server 2005 Books Online

SQL Server 2008 オンラインブック

３.xp_instance_regwriteで設定

１と２の方法が裏で何をやってるのか？
Profilerでトレースしてみるとどちらも同じことしてんじゃんってことがわかります。

2008のEnterprise Evaluation Editionと2005のStandard Editionの既定のインスタンスと
2005のDeveloper及びExpress Editionの名前付きインスタンスでやってみたところ、
混合モードからWindows認証モードへ変更する際どれであっても同じ

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
        N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1

が使われていました。
混合モードにする時は最後の引数が「2」になります。

４.レジストリを直接書き換える

上記の１～３の方法はすべてサーバーに接続しないと行えない方法です。
しかし、結局のところレジストリに書かれているLoginModeの値で認証モードを切り替えているだけなので、
接続しなくてもそこを直接書き換えれば変更できます。（荒業w）

今回使用した環境ではそれぞれ次の場所にLoginModeの値がありました。
①～③は同じ仮想マシンにインストールした順番です。

①2008 Enterprise Evaluation Edition の既定のインスタンス
→HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer

②2005 Developer Edition の名前付きインスタンス
→HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

③2005 Express Edition の名前付きインスタンス
→HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer

認証モードはインスタンス毎の設定なので、
複数のインスタンスが存在する場合はそれぞれの場所に値が書き込まれています。

どうしてもレジストリを直接いじりたくてしょうがない人は、
目的のインスタンスのレジストリの場所を探して自己責任の範囲内でやってみてください。

オープン

Route（佐久間　竜也） — Sun, 25 May 2008 19:10:00 GMT

サブタイトルは今のところ未定。

デザイン等細かいことはそのうちそのうち。

PhotoLog以外のRoute-nオープンです。

当面はたぶん、SQLServerのメモが多くなるんじゃないかと思います。