SET ANSI_NULLS (Transact-SQL)

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)

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

Catatan

SET ANSI_NULLS OFF dan opsi ANSI_NULLS OFF 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

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.

Keterangan

Saat ANSI_NULLS AKTIF, pernyataan SELECT yang menggunakan WHERE column_name = NULL mengembalikan nol baris meskipun ada nilai NULL dalam column_name. Pernyataan SELECT 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-NULL di kolom. Selain itu, pernyataan SELECT yang menggunakan WHERE column_name <> XYZ_value mengembalikan semua baris yang tidak XYZ_value dan yang bukan NULL.

Saat 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 opsi ANSI_NULLS database saat ini berlaku. Untuk informasi selengkapnya tentang opsi database ANSI_NULLS, lihat MENGUBAH DATABASE (Transact-SQL).

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

Ekspresi Boolean SET ANSI_NULLS AKTIF SET ANSI_NULLS NONAKTIF
NULL = NULL UNKNOWN BENAR
1 = NULL UNKNOWN SALAH
NULL <> NULL UNKNOWN SALAH
1 <> NULL UNKNOWN BENAR
NULL > NULL UNKNOWN UNKNOWN
1 > NULL UNKNOWN UNKNOWN
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 memengaruhi perbandingan hanya jika salah satu operand perbandingan adalah variabel yang NULL atau NULL harfiah. Jika kedua sisi perbandingan adalah kolom atau ekspresi gabungan, pengaturan tidak memengaruhi perbandingan.

Agar skrip berfungsi seperti yang dimaksudkan, terlepas dari opsi database ANSI_NULLS atau pengaturan SET ANSI_NULLS, 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, INSERT, dan DELETE apa pun pada tabel dengan indeks pada kolom komputasi atau tampilan terindeks akan gagal. SQL Server mengembalikan kesalahan yang mencantumkan semua opsi SET yang melanggar nilai yang diperlukan. Selain itu, saat Anda menjalankan pernyataan SELECT, 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 Klien Asli SQL Server dan Penyedia OLE DB Klien Asli SQL Server untuk SQL Server secara otomatis mengatur 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 NONAKTIF.

Saat ANSI_DEFAULTS AKTIF, ANSI_NULLS diaktifkan.

Pengaturan ANSI_NULLS didefinisikan pada waktu eksekusi atau run time 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 dan nilai non-null dalam tabel. Contoh juga menunjukkan bahwa IS NULL tidak terpengaruh oleh SET ANSI_NULLS pengaturan.

-- 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