SET ANSI_NULLS (Transact-SQL)

Si applica a: SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di strumenti analitici (PDW)

Specifica il comportamento conforme a ISO degli operatori di confronto Equals (=) e Not Equal To (<>) quando vengono usati con NULL i valori in SQL Server.

• SET ANSI_NULLS ON - Valuta sia {expression} = NULL che {expression} <> NULL come False se il valore di {expression} sia NULL. Questo comportamento è conforme ad ANSI.
• SET ANSI_NULLS OFF- Valuta come e come se il valore di {expression} = NULL sia True.{expression} <> NULLFalse{expression}NULL Questo comportamento non è consigliato perché i NULL valori non devono essere confrontati con = gli operatori e <> .

Nota

SET ANSI_NULLS OFF e l'opzione ANSI_NULLS OFF di database sono deprecate. A partire da SQL Server 2017 (14.x), ANSI_NULLS è sempre impostato su ON. È consigliabile non usare le funzionalità deprecate nelle nuove applicazioni. Per altre informazioni, vedere Funzionalità di motore di database deprecate in SQL Server 2017.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi per SQL Server, pool SQL serverless in Azure Synapse Analytics, Microsoft Fabric

SET ANSI_NULLS { ON | OFF }

Sintassi per Azure Synapse Analytics e piattaforma di strumenti analitici (PDW)

SET ANSI_NULLS ON

Osservazioni:

Quando ANSI_NULLS è ON, un'istruzione SELECT che usa restituisce WHERE column_name = NULL zero righe anche se sono presenti valori NULL in column_name. Un'istruzione SELECT che usa restituisce WHERE column_name <> NULL zero righe anche se in column_name sono presenti valori non NULL.

Quando ANSI_NULLS è OFF, gli operatori di confronto Equals (=) e Not Equal To (<>) non seguono lo standard ISO. Un'istruzione SELECT che usa restituisce WHERE column_name = NULL le righe con valori Null in column_name. Un'istruzione SELECT che utilizza restituisce WHERE column_name <> NULL le righe con valori nonNULL presenti nella colonna. Inoltre, un'istruzione SELECT che usa restituisce WHERE column_name <> XYZ_value tutte le righe che non sono XYZ_value e che non NULLsono .

Quando ANSI_NULLS è ON, tutti i confronti con un valore Null restituiscono UNKNOWN. Quando SET ANSI_NULLS è OFF, i confronti di tutti i dati rispetto a un valore Null restituiscono TRUE se il valore dei dati è NULL. Se SET ANSI_NULLS non viene specificato, viene applicata l'impostazione ANSI_NULLS dell'opzione del database corrente. Per altre informazioni sull'opzione ANSI_NULLS di database, vedere ALTER DATABASE (Transact-SQL).

Nella tabella seguente viene illustrato come l'impostazione di ANSI_NULLS influisce sui risultati delle espressioni booleane usando valori Null e non Null.

Espressione booleana	IMPOSTARE ANSI_NULLS SU	IMPOSTARE ANSI_NULLS OFF
NULL = NULL	SCONOSCIUTO	Vero
1 = NULL	SCONOSCIUTO	Falso
NULL <> NULL	SCONOSCIUTO	Falso
1 <> NULL	SCONOSCIUTO	Vero
NULL > NULL	SCONOSCIUTO	SCONOSCIUTO
1 > NULL	SCONOSCIUTO	SCONOSCIUTO
NULL IS NULL	Vero	Vero
1 IS NULL	Falso	Falso
NULL IS NOT NULL	Falso	Falso
1 IS NOT NULL	Vero	Vero

SET ANSI_NULLS ON influisce su un confronto solo se uno degli operandi del confronto è una variabile che è NULL o un valore letterale NULL. Se entrambi gli operandi del confronto sono colonne o espressioni composte, l'impostazione non ha effetto sul confronto.

Affinché uno script funzioni come previsto, indipendentemente dall'opzione di database o dall'impostazione ANSI_NULLS di SET ANSI_NULLS, usare IS NULL e IS NOT NULL nei confronti che potrebbero contenere valori Null.

ANSI_NULLS deve essere impostato su ON per l'esecuzione di query distribuite.

ANSI_NULLS deve essere attivato anche quando si creano o si modificano indici in colonne calcolate o viste indicizzate. Se SET ANSI_NULLS è OFF, tutte CREATEle istruzioni , UPDATEINSERT, e DELETE nelle tabelle con indici in colonne calcolate o viste indicizzate hanno esito negativo. SQL Server restituisce un errore in cui sono elencate tutte le opzioni SET che violano i valori obbligatori. Inoltre, quando si esegue un'istruzione SELECT , se SET ANSI_NULLS è OFF, SQL Server ignora i valori di indice nelle colonne calcolate o nelle viste e risolve l'operazione di selezione come se tali indici non fossero presenti nelle tabelle o nelle viste.

Nota

ANSI_NULLS è una delle sette opzioni SET che devono essere impostate sui valori obbligatori quando si gestiscono indici in colonne calcolate o viste indicizzate. Anche le opzioni ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER e CONCAT_NULL_YIELDS_NULL devono essere impostate su ON e NUMERIC_ROUNDABORT deve essere impostata su OFF.

Il driver ODBC di SQL Server Native Client e il provider OLE DB di SQL Server Native Client per SQL Server vengono impostati automaticamente su ANSI_NULLS ON durante la connessione. È possibile configurare questa impostazione nelle origini dei dati ODBC, negli attributi di connessione ODBC o nelle proprietà di connessione OLE DB impostate nell'applicazione prima della connessione a un'istanza di SQL Server. Il valore predefinito per SET ANSI_NULLS è OFF.

Quando ANSI_DEFAULTS è ATTIVATO, ANSI_NULLS è abilitato.

L'impostazione di è definita in fase di ANSI_NULLS esecuzione o in fase di esecuzione e non in fase di analisi.

Per visualizzare l'impostazione corrente per questa impostazione, eseguire la query riportata di seguito:

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

Autorizzazioni

È richiesta l'appartenenza al ruolo public .

Esempi

Nell'esempio seguente vengono utilizzati gli operatori di confronto Equals (=) e Not Equal To (<>) per eseguire confronti con NULL o 0 e il null valore in una variabile.

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

I risultati sono illustrati nella tabella seguente.

EqualNull	DifferentNull	Uguale a zero	DifferentZero
Vero	Falso	Falso	Vero

Con SET ANSI_NULLS ON tutte le espressioni viene valutata come 'False' perché NULL non può essere confrontata con NULL o 0 usando questi operatori.

Nell'esempio seguente vengono usati gli operatori di confronto Uguale a (=) e Diverso da (<>) per confrontare i valori NULL e non Null in una tabella. L'esempio mostra anche che l'impostazione SET ANSI_NULLS non influisce su 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 

Impostare ora ANSI_DEFAULTS su ON e testare.

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  

Impostare ora ANSI_DEFAULTS su OFF e testare.

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;  

Contenuto correlato

• Istruzioni SET (Transact-SQL)
• SESSIONPROPERTY (Transact-SQL)
• = (uguale a) (Transact-SQL)
• SE... ELSE (Transact-SQL)
• <> (diverso da) (Transact-SQL)
• SET ANSI_DEFAULTS (Transact-SQL)
• DOVE (Transact-SQL)
• MENTRE (Transact-SQL)