Correct use and space occupation of NULL in SQL Server, servernull

Correct use and space occupation of NULL in SQL Server, servernull

We often encounter NULL in the use or maintenance of SQL Server. What is NULL? The following is a brief description provided by MSDN (see "Null Values "):

• A value of NULL indicates that the value is unknown. A value of NULL is different from an 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 general, NULL is a value, and this value is unknown. NULL cannot be equivalent to any value, or even its own, that is, NULL is not equal to NULL.

To clearly understand the above content, we create a test table Test_NULL, insert two records containing NULL values into the table, and perform related verification operations:

-- Create table Test_NULL (num int not null primary key, fname NVARCHAR (50) NULL, lname NVARCHAR (50) NULL) for a TABLE that allows NULL values -- insert four data records into the TABLE: the last two 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, 'login ')

To verify that the NULL value is unknown, we use the following SQL statement to query the record Test_NULL and perform the = operation on the lname field:

-- If two NULL values can be equal, four records will be output. Only two records are output.

SELECT * FROM Test_NULL tn left join Test_NULL g ON tn. num = g. numWHERE tn. lname = g. lname ---------------------------------------- 1 Tom Jane 1 Tom Jane2 Dave 2 Dave -- query records with lname '', that is, verify that NULL is not equal to ''select * FROM Test_NULL tnWHERE tn. lname = ''-------------------------------------------- 2 Dave

Correct query/use NULL in SQL Server

NULL is unknown, so SQL Server cannot use = or <> to judge or query a NULL record by default (see the above). The correct method is: use is null or is not null to query or filter a NULL record.

In addition, the ISNULL () function can be used to determine and convert NULL to another value.

-- Query records containing NULL through is null select * FROM Test_NULL tnWHERE tn. lname is null ------------------------------------- ---3 Aaron NULL4 Betty NULL--NULL is not equal to any value, even NULL is not equal to NULL -- it cannot be used by default <> OR = match NULLSELECT * FROM Test_NULL tnWHERE tn. lname <> null or tn. lname = NULL ------------------------------------------

Note: SQL Server cannot use = or <> by default. When ANSI_NULLS is set to OFF, you can use = or <> to query NULL values.

In other words, the ANSI_NULLS option is enabled for SQL Server by default.

-- SET ANSI_NULLS to OFF and use the = NULL query record SET ANSI_NULLS OFFSELECT * FROM Test_NULL tnWHERE tn. lname = NULL ------------------------------------- --3 Aaron NULL4 Betty NULL

Insert or update NULL values:

-- INSERT a new record containing null into Test_NULL (num, fname, lname) VALUES (5, 'serena ', NULL) -- Update the field value of a record to NULLUPDATE Test_NULL SET fname = NULLWHERE num = 2

NULL space usage

Generally, NULL does not occupy space in variable-length types (such as nvarchar (50) and varchar (8), and does not occupy space in fixed-length types (such as int) will occupy storage space.

In fact, the above understanding is not rigorous. In reality, NULL occupies space in both the variable length and fixed length types.

In SQL Server non-Sparse Columns, a NULL bitmap mask is required to store the NULL value.

The above is all the content of this article, hoping to help you learn.

Articles you may be interested in:

• SQL Server query data type is ntext is NULL or NULL
• SQL Server processing of NULL values in a field
• Application of sqlserver isnull in Database Query
• How to determine NULL in SQL Server, Oracle, and MySQL
• Go deep into the use of ISNULL and NULLIF in SQLServer
• Brief Introduction to SQL Server's ISNULL function and Mysql's IFNULL function usage
• Sqlserver cannot insert NULL values into the column id (the column cannot be empty)
• SQL Server ISNULL function and SQL statement for determining whether the value is null
• Use ISNULL in SQL Server to execute null value judgment Query