メモ：SELECT - INTO の指定先のテーブルの存在チェックはコンパイル時

SQL Server

1個のクエリ内で drop create で一時テーブル使い回ししたいなーと書いてたらエラーになったのでメモ。

こういうのがエラーになってクエリ自体実行されない。

drop table if exists #temptable
select [Name] into #temptable from sys.tables
select * from #temptable
drop table if exists #temptable

/* ↑で drop table してるのでクエリ実行時にエラー */
select * from #temptable
/*
error Msg 208, Level 16, State 0 
Invalid object name '#temptable'.
*/

/* ↑で drop table してても、こいつがあるとクエリの実行がされない！*/
select [Name] into #temptable from sys.tables
/*
error Msg 2714 Msg 2714, Level 16, State 1 
There is already an object named '#temptable' in the database.
*/

正解は2回目以降は truncate + insert select。

drop table if exists #temptable
select [Name] into #temptable from sys.tables
select * from #temptable

truncate table #temptable

select * from #temptable

insert into #temptable select [Name] into #temptable from sys.tables

メモ：SqlDataReader.GetColumnSchema は データ読まないので明らかに暗黙の型変換でエラーが出る場合でもセーフ

.NET SQL Server

タイトル長い…
まあ当然といえば当然なんですが、明らかにダメなやつでも通るとちょっとびっくりする。。

例：文字型の列に 日付型のパラメータ渡す。

using Microsoft.Data.SqlClient;
using System;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var conn = new SqlConnection(@"接続文字列～"))
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"select * from sys.columns 
where name = @from";
                    cmd.Parameters.Add(new SqlParameter(
                        "@from", System.Data.SqlDbType.DateTime) { Value = DateTime.Now });
                    using (var reader = cmd.ExecuteReader())
                    {
                        // データ読むまではエラーが出ない。
                        foreach (var cs in reader.GetColumnSchema())
                        {
                            Console.WriteLine(cs.ColumnName);
                            Console.WriteLine(cs.ColumnOrdinal);
                            Console.WriteLine(cs.DataTypeName);
                        }
                        // この↓実行するとエラーがでる。
                        while (reader.Read())
                        {
                            Console.WriteLine(reader.GetValue(0));
                        }
                    }
                }
            }
            Console.ReadKey();
        }
    }
}

データ読むとこんなエラー。

メッセージ 241、レベル 16、状態 1、行 1
文字列から日付と時刻、またはそのいずれかへの変換中に、変換が失敗しました。

まあよく考えると、このエラーってこういう時に出るやつで。

select * from [商品] where [文字型の商品コード] = 100

これ商品コードが全部数値に変換出来るだったら、たまたまエラーにならない。
ってのと同じ。
データ型の変換 (データベース エンジン) - SQL Server | Microsoft Docs
暗黙の型変換で上手いこと言ったらセーフってやつ。
※convert_implicit はパフォーマンス上よろしくないので、上手くいっても実はセーフじゃない。。

多分他の DataReader も同じだろうけど、試したのが SQL Server だったので、タイトルは SqlDataReader にしとく。

メモ：T-SQL ある範囲内での 小さい順に空き番を見つける

SQL Server

決められた範囲があって、その中で空いている最小の番号を取得するってクエリ。
例：40000～4000000 までの間で未使用の番号を少ない順に10個とる。

範囲用の表を作って存在しないデータ取得

with [範囲] as (
  select cast(40000 as bigint) as [番号]
  union all
  select [番号] + 1 from [範囲] where [番号] < 4000000
), [使用済] as (
  select [番号] from 
    (values (40001), (40003), (40004), 
      (40005), (40008), (40009), (40010)) as [t]([番号])
)
select top(10) [範囲].[番号]
from [範囲]
where not exists (
  select * from [使用済]
  where [使用済].[番号] = [範囲].[番号]
)
option (maxrecursion 0)

結果はこんなん
f:id:odashinsuke:20210819191504p:plain
実行プランを見ると、Nested Loops の 後に Top なので、サクッと終わる。
f:id:odashinsuke:20210819191528p:plain
これ option (maxrecursion 0) してるけど、実際には、10件取れるまでしかループしてないから、無くてもエラーにならない。
再帰クエリで循環参照した時にどうなるのか？(SQL Server, PostgreSQL) - お だ のスペース
で少し書いたけど、再帰クエリと Top 良い感じ。

データ増えるとどうなるかというと…

drop table if exists [使用済]
create table [使用済] (
  [番号] bigint
);
with cte as (
  -- わざと 40000, 40001 は空けてる
  select cast(40002 as bigint) as [番号] 
  union all
  select [番号] + 1 from [cte] where [番号] < 3999900
)
insert into [使用済] select [番号] from cte
option (maxrecursion 0)
;
insert into [使用済] values (3999905), (3999906), (3999910)
;

with [範囲] as (
  select cast(40000 as bigint) as [番号]
  union all
  select [番号] + 1 from [範囲] where [番号] < 4000000
)
select top(10) [範囲].[番号]
from [範囲]
where not exists (
  select * from [使用済]
  where [使用済].[番号] = [範囲].[番号]
)
option (maxrecursion 0)

f:id:odashinsuke:20210819191949p:plain
めっちゃ遅いし、40000, 40001 が入ってない。。

実行プランを見ると、Nested Loop じゃなくなったので、当然結果も違う。
ってことは order by を付けないとダメなんで件数少なくても全件ループしてしまうのでこのやり方じゃダメ。*1
※order by 付けなくてもこのデータ量だと、再帰のループが多すぎて遅すぎる！
f:id:odashinsuke:20210819192011p:plain
ってことで、別のやり方を。

2021/08/20 追記 範囲用のテーブルが事前にあったら？

Twitter でレスあったので試しましたが、
事前に範囲用のテーブルがあった場合は、挙げたケースの中では一番早いかな。
↑のが遅いのは再帰の回数多くてデータ作るところが問題な訳で、元からデータがあったら問題無し。

drop table if exists [範囲]
;
create table [範囲] (
  [番号] bigint not null primary key
)
;
with cte as (
  select cast(40000 as bigint) as [番号]
  union all
  select [番号] + 1 from cte where [番号] < 4000000
)
insert into [範囲] select [番号] from cte
option (maxrecursion 0)

select top(10) [範囲].[番号]
from [範囲] 
where not exists (
  select * from [使用済]
  where [使用済].[番号] = [範囲].[番号]
) 
option (maxrecursion 0)

範囲が可変の場合に、どこまで事前に持たせるかってのは考え所ではあるかな～。

window 関数使って前後のデータと比較する

正直、クエリだけでやるのは大変なので、
クエリで空き番を取得するための材料を取る方向に逃げます。

LEAD (Transact-SQL) - SQL Server | Microsoft Docs と LAG (Transact-SQL) - SQL Server | Microsoft Docs を使って

-- 取り合えず11件取っとけば、空き番10個あるなら事足りる。  
-- 11件なのは、先頭のデータが範囲の最小の時は、10件だと空き番10個取れない。。
select top(11) * from (
  select [番号]
    , lag([番号]) over (order by [番号]) as [前の番号]
    , lead([番号]) over (order by [番号]) as [次の番号]
  from [使用済]
) data_
where [番号] + 1 <> [次の番号] 
  or [次の番号] is null 
  or [前の番号] is null
order by [番号]

f:id:odashinsuke:20210819192530p:plain
さっきよりは大分マシだけど、試した環境で 6秒位掛かるのでやっぱ待てないかな？

空き番取得 + 先頭 + 最後

1クエリで取るの諦めて、空き番取るだけ + 先頭 + 最後だと

-- 空き番取る
select [空き開始番号], [空き終了番号] 
from (
  select [使用済].[番号] + 1 as [空き開始番号]
    , (
        select min([番号]) - 1 
        from [使用済] as [nest_] 
        where [nest_].[番号] > [使用済].[番号]
    ) as [空き終了番号]
  from [使用済] left join [使用済] as [cond_] 
    on [使用済].[番号] = [cond_].[番号] - 1
  where [cond_].[番号] is null
) as [data_]
where [空き終了番号] is not null
order by 1;
;
-- 先頭
select top(1) [番号] as [先頭] 
from [使用済] order by [番号]
;
-- 最後
select top(1) [番号] as [最後] 
from [使用済] order by [番号] desc
;

f:id:odashinsuke:20210819194105p:plain
クエリ3つ発行するけどこれが一番良さげ。

データ件数とパフォーマンスと実装のしやすさで好きなの選んだらいーんじゃないかな？
ちなみに再帰で範囲データを作ってないやつは、範囲外のデータがある可能性があるので、範囲の条件を付ける必要あるよ！

*1:最初のクエリはたまたま意図した結果が取れただけ！

Internal retry logic providers in SqlClient の動作確認

.NET SQL Server

SqlClient の内部再試行ロジック プロバイダー - ADO.NET Provider for SQL Server | Microsoft Docs
を試したのでログ貼っとく。

環境は

SqlClient での構成可能な再試行ロジック構成ファイル - ADO.NET Provider for SQL Server | Microsoft Docs
構成ファイルで試してるのは、Dapper 使うと、 構成ファイル位でしか SqlCommand に設定出来なさそうなので。
※SqlConnection を Wrap して DbConnection の実装を そのまま SqlConnection から返して、CreateCommand だけ、何か刺せるような 自作 Connection 作るなら別だけど面倒そう。

試行回数：5回、遅延時間：3秒、最長遅延時間：10秒 で

App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="SqlConfigurableRetryLogicCommand" 
             type="Microsoft.Data.SqlClient.SqlConfigurableRetryCommandSection, Microsoft.Data.SqlClient" />
    <section name="AppContextSwitchOverrides"
             type="Microsoft.Data.SqlClient.AppContextSwitchOverridesSection, Microsoft.Data.SqlClient"/>
  </configSections>
  <SqlConfigurableRetryLogicCommand retryMethod ="Create<Fixed/Incremental/Exponential/None のどれか>RetryProvider"
    numberOfTries ="5" deltaTime ="00:00:03" maxTime ="00:00:10"
    transientErrors ="<リトライするエラー番号 をカンマ区切り>"/>
  <AppContextSwitchOverrides 
    value="Switch.Microsoft.Data.SqlClient.EnableRetryLogic=true"/>
</configuration>

ログは該当の箇所のみ抜粋

Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateFixedRetryProvider

固定間隔なので、指定の間隔(今回は3秒)で。

<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 0).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:02.4230000' before the next retry number 1
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:1, upcoming delay:00:00:02.4230000, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:02.4230000' and run the action for retry number 1.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 1).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:02.7400000' before the next retry number 2
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:2, upcoming delay:00:00:02.7400000, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:02.7400000' and run the action for retry number 2.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 2).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:02.4800000' before the next retry number 3
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:3, upcoming delay:00:00:02.4800000, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:02.4800000' and run the action for retry number 3.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 3).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:03.1090000' before the next retry number 4
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:4, upcoming delay:00:00:03.1090000, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:03.1090000' and run the action for retry number 4.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 4).

<sc.SqlRetryLogic.TryNextInterval|INFO> Current retry (4) has reached the maximum attempts (total attempts excluding the first run = 4).
<sc.SqlRetryLogicProvider.CreateException|ERR|THROW> Exiting retry scope (exceeded the max allowed attempts = 5).
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
The number of retries has exceeded the maximum of 5 attempt(s).

Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateIncrementalRetryProvider

インクリメンタルなので、遅延時間ずつ増えていく感じで。
ただし、最長10秒にしてるので10秒を超えなかった。

<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 0).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:02.8490000' before the next retry number 1
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:1, upcoming delay:00:00:02.8490000, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:02.8490000' and run the action for retry number 1.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 1).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:05.9310000' before the next retry number 2
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:2, upcoming delay:00:00:05.9310000, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:05.9310000' and run the action for retry number 2.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 2).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:09.0170000' before the next retry number 3
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:3, upcoming delay:00:00:09.0170000, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:09.0170000' and run the action for retry number 3.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 3).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:08.4747851' before the next retry number 4
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:4, upcoming delay:00:00:08.4747851, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:08.4747851' and run the action for retry number 4.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 4).

<sc.SqlRetryLogic.TryNextInterval|INFO> Current retry (4) has reached the maximum attempts (total attempts excluding the first run = 4).
<sc.SqlRetryLogicProvider.CreateException|ERR|THROW> Exiting retry scope (exceeded the max allowed attempts = 5).
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
The number of retries has exceeded the maximum of 5 attempt(s).

Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateExponentialRetryProvider

指数関数的に増えるらしいので、最長時間を最大値の 2分 (120秒) に変更して確認。
3(31)、9(32)、27(33)、81(34) と増えるのかと思ったけど、そこまで急激じゃなかった。
まあ最長が120秒なのでそんなもんなんかな。

<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 0).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:03.4410000' before the next retry number 1
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:1, upcoming delay:00:00:03.4410000, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:03.4410000' and run the action for retry number 1.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 1).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:08.2770000' before the next retry number 2
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:2, upcoming delay:00:00:08.2770000, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:08.2770000' and run the action for retry number 2.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 2).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:24.9620000' before the next retry number 3
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:3, upcoming delay:00:00:24.9620000, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:24.9620000' and run the action for retry number 3.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 3).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:53.4450000' before the next retry number 4
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:4, upcoming delay:00:00:53.4450000, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:53.4450000' and run the action for retry number 4.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 4).

<sc.SqlRetryLogic.TryNextInterval|INFO> Current retry (4) has reached the maximum attempts (total attempts excluding the first run = 4).
<sc.SqlRetryLogicProvider.CreateException|ERR|THROW> Exiting retry scope (exceeded the max allowed attempts = 5).
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
The number of retries has exceeded the maximum of 5 attempt(s).

一応 最長時間を10秒のも貼っとく。10秒前で頭打ち。

<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 0).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:03.2150000' before the next retry number 1
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:1, upcoming delay:00:00:03.2150000, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:03.2150000' and run the action for retry number 1.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 1).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:09.0030000' before the next retry number 2
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:2, upcoming delay:00:00:09.0030000, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:09.0030000' and run the action for retry number 2.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 2).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:09.2335084' before the next retry number 3
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:3, upcoming delay:00:00:09.2335084, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:09.2335084' and run the action for retry number 3.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 3).

<sc.SqlRetryLogic.TryNextInterval|INFO> Next gap time will be '00:00:09.9630303' before the next retry number 4
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Running the retrying event.
<sc.SqlConfigurableRetryLogicLoader.OnRetryingEvent|INFO>: Default configurable retry logic for SqlCommand object. attempts count:4, upcoming delay:00:00:09.9630303, Last exception:<～>
<sc.SqlRetryLogicProvider.ApplyRetryingEvent|INFO> Wait '00:00:09.9630303' and run the action for retry number 4.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogicProvider.ExecuteAsync<TResult>|INFO> Found an action eligible for the retry policy (retried attempts = 4).

<sc.SqlRetryLogic.TryNextInterval|INFO> Current retry (4) has reached the maximum attempts (total attempts excluding the first run = 4).
<sc.SqlRetryLogicProvider.CreateException|ERR|THROW> Exiting retry scope (exceeded the max allowed attempts = 5).
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
The number of retries has exceeded the maximum of 5 attempt(s).

Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateNoneRetryProvider

None なのでリトライはしない。
ただ None の時は、Provider 生成の箇所のログが面白いので貼っとく。

<sc.SqlConfigurableRetryLogicManager.CommandProvider|INFO> Requested the CommandProvider value.
<sc.AppConfigManager.FetchConfigurationSection|INFO>: Unable to load custom `SqlConfigurableRetryLogicConnection`. Default value of `T` type returns.
<sc.AppConfigManager.FetchConfigurationSection|INFO> Successfully loaded the configurable retry logic settings from the configuration file's section 'SqlConfigurableRetryLogicCommand'.
<sc.SqlConfigurableRetryLogicLoader.CreateRetryLogicProvider|INFO> Entry point.
<sc.SqlConfigurableRetryLogicLoader.CreateRetryLogicProvider|INFO> Successfully created a SqlRetryLogicOption object to use on creating a retry logic provider from the section 'SqlConfigurableRetryLogicCommand'.
<sc.SqlConfigurableRetryLogicLoader.ResolveRetryLogicProvider|INFO> Entry point.
<sc.SqlConfigurableRetryLogicLoader.LoadType|INFO> Entry point.
<sc.SqlConfigurableRetryLogicLoader.LoadType|INFO> Couldn't resolve the requested type by ''; The internal `Microsoft.Data.SqlClient.SqlConfigurableRetryFactory` type is returned.
<sc.SqlConfigurableRetryLogicLoader.LoadType|INFO> Exit point.
<sc.SqlConfigurableRetryLogicLoader.CreateInstance|INFO> Entry point.
<sc.SqlConfigurableRetryLogicLoader.CreateInstance|INFO> The given type `SqlConfigurableRetryFactory` infers as internal `Microsoft.Data.SqlClient.SqlConfigurableRetryFactory` type.
<sc.SqlConfigurableRetryLogicLoader.CreateInstance|INFO> The `Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateNoneRetryProvider()` method has been discovered as the `CreateNoneRetryProvider` method name.
'netcore5.exe' (CoreCLR: clrhost): 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\5.0.7\System.Reflection.Metadata.dll' が読み込まれました。シンボルの読み込みをスキップしました。モジュールは最適化されていて、デバッグ オプションの [マイ コードのみ] 設定が有効になっています。
'netcore5.exe' (CoreCLR: clrhost): 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\5.0.7\System.Collections.Immutable.dll' が読み込まれました。シンボルの読み込みをスキップしました。モジュールは最適化されていて、デバッグ オプションの [マイ コードのみ] 設定が有効になっています。
<sc.SqlConfigurableRetryLogicLoader.CreateRetryLogicProvider|INFO> System.Exception: Exception occurred when running the `Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateNoneRetryProvider()` method.
 ---> System.InvalidOperationException: Failed to create SqlRetryLogicBaseProvider object because of invalid CreateNoneRetryProvider's parameters.
The function must have a paramter of type 'SqlRetryLogicOption'.
   at Microsoft.Data.SqlClient.SqlConfigurableRetryLogicLoader.PrepareParamValues(ParameterInfo[] parameterInfos, SqlRetryLogicOption option, String retryMethod)
   at Microsoft.Data.SqlClient.SqlConfigurableRetryLogicLoader.CreateInstance(Type type, String retryMethodName, SqlRetryLogicOption option)
   at Microsoft.Data.SqlClient.SqlConfigurableRetryLogicLoader.ResolveRetryLogicProvider(String configurableRetryType, String retryMethod, SqlRetryLogicOption option)
   --- End of inner exception stack trace ---
   at Microsoft.Data.SqlClient.SqlConfigurableRetryLogicLoader.ResolveRetryLogicProvider(String configurableRetryType, String retryMethod, SqlRetryLogicOption option)
   at Microsoft.Data.SqlClient.SqlConfigurableRetryLogicLoader.CreateRetryLogicProvider(String sectionName, ISqlConfigurableRetryConnectionSection configSection)
<sc.SqlConfigurableRetryLogicLoader.CreateRetryLogicProvider|INFO> Due to an exception, the default non-retriable logic will be applied.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.
<sc.SqlRetryLogic.RetryCondition|INFO> (retry condition = 'True') Avoids retry if it runs in a transaction or is skipped in the command's statement checking.

Provider 生成時に例外が出て、例外が出た時は、default の non-retriable logic を 適用してる。
例外が出るのは、構成ファイルで指定するメソッドは、第一引数に SqlRetryLogicOption を受け取る必要があるけど、CreateNoneRetryProvider は引数無しだから。

https://github.com/dotnet/SqlClient/blob/a51a67453c27c81816d90e15b6100c308108840c/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/Reliability/SqlConfigurableRetryLogicLoader.cs#L162-L178

if (type == typeof(SqlConfigurableRetryFactory) || type == null)
{
    SqlClientEventSource.Log.TryTraceEvent("<sc.{0}.{1}|INFO> The given type `{2}` infers as internal `{3}` type."
                                            , TypeName, methodName, type?.Name, typeof(SqlConfigurableRetryFactory).FullName);
    MethodInfo internalMethod = typeof(SqlConfigurableRetryFactory).GetMethod(retryMethodName);
    if (internalMethod == null)
    {
        throw new InvalidOperationException($"Failed to resolve the '{retryMethodName}' method from `{typeof(SqlConfigurableRetryFactory).FullName}` type.");
    }


    SqlClientEventSource.Log.TryTraceEvent("<sc.{0}.{1}|INFO> The `{2}.{3}()` method has been discovered as the `{4}` method name."
                                            , TypeName, methodName, internalMethod.ReflectedType.FullName, internalMethod.Name, retryMethodName);
    object[] internalFuncParams = PrepareParamValues(internalMethod.GetParameters(), option, retryMethodName);
    SqlClientEventSource.Log.TryTraceEvent("<sc.{0}.{1}|INFO> Parameters are prepared to invoke the `{2}.{3}()` method."
                                            , TypeName, methodName, internalMethod.ReflectedType.FullName, internalMethod.Name);
    return internalMethod.Invoke(null, internalFuncParams);
}

https://github.com/dotnet/SqlClient/blob/a51a67453c27c81816d90e15b6100c308108840c/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/Reliability/SqlConfigurableRetryLogicLoader.cs#L215-L223

private static object[] PrepareParamValues(ParameterInfo[] parameterInfos, SqlRetryLogicOption option, string retryMethod)
{
    // The retry method must have at least one `SqlRetryLogicOption`
    if (parameterInfos.FirstOrDefault(x => x.ParameterType == typeof(SqlRetryLogicOption)) == null)
    {
        string message = $"Failed to create {nameof(SqlRetryLogicBaseProvider)} object because of invalid {retryMethod}'s parameters." +
            $"{Environment.NewLine}The function must have a paramter of type '{nameof(SqlRetryLogicOption)}'.";
        throw new InvalidOperationException(message);
    }

で、例外が発生したら、既定の動作で CreateNonRetryProvider を呼び出してる。

https://github.com/dotnet/SqlClient/blob/a51a67453c27c81816d90e15b6100c308108840c/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/Reliability/SqlConfigurableRetryLogicLoader.cs#L97-L100

SqlClientEventSource.Log.TryTraceEvent("<sc.{0}.{1}|INFO> Due to an exception, the default non-retriable logic will be applied.",
                                        TypeName, methodName);
// Return default provider if an exception occured.
return SqlConfigurableRetryFactory.CreateNoneRetryProvider();

なんで、明示的に CreateNoneRetryProvider を指定してたら、ログに例外出てるけど気にせずに。

メモ：Azure SDK .NET スレッドセーフ

.NET Microsoft Azure

.NET Azure SDK Design Guidelines | Azure SDKs

✅ DO be thread-safe. All public members of the client type must be safe to call from multiple threads concurrently.

Azure SDK for .NET でのスレッド セーフ | Microsoft Docs