Проблемы с проектированием T-SQL

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure базе данных SQL в Microsoft Fabric

При анализе кода T-SQL в проекте базы данных один или несколько предупреждений могут быть классифицированы как проблемы с проектированием. Чтобы избежать следующих ситуаций, следует устранить проблемы с проектированием.

• Последующие изменения в базе данных могут нарушить приложения, зависящие от него.
• Код может не привести к ожидаемому результату.
• Код может прерваться, если он запускается с будущими выпусками SQL Server.

Как правило, вы не должны подавлять проблему разработки, так как она может нарушить приложение либо сейчас, либо в будущем.

Указанные правила определяют следующие проблемы проектирования:

• SR0001: избегайте SELECT * в хранимых процедурах, представлениях и функциях с табличным значением
• SR0008: рекомендуется использовать SCOPE_IDENTITY вместо @@IDENTITY
• SR0009: избегайте использования типов переменной длины, размер которых составляет 1 или 2
• SR0010: избегайте использования устаревшего синтаксиса при присоединении таблиц или представлений
• SR0013: выходной параметр (параметр) не заполняется во всех путях кода
• SR0014: при приведение от {Type1} к {Type2} может возникать потеря данных

SR0001: избегайте SELECT * в хранимых процедурах, представлениях и функциях с табличным значением

Если вы используете подстановочный знак в хранимой процедуре, представлении или табличном значении функции для выбора всех столбцов в таблице или представлении, число или форма возвращаемых столбцов может измениться, если базовая таблица или представление изменяются. Форма столбца — это сочетание его типа и размера. Это отклонение может привести к проблемам в приложениях, использующих хранимую процедуру, представление или табличное значение функции, так как эти потребители ожидают другое количество столбцов.

Устранение нарушений

Вы можете защитить потребителей хранимой процедуры, представления или табличного значения от изменений схемы, заменив подстановочный знак полным списком имен столбцов.

Пример

В следующем примере сначала определяется таблица с именем [Table2], а затем определяет две хранимые процедуры. Первая процедура содержит правило SELECT *SR0001, которое нарушает правило SR0001. Вторая процедура избегает и явно перечисляет 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 синтаксис. Вам доступны следующие варианты:

• ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ ИЛИ ЛЕВОЕ СОЕДИНЕНИЕ
• ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ ИЛИ ПРАВОЕ СОЕДИНЕНИЕ

Примеры устаревшего синтаксиса и обновленного синтаксиса приведены в следующих примерах. Дополнительные сведения о соединениях см. в разделе "Соединения".

Примеры

В шести примерах показаны следующие параметры:

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 должен быть преобразован. Хуже, данные могут быть потеряны, если строка Юникода преобразуется в строку ASCII, использующую другую кодовую страницу.

Это правило не делает следующее:

• Проверьте тип вычисляемого столбца, так как тип не известен до времени выполнения.
• Анализ всего внутри инструкции CASE. Он также не анализирует возвращаемое значение инструкции CASE.
• Анализ входных параметров или возвращаемого значения вызова ISNULL

В этой таблице перечислены проверки, охватываемые правилом SR0014:

Конструкция языка	Что проверяется	Пример
Значение параметров по умолчанию	Тип данных параметра	CREATE PROCEDURE p1(@p1 INT = 1) AS BEGIN END
CREATE INDEX predicate	Предикат является логическим	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 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

Устранение нарушений

Вы можете избежать и устранить эти проблемы, назначив типы данных последовательно и явно преобразуя типы, в которых они необходимы. Дополнительные сведения о том, как явно преобразовать типы данных, см. на этой странице на веб-сайте Майкрософт: 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

Связанный контент

• Анализ кода SQL для улучшения качества кода
• Проблемы с именованием T-SQL
• Проблемы с производительностью T-SQL
• Анализ кода T-SQL для поиска дефектов