SET ANSI_NULLS (Transact-SQL)

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics AnalyticsPlatform System (PDW)

Menentukan perilaku yang mematuhi ISO dari operator perbandingan Equals (=) dan Not Equal To (<>) ketika digunakan dengan NULL nilai di SQL Server.

• SET ANSI_NULLS ON - Mengevaluasi dan {expression} = NULL{expression} <> NULL seolah-olah False nilainya {expression} adalah NULL. Perilaku ini sesuai dengan ANSI.
• SET ANSI_NULLS OFF - Mengevaluasi {expression} = NULL sebagai True dan {expression} <> NULL seolah-olah False nilainya {expression} adalah NULL. Perilaku ini tidak disarankan, karena NULL nilai tidak boleh dibandingkan menggunakan = operator dan <> .

Catatan

SET ANSI_NULLS OFF ANSI_NULLS OFF dan opsi database tidak digunakan lagi. Dimulai dengan SQL Server 2017 (14.x), ANSI_NULLS selalu diatur ke AKTIF. Fitur yang tidak digunakan lagi tidak boleh digunakan dalam aplikasi baru. Untuk informasi selengkapnya, lihat Fitur Mesin Database yang Tidak Digunakan Lagi di SQL Server 2017.

Konvensi sintaks transact-SQL

Sintaks

Sintaks untuk SQL Server, kumpulan SQL tanpa server di Azure Synapse Analytics, Microsoft Fabric

SET ANSI_NULLS { ON | OFF }

Sintaks untuk Azure Synapse Analytics dan Analytics Platform System (PDW)

SET ANSI_NULLS ON

Keterangan

Saat ANSI_NULLS AKTIF, SELECT pernyataan yang menggunakan WHERE column_name = NULL mengembalikan baris nol meskipun ada nilai NULL dalam column_name. SELECT Pernyataan yang menggunakan WHERE column_name <> NULL mengembalikan baris nol meskipun ada nilai non-NULL dalam column_name.

Saat ANSI_NULLS NONAKTIF, operator perbandingan Equals (=) dan Not Equal To (<>) tidak mengikuti standar ISO. Pernyataan SELECT yang menggunakan WHERE column_name = NULL mengembalikan baris yang memiliki nilai null dalam column_name. Pernyataan SELECT yang menggunakan WHERE column_name <> NULL mengembalikan baris yang memiliki nilai non-dalamNULL kolom. Selain itu, SELECT pernyataan yang menggunakan WHERE column_name <> XYZ_value mengembalikan semua baris yang tidak XYZ_value dan itu bukan NULL.

Ketika ANSI_NULLS AKTIF, semua perbandingan terhadap nilai null dievaluasi ke UNKNOWN. Saat SET ANSI_NULLS NONAKTIF, perbandingan semua data terhadap nilai null dievaluasi ke TRUE jika nilai data adalah NULL. Jika SET ANSI_NULLS tidak ditentukan, pengaturan ANSI_NULLS opsi database saat ini berlaku. Untuk informasi selengkapnya tentang ANSI_NULLS opsi database, lihat MENGUBAH DATABASE (Transact-SQL).

Tabel berikut ini memperlihatkan bagaimana pengaturan ANSI_NULLS memengaruhi hasil ekspresi Boolean menggunakan nilai null dan non-null.

Ekspresi Boolean	SET ANSI_NULLS AKTIF	SET ANSI_NULLS NONAKTIF
NULL = NULL	TIDAK DIKETAHUI	BENAR
1 = NULL	TIDAK DIKETAHUI	SALAH
NULL <> NULL	TIDAK DIKETAHUI	SALAH
1 <> NULL	TIDAK DIKETAHUI	BENAR
NULL > NULL	TIDAK DIKETAHUI	TIDAK DIKETAHUI
1 > NULL	TIDAK DIKETAHUI	TIDAK DIKETAHUI
NULL IS NULL	BENAR	BENAR
1 IS NULL	SALAH	SALAH
NULL IS NOT NULL	SALAH	SALAH
1 IS NOT NULL	BENAR	BENAR

SET ANSI_NULLS ON mempengaruhi perbandingan hanya jika salah satu operan perbandingan adalah variabel yang atau NULL harfiah NULL. Jika kedua sisi perbandingan adalah kolom atau ekspresi gabungan, pengaturan tidak memengaruhi perbandingan.

Agar skrip berfungsi seperti yang dimaksudkan, terlepas dari ANSI_NULLSSET ANSI_NULLSopsi database atau pengaturan , gunakan IS NULL dan IS NOT NULL dalam perbandingan yang mungkin berisi nilai null.

ANSI_NULLS harus diatur ke AKTIF untuk menjalankan kueri terdistribusi.

ANSI_NULLS juga harus AKTIF saat Anda membuat atau mengubah indeks pada kolom komputasi atau tampilan terindeks. Jika SET ANSI_NULLS NONAKTIF, pernyataan , CREATE, UPDATE, dan INSERT apa pun DELETEpada tabel dengan indeks pada kolom komputasi atau tampilan terindeks gagal. SQL Server mengembalikan kesalahan yang mencantumkan semua opsi SET yang melanggar nilai yang diperlukan. Selain itu, saat Anda menjalankan SELECT pernyataan, jika SET ANSI_NULLS NONAKTIF, SQL Server mengabaikan nilai indeks pada kolom atau tampilan yang dihitung dan menyelesaikan operasi pilih seolah-olah tidak ada indeks tersebut pada tabel atau tampilan.

Catatan

ANSI_NULLS adalah salah satu dari tujuh opsi SET yang harus diatur ke nilai yang diperlukan saat berhadapan dengan indeks pada kolom komputasi atau tampilan terindeks. Opsi ANSI_PADDING, , ANSI_WARNINGSARITHABORT, QUOTED_IDENTIFIER, dan CONCAT_NULL_YIELDS_NULL juga harus diatur ke AKTIF, dan NUMERIC_ROUNDABORT harus diatur ke NONAKTIF.

Driver ODBC SQL Server Native Client dan SQL Server Native Client OLE DB Provider untuk SQL Server secara otomatis diatur ANSI_NULLS ke AKTIF saat menyambungkan. Pengaturan ini dapat dikonfigurasi di sumber data ODBC, di atribut koneksi ODBC, atau di properti koneksi OLE DB yang diatur dalam aplikasi sebelum menyambungkan ke instans SQL Server. Default untuk SET ANSI_NULLS adalah NONAKTIF.

Ketika ANSI_DEFAULTS AKTIF, ANSI_NULLS diaktifkan.

Pengaturan ANSI_NULLS didefinisikan pada waktu eksekusi atau eksekusi dan bukan pada waktu penguraian.

Untuk menampilkan pengaturan saat ini untuk pengaturan ini, jalankan kueri berikut:

DECLARE @ANSI_NULLS VARCHAR(3) = 'OFF';  
IF ( (32 & @@OPTIONS) = 32 ) SET @ANSI_NULLS = 'ON';  
SELECT @ANSI_NULLS AS ANSI_NULLS;   

Izin

Memerlukan keanggotaan dalam peran publik .

Contoh

Contoh berikut menggunakan operator perbandingan Equals (=) dan Not Equal To (<>) untuk membuat perbandingan dengan NULL atau 0 dan null nilai dalam variabel.

SET ANSI_NULLS OFF
DECLARE @var INT = NULL
SELECT
    IIF(@var = NULL,  'True', 'False') as EqualNull,
    IIF(@var <> NULL, 'True', 'False') as DifferentNull,
    IIF(@var = 0,     'True', 'False') as EqualZero,
    IIF(@var <> 0,    'True', 'False') as DifferentZero

Hasilnya diperlihatkan dalam tabel berikut.

Sama Null	BerbedaNull	Sama dengan nol	Nol Berbeda
Benar	Tidak benar	Tidak benar	Benar

Dengan SET ANSI_NULLS ON semua ekspresi akan dievaluasi sebagai 'False' karena NULL tidak dapat dibandingkan dengan NULL atau 0 menggunakan operator ini.

Contoh berikut menggunakan operator perbandingan Equals (=) dan Not Equal To (<>) untuk membuat perbandingan dengan NULL dan nilai non-null dalam tabel. Contoh juga menunjukkan SET ANSI_NULLS pengaturan tidak memengaruhi IS NULL.

-- Create table t1 and insert values.  
CREATE TABLE dbo.t1 (a INT NULL);  
INSERT INTO dbo.t1 values (NULL),(0),(1);  
GO  
  
-- Print message and perform SELECT statements.  
PRINT 'Testing default setting';  
DECLARE @varname int;   
SET @varname = NULL;  
  
SELECT a  
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO 

Sekarang atur ANSI_NULLS ke AKTIF dan uji.

PRINT 'Testing ANSI_NULLS ON';  
SET ANSI_NULLS ON;  
GO  
DECLARE @varname int;  
SET @varname = NULL  
  
SELECT a   
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO  

Sekarang atur ANSI_NULLS ke NONAKTIF dan uji.

PRINT 'Testing ANSI_NULLS OFF';  
SET ANSI_NULLS OFF;  
GO  
DECLARE @varname int;  
SET @varname = NULL;  
SELECT a   
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO  
  
-- Drop table t1.  
DROP TABLE dbo.t1;  

Konten terkait

• Pernyataan SET (Transact-SQL)
• SESSIONPROPERTY (Transact-SQL)
• = (Sama dengan) (Transact-SQL)
• KALAU... ELSE (Transact-SQL)
• <> (Tidak Sama Dengan) (T-SQL)
• SET ANSI_DEFAULTS (Transact-SQL)
• where (Transact-SQL)
• SEMENTARA (T-SQL)