次の方法で共有

T-SQL の設計に関する問題

  • [アーティクル]

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance Microsoft Fabric SQL Database

データベース プロジェクトで T-SQL コードを分析すると、1 つまたは複数の警告が設計上の問題として分類される場合があります。 次の状況を回避するには、設計上の問題に対処する必要があります:

  • データベースに対する後続の変更により、データベースに依存するアプリケーションが中断される可能性があります。
  • そのコードは期待した結果をもたらさない可能性があります。
  • SQL Server の今後のリリースでコードを実行すると、そのコードが中断する可能性があります。

一般に、現在も将来も、アプリケーションが中断する可能性があるからといって、設計上の問題を抑制してはいけません。

指定されたルールは、次の設計上の問題を特定します:

SR0001: ストアド プロシージャ、ビュー、およびテーブル値関数では SELECT * を使用しないようにする

ストアド プロシージャ、ビュー、またはテーブル値関数でワイルドカード文字を使用してテーブルまたはビュー内のすべての列を選択すると、基になるテーブルまたはビューが変更されると、返される列の数または形状が変わる可能性があります。 列の形状は、その型とサイズの組み合わせです。 この差異により、ストアド プロシージャ、ビュー、またはテーブル値関数を使用するアプリケーションで問題が発生する可能性があります。これらのコンシューマーは異なる数の列を想定するためです。

違反の修正方法

ワイルドカード文字を列名の完全修飾リストに置き換えることで、ストアド プロシージャ、ビュー、またはテーブル値関数のコンシューマーをスキーマの変更から保護できます。

例

次の例では、最初に [Table2] という名前のテーブルを定義し、次に 2 つのストアド プロシージャを定義します。 最初のプロシージャには、ルール SR0001 に違反する SELECT * が含まれています。 2 番目のプロシージャでは、SELECT * を避け、SELECT ステートメント内の列を明示的に一覧表示します。

CREATE TABLE [dbo].[Table2]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL,
[Comment] NVARCHAR (50)
)
ON [PRIMARY]

CREATE PROCEDURE [dbo].[procWithWarning]
AS
BEGIN
-- Contains code that breaks rule SR0001
SELECT *
FROM [dbo].[Table2]
END

CREATE PROCEDURE [dbo].[procFixed]
AS
BEGIN
-- Explicitly lists the column names in a SELECT statement
SELECT [dbo].[Table2].[ID], [dbo].[Table2].[c1], [dbo].[Table2].[Comment]
FROM [dbo].[Table2]
END

SR0008: @@IDENTITY の代わりに SCOPE_IDENTITY を使用することを検討する

@@IDENTITY はグローバル ID 値であるため、現在のスコープ外で更新され、予期しない値が取得された可能性があります。 レプリケーションによって使用される入れ子になったトリガーを含むトリガーは、現在の範囲外の @@IDENTITY を更新できます。

違反の修正方法

この問題を解決するには、@@IDENTITY への参照を SCOPE_IDENTITY に置き換える必要があります。これにより、ユーザー ステートメントの範囲内の最新の ID 値が返されます。

例

最初の例では、テーブルにデータを挿入するストアド プロシージャで @@IDENTITY が使用されています。 その後、マージ レプリケーション用にテーブルが発行され、発行されたテーブルにトリガーが追加されます。 このため、@@IDENTITY では、ユーザ・テーブルへの挿入操作の代わりに、レプリケーション システム テーブルへの挿入操作の値を返すことができます。

Sales.Customer テーブルの ID の最大値は 29483 です。 このテーブルに行を挿入する場合、@@IDENTITY と SCOPE_IDENTITY は異なる値を返します。 SCOPE_IDENTITY() はユーザー テーブルへの挿入操作による値を返すのに対し、@@IDENTITY は、レプリケーション システム テーブルへの挿入操作による値を返します。

2 番目の例は、SCOPE_IDENTITY() を使用して挿入された ID 値にアクセスし、警告を解決する方法を示しています。

CREATE PROCEDURE [dbo].[ProcedureWithWarning]
@param1 INT,
@param2 NCHAR(1),
@Param3 INT OUTPUT
AS
BEGIN
INSERT INTO Sales.Customer ([TerritoryID],[CustomerType]) VALUES (@param1,@param2);

SELECT @Param3 = @@IDENTITY
END

CREATE PROCEDURE [dbo].[ProcedureFixed]
@param1 INT,
@param2 NCHAR(1),
@param3 INT OUTPUT
AS
BEGIN
INSERT INTO Sales.Customer ([TerritoryID],[CustomerType]) VALUES (@param1,@param2);

SELECT @Param3 = SCOPE_IDENTITY()
END

SR0009: サイズ 1 または 2 の可変長の型を使用しないようにする

VARCHAR、NVARCHAR、VARBINARY などの可変長のデータ型を使用すると、データ型に格納されている値の長さを追跡するための追加のストレージ コストが発生します。 さらに、可変長の列は、固定長のすべての列の後に格納され、それによりパフォーマンスに影響を与える可能性があります。 VARCHAR などの可変長の型を宣言しても、長さを指定しない場合は、警告が表示されます。 この警告は、指定しない場合、既定の長さが 1 であるために発生します。

違反の修正方法

型の長さが非常に小さく (サイズ 1 または 2)、一貫性がある場合は、CHAR、NCHAR、BINARY などの固定長の型として宣言します。

例

この例では、2 つのテーブルの定義を示します。 最初のテーブルでは、長さが 2 の可変長の文字列が宣言されています。 2 番目のテーブルでは、代わりに固定長の文字列が宣言されるため、警告は回避されます。

CREATE TABLE [dbo].[TableWithWarning]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL PRIMARY KEY,
[c2] INT,
[c3] INT,
[SmallString] VARCHAR(2)
)
ON [PRIMARY]

CREATE TABLE [dbo].[FixedTable]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL PRIMARY KEY,
[c2] INT,
[c3] INT,
[SmallString] CHAR(2)
)
ON [PRIMARY]

可変長の型のデータは、固定長の型のデータの後に物理的に格納されます。 そのため、空でないテーブルの列を変数から固定長に変更すると、データ移動が発生します。

SR0010: テーブルまたはビューを結合するときに非推奨の構文を使用しないようにする

非推奨の構文を使用する結合は、次の 2 つのカテゴリに分類されます:

  • 内部結合: 内部結合の場合、結合される列の値は、=、<、>= などの比較演算子を使用して比較されます。 内部結合は、各テーブルの行が結合条件と少なくとも 1 つ一致する場合にのみ行を返します。
  • 外部結合: 外部結合からは、FROM 句で指定された少なくとも 1 つのテーブルまたはビューにあり、任意の WHERE 検索条件または HAVING 検索条件を満たしているすべての行が返されます。 外部結合を指定するために = または = を使用する場合は、非推奨の構文を使用しています。

違反の修正方法

内部結合の違反を修正するには、INNER JOIN 構文を使用します。

外部結合の違反を修正するには、適切な OUTER JOIN 構文を使用します。 次のようなオプションがあります。

  • 左外部結合または左結合
  • 右外部結合または右結合

非推奨の構文と更新された構文の例を次の例に示します。 結合の詳細については、「結合」を参照してください。

例

6 つの例により次のオプションを示します:

  1. 例 1 は、内部結合の非推奨の構文を示しています。
  2. 例 2 は、現在の構文を使用するように例 1 を更新する方法を示しています。
  3. 例 3 は、左外部結合の非推奨の構文を示しています。
  4. 例 4 は、現在の構文を使用するように例 2 を更新する方法を示しています。
  5. 例 5 は、右外部結合の非推奨の構文を示しています。
  6. 例 6 は、現在の構文を使用するように例 5 を更新する方法を示しています。
-- Example 1: Deprecated syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] = [T2].[ID]

-- Example 2: Current syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
INNER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]

-- Example 3: Deprecated syntax for a left outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] *= [T2].[ID]

-- Example 4: Fixed syntax for a left outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
LEFT OUTER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]

-- Example 5: Deprecated syntax for a right outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] =* [T2].[ID]

-- Example 6: Fixed syntax for a right outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
RIGHT OUTER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]

SR0013: 出力パラメーター (パラメーター) がすべてのコード パスに設定されていない

この規則は、ストアド プロシージャまたは関数を介して、出力パラメーターが 1 つまたは複数のコード パスの値に設定されていないコードを識別します。 この規則では、出力パラメーターを設定する必要があるパスは識別されません。 複数の出力パラメーターにこの問題がある場合は、パラメーターごとに 1 つの警告が表示されます。

違反の修正方法

2 つの方法のいずれかでこの問題を修正することができます。 この問題は、プロシージャ本体の開始時に出力パラメーターを既定値に初期化すると、最も簡単に修正できます。 別の方法として、出力パラメーターを、パラメーターが設定されていない特定のコード パスの値に設定することもできます。 ただし、複雑なプロシージャでは一般的でないコード パスを見落とす可能性があります。

重要

プロシージャ宣言内で値 (CREATE PROC MyProcedure (@param1 INT = 10 OUTPUT) など) を指定した場合、問題は解決されません。 プロシージャ本体内の出力パラメーターに値を割り当てる必要があります。

例

次の例は、簡単なプロシージャを 2 つ示しています。 最初のプロシージャでは、出力パラメーターの値 (@Sum) は設定されません。 2 番目のプロシージャは、プロシージャの開始時に @Sum パラメーターを初期化します。これにより、値がすべてのコード パスに確実に設定されます。

CREATE PROCEDURE [dbo].[procedureHasWarning]
(
@Value1 BIGINT,
@Value2 INT,
@Value3 INT,
@Sum INT OUTPUT
)
AS
BEGIN
-- No initialization of the output parameter
--
-- Additional statements here.
--
RETURN 0;
END
--
CREATE PROCEDURE [dbo].[procedureFixed]
(
@Value1 BIGINT,
@Value2 INT,
@Value3 INT,
@Sum INT OUTPUT
)
AS
BEGIN
-- Initialize the out parameter
SET @Sum = 0;
--
-- Additional statements here
--
RETURN 0;
END

SR0014: {Type1} から {Type2} へのキャスト時にデータ損失が発生する可能性があります

データ型が列、変数、またはパラメーターに一貫して割り当てられている場合、それらのオブジェクトを含む Transact-SQL コードが実行されると、暗黙的に変換されます。 この種類の変換により、パフォーマンスが低下するだけでなく、場合によっては、データが微妙に失われる場合もあります。 たとえば、WHERE 句内のすべての列を変換する必要がある場合、テーブル スキャンが実行される場合があります。 さらに悪いことに、Unicode 文字列が別のコード ページを使用する ASCII 文字列に変換されると、データが失われる可能性があります。

この規則では、次の操作は行われません:

  • 計算列の型は実行時まで認識されないため、その型をチェックします。
  • CASE ステートメント内の何かを分析します。 また、CASE ステートメントの戻り値も分析しません。
  • ISNULL への呼び出しの入力パラメーターまたは戻り値を分析します

次の表は、ルール SR0014 の対象となるチェックをまとめたものです:

言語コンストラクト確認内容例
パラメーターの既定値パラメーターのデータ型 
CREATE PROCEDURE p1(@p1 INT = 1)
AS
BEGIN
END
CREATE INDEX 述語述語はブール値です 
CREATE INDEX index1 ON table1 (column1)
WHERE column1 > 10
LEFT 関数または RIGHT 関数の引数文字列引数の型と長さ 
SET @v = LEFT('abc', 2)
CAST 関数と CONVERT 関数の引数式と型は有効です 
SET @v = CAST('abc' AS CHAR(10))
SET ステートメント左側と右側には互換性のある型があります 
SET @v1 = 'xyz'
SELECT @v1 = c1 FROM t1
IF ステートメント述語述語はブール値です 
IF (@v > 10)
WHILE ステートメント述語述語はブール値です 
WHILE (@v > 10)
INSERT ステートメント値と列が正しい 
INSERT INTO t1(c1, c2) VALUES (99, 'xyz')
INSERT INTO t1 SELECT c1 FROM t2.
SELECT WHERE 述語述語はブール値です 
SELECT * FROM t1 WHERE c1 > 10
SELECT TOP 式式は整数型または Float 型です 
SELECT TOP 4 * FROM t1
SELECT TOP 1.5 PERCENT * FROM t1
UPDATE ステートメント式と列に互換性のある型がある 
UPDATE t1 SET c1 = 100
UPDATE 述語述語はブール値です 
UPDATE t1 SET c1 = 100
WHERE c1 > 100
UPDATE TOP 式式は整数型または Float 型です 
UPDATE TOP 4 table1
DELETE PREDICATE述語はブール値です 
DELETE t1 WHERE c1 > 10
DELETE TOP 式式は整数型または Float 型です 
DELETE TOP 2 FROM t1
DECLARE 変数宣言初期値とデータ型に互換性があります 
DECLARE @v INT = 10
EXECUTE ステートメントの引数と戻り値の型パラメーターと引数 
CREATE PROCEDURE p1 (@p1 INT) AS
GO
EXECUTE p1 100
EXECUTE @v1 = p1 100
RETURN ステートメントRETURN 式に互換性のあるデータ型があります 
CREATE FUNCTION f1() RETURNS INT
AS
BEGIN
  RETURN 100
END
MERGE ステートメントの条件条件はブール値です 
MERGE t1 USING t2
ON t1.c1 = t2.c2
WHEN t1.c1 > 10 THEN DELETE

違反の修正方法

これらの問題を回避して解決するには、データ型を一貫して割り当て、必要な場所で型を明示的に変換します。 データ型を明示的に変換する方法の詳細については、Microsoft Web サイトの CAST と CONVERT (Transact-SQL) に関するこちらのページを参照してください。

例

この例では、テーブルにデータを挿入する 2 つのストアド プロシージャを示します。 最初のプロシージャ procWithWarning では、データ型が暗黙的に変換されます。 2 番目のプロシージャ procFixed では、明示的な変換を追加してパフォーマンスを最大化し、すべてのデータを保持する方法を示します。

CREATE TABLE [dbo].[Table2]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL,
[c2] INT NOT NULL,
[c3] BIGINT NOT NULL,
[Comment] VARCHAR (25)
)
ON [PRIMARY]

CREATE PROCEDURE [dbo].[procWithWarning]
(
@Value1 INT,
@Value2 INT,
@Value3 BIGINT,
@Comment CHAR(30)
)
AS
BEGIN
INSERT INTO [Table2] ([c1], [c2], [c3], Comment)
VALUES (@Value1, @Value2, @Value3, @Comment)

END

CREATE PROCEDURE [dbo].[procFixed]
(
@Value1 INT,
@Value2 INT,
@Value3 BIGINT,
@Comment CHAR(10)
)
AS
BEGIN
INSERT INTO [Table2] ([c1], [c2], [c3], Comment)
VALUES (@Value1, @Value2, @Value3, CAST(@Comment AS VARCHAR(25)))

END

関連するコンテンツ