通过

T-SQL 设计问题

  • 项目

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Microsoft Fabric 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 谓词谓词是布尔值 
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

相关内容