In this blog, we’ll discuss about NULL keyword in the table.
In MS SQL Server NULL means UNKNOWN / MISSING / UNDISCOVERED value.
- Two NULL values cannot be compared by using Equal operator (=) in between them.(Default property)
- Comparing 2 NULL values with Equal operator (=) can be done by setting the property SET ANSI_NULLS OFF. Note : Microsoft SQL Server is going to remove in future version. For more details please click here for MSDN.
- NULL values can be check with either IS NULL, IS NOT NULL,ISNULL(),COALESC().
- Any new column will be automatically set to NULL value by default either created with the table or adding a new column to existing table.
- Default constraint can have NULL value.
create table City
Id int primary key,
–Example1: Get all the records where City where name is NULL
Select * from City where ISNULL(name,”)=”
Select * from City where COALESCE(name,”)=”
Select * from City where name IS NULL
–Example2: Get all the records where City is NOT NULL
Select * from City where ISNULL(name,”)<>”
Select * from City where COALESCE(name,”)<>”
Select * from City where name IS NOT NULL
–Example3: Use SET ANSI_NULLS OFF to get all the city where name is NULL. It is not recomended practice to use it by MS BOL.
SET ANSI_NULLS OFF
Select * from City where name=NULL
–Example4: Use NULL as the default value
Create table test
name varchar(50) DEFAULT(NULL)
–Both the below statement is equivalent and insert default NULL value in the table
insert into test DEFAULT values
insert into test values(NULL)
NULL values can be remove by 2 ways
- By updating the NULL valued column with Update statement.
- By updating the column property, apply the NOT NULL constraint and provide new value to all the rows where the column is having the NULL value. Note : If the database is very large and consists of many NULL values in the column, need to check the fragmentation & log file size of the table before & after replacement of the NULL values.
–To Get the log file size of all the Database
–To get the size of the db
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name as DB_Path, (size*8)/1024 SizeMB,((size*8)/1024)/1024 SizeGB
Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.