【SQL SERVER】 テーブルのコピー

SQL SERVERでデータを更新する時に簡易にテーブルデータのバックアップを取って置きたいことがあります。そんな場合は以下のようなSQL文を実行します。
私は日付やOLDをつけてコピーしています。

select into [DestinationTable] from [CopyTable]

DestinationTableをあらかじめ作成する必要は無くお手軽です。
ただし、CopyTableの行動とデータのみをコピーするため、キーやインデックス等のCONSTRAINT条件は付加されません。
あくまでも簡易バックアップなので。
必要がなくなったらごみになるので、コピーしたテーブルは削除しましょう。

以上、簡単なテーブルのコピーの開発備忘録でした。

【SQL Server】Alter Table 項目の追加・変更・削除

今日はSQL Serverネタです。
最近、Azure SQLの開発をしているのですが、SSMS(SQL Server Management studio)からテーブルの構造を更新できないのでデータ定義言語(Data Definition Language, DDL)で更新しています。

開発時はローカルのSQL Serverで全て行っていたのですが、いざAzure SQLにリリースという段階になるとローカルで修正して本番に移すという作業が出来ない状況になることがあります。
客先で桁あふれデータを見つけたりしてね。。。
そんな時、常日頃SSMSで行っていたテーブル構造の変更をDDLでやらなくてはいけないので、あれ?なんだったけ?ってなります。

ちなみに、Azure SQLをSSMSで行うことはSQL Server 2016のSSMSまではできませんでしたが、SQL Server 2016のSSMSでは可能になりました。この記事を書いている段階ではCTP3版ですが、問題なく編集できます。
非常に助かります。今まで当たり前にできてたことができないって、ほんと面倒に思えますから。
テーブル構造も早く変更できるようにしてもらえればありがたいのですが。。。

本題に戻ります。
忘れてしまっていたテーブル構造を変更するDDLですが、以下の通りです。
追加:Alert Table [table] add [column_name] [column_datatype](,[column_name] [column_datatype]);
変更:Alter Table [table] alter column [column_name] [column_datatype](,[column_name] [column_datatype]);
column名の変更:sp_rename @objname='[table].[old_column_name]’, @newname='[new_column_name]’, @objtype=’column’;
削除:Alter Table [table] drop column [column_name](,[column_name]);

こんな感じです。
その内SSMSで変更できるようになると思います。
本来、Azure SQLにテストリリースする段階では、仕様の変更など無いにこしたことなのですが、実データを流し込むと桁あふれが起こったり、エンドユーザが実画面を見て「こうだったらもっと便利なのに」ってフラグを追加して処理を分岐させたり、いろいろなケースでちょっと修正となることも多いと思います。
特に中小企業では業務設計も仕様確定も固まっていないことも多く、手戻りを覚悟して構築していくことも多々あるので。
この辺りの事前把握を行う構築スキルをもっと磨いていきたいです。

Alter TableのSQL Serverの備忘録まで。

【SQLServer】Windows10 SQL Server2014 外部接続 ファイアウォール設定

今日は、設定の備忘録です。
最近最近ノートPCに、Windows 10、Visual Studio 2015とSQL Server 2014をインストールして、出先でデモとか仕様確認をする際に開発中のアプリを動かして使用しています。

で、出先でデータスキーマを触ってしまうことも有って、開発マシンからノートPCのSQL Serverにアクセスしたくなることも多くて。。。
今回はそのWindows10上のSQL Server 2014に外部から接続する設定を書いておこうかと。まぁ、設定自体は今までと変わりないのですが、最近ブログを書く機会も少ないので^^;書いておこうかと。。。^^;;;

まずは、SQL Server Configuration Manager (SQL Server 構成マネージャー)を立ち上げます。
これって、SQL Server 2012と違ってまたスタートメニューから起動できるようになりましたね。ちょっと便利になって嬉しいです。

で、名前付きパイプかTCP/IP使う方を有効にします。私は面倒なので両方とも有効にしちゃいます。
ちなみにTCP/IPの既定のポート番号は1433ですが、ここをセキュリティの為に変更する人はここに見に来ませんよね?変える時はちょっと注意が必要なので。。。

で、次はfirewallに穴を空けてSQL Serverの通信が通るようにしてあげます。
windows 10 で穴を空けるのは初めてなので、画面のキャプチャーを12枚も撮ってしまいました。。。

SQLServerFireWallSetting01まずはWindows 10の設定画面を立ち上げます。

イーサネットをクリックして、更にWindowsファイアウォールをクリックします。

SQLServerFireWallSetting02

 

 

 

Windowsのファイアウォール設定画面が開きます。

詳細設定をクリックします。

 

SQLServerFireWallSetting03

 

ファイアウォールの設定が見られる画面が開きます。

受信の規則をクリックします。
新しい規則。。。をクリックします。

SQLServerFireWallSetting04

SQL Serverの通信を通る規則を作ります。
TCP/IPのポート1433で作っても良いし、プログラムを指定して作ってもOKです。
今回はプログラムを指定しています。

SQLServerFireWallSetting05

 

プログラムのパスを指定します。自力で書くには難しいので、参照をクリックしてsqlserverの本体プログラムを指定します。
参照をクリックしましょう。


SQLServerFireWallSetting06

 

 

SQL Serverのファイルの在りかを事前に確かめておくと良いですが、大体は「C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn」辺りに居ると思います。

SQL Serverをインストールする時に規定にするか覚えておくと探さなくて済みます。
ファイルをクリックして、開くをクリックします。

SQLServerFireWallSetting07
先ほど指定したパスが張り付いていると思います。
次へをクリックします。

 

 

SQLServerFireWallSetting08

 

 

ここら辺は自分の環境に合わせて設定してください。
私は開発環境なので特にセキュリティは意識していませんが、実務に使う設定なら気にする必要があるかもしれないので。

SQLServerFireWallSetting09

 

 

ここも同じですね。自分の環境に合わせて設定してください。

SQLServerFireWallSetting10

 

 

 

 

ここでは後で見た時にわかりやすく名前で登録しておきます。
私は何も考えず、SQL Servarとしています。
名前を入力したら完了をクリックします。


SQLServerFireWallSetting11

 

設定した規則が登録されているはずですので確認します。

 

 

 

後は、実際に他のPCから接続が確認出来れば、作業は完了です。
Windows10でSQLServer2014に外部から接続出来るようにファイアウォールを設定したシステム開発備忘録でした。

[SQL Server] nvarchar と varchar

nvarchar と varchar の使い分けしていますか?
もしくは、nchar と char の使い分けしていますか?

nchar および nvarchar は Unicode 文字列データを扱う UNICODE UCS-2 文字セットを使用する文字データ型です。
char および varchar は Unicode ではない固定長の文字列データを扱う文字データ型です。

nvarcharとvarcharの違い

上の説明だとわかったような、わからないような気がしますか?

ざっくり言うと nvarchar と varchar の違いは Unicode か Shift_JIS かっていうことですよね。全角2バイト、半角1バイトとかって言う古い概念が varchar で、文字は文字数だけっていうのが nvarchar で、その辺りの違いとも言えます。

特殊な環境で稼働させている場合を除いて、SQL Serverを使っている人は、おおよそOSにWindowsを使っていると思います。Windows自体は現在はJIS X 203(JIS2004)を使っていますが、その土台を支えるFrameworkはUnicode UTF-16 (Unicode Transformation Format、16 ビット エンコーディング形式) を使用しています。
どちらが親和性が高いかと言えば、Unicode かな、、、実際はどっちなんでしょうね?
nvarchar と varchar の変換は内部で自動で行ってくれるので、プログラマ的にはバイナリ―で比較するとかしない限りは無視しても問題ないレベルだと思うし。でも型を曖昧にした変なプログラムを書いちゃダメですけどね。。。

nvarchar と varcharどちらを使う?

じゃ、実際データベースの設計をする時どちらを使う?ってなると、悩むというか、どちらでも良いというか。。。
私はとりあえず最近の設計は全て nvarchar にしてます。文字数を数える時に全角2バイトとか半角1バイトって変換しなくて済むし。
でも、限られたスペースでギリギリに表示を考えている場合は半角なら何文字とか数える人が居ますよね?そういう人が設計に入ってくると文字数を数えて表示幅がどんだけだから。。。ってプログラムで分岐しますよね。そういう人が居るなら数えるのが便利な varcharかな。。。

歯切れがとても悪いのですが、結論はなんとなくnchar、nvarchar、ntextのUnicodeが扱える方が良いかな〜程度です。
明確な理由が無ければそうすると思います。
皆さんはどのように分けて使ってますか?

【SQL Server】カーソル CURSOR

今回はカーソルについてです。
基本的にデータベースのデータ操作を行う際はメモリIO、ディスクIOを抑えるため一括して行う方が効率良くデータ処理ができます。しかしながら、一括でデータ操作ができないケースも出てきます。
そんな時に活躍するのがカーソル(CURSOR)です。

カーソル CURSORとは

詳細な説明はSQL Serverのドキュメントで見て欲しいのですが、カーソル(CURSOR)はselectで抽出したデータに対して一行づつ取り出すことができる逐次処理ができる仕組み・機能です。
一行づつ取り出しができるので、その行に対して処理を書けることができます。一括で処置ができない、または一括では処理が重たくてレスポンスが悪くなるなどのケースに使用します。
カーソル CURSORの使い方

カーソル(CURSOR)の使い方は難しくありません。カーソル(CURSOR)のネストもできます。ただし、もともと複雑なことを対象に処理を行うことを考えるとトランザクション設計などはきちんとしないといけませんが。。。

カーソル(CURSOR)を使うには、カーソルの宣言を行います。カーソルの宣言にはデータを抽出するSQLも合わせて示すことになります。
また、抽出した際に抽出データを入れる変数も予め宣言しておかなくてはいけません。
その後、カーソル(CURSOR)をOPENして、データが無くなる(Fetchが失敗する)までループさせ一行づつ処理を行います。
データが無くなる(強制的にループを終了させる)った後は、カーソル(CURSOR)をCLOSEして、カーソル(CURSOR)をDEALLOCATEして終わりです。

DEALLOCATEを使ったSQL例

行っていることは、

  • 変数テーブルを作成
  • 適当にデータを入れる
  • 比較する為にテーブル内のデータ抽出表示
  • 抽出用の変数を宣言
  • カーソル(CURSOR)を宣言
  • カーソル(CURSOR)のOPEN
  • Fetch(一行取り出す)
  • while文でFetchが失敗してないか評価
  • Fetchできていたら、その行のポイントを前の行のポイントを加算して更新Fetch(一行取り出す)
  • Fetch(一行取り出す)・・・ループ
  • カーソル(CURSOR)をCLOSE
  • カーソル(CURSOR)をDEALLOCATE
  • 比較する為にテーブル内のデータ抽出表示
declare @tbl1 table(
	id int,
	kbn nchar(10) ,
	point int
)

declare @start_id as int
set @start_id=0

while @start_id < 10
	begin
		print @start_id
		insert into @tbl1 values(@start_id, @start_id % 3, @start_id * Rand() * 10)
		set @start_id = @start_id +1
	end

select * from @tbl1

declare @test_id int, @test_point int
declare @add_point int
set @add_point=100

--カーソル
declare test_cursor cursor for
select id, point from @tbl1 where kbn = 1 order by id
open test_cursor

fetch next from test_cursor into @test_id, @test_point

while @@FETCH_STATUS = 0
begin
	update @tbl1 set point+=@add_point where id=@test_id
	set @add_point=@test_point
	fetch next from test_cursor into @test_id, @test_point
end
close test_cursor
deallocate test_cursor

select * from @tbl1

実行結果
sql-result02

以上、カーソル CURSORの使い方まで

 

【SQL Server】算術演算子

今回はSQL Serverの算術演算子です。
SQLの仕事から離れて、また戻ってくるとMOD(余剰)の演算子を忘れています。

SQL SERVERの算術演算子

 

operator 演算子 意味
加算 + 加算
減算 減算
乗算 * 乗算
除算 / 除算
剰余 % 除算による整数の剰余を返します。 たとえば、12 % 5 の場合、12 を 5 で割ると余りは 2 なので、12 % 5 = 2 となります。

簡単なSQLで確かめてみます。

declare @tbl1 table(num1 int, num2 int)

insert into @tbl1 values(4, 2)
insert into @tbl1 values(5, 2)
insert into @tbl1 values(6, 2)
insert into @tbl1 values(7, 3)
insert into @tbl1 values(8, 3)

select num1, num2, num1 + num2 as '加算', num1 - num2  as '減算',num1 * num2 as '乗算',num1 / num2 as '割算',num1 % num2 as '剰余' from @tbl1

SQLの結果です。
sql-result01

以上、SQL SERVERの算術演算子でした。

【SQL Server】SQL Server Management Studioで上位1000行の選択の行数を変更する

SQL Server Management Studioを使っていて、テーブルを右クリックして出てくるポップアップメニューで「上位1000行の選択」って有りますよね?
1000行以上欲しい時もたまにあるんですが、そういう時は手でSQLを書いてました。まぁ、簡単なSQLなので苦にもならないというか。。。でも、変更する手段があったんです。

SQL Server Management Studio(SSMS)で上位1000行の選択の行数を変更する

ツール>オプションを選択すると、ポップアップウィンドウが開きます。
その中の項目に「SQL Server オブジェクト エクスプローラー」なるものが有ります。
そう、SSMSのオブジェク トエクスプローラーのオプションなんです。
SSMS01
拡大表示して見てもらえばわかる通り、ここで0を設定したら全件を返すようにもできます。あまりにデカい件数のテーブル開いてしまった時の為に、10万件くらいが良いのかも。その辺りはご自由に。

ちなみに、選択の件数だけじゃなく編集の件数もここで修正できます。編集の件数は多分必要ないと思うけど念のため。

以上、備忘録まで。

【SQL Server】windows 8.1のファイアウォールの例外設定

さきの投稿に続き、SQL Server 2012のリモート接続を許可する時のファイアウォールの例外設定方法を備忘録しておきます。
やることは単純にSQL Serverで使用するポートに穴を空けるだけなのですが、windows 8.1のファイアウォールの設定呼び出しをどこから?って迷ったので。。。

呼び出し先
FireWall201505141
まるで囲った「windows アプリケーションによるファイアウォールの許可」をクリック。
「許可されたアプリ」を開く。

次に例外を許可するアプリを選択するのですが、
FireWall201505142
まず、「設定の変更」をクリックし変更許可。
次に、表示されている許可されたアプリ一覧に挙がっていないので、「別のアプリの許可」をクリック。
「アプリの追加」を開く。

FireWall201505144
ここでSQL Server 2012を「参照」をクリックしてSQL Server 2012の実行ファイルを選んでやることで、アプリの追加の一覧に呼び出す。
※「参照」してSQL Server 2012の実行ファイルを選択する操作は割愛します。
一覧にSQL SERVER WINDOWSと表示されるので、選択して「追加」をクリック。

以上で、よそのPCからでもアクセス可能になります。

【SQL Server】SQL Server Configuration Managerが見つからない

SQL Serverネタです。

Windows8.1、SQL Server 2012の環境でSQL Server Configuration Managerを立ち上げようと思いアプリケーションを探したところすぐに見つかりませんでした。
そこで、備忘録です。

SQL Server 構成マネージャーは Microsoft 管理コンソール プログラムのスナップインであり、スタンドアロン プログラムではありません。そのため、Windows 8 を実行している場合、SQL Server 構成マネージャーはアプリケーションとして表示さないそうです。
SQL Server 構成マネージャーを立ち上げるには、「SQLServerManager11.msc」(SQL Server 2012 の場合) または「SQLServerManager10.msc」(SQL Server 2008 の場合) を検索に入力してManagement Saved Consoleを探します。

多分、検索結果に表示され(ると思い)ます。
クリックすれば、Microsoft Management Consoleが立ち上がりSQL Server Configuration Managerが使用できます。

以上、SQL Sever絡みの備忘録まで。

【SQL Server】DTSXでDB接続PASSWORDをConifgから参照させるように手動で修正する

DTSXでDB接続PASSWORDをConifgから参照させるように手動で修正する

手元の開発PCのSql Server Business Intelligence Development Studioのパッケージ構成ツールが動かなくなっていました。。。
SQL SERVER 2008やVisual studio 2008やVisual studio 2010などを入れてしまったので、何かおかしくなってしまったのかと思います。

Sql Server Business Intelligence Development Studio 2005で過去に作成したDTSXを外部のConfigを参照させるようにしたいため、手で書き換えてやることにしました。
その備忘録です。

1)まずは、外部のConfigファイルを参照できる権限を与えます。
DTSXを作った当初は、


<DTS:Property DTS:Name="EnableConfig">0</DTS:Property>

   になっています。
   そこで、


<DTS:Property DTS:Name="EnableConfig">-1</DTS:Property>

   のように値を修正します。

2)DBのPASSWORDを保存している箇所を修正します。
   今までのPASSWORDの保存方法でどのように格納されているかで変わってきますが、

   <DTS:Password DTS:Name="Password" Sensitive="1" Encrypted="1">********</DTS:Password>

   等々になっているとこを

   <DTS:Password DTS:Name="Password" Sensitive="1"></DTS:Password>

   にしてやります。

3)Gonfigファイルの在処を追加します。
   DTSIDの値は、ConnectionManagerあたりののDTSIDのコピーで私の環境では動きました。

   
<DTS:ConnectionManager></DTS:ConnectionManager>

   の次に追加します。

   <DTS:Configuration>
     <DTS:Property DTS:Name="ConfigurationType">1</DTS:Property>
     <DTS:Property DTS:Name="ConfigurationString">ここにファイルの在処を記述</DTS:Property>
     <DTS:Property DTS:Name="ConfigurationVariable"></DTS:Property>
     <DTS:Property DTS:Name="ObjectName">構成 </DTS:Property>
     <DTS:Property DTS:Name="DTSID">{ConnectionManagerのDTSIDをコピー}</DTS:Property>
     <DTS:Property DTS:Name="Description"></DTS:Property>
     <DTS:Property DTS:Name="CreationName"></DTS:Property>
    </DTS:Configuration>

4)Configファイルを作成し指定したフォルダに保存します。

   <DTS:ConnectionManager>

   で記述した

<DTS:Property DTS:Name="ObjectName">DB接続名1</DTS:Property>

を指定します。
   ファイル内容は以下のとおり。ファイル名は適当にSetting.xmlとかに。

  <?xml version="1.0"?>
  <DTSConfiguration>
    <Configuration ConfiguredType="Property" Path="\Package.Connections[DB接続名1].Properties[Password]" ValueType="String">
     <ConfiguredValue>PASSWORD</ConfiguredValue>
    </Configuration>
  </DTSConfiguration>

以上で、実行時に指定されたConfigファイルのPASSWORDを見に行くようになります。