SET ANSI_NULLS (Transact-SQL)

  • Articolo

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 valori Null in SQL Server.

Nota

SET ANSI_NULLS OFF e l'opzione di database ANSI_NULLS OFF 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

Nota

Per visualizzare la sintassi Transact-SQL per SQL Server 2014 (12.x) e versioni precedenti, vedere la documentazione delle versioni precedenti.

Osservazioni:

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

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

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

La tabella seguente illustra come l'impostazione di ANSI_NULLS influisce sui risultati di un certo numero di espressioni booleane che usano valori NULL e non NULL.

Espressione booleana edizione Standard T ANSI_NULLS ON edizione Standard T ANSI_NULLS OFF
NULL = NULL SCONOSCIUTO TRUE
1 = NULL SCONOSCIUTO FALSE
NULL <> NULL SCONOSCIUTO FALSE
1 <> NULL SCONOSCIUTO TRUE
NULL > NULL SCONOSCIUTO SCONOSCIUTO
1 > NULL SCONOSCIUTO SCONOSCIUTO
NULL IS NULL TRUE TRUE
1 IS NULL FALSE FALSO
NULL IS NOT NULL FALSO FALSE
1 IS NOT NULL TRUE TRUE

SET ANSI_NULLS ON influisce su un confronto solo se uno degli operandi del confronto è una variabile 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 presenti il funzionamento previsto indipendentemente dall'opzione di database ANSI_NULLS o dall'impostazione di SET ANSI_NULLS, utilizzare IS NULL e IS NOT NULL nei confronti che potrebbero includere valori Null.

Per l'esecuzione di query distribuite, è necessario impostare l'opzione ANSI_NULLS su ON.

È necessario che l'opzione ANSI_NULLS sia impostata su ON anche per la creazione o la modifica di indici su colonne calcolate o viste indicizzate. Se l'opzione SET ANSI_NULLS è impostata su OFF, qualsiasi istruzione CREATE, UPDATE, INSERT e DELETE eseguita in tabelle con indici su colonne calcolate o viste indicizzate avrà esito negativo. SQL Server restituisce un errore in cui sono elencate tutte le opzioni SET che violano i valori obbligatori. Se inoltre l'opzione SET ANSI_NULLS è impostata su OFF durante l'esecuzione di un'istruzione SELECT, SQL Server ignora i valori di indice delle colonne calcolate o delle viste e l'operazione di selezione viene risolta come se le tabelle o le viste non includessero tali indici.

Nota

ANSI_NULLS è una delle sette opzioni SET che è necessario impostare sui valori richiesti quando si utilizzano 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 impostano automaticamente l'opzione QUOTED_IDENTIFIER su ANSI_NULLS al momento della 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 l'opzione ANSI_DEFAULTS è impostata su ON, l'opzione ANSI_NULLS è abilitata.

L'opzione ANSI_NULLS viene definita in fase di esecuzione, 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 usati gli operatori di confronto Uguale a (=) e Diverso da (<>) per confrontare i valori NULL e non Null in una tabella. L'esempio evidenzia anche il fatto che l'impostazione dell'opzione 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