ZESTAW ANSI_NULLS (Transact-SQL)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Database w Microsoft Fabric

Określa zachowanie zgodne ze standardem ISO operatorów porównania Equals (=) i Not Equal To (<>), gdy są one używane z wartościami NULL w programie SQL Server.

• SET ANSI_NULLS ON - Oblicza zarówno , {expression} = NULL jak i {expression} <> NULL tak, jakby False wartość parametru {expression} to NULL. To zachowanie jest zgodne ze standardem ANSI.
• SET ANSI_NULLS OFF - Ocenia {expression} = NULL jako i True{expression} <> NULL tak, jakby False wartość parametru {expression} to NULL. To zachowanie nie jest zalecane, ponieważ NULL wartości nie powinny być porównywane przy użyciu = operatorów i <> .

Uwaga / Notatka

SET ANSI_NULLS OFF ANSI_NULLS OFF opcja bazy danych jest przestarzała. Począwszy od programu SQL Server 2017 (14.x), ANSI_NULLS jest zawsze ustawiona na wartość WŁĄCZONE. Przestarzałe funkcje nie powinny być używane w nowych aplikacjach. Aby uzyskać więcej informacji, zobacz Przestarzałe funkcje aparatu bazy danych w programie SQL Server 2017.

Transact-SQL konwencje składni

Składnia

Składnia dla programu SQL Server, bezserwerowej puli SQL w usłudze Azure Synapse Analytics, Microsoft Fabric

SET ANSI_NULLS { ON | OFF }

Składnia dla usługi Azure Synapse Analytics i systemu platformy analizy (PDW)

SET ANSI_NULLS ON

Uwagi

Gdy ANSI_NULLS jest włączona, instrukcja używająca SELECTWHERE column_name = NULL zwraca zero wierszy, nawet jeśli w column_name istnieją wartości NULL. Instrukcja SELECT , która używa WHERE column_name <> NULL zerowych wierszy, nawet jeśli w column_name istnieją wartości inne niż NULL.

Gdy ANSI_NULLS jest wyłączona, operatory porównania Equals (=) i Not Equal To (<>) nie są zgodne ze standardem ISO. Instrukcja SELECT , która używa WHERE column_name = NULL , zwraca wiersze, które mają wartości null w column_name. Instrukcja SELECT , która używa WHERE column_name <> NULL , zwraca wiersze, które mają wartości inneNULL niż w kolumnie. Ponadto instrukcja SELECT , która używa WHERE column_name <> XYZ_value , zwraca wszystkie wiersze, które nie są XYZ_value i które nie NULLsą .

Gdy ANSI_NULLS wartość jest włączona, wszystkie porównania z wartością null są obliczane na WARTOŚĆ UNKNOWN. Gdy SET ANSI_NULLS wartość jest wyłączona, porównania wszystkich danych z wartością null są obliczane na wartość TRUE, jeśli wartość danych to NULL. Jeśli SET ANSI_NULLS nie zostanie określony, ma zastosowanie ustawienie ANSI_NULLS opcji bieżącej bazy danych. Aby uzyskać więcej informacji na temat ANSI_NULLS opcji bazy danych, zobacz ALTER DATABASE (Transact-SQL).

W poniższej tabeli pokazano, jak ustawienie ANSI_NULLS funkcji wpływa na wyniki wyrażeń logicznych przy użyciu wartości null i wartości innych niż null.

Wyrażenie logiczne	USTAW ANSI_NULLS WŁĄCZONE	USTAW ANSI_NULLS WYŁĄCZONE
NULL = NULL	NIEZNANY	PRAWIDŁOWE
1 = NULL	NIEZNANY	FAŁSZYWY
NULL <> NULL	NIEZNANY	FAŁSZYWY
1 <> NULL	NIEZNANY	PRAWIDŁOWE
NULL > NULL	NIEZNANY	NIEZNANY
1 > NULL	NIEZNANY	NIEZNANY
NULL IS NULL	PRAWIDŁOWE	PRAWIDŁOWE
1 IS NULL	FAŁSZYWY	FAŁSZYWY
NULL IS NOT NULL	FAŁSZYWY	FAŁSZYWY
1 IS NOT NULL	PRAWIDŁOWE	PRAWIDŁOWE

SET ANSI_NULLS ON ma wpływ na porównanie tylko wtedy, gdy jeden z operandów porównania jest zmienną, która jest NULL lub literałem NULL. Jeśli obie strony porównania to kolumny lub wyrażenia złożone, ustawienie nie ma wpływu na porównanie.

Aby skrypt działał zgodnie z ANSI_NULLS oczekiwaniami, niezależnie od opcji bazy danych lub ustawienia SET ANSI_NULLS, użyj polecenia IS NULL i IS NOT NULL w porównaniach, które mogą zawierać wartości null.

ANSI_NULLS należy ustawić wartość WŁĄCZONE na potrzeby wykonywania zapytań rozproszonych.

ANSI_NULLS Podczas tworzenia lub zmieniania indeksów w kolumnach obliczeniowych lub widokach indeksowanych muszą być również włączone. Jeśli właściwość SET ANSI_NULLS jest wyłączona, wszystkie CREATEinstrukcje , UPDATE, INSERTi DELETE w tabelach z indeksami w obliczonych kolumnach lub indeksowanych widokach kończą się niepowodzeniem. Program SQL Server zwraca błąd, który wyświetla listę wszystkich opcji ZESTAWU, które naruszają wymagane wartości. Ponadto podczas wykonywania SELECT instrukcji, jeśli SET ANSI_NULLS jest wyłączona, program SQL Server ignoruje wartości indeksu dla obliczonych kolumn lub widoków i rozwiąże operację wybierania tak, jakby nie było takich indeksów w tabelach lub widokach.

Uwaga / Notatka

ANSI_NULLS jest jedną z siedmiu opcji ZESTAWU, które muszą być ustawione na wymagane wartości podczas pracy z indeksami w obliczonych kolumnach lub widokach indeksowanych. Opcje ANSI_PADDING, , ANSI_WARNINGSARITHABORT, QUOTED_IDENTIFIERi CONCAT_NULL_YIELDS_NULL muszą być również ustawione na WŁĄCZONE i NUMERIC_ROUNDABORT muszą być ustawione na WARTOŚĆ WYŁĄCZONE.

Sterownik ODBC klienta natywnego programu SQL Server i dostawca OLE DB klienta natywnego programu SQL Server dla programu SQL Server automatycznie ustawić wartość ANSI_NULLS WŁĄCZONE podczas nawiązywania połączenia. To ustawienie można skonfigurować w źródłach danych ODBC, w atrybutach połączenia ODBC lub we właściwościach połączenia OLE DB ustawionych w aplikacji przed nawiązaniem połączenia z wystąpieniem programu SQL Server. Wartość domyślna to SET ANSI_NULLS OFF.

Gdy ANSI_DEFAULTS jest włączona, ANSI_NULLS jest włączona.

Ustawienie elementu jest definiowane ANSI_NULLS w czasie wykonywania lub wykonywania, a nie w czasie analizy.

Aby wyświetlić bieżące ustawienie dla tego ustawienia, uruchom następujące zapytanie:

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

Uprawnienia

Wymaga członkostwa w publicznej roli .

Przykłady

W poniższym przykładzie użyto operatorów porównania Equals (=) i Not Equal To (<>) w celu porównania z NULL zmienną lub 0 i null wartością w zmiennej.

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

Wyniki są wyświetlane w poniższej tabeli.

EqualNull (Równa wartość null)	RóżneNull	EqualZero (Równowartość Zero)	RóżneZero
Prawda	Nieprawda	Nieprawda	Prawda

W przypadku SET ANSI_NULLS ON wszystkich wyrażeń zostanie obliczona jako "Fałsz", ponieważ NULL nie można jej porównać ani NULL0 użyć tych operatorów.

W poniższym przykładzie użyto operatorów porównania Equals (=) i Not Equal To (<>) w celu porównania z wartościami NULL innymi niż null w tabeli. W przykładzie pokazano również, że SET ANSI_NULLS ustawienie nie ma wpływu na IS NULLwartość .

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

Teraz ustaw ANSI_NULLS na WARTOŚĆ WŁ. i przetestuj.

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  

Teraz ustaw ANSI_NULLS na wartość WYŁ. i przetestuj.

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;  

Treści powiązane

• INSTRUKCJE SET (Transact-SQL)
• SESSIONPROPERTY (Transact-SQL)
• = (Równa się) (Transact-SQL)
• JEŚLI... ELSE (Transact-SQL)
• <> (Nie równa się) (Transact-SQL)
• ZESTAW ANSI_DEFAULTS (Transact-SQL)
• GDZIE? (Transact-SQL)
• PODCZAS (Transact-SQL)