メモ：T-SQL の TRIM を使って、前後の括弧とか囲ってる文字を取り除く

2017 から SQL Server にも
TRIM (Transact-SQL) - SQL Server | Microsoft Docs
が追加されてます。

それまでは、LTRIM (Transact-SQL) - SQL Server | Microsoft Docs、RTRIM (Transact-SQL) - SQL Server | Microsoft Docs を組み合わせるという面倒な事してたのが一発でいけるようになってます。

2017 より前

select rtrim(ltrim(N'  あいうえお  ')) -- あいうえお

2017 から

select trim(N'  あいうえお  ') -- あいうえお

で、TRIM はドキュメントを見ると空白以外にも指定した文字を取り除くことが出来ます。
なので、[] や ""で囲まれてるとかを、 いちいち長さ取って substring で先頭と最後の文字を取り除くみたいな事しなくてもOK。

declare @v nvarchar(100) = N'[あいうえお]'
select substring(@v, 2, len(@v) - 2) -- あいうえお
select trim('[]' from @v) -- あいうえお

構文がややキモイけど、便利になったのでOKってことで。

メモ：EF (Core) で IDbContextTransaction の Rollback は要る？

entity framework - DbContextTransaction Rollback - Stack Overflow

ちゃんと Dispose してたら SQL Server 相手にしてる分には無くても良さげ。
他の DB は実装次第なんでちゃんとやった方が良いけど、ちゃんとやるのめんどくさい。。*1

*1:TransactionScope が出るまで(.NET 1.1)は面倒なことやってたのに堕落…

メモ：Application Insights で SQL Server のクエリを勝手に取る設定が変わってた

知らんうちに変わってた。
Azure Application Insights における依存関係の追跡 - Azure Monitor | Microsoft Docs

SQL 呼び出しの場合、サーバーとデータベースの名前が常に収集され、収集された DependencyTelemetry の名前として保存されます。 "データ" という名称の追加フィールドがあります。これに完全な SQL クエリ テキストを含めることができます。 ASP.NET Core アプリケーションの場合は、次を使用して SQL テキスト コレクションをオプトインすることが必要になりました。

services.ConfigureTelemetryModule((module, o) => { module. EnableSqlCommandTextInstrumentation = true; });

なんか前は Azure SQL DB だけだけど勝手に取ってくれてたけど、これに変わったらローカルの SQL Server も取ってくれるようになってた。

進化してるんやねー。

再帰クエリで循環参照した時にどうなるのか？(SQL Server, PostgreSQL)

PostgreSQL で循環参照したらどうなるん？って聞かれたので。

PostgreSQL

with recursive d as (
select 1 as id, 4 as parentid
union 
select 2 as id, 1 as parentid
union 
select 3 as id, 2 as parentid
union 
select 4 as id, 3 as parentid
), cte as (
  select id, parentid, 1 as level from d where id = 1
  union all
  select d.id, d.parentid, cte.level + 1 as level
  from cte inner join d on cte.parentid = d.id
  -- where cte.level < 100 これないと無限に再帰する
)
select * from cte
where cte.level < 100 -- ここで条件書いても無限再帰は止まらない！！
 --limit 100 limit 付けてると無限に再帰するやつでも先頭 n 件だけで返ってくる

特に上限回数みたいなのは無いっぽい、リソースが尽きるまで回り続けるんかな？

7.6. LIMITとOFFSET
7.8. WITH問い合わせ（共通テーブル式）

ループするかどうか確信が持てない問い合わせをテストする有益な秘訣として、親問い合わせにLIMITを配置します。 例えば、以下の問い合わせはLIMITがないと永久にループします。

WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

これが動作するのは、PostgreSQLの実装が、実際に親問い合わせで取り出されるのと同じ数のWITH問い合わせの行のみを評価するからです。 この秘訣を実稼動環境で使用することは勧められません。 他のシステムでは異なった動作をする可能性があるからです。 同時に、もし外部問い合わせを再帰的問い合わせの結果を並べ替えたり、またはそれらを他のテーブルと結合するような書き方をした場合、動作しません。 このような場合、外部問い合わせは通常、WITH問い合わせの出力をとにかくすべて取り込もうとするからです。

SQL Server

ついでに SQL Server も。

with d as (
select 1 as id, 4 as parentid
union 
select 2 as id, 1 as parentid
union 
select 3 as id, 2 as parentid
union 
select 4 as id, 3 as parentid
), cte as (
  select id, parentid, 1 as level from d where id = 1
  union all
  select d.id, d.parentid, cte.level + 1 as level
  from cte inner join d on cte.parentid = d.id
  -- where cte.level < 100 これないと無限に再帰する
)
select /*top(100)*/ * from cte -- top が無いと再帰回数上限エラーが出る
where cte.level < 100 -- ここで条件書いても無限再帰は止まらない！！

SQL Server は再帰の上限回数が既定では 100 になってるので、100 回再帰した時点でエラー。
再帰回数の上限や上限変更方法は、
WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs *1
見てね。

再帰回数を1000回にする例だと…

with d as (
select 1 as id, 4 as parentid
union 
select 2 as id, 1 as parentid
union 
select 3 as id, 2 as parentid
union 
select 4 as id, 3 as parentid
), cte as (
  select id, parentid, 1 as level from d where id = 1
  union all
  select d.id, d.parentid, cte.level + 1 as level
  from cte inner join d on cte.parentid = d.id
)
select top(1000) * from cte
option (maxrecursion 1000)

後は、TOP (Transact-SQL) - SQL Server | Microsoft Docs と
ORDER BY 句 (Transact-SQL) - SQL Server | Microsoft Docs
は似たような動きするけど、このケースでは offset fetch はダメ。
offset fetch は order by 必須なので、並び替え中に再帰の上限に達してエラーになる。

SQL Server の Top は PostgreSQL の limit と同じで order by 無いと全データ見ないで返す感じになってるみたい。

まとめ

• 再帰クエリの外で条件書いても再帰の上限は変わらない
  • 再帰クエリ(共通テーブル式)の中で再帰の条件書いとこう！
• どうしても条件書けないときは limit / top で件数絞る！
  • order by はダメ。

*1:日本語は訳が酷いので英語の方が良さげ

メモ：Azure Cognitive Search デフォルトのアナライザー の ストップワード(1文字限定) を調べる

カスタムアナライザー使うのに ja.microsoft がどんなストップワード設定してるか確認用。
※複数文字は日本語なので取り合えずいいや。

雑いけど、ひらがな1文字ずつに分かれるテキスト渡して消えたやつがストップワードだ！って感じで。
テキストの分析 (Azure Cognitive Search REST API) | Microsoft Docs

{
  "text": "あ い う え お か き く け こ さ し す せ そ た ち つ て と な に ぬ ね の は ひ ふ へ ほ ま み む め も や ゆ よ ら り る れ ろ わ を ん が ぎ ぐ げ ご ざ じ ず ぜ ぞ だ ぢ づ で ど ば び ぶ べ ぼ ぱ ぴ ぷ ぺ ぽ",
  "analyzer": "ja.microsoft"
}

2/5(金) devblogラジオ vol.9 で DB の雑談します

明日ですが、
devblogラジオ vol.9 - connpass
に参加します。

ムッシュ と 兄貴 とお話しするの久々なのでめっちゃ楽しみ！

夜ですけどお時間あるかたは是非ご参加を～。

ScriptDom で サブクエリを CTE に書き換えるサンプル(叩き台)

だいぶ昔に書いた
SQL Server のバージョン毎の TSqlParser - お だ のスペース
にコメントが付いてたので雑にサンプル。

 yutap01

だいぶ昔の投稿についての質問になります。
よろしければお知恵を拝借したいのですが、

TSqlParserを使って、複雑なTSQLを複数のSQLに分解したいと考えています。
具体的には、SQL文から自動的にサブクエリをwith句として外に抽出するという処理なのですが、具体的な実装方法についてご教授いただけないでしょうか？

from で join とかするサブクエリは、
QueryDerivedTable Class (Microsoft.SqlServer.TransactSql.ScriptDom) | Microsoft Docs
っぽいです。 = や in のサブクエリは
ScalarSubquery Class (Microsoft.SqlServer.TransactSql.ScriptDom) | Microsoft Docs
なのかな？

CTE 作るとこまではサクッと出来たけど、元の From のサブクエリを消すのは面倒そうなので、手抜き。
サブクエリの中のサブクエリとかはゴリゴリやらないとダメかもね。
※エイリアスが被る可能性がありそう

select * from 
(select s.Id from (select Id from Table1) s) sub1
inner join 
(select s.Id from (select Id from Table2) s) sub2
on sub1.Id = sub2.Id

サンプルコード

雑なサンプルなので

• .NET Framework 4.7.2
• NuGet Gallery | Microsoft.SqlServer.TransactSql.ScriptDom 150.4897.1

使ってます。

using Microsoft.SqlServer.TransactSql.ScriptDom;
using System;
using System.Collections.Generic;
using System.IO;

namespace TSqlParser
{
    class Program
    {
        static void Main(string[] args)
        {
            var baseQuery = @"select
  [SubQuery1].[Id]
  , [SubQuery1].[Name]
  , [Table1].[Id]
  , [Table1].[Name]
from 
  ( select
      [Id]
      , [Name]
    from [Table2]
  ) SubQuery1 inner join [Table1] on 
    [SubQuery1].[Id] = [Table1].[Id]
  inner join [Table4] on 
    [Table1].[Id] = [Table4].[Id]
  inner join (
    select 
      [Id]
      , [DivId]
    from [Table5]
    where [EntryDate] >= '2021-01-01'
  ) [SubQuery2] on
    [Table1].[Id] = [SubQuery2].[Id]
where
  [Table4].[Name] like '%hoge%'
  and [SubQuery2].[DivId] > 2
order by
  [SubQuery1].[Id]";

            var parser = new TSql150Parser(false);
            IList<ParseError> errors;
            var parsed = parser.Parse(new StringReader(baseQuery), out errors);
            var v = new SubQueryVisitor();
            parsed.Accept(v);

            var cte = v.SelectStatement.WithCtesAndXmlNamespaces 
                ?? new WithCtesAndXmlNamespaces();

            foreach (var sub in v.SubQueries)
            {
                cte.CommonTableExpressions.Add(new CommonTableExpression()
                {
                    QueryExpression = sub.QueryExpression,
                    ExpressionName = sub.Alias
                });
                sub.QueryExpression = null;
            }
            
            v.SelectStatement.WithCtesAndXmlNamespaces = cte;            

            var generator = new Sql150ScriptGenerator();
            generator.GenerateScript(parsed, out var s);
            foreach (var sub in v.SubQueries)
            {
                s = s.Replace($"() AS [{sub.Alias.Value}]", $"[{sub.Alias.Value}]");
            }
            Console.WriteLine(s);            

            parser.Parse(new StringReader(s), out errors);
            Console.WriteLine("-- エラー件数 --");
            Console.WriteLine(errors.Count);
            Console.ReadKey();
        }
    }

    public class SubQueryVisitor : TSqlFragmentVisitor
    {
        public List<QueryDerivedTable> SubQueries { get; set; } 
            = new List<QueryDerivedTable>();
        public SelectStatement SelectStatement { get; set; }
        public override void ExplicitVisit(SelectStatement node)
        {
            SelectStatement = node;
            base.ExplicitVisit(node);
        }
        public override void ExplicitVisit(QueryDerivedTable node)
        {
            SubQueries.Add(node);
            // サブクエリのエイリアスを [] で囲ってない場合も [] で囲むようにする。(手抜きの Replace用に)
            node.Alias.QuoteType = QuoteType.SquareBracket;
            base.ExplicitVisit(node);
        }
    }
}