Problémy s návrhem T-SQL

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL databáze v Microsoft Fabric

Při analýze kódu T-SQL v databázovém projektu může být jedno nebo více upozornění zařazeno do kategorií jako problémy s návrhem. Měli byste řešit problémy s návrhem, abyste se vyhnuli následujícím situacím:

• Následné změny databáze můžou narušit aplikace, které na ní závisejí.
• Kód nemusí vytvořit očekávaný výsledek.
• Kód se může přerušit, pokud ho spustíte s budoucími verzemi SQL Serveru.

Obecně platí, že byste neměli potlačit problém s návrhem, protože by to mohlo narušit vaši aplikaci, a to buď teď, nebo v budoucnu.

Poskytnutá pravidla identifikují následující problémy s návrhem:

• SR0001: Vyhněte se příkazu SELECT * v uložených procedurách, zobrazeních a funkcích s hodnotami tabulky
• SR0008: Zvažte použití SCOPE_IDENTITY místo @@IDENTITY
• SR0009: Nepoužívejte typy proměnlivé délky, které mají velikost 1 nebo 2
• SR0010: Nepoužívejte zastaralou syntaxi při spojení tabulek nebo zobrazení
• SR0013: Výstupní parametr (parametr) není vyplněný ve všech cestách kódu.
• SR0014: Při přetypování z typu {Type1} do {Type2} může dojít ke ztrátě dat.

SR0001: Vyhněte se příkazu SELECT * v uložených procedurách, zobrazeních a funkcích s hodnotami tabulky

Pokud použijete zástupný znak v uložené proceduře, zobrazení nebo funkci s hodnotou tabulky k výběru všech sloupců v tabulce nebo zobrazení, může se číslo nebo tvar vrácených sloupců změnit, pokud se podkladová tabulka nebo zobrazení změní. Tvar sloupce je kombinací jeho typu a velikosti. Tato odchylka může způsobit problémy v aplikacích, které využívají uloženou proceduru, zobrazení nebo funkci s hodnotou tabulky, protože tito příjemci budou očekávat jiný počet sloupců.

Jak opravit porušení

Uživatele uložené procedury, zobrazení nebo funkce s hodnotou tabulky můžete chránit před změnami schématu nahrazením zástupného znaku plně kvalifikovaným seznamem názvů sloupců.

Example

Následující příklad nejprve definuje tabulku s názvem [Table2] a pak definuje dvě uložené procedury. První postup obsahuje SELECT *, které porušuje pravidlo SR0001. Druhý postup se vyhne SELECT * a explicitně uvede sloupce v příkazu 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: Zvažte použití SCOPE_IDENTITY místo @@IDENTITY

Protože @@IDENTITY je globální hodnota identity, mohla být aktualizována mimo aktuální obor a získala neočekávanou hodnotu. Triggery, včetně vnořených triggerů používaných replikací, můžou aktualizovat @@IDENTITY mimo váš aktuální obor.

Jak opravit porušení

Chcete-li tento problém vyřešit, je nutné nahradit odkazy na @@IDENTITY SCOPE_IDENTITY, která vrátí nejnovější hodnotu identity v rozsahu příkazu uživatele.

Example

V prvním příkladu se @@IDENTITY používá v uložené proceduře, která vkládá data do tabulky. Tabulka se pak publikuje pro slučovací replikaci, která přidá triggery do publikovaných tabulek. Proto @@IDENTITY může vrátit hodnotu operace vložení do systémové tabulky replikace místo operace vložení do uživatelské tabulky.

Tabulka Sales.Customer má maximální hodnotu identity 29483. Pokud do tabulky vložíte řádek, @@IDENTITY a SCOPE_IDENTITY() vrátí jiné hodnoty. SCOPE_IDENTITY() vrátí hodnotu operace vložení do tabulky uživatele, ale @@IDENTITY vrátí hodnotu operace vložení do systémové tabulky replikace.

Druhý příklad ukazuje, jak můžete použít SCOPE_IDENTITY() pro přístup k vložené hodnotě identity a vyřešit upozornění.

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: Nepoužívejte typy proměnlivé délky, které mají velikost 1 nebo 2

Pokud používáte datové typy s proměnlivou délkou, jako je VARCHAR, NVARCHAR a VARBINARY, vznikají vám dodatečné náklady na úložiště na sledování délky uložené hodnoty v datovém typu. Sloupce s proměnlivou délkou se navíc ukládají po všech sloupcích s pevnou délkou, což může mít vliv na výkon. Zobrazí se také upozornění, pokud deklarujete typ proměnné délky, například VARCHAR, ale nezadáte žádnou délku. K tomuto upozornění dochází, protože pokud není zadáno, je výchozí délka 1.

Jak opravit porušení

Pokud bude délka typu velmi malá (velikost 1 nebo 2) a konzistentní, deklarujte je jako typ pevné délky, například CHAR, NCHAR a BINARY.

Example

Tento příklad ukazuje definice pro dvě tabulky. První tabulka deklaruje řetězec proměnné délky, který má délku 2. Druhá tabulka místo toho deklaruje řetězec s pevnou délkou, který zabrání upozornění.

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]

Data pro typy proměnlivé délky jsou fyzicky uložena po datech pro typy pevné délky. Pokud tedy změníte sloupec z proměnné na pevnou délku v tabulce, která není prázdná, způsobíte přesun dat.

SR0010: Nepoužívejte zastaralou syntaxi při spojení tabulek nebo zobrazení.

Spojení, která používají zastaralou syntaxi, spadají do dvou kategorií:

• Vnitřní spojení: Pro vnitřní spojení se hodnoty ve sloupcích, které jsou spojené, porovnávají pomocí relačního operátoru, jako je =, <, >= a tak dále. Vnitřní spojení vrací řádky pouze v případě, že alespoň jeden řádek z každé tabulky odpovídá podmínce spojení.
• Vnější spojení: Vnější spojení vrátí všechny řádky z alespoň jedné z tabulek nebo zobrazení zadaných v klauzuli FROM, pokud tyto řádky splňují libovolnou podmínku WHERE nebo HAVING. Pokud k zadání vnějšího spojení použijete = nebo = , používáte zastaralou syntaxi.

Jak opravit porušení

Pokud chcete opravit porušení vnitřního spojení, použijte INNER JOIN syntaxi.

Chcete-li opravit porušení vnějšího spojení, použijte odpovídající OUTER JOIN syntaxi. Máte následující možnosti:

• LEVÉ VNĚJŠÍ SPOJENÍ nebo LEVÉ SPOJENÍ
• PRAVÉ VNĚJŠÍ SPOJENÍ NEBO PRAVÉ SPOJENÍ

Příklady zastaralé syntaxe a aktualizované syntaxe jsou uvedené v následujících příkladech. Další informace o spojeních najdete na webu Joins.

Examples

Šest příkladů ukazuje následující možnosti:

1. Příklad 1 ukazuje zastaralou syntaxi vnitřního spojení.
2. Příklad 2 ukazuje, jak můžete aktualizovat příklad 1 tak, aby používal aktuální syntaxi.
3. Příklad 3 ukazuje zastaralou syntaxi levého vnějšího spojení.
4. Příklad 4 ukazuje, jak můžete aktualizovat příklad 2 tak, aby používal aktuální syntaxi.
5. Příklad 5 ukazuje zastaralou syntaxi pro pravé vnější spojení.
6. Příklad 6 ukazuje, jak aktualizovat Příklad 5 tak, aby používal aktuální syntaxi.

-- 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: Výstupní parametr (parametr) není vyplněný ve všech cestách kódu.

Toto pravidlo identifikuje kód, ve kterém není výstupní parametr nastaven na hodnotu v jedné nebo více cestách kódu prostřednictvím uložené procedury nebo funkce. Toto pravidlo neidentifikuje, ve kterých cestách má být nastaven výstupní parametr. Pokud má tento problém více výstupních parametrů, zobrazí se pro každý parametr jedno upozornění.

Jak opravit porušení

Tento problém můžete opravit jedním ze dvou způsobů. Tento problém můžete nejsnáže vyřešit, pokud inicializujete výstupní parametry na výchozí hodnotu na začátku textu procedury. Jako alternativu můžete také nastavit výstupní parametr na hodnotu v konkrétních cestách kódu, ve kterých není parametr nastaven. V komplexním postupu však můžete přehlédnout neobvyklou cestu kódu.

Important

Určení hodnoty v deklaraci procedury, například CREATE PROC MyProcedure (@param1 INT = 10 OUTPUT) nebude problém vyřešen. Výstupnímu parametru v těle procedury musíte přiřadit hodnotu.

Example

Následující příklad ukazuje dva jednoduché postupy. První procedura nenastavuje hodnotu výstupního parametru @Sum. Druhý postup inicializuje @Sum parametr na začátku procedury, což zajišťuje, že hodnota bude nastavena ve všech cestách kódu.

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: Při přetypování z typu {Type1} na {Type2} může dojít ke ztrátě dat.

Pokud jsou datové typy nekonzistentně přiřazeny ke sloupcům, proměnným nebo parametrům, jsou implicitně převedeny při spuštění Transact-SQL kódu obsahujícího tyto objekty. Tento typ převodu nejen snižuje výkon, ale také v některých případech způsobuje drobné ztráty dat. Prohledávání tabulky se může spustit například v případě, že je nutné převést každý sloupec v klauzuli WHERE. Horší je, že data mohou být ztracena, pokud je řetězec Unicode převeden na řetězec ASCII, který používá jinou znakovou stránku.

Toto pravidlo NE:

• Zkontrolujte typ počítaného sloupce, protože typ není známý, dokud není spuštěn.
• Analyzujte cokoli uvnitř příkazu typu CASE. Také neanalyzuje návratovou hodnotu příkazu CASE.
• Analýza vstupních parametrů nebo návratové hodnoty volání ISNULL

Tato tabulka shrnuje kontroly, na které se vztahuje pravidlo SR0014:

Jazyková konstrukce	Co je zaškrtnuté	Example
Výchozí hodnota parametrů	Datový typ parametru	CREATE PROCEDURE p1(@p1 INT = 1) AS BEGIN END
CREATE INDEX příkaz predikát	Predikát je Boolovská hodnota	CREATE INDEX index1 ON table1 (column1) WHERE column1 > 10
Argumenty funkcí LEFT nebo RIGHT	Typ a délka argumentu string	SET @v = LEFT('abc', 2)
Argumenty funkcí CAST a CONVERT	Výrazy a typy jsou platné.	SET @v = CAST('abc' AS CHAR(10))
Příkaz SET	Levé a pravé strany mají kompatibilní typy	SET @v1 = 'xyz' SELECT @v1 = c1 FROM t1
Predikát příkazu IF	Predikát je Boolovská hodnota	IF (@v > 10)
Predikát příkazu WHILE	Predikát je Boolovská hodnota	WHILE (@v > 10)
Příkaz INSERT	Hodnoty a sloupce jsou správné.	INSERT INTO t1(c1, c2) VALUES (99, 'xyz') INSERT INTO t1 SELECT c1 FROM t2.
VÝBĚR predikátu WHERE	Predikát je Boolovská hodnota	SELECT * FROM t1 WHERE c1 > 10
SELECT TOP – výraz	Výraz je typu Integer nebo Float.	SELECT TOP 4 * FROM t1 SELECT TOP 1.5 PERCENT * FROM t1
Příkaz UPDATE	Výraz a sloupec mají kompatibilní typy	UPDATE t1 SET c1 = 100
Aktualizovat predikát	Predikát je Boolovská hodnota	UPDATE t1 SET c1 = 100 WHERE c1 > 100
Aktualizace TOP výraz	Výraz je typu Integer nebo Float.	UPDATE TOP 4 table1
ODSTRANIT PREDIKÁT	Predikát je Boolovská hodnota	DELETE t1 WHERE c1 > 10
DELETE TOP – výraz	Výraz je typu Integer nebo Float.	DELETE TOP 2 FROM t1
Deklarace proměnné DECLARE	Počáteční hodnota a datový typ jsou kompatibilní	DECLARE @v INT = 10
Argumenty příkazu EXECUTE a návratový typ	Parametry a argumenty	CREATE PROCEDURE p1 (@p1 INT) AS GO EXECUTE p1 100 EXECUTE @v1 = p1 100
Příkaz RETURN	Výraz RETURN má kompatibilní datový typ.	CREATE FUNCTION f1() RETURNS INT AS BEGIN RETURN 100 END
Podmínky příkazu MERGE	Podmínka je logická hodnota	MERGE t1 USING t2 ON t1.c1 = t2.c2 WHEN t1.c1 > 10 THEN DELETE

Jak opravit porušení

Těmto problémům se můžete vyhnout a vyřešit tím, že datové typy přiřadíte konzistentně a explicitně převedete typy tam, kde jsou potřeba. Další informace o tom, jak explicitně převést datové typy, naleznete na této stránce na webu společnosti Microsoft: CAST a CONVERT (Transact-SQL).

Example

Tento příklad ukazuje dvě uložené procedury, které vkládají data do tabulky. První postup, procWithWarning, způsobí implicitní převod datového typu. Druhý postup, procFixed, ukazuje, jak můžete přidat explicitní převod pro maximalizaci výkonu a zachování všech dat.

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

Související obsah

• analýzy kódu SQL za účelem zlepšení kvality kódu
• problémy s pojmenováním T-SQL
• problémy s výkonem T-SQL
• Analýza kódu T-SQL za účelem zjištění vad