私の良き友人Aaron Bertrandは私にこの記事を書くよう促しました。 彼は時々私達が私達に明らかにようで、常にそれらの後ろの完全な物語を点検することを迷惑を掛けないとき当然のための事をいかに取るか私に T-SQLとの関連性は、特定のT-SQL機能について知っておくべきことをすべて知っていると仮定することがあり、ドキュメントをチェックしてそれ以上 この記事では、完全に見落とされることが多いT-SQL機能、または見落とされることが多いパラメータや機能をサポートするT-SQL機能について説明します。 見過ごされがちな独自のT-SQL gemの例がある場合は、この記事のコメントセクションでそれらを共有してください。この記事を読み始める前に、EOMONTH、TRANSLATE、TRIM、CONCATおよびCONCAT_WS、LOG、cursor変数、およびMERGE with OUTPUTのT-SQL機能について何を知っているかを自問してください。私の例では、TSQLV5というサンプルデータベースを使用します。 このデータベースを作成および移入するスクリプトはここにあり、そのER図はここにあります。
EOMONTHには2番目のパラメータがあります
EOMONTH関数はSQL Server2012で導入されました。 多くの人は、入力日付を保持するパラメータが1つだけサポートされており、入力日付に対応する月末の日付を返すだけだと考えています。
前月の終わりを計算するために、もう少し洗練された必要性を考えてみましょう。 たとえば、売上を照会する必要があるとします。Ordersテーブル、および前月の終わりに配置された返品注文。これを実現する1つの方法は、EOMONTH関数をSYSDATETIMEに適用して現在の月の月末の日付を取得し、DATEADD関数を適用して結果から月を減算することです。
EOMONTH ( start_date )
次のように、関数の第二のパラメータとして-1を指定するだけで、より簡単かつ自然にタスクを達成できます:
SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(SYSDATETIME(), -1);
TRANSLATEはREPLACEよりも単純なことがあります
多くの人がREPLACE関数とその仕組みに精通しています。 これは、入力文字列内のある部分文字列のすべての出現箇所を別の部分文字列に置き換える場合に使用します。 ただし、適用する必要がある複数の置換がある場合、REPLACEを使用すると少しトリッキーで、複雑な式が発生することがあります。
例として、スペイン語の書式設定を持つ数値を含む入力文字列@sが与えられているとします。 スペインでは、数千のグループの区切り文字としてピリオドを使用し、小数点区切り文字としてコンマを使用します。 ここでは、カンマは千のグループの区切り文字として使用され、ピリオドは小数点区切り文字として使用されます。
REPLACE関数の呼び出しを使用すると、ある文字または部分文字列のすべての出現箇所のみを別の文字に置き換えることができます。 2つの置換（ピリオドをカンマに、カンマをピリオドに）を適用するには、関数呼び出しをネストする必要があります。 トリッキーな部分は、REPLACE onceを使用してピリオドをコンマに変更し、結果に対してコンマをピリオドに変更すると、ピリオドだけになるということです。 それを試してみてください：
DECLARE @s AS VARCHAR(20) = '123.456.789,00'; SELECT REPLACE(REPLACE(@s, '.', ','), ',', '.');
次の出力が得られます。
123.456.789.00
REPLACE関数の使用に固執したい場合は、次のようになります。三つの関数呼び出しが必要です。 ピリオドを、通常はデータに表示できないことがわかっている中立文字（たとえば、~）に置き換えるもの。 別の結果に対して、すべてのコンマをピリオドに置き換えます。 一時的な文字（この例では~）のすべての出現をコンマで置き換える結果に対して別のもの。 ここでは完全な式です：
DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT REPLACE(REPLACE(REPLACE(@s, '.', '~'), ',', '.'), '~', ',');
それは長くて複雑な表現になります。 あなたが適用するより多くの代替品を持っていた場合はどうなりますか？多くの人々は、SQL Server2017がTRANSLATEと呼ばれる新しい関数を導入し、そのような置換を大幅に簡素化したことを認識していません。 関数の構文は次のとおりです。
TRANSLATE ( inputString, characters, translations )
2番目の入力（文字）は、置換する個々の文字のリストを含む文字列であり、3番目の入力（翻訳）は、ソース文字を置 これは当然のことながら、2番目と3番目のパラメータの文字数が同じでなければならないことを意味します。 この関数について重要なのは、置換ごとに別々のパスを実行しないことです。 もしそうであれば、REPLACE関数への2つの呼び出しを使用して示した最初の例と同じバグが発生する可能性があります。 その結果、取り扱い私たちに課せられた仕事がない-同じ系列。
DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT TRANSLATE(@s, '.,', ',.');
このコードを生成し、ご希望の出力:p>
123,456,789.00
それはかなりきちんとしています！
TRIMはLTRIM(RTRIM())よりも大きい
SQL Server2017では、TRIM関数のサポートが導入されました。 私自身も含めて、多くの人々は、最初はそれがLTRIM（RTRIM（input））への単純なショートカットに過ぎないと仮定しています。 ただし、ドキュメントを確認すると、実際にはそれよりも強力であることがわかります。
詳細に入る前に、次のタスクを検討してください：入力文字列@sが与えられた場合、先頭と末尾のスラッシュ（後方と前方）を削除します。 例として、@sに次の文字列が含まれているとします:必要な出力は次のとおりです。
//\\ remove leading and trailing backward (\) and forward (/) slashes \\//
出力は先頭と末尾のスペースを保持する必要があります。
remove leading and trailing backward (\) and forward (/) slashes
出力は先頭と末尾のスペースを保持する必要があることに注意してください。TRIMの完全な機能を知らなかった場合は、タスクを解決した可能性のある1つの方法があります。
DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRANSLATE(TRIM(TRANSLATE(TRIM(TRANSLATE(@s, ' /', '~ ')), ' \', '^ ')), ' ^~', '\/ ') AS outputstring;
解決策は、すべてのスペースをニュートラル文字（〜）に置き換え、スラッシュをスペースに置き換えるためにTRANSLATEを使用することから始まります。その後、trimを使用して、結果から先頭と末尾のスペースをトリムします。 この手順では、基本的に先頭と末尾のスラッシュをトリムし、元のスペースの代わりに~を一時的に使用します。 このステップの結果は次のとおりです。
\\~remove~leading~and~trailing~backward~(\)~and~forward~( )~slashes~\\
次のステップでは、TRANSLATEを使用してすべてのスペースを別の中立文字（^）に置き換え、後方スラ この手順では、基本的に、中間スペースの代わりに^を一時的に使用して、先頭と末尾の後方スラッシュをトリムします。 このステップの結果は次のとおりです:
~remove~leading~and~trailing~backward~( )~and~forward~(^)~slashes~
最後のステップでは、TRANSLATEを使用してスペースを後方スラッシュ、^を前方スラッシュ、~をスペースに置き換え、目的の出力を生成します。
remove leading and trailing backward (\) and forward (/) slashes
練習として、TRIMとTRANSLATEを使用できないsql Server2017以前の互換性のあるソリューションでこのタスクを解決してみてください。
SQL Server2017以降に戻って、ドキュメントをチェックしても、TRIMが最初に考えたものよりも洗練されていることを発見したでしょう。 関数の構文は次のとおりです:
TRIM ( string )
partからのオプションの文字を使用すると、入力文字列の先頭と末尾からトリミングしたい一つ以上の文字を指定することがで 私たちの場合、あなたがする必要があるのは、この部分として’/\’を指定することだけです。
DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRIM( '/\' FROM @s) AS outputstring;
これは以前の解決策と比較してかなり大幅に改善されています！しばらくの間T-SQLを使用していた場合、文字列を連結する必要があるときにNullを処理するのがどれほど厄介であるかを知っています。 例えば、検討の場所で録音したデータのための従業員のである。従業員のテーブル
SELECT empid, country, region, cityFROM HR.Employees;
このクエリを生成し、以下の出力:
empid country region city----------- --------------- --------------- ---------------1 USA WA Seattle2 USA WA Tacoma3 USA WA Kirkland4 USA WA Redmond5 UK NULL London6 UK NULL London7 UK NULL London8 USA WA Seattle9 UK NULL London
通知する従業員の地域では無関係と鎖領域で表される、NULLになります。 区切り文字としてカンマを使用して、場所の部分（国、地域、都市）を連結する必要があるが、NULL領域は無視する必要があるとします。 地域が関連している場合は、結果の形式を<coutry>,<region>,<city><country>,<city>にします。 通常、何かをNULLに連結すると、NULL結果が生成されます。 この動作を変更するには、CONCAT_NULL_YIELDS_NULLセッションオプションをオフにしますが、非標準の動作を有効にすることはお勧めしません。
あなたが知らの存在のCONCATとCONCAT_WS機能を使っていただろうを使用その他合体で置き換えるのにはNULLと空文字列にはこのように:
SELECT empid, country + ISNULL(',' + region, '') + ',' + city AS locationFROM HR.Employees;
ここでは、このクエリ:
empid location----------- -----------------------------------------------1 USA,WA,Seattle2 USA,WA,Tacoma3 USA,WA,Kirkland4 USA,WA,Redmond5 UK,London6 UK,London7 UK,London8 USA,WA,Seattle9 UK,London
SQL Server2012の機能CONCAT. この関数は、文字列入力のリストを受け取り、それらを連結し、そうしている間、Nullを無視します。 したがって、CONCATを使用すると、次のように解を単純化できます:それでも、関数の入力の一部として区切り文字を明示的に指定する必要があります。 私たちの生活をさらに簡単にするために、SQL Server2017ではCONCAT_WSと呼ばれる同様の関数が導入されました。 この関数を使用すると、解は次のようにさらに単純化されます:
SELECT empid, CONCAT_WS(',', country, region, city) AS locationFROM HR.Employees;
次のステップはもちろん この関数は空の入力を受け入れ、あなたの心を読んでどの要素を連結したいのかを自動的に把握します。 クエリは次のようになります:
SELECT empid, CONCAT_MR() AS locationFROM HR.Employees;
ログには第二のパラメータがあります
EOMONTH関数と同様に、多くの人はSQL Server2012対数の底を示します。 それ以前は、t-SQLでは、入力の自然対数を返す関数LOG(input)(定数eを基数として使用)と、10を基数として使用するLOG10(input)がサポートされていました。
LOG関数の2番目のパラメータの存在を認識していないため、Bがeと10以外の基底であるLogb（x）を計算したいとき、彼らはしばしば長い道のりをしました。 次の式に依存することができます。
例として、Log2(8)を計算するには、次の式に依存します。
T-SQLに変換すると、次の計算:/p>
DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x) / LOG(@b);
ログがベースを示す第二のパラメータをサポートしていること
DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x, @b);
カーソル変数
しばらくt-sqlで作業していたら、おそらくカーソルで作業する機会がたくさんありました。 ご存知のように、カーソルを操作するときは、通常、次の手順を使用します:
- カーソルを宣言する
- カーソルを開く
- カーソルレコードを反復処理する
- カーソルを閉じる
- カーソルの割り当てを解除する
例として、インスタンス内のデータベースごとにいくつかのタスクを実行する必要があるとします。 カーソルを使用すると、通常は次のようなコードを使用します。
DECLARE @dbname AS sysname; DECLARE C CURSOR FORWARD_ONLY STATIC READ_ONLY FOR SELECT name FROM sys.databases; OPEN C; FETCH NEXT FROM C INTO @dbname; WHILE @@FETCH_STATUS = 0BEGIN PRINT N'Handling database ' + QUOTENAME(@dbname) + N'...'; /* ... do your thing here ... */ FETCH NEXT FROM C INTO @dbname;END; CLOSE C;DEALLOCATE C;
CLOSEコマンドは現在の結果セットを解放し、ロックを解放します。 DEALLOCATEコマンドはカーソル参照を削除し、最後の参照が割り当て解除されると、カーソルを構成するデータ構造を解放します。 CLOSEコマンドとDEALLOCATEコマンドを使用せずに上記のコードを2回実行しようとすると、次のエラーが表示されます。
Msg 16915, Level 16, State 1, Line 4A cursor with the name 'C' already exists.Msg 16905, Level 16, State 1, Line 6The cursor is already open.
続行する前に、CLOSEコマンドとDEALLOCATE
多くの人は、通常のカーソルを使用する代わりに、カーソル変数を使用することができる最も一般的なケースである、カーソルを1つのバッチで操作する必要があるときに認識していません。 他の変数と同様に、カーソル変数のスコープは、それが宣言されたバッチのみです。 これは、バッチが終了するとすぐに、すべての変数が期限切れになることを意味します。 カーソル変数を使用すると、バッチが終了すると、SQL Serverは自動的に閉じて割り当てを解除するため、CLOSEコマンドとDEALLOCATEコマンドを明示的に実行する必要が
今回はカーソル変数を使用して修正されたコードを次に示します:p>
DECLARE @dbname AS sysname, @C AS CURSOR; SET @C = CURSOR FORWARD_ONLY STATIC READ_ONLY FOR SELECT name FROM sys.databases; OPEN @C; FETCH NEXT FROM @C INTO @dbname; WHILE @@FETCH_STATUS = 0BEGIN PRINT N'Handling database ' + QUOTENAME(@dbname) + N'...'; /* ... do your thing here ... */ FETCH NEXT FROM @C INTO @dbname;END;
それを複数回実行して、今回はエラーが発生しないことに気 それはちょうどきれいだし、カーソルを閉じて割り当てを解除するのを忘れた場合、カーソルリソースを保持することを心配する必要はありません。
MERGE with OUTPUT
SQL Server2005でmodificationステートメントのOUTPUT句が開始されて以来、変更された行からデータを返すときはいつでも非常に実用的なツールであることが 人々は、アーカイブ、監査、および他の多くのユースケースのような目的のために定期的にこの機能を使用しています。 ただし、この機能に関する厄介なことの1つは、INSERT文で使用すると、挿入された行からデータを返すことのみが許可され、出力列の前にinsertが付けられます。 ソーステーブルの列にアクセスすることはできませんが、ソースからの列をターゲットからの列と一緒に返す必要がある場合もあります。
例として、次のコードを実行して作成および移入するテーブルT1およびT2を考えてみましょう:
DROP TABLE IF EXISTS dbo.T1, dbo.T2;GO CREATE TABLE dbo.T1(keycol INT NOT NULL IDENTITY PRIMARY KEY, datacol VARCHAR(10) NOT NULL); CREATE TABLE dbo.T2(keycol INT NOT NULL IDENTITY PRIMARY KEY, datacol VARCHAR(10) NOT NULL); INSERT INTO dbo.T1(datacol) VALUES('A'),('B'),('C'),('D'),('E'),('F');
両方のテーブルでキーを生成するためにidentityプロパティが使用されていることに注意してください。いくつかの行をt1からT2にコピーする必要があるとします。keycol%2=1の行を言います。 OUTPUT句を使用して、t2で新しく生成されたキーを返しますが、それらのキーと一緒にt1のそれぞれのソースキーも返します。 直感的な期待は、次のINSERT文を使用することです:
INSERT INTO dbo.T2(datacol) OUTPUT T1.keycol AS T1_keycol, inserted.keycol AS T2_keycol SELECT datacol FROM dbo.T1 WHERE keycol % 2 = 1;
残念ながら、前述のように、OUTPUT句ではソーステーブルの列を参照できないため、次のエラーが発生します。
マルチパート識別子”t1.keycol”はバインドできませんでした。奇妙なことに、この制限がMERGE文には適用されないことに多くの人が気付いていません。 そのため、少し厄介ですが、INSERT文をMERGE文に変換できますが、そのためには、MERGE述語を常にfalseにする必要があります。 これにより、WHEN NOT MATCHED句が有効になり、サポートされている唯一の挿入アクションが適用されます。 1=2などのダミーのfalse条件を使用できます。 完全な変換コードは次のとおりです。
MERGE INTO dbo.T2 AS TGTUSING (SELECT keycol, datacol FROM dbo.T1 WHERE keycol % 2 = 1) AS SRC ON 1 = 2WHEN NOT MATCHED THEN INSERT(datacol) VALUES(SRC.datacol)OUTPUT SRC.keycol AS T1_keycol, inserted.keycol AS T2_keycol;
今回はコードが正常に実行され、次の出力が生成されます。
T1_keycol T2_keycol----------- -----------1 13 25 3