【SQL Server】1~500までの連番のレコードを生成する

使う機会があるか分かりませんが、テストで大量のユニークなレコードが欲しい場合とかに役に立つかも…

with allNumber as (
	/*1~500までの連番のレコード生成*/
	select 1 as no
	union all
	select no + 1
	from allNumber
	where  no < 500
)

select
  no
from allNumber

/*再帰の上限がデフォルトで100までなので、上限無し(=0)に変更*/
OPTION ( MAXRECURSION 0 )

 

 

【SQL Server】ビット演算(AND,OR)を行う

/*
 1111111100000000 FF00(16進数)
 1110101011011011 60123(10進数)
--------------------------------------
 1110101000000000 59904(10進数) AND演算結果
 1111111111011011 65499(10進数) OR演算結果
 */
select
	CAST(0xFF00 as int) & 60123 as [AND演算],
	CAST(0xFF00 as int) | 60123 as [OR演算]

&(AND)と|(OR)の記号を利用する。

上記の例では、見やすいように16進数で記載する例も併せて載せている

 

【SQL Server】SQL Server への接続を確立しているときにネットワーク関連またはインスタンス固有のエラーが発生しました

エラーメッセージ

System.Data.SqlClient.SqlException: SQL Server への接続を確立しているときにネットワーク関連またはインスタンス固有のエラーが発生しました。サーバーが見つからないかアクセスできません。インスタンス名が正しいこと、および SQL Server がリモート接続を許可するように構成されていることを確認してください。 (provider: SQL Network Interfaces, error: 26 - 指定されたサーバーまたはインスタンスの位置を特定しているときにエラーが発生しました)

原因

そもそもIDやPWを間違えている場合なども考えられるが、今回はSQL Server上の「SQL Server Browser」サービスが起動していないことが原因だった。

SQL Server上のManagement Studioからだとこのサービスが起動していなくても接続できたので、気付きにくい。

 

 

【SQL Server】メールアドレスの@より前と後を分割して取得する

@の位置を取得して、その前後を取得すればOK。

/*サンプルテーブル*/
with sampleTable as (
	select 'hoge@example.com' as mailAddress union
	select 'hoge2@example.com' union
	select 'invalid_address'
)

select
	mailAddress as [メールアドレス],
	/*@より前の値を取得する*/
	substring(mailAddress, 0, charindex('@',mailAddress)) as [ローカルパート],
	/*@より後の値を取得する(末尾は適当に長くしている…)*/
	substring(mailAddress, charindex('@',mailAddress) + 1, 999) as [ドメインパート]
from
	sampleTable
where
	/*@が無いものはメールアドレスではないので除いておく*/
	mailAddress like '%@%'

 

【SQL Server2019】saでログインできない。

問題

SQL Server2019をデフォルトのままインストールを進めたところ、saでログインできなかった。

「ユーザー’sa’はログインできませんでした。(Microsoft SQL Server、エラー:18456)」エラーになる。

対処方法は以下。

saアカウントを有効にする

  1. Windows認証モードでSQL Server Management Studioへログイン(SQL Server 2019には同梱されていないので、Management Studioを追加でインストールする必要あり)
  2. オブジェクトエクスプローラーからセキュリティ→ログイン→saを右クリックしてプロパティをクリック
  3. 全般のパスワードを入力
  4. 状態のログインを有効に変更して「OK」をクリックして保存

認証モードで、SQL Server認証モードを追加

  1. オブジェクトエクスプローラーのサーバ名を右クリックし、プロパティをクリック。
  2. セキュリティのサーバー認証で「Windows認証モード」から「SQL Server認証モードとWindows認証モード」に変更して「OK」をクリックして保存
  3. オブジェクトエクスプローラーのサーバ名を右クリックし、「再起動」をクリック
  4. (マシン名)のMSSQL$SQLEXPRESSサービスを再起動しますか?というメッセージが表示されるので「はい」をクリックして再起動する。(このメッセージボックスがManagement Studioの裏に隠れてしまう場合があるので注意)

以上の点を実施することにより、saでログインできるようになった。

 

【SQL Server】複数行データをセミコロン区切りの1行に纏める

※SQL Server 2016にて確認

-- ダミーテーブル
with dummyData as (
	select 'AGroup' as code, 'A01' as child
	union 
	select 'AGroup' , 'A02'
	union
	select 'AGroup' , 'A03'
	union
	select 'BGroup' , 'B01'
	union 
	select 'BGroup' , 'B02'
	union
	select 'BGroup' , 'B03'
	union
	select 'CGroup' , 'C01'
)

select
	code,
	left(hoge.child, len(hoge.child) - 1) as child	--末尾のセミコロンを除く
from
	(
	select distinct
		dummy.code,
		(
			select 
				dummyData.child + ';' AS [text()]	--セミコロン区切りしたい列を指定
			from 
				dummyData
			where
				dummyData.code = dummy.code		--大元のfrom句で指定されているテーブルとJOIN
			for xml path ('')
		) as child
	from
		dummyData dummy
	) hoge

■実行結果

codevalue
AGroupA01;A02;A03
BGroup B01;B02;B03
CGroup C01

【SQL Server】セミコロン区切りの1行データを複数行に変換する

※SQL Server 2016にて確認

-- 1つのコードに複数のデータが入っているダミーテーブル
with dummyData as (
	select 'AGroup' as code, 'A01;A02;A03' as child
	union
	select 'BGroup' , 'B01;B02;B03'
)

select 
	code,	--元々のコード
	value	--childを;で分割した値が入る
from
	dummyData
	cross apply string_split(child, ';')	--セミコロンで区切る

■実行結果

codevalue
AGroupA01
AGroup A02
AGroup A03
BGroupB01
BGroup B02
BGroup B03

【SQL Server】invoke-sqlcmd で 重複する列名は~ エラーが発生する

■エラー内容

「invoke-sqlcmd : 重複する列名は、SQL PowerShell では許可されません。列を繰り返す場合、重複する列には Column_Name AS New_Name の形式で列の別名を使用します。」

■原因

invoke-sqlcmdの-InputFileで指定したSQLファイルの文字コードがSJISであったために、as句で付与した列別名が文字化けしてしまったために発生していた。

SQLファイルの文字コードをサクラエディタなどでUTF-8に修正したところ、エラーが解消された。