[sql] In SQL Server, what does "SET ANSI_NULLS ON" mean?

The definition says:

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are non-null values in column_name.

Does this mean that no nulls will be included in this query?

SELECT Region
FROM employees
WHERE Region = @region

Or do ANSI_NULLs concern only queries like this one (where the WHERE includes the specific word NULL)?

SELECT Region
FROM employees
WHERE Region = NULL

This question is related to sql tsql stored-procedures

The answer is


SET ANSI_NULLS ON

IT Returns all values including null values in the table

SET ANSI_NULLS off

it Ends when columns contains null values


If ANSI_NULLS is set to "ON" and if we apply = , <> on NULL column value while writing select statement then it will not return any result.

Example

create table #tempTable (sn int, ename varchar(50))

insert into #tempTable
values (1, 'Manoj'), (2, 'Pankaj'), (3, NULL), (4, 'Lokesh'), (5, 'Gopal')

SET ANSI_NULLS ON

select * from #tempTable where ename is NULL -- (1 row(s) affected)
select * from #tempTable where ename = NULL -- (0 row(s) affected)
select * from #tempTable where ename is not NULL -- (4 row(s) affected)
select * from #tempTable where ename <> NULL -- (0 row(s) affected)

SET ANSI_NULLS OFF

select * from #tempTable where ename is NULL -- (1 row(s) affected)
select * from #tempTable where ename = NULL -- (1 row(s) affected)
select * from #tempTable where ename is not NULL -- (4 row(s) affected)
select * from #tempTable where ename <> NULL -- (4 row(s) affected)

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

For e.g

DECLARE @TempVariable VARCHAR(10)
SET @TempVariable = NULL

SET ANSI_NULLS ON
SELECT 'NO ROWS IF SET ANSI_NULLS ON' where    @TempVariable = NULL
-- IF ANSI_NULLS ON , RETURNS ZERO ROWS


SET ANSI_NULLS OFF
SELECT 'THERE WILL BE A ROW IF ANSI_NULLS OFF' where    @TempVariable =NULL
-- IF ANSI_NULLS OFF , THERE WILL BE ROW !

Set ANSI NULLS OFF will make NULL = NULL comparision return true. EG :

        SET ANSI_NULLS OFF
        select * from sys.tables
        where principal_id = Null

will return some result as displayed below: zcwInvoiceDeliveryType 744547 NULL zcExpenseRptStatusTrack 2099048 NULL ZCVendorPermissions 2840564 NULL ZCWOrgLevelClientFee 4322525 NULL

While this query will not return any results:

        SET ANSI_NULLS ON 
        select * from sys.tables
        where principal_id = Null

If @Region is not a null value (lets say @Region = 'South') it will not return rows where the Region field is null, regardless of the value of ANSI_NULLS.

ANSI_NULLS will only make a difference when the value of @Region is null, i.e. when your first query essentially becomes the second one.

In that case, ANSI_NULLS ON will not return any rows (because null = null will yield an unknown boolean value (a.k.a. null)) and ANSI_NULLS OFF will return any rows where the Region field is null (because null = null will yield true)


I guess the main thing here is:

Never user:

  • @anything = NULL
  • @anything <> NULL
  • @anything != null

Always use:

  • @anything IS NULL
  • @anything IS NOT NULL

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to stored-procedures

How to create temp table using Create statement in SQL Server? How do I pass a list as a parameter in a stored procedure? SQL Server IF EXISTS THEN 1 ELSE 2 Stored procedure with default parameters Could not find server 'server name' in sys.servers. SQL Server 2014 How to kill all active and inactive oracle sessions for user EXEC sp_executesql with multiple parameters MySQL stored procedure return value SQL Server: use CASE with LIKE SQL server stored procedure return a table