共用方式為

T-SQL 設計問題

  • 發行項

適用於：Microsoft Fabric 中的 SQL ServerAzure SQL 資料庫 Azure SQL 受控執行個體 SQL 資料庫

當您在資料庫專案中分析 T-SQL 程式碼時，可能會將一或多個警告分類為設計問題。 您應該解決設計問題，以避免下列情況：

  • 資料庫的後續變更可能會中斷依賴資料庫的應用程式。
  • 程式碼可能不會產生預期的結果。
  • 如果您以未來的 SQL Server 版本執行程式碼，則程式碼可能會損壞。

一般而言，您不應隱藏設計問題，因為它可能會中斷您的應用程式，無論是現在還是未來。

提供的規則可識別下列設計問題：

SR0001：避免在預存程序、檢視和資料表值函式中使用 SELECT*

如果您在預存程序、檢視或資料表值函式中使用萬用字元來選取資料表或檢視中的所有資料行，且如果基礎資料表或檢視發生變更，則傳回資料行的數目或圖形可能會變更。 資料行的圖形是其類型和大小的組合。 此差異可能會在取用預存程序、檢視或資料表值函式的應用程式中造成問題，因為這些取用者預期會有不同數目的資料行。

如何修正違規

您可以將萬用字元取代為資料行名稱的完整清單，以保護預存程序、檢視或資料表值函式的取用者免於結構描述變更。

範例

下列範例會先定義名為 [Table2] 的資料表，然後定義兩個預存程序。 第一個程序包含違反規則 SR0001 的 SELECT *。 第二個程序會避免 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：考慮使用 SCOPE_IDENTITY，而不是使用 @@IDENTITY

因為 @@IDENTITY 是全域身分識別值，所以可能已在目前範圍之外更新，並取得非預期的值。 觸發程式，包括複寫所使用的巢狀觸發程式，可更新您目前範圍以外的@@IDENTITY。

如何修正違規

若要解決此問題，您必須將 @@IDENTITY 的參考取代為 SCOPE_IDENTITY，這會傳回使用者陳述式範圍中最新的識別值。

範例

在第一個範例中，@@IDENTITY 用於將資料插入資料表的預存程序中。 然後會發佈資料表以進行合併式複寫，這會將觸發程式新增至已發佈的資料表。 因此，@@IDENTITY 可能會根據複寫系統資料表的插入作業 (而非使用者資料表的插入作業) 傳回值。

Sales.Customer 資料表的最大識別值為 29483。 如果您將資料列插入此資料表，@@IDENTITY 和 SCOPE_IDENTITY() 就會傳回不同的值。 SCOPE_IDENTITY() 會根據使用者資料表的插入作業傳回值，而 @@IDENTITY 會根據複寫系統資料表的插入作業傳回值。

第二個範例示範如何使用 SCOPE_IDENTITY() 來存取插入的身分識別值並解決警告。

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 的可變長度字串。 而第二個資料表會宣告固定長度的字串，以避免出現警告。

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：當您聯結資料表或檢視時，避免使用已被取代的語法

使用已被取代的語法的聯結分為兩個類別：

  • 內部聯結：對於內部聯結，所聯結之資料行中的值會使用 =、<、>= 等比較運算子進行比較。 只有在每個資料表至少有一個資料列符合聯結條件時，內部聯結才會傳回資料列。
  • 外部聯結：外部聯結則至少傳回 FROM 子句指定的一個資料表或檢視，只要這些資料列符合任何 WHERE 或 HAVING 搜尋條件。 如果使用 = 或 = 來指定外部聯結，則會使用已被取代的語法。

如何修正違規

要修正內部聯結中的違規，請使用 INNER JOIN 語法。

要修正外部聯結中的違規，請使用適當的 OUTER JOIN 語法。 下列選項可供您選擇：

  • LEFT OUTER JOIN 或 LEFT JOIN
  • RIGHT OUTER JOIN 或 RIGHT JOIN

下列範例提供了已被取代的語法和已更新語法的範例。 如需關於聯結的詳細資訊，請參閱聯結。

範例

這六個範例將示範下列選項：

  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：輸出參數 (參數) 未填入所有程式碼路徑

此規則會識別輸出參數未透過預存程序或函式，在一或多個程式碼路徑中設定為值的程式碼。 此規則不會識別應設定輸出參數的路徑。 如果有多個輸出參數發生此問題，每個參數會出現一個警告。

如何修正違規

您可以透過兩種方式之一校正這個問題。 如果您將輸出參數初始化為程序本文開頭的預設值，可以更輕鬆地修正此問題。 或者，您也可以將輸出參數設定為未設定參數之特定程式碼路徑中的值。 不過，您可能會忽略複雜程序中不常見的程式碼路徑。

重要

在程序宣告內指定一個值 (例如 CREATE PROC MyProcedure (@param1 INT = 10 OUTPUT)) 無法解決問題。 您必須將值指派給程序主體內的輸出參數。

範例

下列範例顯示兩個簡單的程序。 第一個程序不會設定輸出參數 @Sum 的值。 第二個程序會在程序開頭初始化 @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 運算式運算式是整數或浮點數類型 
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 運算式運算式是整數或浮點數類型 
UPDATE TOP 4 table1
DELETE PREDICATE述詞為布林值 
DELETE t1 WHERE c1 > 10
DELETE TOP 運算式運算式是整數或浮點數類型 
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 網站上的此頁面：CAST 和 CONVERT (Transact-SQL)。

範例

此範例顯示兩個將資料插入資料表的預存程序。 第一個程序 procWithWarning 會導致資料類型的隱式轉換。 第二個程序 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

相關內容