:::: MENU ::::

using NULL in SQL Server T-SQL

dbDigger, Null Values

using NULL in SQL Server T-SQL

Null is a special value or mark, that is to indicate absence of any data value. It is not a data value but an indicator that we are missing data.
Following are interesting considerations about nulls

  • Use NULL to indicate unknown/missing information only. Do not use NULL in place of zeroes, zero-length strings or other “known” blank values.
  • Update your NULL with proper information as soon as possible.
  • According to ANSI-92 SQL Standard, comparisons with NULL always result in Unknown.

e.g.

SET ANSI_NULLS ON
GO

SELECT * FROM Northwind..Employees
WHERE Region IS NULL
GO

This query returns four employee records. But following query will return no record, the reason for this is that Region = NULL’ evaluates to Unknown every time. Since it never evaluates to True for any rows, it returns no records.

SET ANSI_NULLS ON
GO

SELECT * FROM Northwind.Employees
WHERE Region = NULL
GO

It has been widely miss stated that “the result of a comparison is false if one of the operands is NULL.” In fact, the ANSI-92 SQL Standard states that if one of the operands is NULL, the result of the comparison is “UNKNOWN”.
Presumably to make the transition smoother for 3GL and 4GL programmers from other languages moving over to SQL, Microsoft implemented the SET ANSI_NULLS OFF option, which allows you to perform = and <> comparisons with NULL values. To demonstrate this in action, we can perform the following query:

SET ANSI_NULLS OFF
GO

SELECT * FROM [Northwind].[dbo].[Employees]
WHERE [Region] = NULL
GO

This allows you to perform basic equality comparisons to NULL; however, SQL written using this option may not be portable to other platforms, and might confuse others who use ANSI Standard NULL-handling.

Use SET ANSI_NULLS ON, and always use ANSI Standard SQL Syntax for NULLs. Straying from the standard can cause problems including portability issues, incompatibility with existing code and databases and returning incorrect results.

If you perform scalar math operations and string concatenation functions with NULL, the result is always NULL. For instance, this query returns NULL.

Consult us to explore the Databases. Contact us