日付

dateadd(Y,1,CURRENT_TIMESTAMP)
というのをネットで見つけた

実際にクエリ発行してみると

select dateadd(Y,1,CURRENT_TIMESTAMP) は 2018-04-03 20:52:17.357

明日になる・・Yって何?
では
select format(CURRENT_TIMESTAMP,’Y’) は 2018年4月

うーむ
調べるとYはdayofyaer の略で年始からの日数

ここで謎だったVBAの
debug.Print format(now,”Y”) が 92 なのだが
これがSQLserverのdayofyearに当たることに気がついた。

ならば SQLserverの
select format(CURRENT_TIMESTAMP,’Y’) は 92 になってほしい所だが
なぜか
2018年4月

一時テーブルが消えるその前に

SQLserverの#テーブルは消えてしまうものですが
消えてしまう前になんとかフロントエンドのAccessに保存してしまう方法

SQLserver側で ストアド dbo.test
たとえば

select * into #table from other table
中略
–ストアドの最後に
select * from #table
end

Access側で
パススルークエリ,sqlは
dbo.test
これだけ書いて保存 Qtest

さらにテーブル作成クエリでQtestをソースにしてAccessのローカルテーブルに保存する

row_number関数でupdate

row_numberやdense_rankは便利ですが
すでに連番カラムがあるテーブルの連番を付け直すには

UPDATE table
SET
SeqColumn = Buf.NewSeq
FROM
(
SELECT key1,key2
ROW_NUMBER() OVER
(PARTITION BY key1, key2 ORDER BY sortColumn DESC) AS NewSeq
FROM table
) AS Buf
WHERE
table.key1 = Buf.key1
table.key2 = Buf.key2

こんなかんじですがちょっと難しいし覚えられない。その都度Google先生にお聞きしている。
CTE、これもちょっと難しいのですがこれを使うと

 With MyCTE as
(
select *,
ROW_NUMBER() over(partition by key1,key2 order by SortColumn desc) as NewSeq from Table
)
update MyCTE set SeqColumn=NewSeq;

ぐっと簡単になった
これならなんとか覚えられそう

空白パディング@SQLserver

0パディング(0fill)は以前書いたけど
空白パディング、スペースフィルです。
いろいろ方法はあると思うけど
文字列 ’12345′
10桁で頭に空白パディング
select concat(space(10-len(‘12345′)),’12345’)

SQLserverの一時テーブルへの接続は競合しないのか?

答えはしないです。
フロントAccessでDBアプリを開発中ですが不安になりました。
レコードセット取らずにcn.executeで直接#テーブルいじってますので・・
クライアント2台で試してみました。

cn.Execute “drop table if exists #no123”
cn.Execute “select カラム into #no123 from テーブル where カラム like ‘%ほにゃらら%'”
rs.Open “select * from #no123”, cn, adOpenKeyset, adLockOptimistic
rs.MoveFirst
Do Until rs.EOF
MsgBox rs!カラム
rs.MoveNext
Loop

一台で操作途中でもう一台から同操作
競合するならdropされるはずですが大丈夫でした。
このときSQLserverはどうしているのかというと

一時テーブルが2つ出来ていました。
これは便利です

rs.Rcordcountは必ずしも遅くなかった

rs.recordcountが遅いかもの件です
5万件ぐらいのテーブルで確認してみました。
where条件にもよるのでしょうけど

rs.Open “select id from テーブル where条件”, cn, adOpenStatic, adLockOptimistic
Debug.Print rs.RecordCount

rs.Open “select id from テーブル where条件”,cn, adOpenStatic, adLockOptimistic
rs.Move first
i = 1
Do Until rs.EOF
rs.MoveNext
i = i + 1
Loop
Debug.Print i

はrs.recordcountの2馬身ぐらいで勝ちでしたが

rs.Open “select id from テーブル where条件”, cn, adOpenStatic, adLockOptimistic
Debug.Print rs.RecordCount

rs.Open “select id from テーブル where条件”,cn, adOpenForwardOnly, adLockReadOnly
rs.Move first
i = 1
Do Until rs.EOF
rs.MoveNext
i = i + 1
Loop
Debug.Print i

はloopの圧倒的勝利でした。

rs.addnew rs.update など流れの中で件数取得ならrecordcount
件数取得だけが目的ならloopということかな?

文字

文字は難しい
SQLserverでカラムの型 nvarchar(76) 76は76バイト
全角一文字2バイトだから38文字はいるはず。
38文字以上入力してエラー時の挙動確認しようと思ったら
50,60文字入力してもエラーなし

どういうこと?

正解はnvarcharはunicode文字列 全角、半角区別なし
なのでnvarchar(76)は全角文字76文字入るということらしいです

string_agg と string_split

SQLserverでGroup concatみたいな、縦の列のCSVみたいなことは以前書いた様に
for xml path を使ってできていたのですが何度見ても難解で覚えられないクエリでした。

SQLserver2017 からはstring_agg関数で
string_agg(column,’,’) within group (order by Seqcolumn asc)
などと簡単にできる様になりました。
これならなんとか覚えられそうです。

これでCSVを作っておいて
select * from string_split(‘CSV’,’,’)
とかやれば配列みたいなこともできそうな・・
ついでに何番目の要素とかも返してくれる関数があるとありがたいのですが今のところなさそうです。
文字列操作でできるとは思いますが何万列とかになると負荷高すぎてだめかな、たぶん。
IDのあるテーブルにinsertすればよさげな気がする。

string_agg は文字列を返しますが
string_split テーブルで返ってきます。 

とかく日付は難しい

SQLserverでもMySQLでもVBAでも
2018/1/1を文字の様に’2018/1/1’とか”2018/1/1”とか書けば日付として認識してくれる
と思っていたけど日付の比較で・・

SQLserverで
select iif(‘2018/1/4’>’2018-1-1′,’true’,’false’)
はtrueと思っていたらfalseを返します。文字と認識?
もちろん
declare @ParamS datetime,@ParamE datetime
select @paramS=’2018-1-1′
select @ParamE=’2018/1/31′
select iif(‘2018/1/4′ between @paramS and @paramE,’true’,’false’)
ときちんと宣言代入すればtrueです。

あるいは
select iif(‘2018/1/4’>cast(‘2018-1-1′ as datetime),’true’,’false’)
はtrueです。
片方だけでも日付型にしてあげるともう一方も日付と見てくれるようです。

VBAでもMySQLでも同じ挙動でした。
MySQLにiif関数はないのでnullif関数で
select nullif(‘2018-1-4′,’2018/1/4’);
select nullif(‘2018-1-4’,cast(‘2018/1/4′ as datetime)); 
で確認
上は別物と見なされ’2018-1-4’
下は同一と見なされnull
です。

if exists

mysqlでもsqlserverでも
訂正 SQLserverのみ
MySQLはテーブルがあらかじめないとダメでした。

drop table if exists Table
select * into Table from otherT where ・・・

は便利と思うのですが同じことをAccessのローカルテーブルにしようと
VBAでcurrentproject.connection に
cn.execute “drop table if exists Table” とやっても通用しません

しばらく悩みましたが
on error resume next
docmd.setwarnings false
cn.execute “drop table Table”
docmd.setwarnings true
でとりあえずはなんとか