Masalah desain T-SQL

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric

Saat Anda menganalisis kode T-SQL dalam proyek database Anda, satu atau beberapa peringatan mungkin dikategorikan sebagai masalah desain. Anda harus mengatasi masalah desain untuk menghindari situasi berikut:

• Perubahan berikutnya pada database Anda mungkin merusak aplikasi yang bergantung padanya.
• Kode mungkin tidak menghasilkan hasil yang diharapkan.
• Kode mungkin rusak jika Anda menjalankannya dengan rilis SQL Server di masa mendatang.

Secara umum, Anda tidak boleh menekan masalah desain karena mungkin merusak aplikasi Anda, baik sekarang atau di masa depan.

Aturan yang disediakan mengidentifikasi masalah desain berikut:

• SR0001: Hindari SELECT * dalam prosedur tersimpan, tampilan, dan fungsi bernilai tabel
• SR0008: Pertimbangkan untuk menggunakan SCOPE_IDENTITY alih-alih @@IDENTITY
• SR0009: Hindari menggunakan jenis panjang variabel yang berukuran 1 atau 2
• SR0010: Hindari menggunakan sintaksis yang tidak digunakan lagi saat Anda menggabungkan tabel atau tampilan
• SR0013: Parameter output (parameter) tidak diisi di semua jalur kode
• SR0014: Kehilangan data mungkin terjadi saat transmisi dari {Type1} ke {Type2}

SR0001: Hindari SELECT * dalam prosedur tersimpan, tampilan, dan fungsi bernilai tabel

Jika Anda menggunakan karakter wildcard dalam prosedur tersimpan, tampilan, atau fungsi bernilai tabel untuk memilih semua kolom dalam tabel atau tampilan, jumlah atau bentuk kolom yang dihasilkan mungkin berubah jika tabel atau tampilan yang mendasar berubah. Bentuk kolom adalah kombinasi dari jenis dan ukurannya. Varians ini dapat menyebabkan masalah dalam aplikasi yang menggunakan prosedur tersimpan, tampilan, atau fungsi bernilai tabel karena konsumen tersebut akan mengharapkan jumlah kolom yang berbeda.

Cara memperbaiki pelanggaran

Anda dapat melindungi konsumen dari prosedur tersimpan, tampilan, atau fungsi bernilai tabel tersebut dari perubahan skema dengan mengganti karakter wildcard dengan daftar nama kolom yang lengkap.

Example

Contoh berikut pertama-tama mendefinisikan tabel yang diberi nama [Table2] lalu menentukan dua prosedur tersimpan. Prosedur pertama berisi SELECT *, yang melanggar aturan SR0001. Prosedur kedua menghindari SELECT * dan secara eksplisit mencantumkan kolom dalam pernyataan 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: Pertimbangkan untuk menggunakan SCOPE_IDENTITY alih-alih @@IDENTITY

Karena @@IDENTITY adalah nilai identitas global, nilai tersebut mungkin telah diperbarui di luar cakupan saat ini dan mendapatkan nilai yang tidak terduga. Pemicu, termasuk pemicu berlapis yang digunakan oleh replikasi, dapat memperbarui @@IDENTITY di luar cakupan Anda saat ini.

Cara memperbaiki pelanggaran

Untuk mengatasi masalah ini, Anda harus mengganti referensi ke @@IDENTITY dengan SCOPE_IDENTITY, yang mengembalikan nilai identitas terbaru dalam cakupan pernyataan pengguna.

Example

Dalam contoh pertama, @@IDENTITY digunakan dalam prosedur tersimpan yang menyisipkan data ke dalam tabel. Tabel kemudian diterbitkan untuk replikasi penggabungan, yang menambahkan pemicu ke tabel yang diterbitkan. Oleh karena itu, @@IDENTITY dapat mengembalikan nilai dari operasi sisipkan ke dalam tabel sistem replikasi alih-alih operasi sisipkan ke dalam tabel pengguna.

Tabel Sales.Customer memiliki nilai identitas maksimum 29483. Jika Anda menyisipkan baris ke dalam tabel, @@IDENTITY dan SCOPE_IDENTITY() mengembalikan nilai yang berbeda. SCOPE_IDENTITY() mengembalikan nilai dari operasi sisipkan ke dalam tabel pengguna, tetapi @@IDENTITY mengembalikan nilai dari operasi sisipkan ke dalam tabel sistem replikasi.

Contoh kedua menunjukkan bagaimana Anda dapat menggunakan SCOPE_IDENTITY() untuk mengakses nilai identitas yang disisipkan dan mengatasi peringatan.

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: Hindari menggunakan jenis panjang variabel yang berukuran 1 atau 2

Saat Anda menggunakan jenis data dengan panjang variabel seperti VARCHAR, NVARCHAR, dan VARBINARY, Anda dikenakan biaya penyimpanan tambahan untuk melacak panjang nilai yang disimpan dalam jenis data. Selain itu, kolom panjang variabel disimpan setelah semua kolom dengan panjang tetap, yang dapat memiliki implikasi performa. Anda juga akan menerima peringatan jika Anda mendeklarasikan jenis panjang variabel, seperti VARCHAR, tetapi Anda tidak menentukan panjangnya. Peringatan ini terjadi karena, jika tidak ditentukan, panjang defaultnya adalah 1.

Cara memperbaiki pelanggaran

Jika panjang jenisnya akan sangat kecil (ukuran 1 atau 2) dan konsisten, nyatakan sebagai jenis panjang tetap, seperti CHAR, NCHAR, dan BINARY.

Example

Contoh ini memperlihatkan definisi untuk dua tabel. Tabel pertama mendeklarasikan string panjang variabel untuk memiliki panjang 2. Tabel kedua mendeklarasikan string panjang tetap sebagai gantinya, yang menghindari peringatan.

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 untuk jenis panjang variabel disimpan secara fisik setelah data untuk jenis panjang tetap. Oleh karena itu, Anda akan menyebabkan pergerakan data jika Anda mengubah kolom dari variabel menjadi panjang tetap dalam tabel yang tidak kosong.

SR0010: Hindari menggunakan sintaksis yang tidak digunakan lagi saat Anda menggabungkan tabel atau tampilan

Gabungan yang menggunakan sintaksis yang tidak digunakan lagi termasuk dalam dua kategori:

• Gabungan Dalam: Untuk gabungan dalam, nilai dalam kolom yang sedang digabungkan dibandingkan dengan menggunakan operator perbandingan seperti =, <, >=, dan sebagainya. INNER JOIN mengembalikan baris hanya jika setidaknya satu baris dari setiap tabel memenuhi kondisi gabungan.
• Gabungan Luar: Gabungan luar mengembalikan semua baris dari setidaknya salah satu tabel atau tampilan yang ditentukan dalam klausa FROM, selama baris tersebut memenuhi kondisi pencarian WHERE atau HAVING. Jika Anda menggunakan = atau = untuk menentukan gabungan luar, Anda menggunakan sintaksis yang tidak digunakan lagi.

Cara memperbaiki pelanggaran

Untuk memperbaiki pelanggaran dalam gabungan dalam, gunakan sintaks INNER JOIN.

Untuk memperbaiki pelanggaran pada outer join, gunakan sintaks OUTER JOIN yang tepat. Anda memiliki opsi berikut:

• LEFT OUTER JOIN atau LEFT JOIN
• RIGHT OUTER JOIN atau RIGHT JOIN

Contoh sintaksis yang tidak digunakan lagi dan sintaks yang diperbarui disediakan dalam contoh berikut. Informasi lebih lanjut tentang gabungan dapat ditemukan di Gabungan.

Examples

Enam contoh menunjukkan opsi berikut:

1. Contoh 1 menunjukkan sintaksis yang tidak digunakan lagi untuk gabungan dalam.
2. Contoh 2 menunjukkan bagaimana Anda dapat memperbarui Contoh 1 untuk menggunakan sintaks saat ini.
3. Contoh 3 menunjukkan sintaksis usang untuk join luar kiri.
4. Contoh 4 menunjukkan bagaimana Anda dapat memperbarui Contoh 2 untuk menggunakan sintaks saat ini.
5. Contoh 5 menunjukkan sintaksis yang tidak digunakan lagi untuk gabungan luar kanan.
6. Contoh 6 menunjukkan bagaimana Anda dapat memperbarui Contoh 5 untuk menggunakan sintaks saat ini.

-- 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: Parameter output (parameter) tidak diisi di semua jalur kode

Aturan ini mengidentifikasi kode di mana parameter output tidak diatur ke nilai dalam satu atau beberapa jalur kode melalui prosedur atau fungsi tersimpan. Aturan ini tidak mengidentifikasi jalur mana parameter output harus diatur. Jika beberapa parameter output memiliki masalah ini, satu peringatan muncul untuk setiap parameter.

Cara memperbaiki pelanggaran

Anda dapat memperbaiki masalah ini dengan salah satu dari dua cara. Anda dapat memperbaiki masalah ini dengan paling mudah jika Anda menginisialisasi parameter output ke nilai default di awal isi prosedur. Sebagai alternatif, Anda juga dapat mengatur parameter output ke nilai di jalur kode tertentu di mana parameter tidak diatur. Namun, Anda mungkin mengabaikan jalur kode yang tidak biasa dalam prosedur yang kompleks.

Important

Menentukan nilai dalam deklarasi prosedur, seperti CREATE PROC MyProcedure (@param1 INT = 10 OUTPUT) tidak akan menyelesaikan masalah. Anda harus menetapkan nilai ke parameter output dalam isi prosedur.

Example

Contoh berikut menunjukkan dua prosedur sederhana. Prosedur pertama tidak menetapkan nilai parameter output, @Sum. Prosedur kedua menginisialisasi @Sum parameter di awal prosedur, yang memastikan bahwa nilai akan diatur di semua jalur kode.

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: Kehilangan data mungkin terjadi saat transmisi dari {Type1} ke {Type2}

Jika jenis data secara tidak konsisten ditetapkan ke kolom, variabel, atau parameter, mereka secara implisit dikonversi saat kode Transact-SQL yang berisi objek tersebut dijalankan. Jenis konversi ini tidak hanya mengurangi kinerja tetapi juga, dalam beberapa kasus, menyebabkan hilangnya data secara halus. Misalnya, pemindaian tabel mungkin berjalan jika setiap kolom dalam klausa WHERE harus dikonversi. Lebih buruk lagi, data mungkin hilang jika string Unicode dikonversi ke string ASCII yang menggunakan halaman kode yang berbeda.

Aturan ini TIDAK:

• Periksa jenis kolom komputasi karena jenis tidak diketahui hingga run-time.
• Analisis apa saja di dalam pernyataan CASE. Ini juga tidak menganalisis nilai pengembalian pernyataan CASE.
• Menganalisis parameter input atau mengembalikan nilai panggilan ke ISNULL

Tabel ini meringkas pemeriksaan yang dicakup oleh aturan SR0014:

Konstruksi bahasa	Apa yang Diperiksa	Example
Nilai default parameter	Jenis data parameter	CREATE PROCEDURE p1(@p1 INT = 1) AS BEGIN END
MEMBUAT predikat INDEKS	Predikatnya adalah Boolean	CREATE INDEX index1 ON table1 (column1) WHERE column1 > 10
Argumen fungsi LEFT atau RIGHT	Jenis dan panjang argumen string	SET @v = LEFT('abc', 2)
Argumen fungsi CAST dan CONVERT	Ekspresi dan jenis valid	SET @v = CAST('abc' AS CHAR(10))
Pernyataan SET	Sisi kiri dan sisi kanan memiliki jenis yang kompatibel	SET @v1 = 'xyz' SELECT @v1 = c1 FROM t1
Predikat pernyataan IF	Predikatnya adalah Boolean	IF (@v > 10)
Predikat pernyataan WHILE	Predikatnya adalah Boolean	WHILE (@v > 10)
Pernyataan INSERT	Nilai dan kolom sudah benar	INSERT INTO t1(c1, c2) VALUES (99, 'xyz') INSERT INTO t1 SELECT c1 FROM t2.
PILIH predikat WHERE	Predikatnya adalah Boolean	SELECT * FROM t1 WHERE c1 > 10
SELECT TOP ekspresi	Ekspresi adalah tipe Bilangan Bulat atau Float	SELECT TOP 4 * FROM t1 SELECT TOP 1.5 PERCENT * FROM t1
Pernyataan UPDATE	Ekspresi dan kolom memiliki jenis yang kompatibel	UPDATE t1 SET c1 = 100
PERBARUI predikat	Predikatnya adalah Boolean	UPDATE t1 SET c1 = 100 WHERE c1 > 100
PERBARUI ekspresi TOP	Ekspresi adalah tipe Bilangan Bulat atau Float	UPDATE TOP 4 table1
HAPUS PREDIKAT	Predikatnya adalah Boolean	DELETE t1 WHERE c1 > 10
Perintah DELETE TOP	Ekspresi adalah tipe Bilangan Bulat atau Float	DELETE TOP 2 FROM t1
Deklarasi variabel DECLARE	Nilai awal dan jenis data kompatibel	DECLARE @v INT = 10
Argumen pernyataan EXECUTE dan jenis pengembalian	Parameter dan argumen	CREATE PROCEDURE p1 (@p1 INT) AS GO EXECUTE p1 100 EXECUTE @v1 = p1 100
Pernyataan RETURN	Ekspresi RETURN memiliki tipe data yang kompatibel	CREATE FUNCTION f1() RETURNS INT AS BEGIN RETURN 100 END
Kondisi pernyataan MERGE	Kondisi adalah Boolean	MERGE t1 USING t2 ON t1.c1 = t2.c2 WHEN t1.c1 > 10 THEN DELETE

Cara memperbaiki pelanggaran

Anda dapat menghindari dan mengatasi masalah ini dengan menetapkan jenis data secara konsisten dan dengan mengonversi jenis secara eksplisit jika diperlukan. Untuk informasi selengkapnya tentang cara mengonversi tipe data secara eksplisit, lihat halaman ini di situs Web Microsoft: CAST dan CONVERT (Transact-SQL).

Example

Contoh ini memperlihatkan dua prosedur tersimpan yang menyisipkan data ke dalam tabel. Prosedur pertama, procWithWarning, akan menyebabkan konversi implisit dari jenis data. Prosedur kedua, procFixed, menunjukkan bagaimana Anda dapat menambahkan konversi eksplisit untuk memaksimalkan performa dan menyimpan semua data.

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

Konten terkait

• Analisis kode SQL untuk meningkatkan kualitas kode
• Masalah penamaan T-SQL
• Masalah performa T-SQL
• Menganalisis kode T-SQL untuk menemukan cacat