Correct use of NULL in SQL Server and space occupancy _mssql

We often encounter null in the use or maintenance of SQL Server, so what is NULL? The following is a short description from MSDN (see "Null Values"):

• A value of NULL indicates that's the value is unknown. A value of NULL is different A empty or zero value. No two null values are equal. Comparisons between two null values, or between a null and any other value, return unknown because the value of each null is unknown.

In layman's terms, NULL is a value, and the value is unknown (unknown); null cannot be equivalent to any value, not even to itself, that is, NULL is not equal to NULL.

To get a clear understanding of the above, we create a test table test_null, and then insert 2 records with NULL values on the table and perform the related validation action:

--Creates a table with a null value that is
test_null (
  num INT not NULL PRIMARY KEY
  , fname NVARCHAR () null
  , lname NVA Rchar null
)-

-Inserts 4 data into the table: The last 2 records contain NULL values
insert into Test_null (num,fname,lname) VALUES (1, ' Tom ', ' Jane '
INSERT INTO Test_null (Num,fname,lname) VALUES (2, ' Dave ', ") insert into
test_null (num,fname) VALUES (3, ' Aaron ')
INSERT into Test_null (num,fname) VALUES (4, ' Betty ')

To verify that null values are unknown, we use the following SQL query table Test_null records to make the lname field = action:

If two null can be equal, then 4 records will be output. Actually output only 2 records

SELECT
  * from
test_null tn left 
JOIN test_null g on
  tn.num = g.num
WHERE tn.lname = g.lname
   
     >------------------------------------------
1  Tom Jane  1  Tom Jane
2  Dave    2  Dave  

--Query lname as ' the record, that is, verify NULL is not equal to ' SELECT ' from
test_null tn
WHERE tn.lname = '
------------------------------------------
2  Dave.  


   

Correct query/use NULL in SQL Server

Because null is unknown, we cannot use = or <> to determine or query a NULL record by default in SQL Server (see above), and the correct way is to query or filter a null-containing record using is NULL or is not NULL.

There is also a function isnull () that can be used to determine and convert null to other values.

--null-containing records through is null query
SELECT
  * from
test_null tn
WHERE tn.lname is null
----------------------- -------------------
3  Aaron  null
4  Betty  null

--null is not equal to any value, even null is not equivalent to null
--Default cannot use <> or = match null
SELECT
  * from
test_null tn
WHERE tn.lname <> null or tn.lname = NULL
------------------------------------------

Note, however, that SQL Server cannot use = or <> only by default, and you can use = or <> query null values when ANSI_NULLS is set to OFF

In other words, SQL Server opens the ANSI_NULLS option by default.

--Set ANSI_NULLS to OFF and use =null query to record
set ANSI_NULLS off
SELECT * from
test_null tn
WHERE tn.lname = Null
------------------------------------------
3  Aaron  null
4  Betty  null

To insert or update a null value:

--Insert 1 new records with NULL insert into
test_null (num,fname,lname) VALUES (5, ' Serena ', null)-

-Update the field value of a record to null
UPDATE test_null SET fname = NULL
WHERE num = 2

Space occupancy of NULL

The common understanding is that null does not occupy space in variable-length types (such as nvarchar (8)) and occupies storage space in fixed-length types such as int.

In fact, the above understanding is not rigorous. The real situation is that NULL takes up space in a variable length and fixed-length type

In SQL Server sparse columns, a null bitmap mask of 1 bits is required to store null values.

The above is the entire content of this article, I hope to help you learn.