NULL keyword

In this blog, we’ll discuss about NULL keyword in the table.

In MS SQL Server NULL means UNKNOWN / MISSING / UNDISCOVERED value.

  1. Two NULL values cannot be compared by using Equal operator (=) in between them.(Default property)
  2. 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.
  3. NULL values can be check with either IS NULL, IS NOT NULL,ISNULL(),COALESC().
  4. 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.
  5. Default constraint can have NULL value.

Examples

create table City
(
Id int primary key,
name varchar(50)
)

–Example1: Get all the records where City where name is NULL
Select * from City where ISNULL(name,”)=”
–OR
Select * from City where COALESCE(name,”)=”
–OR
Select * from City where name IS NULL

–Example2: Get all the records where City is NOT NULL
Select * from City where ISNULL(name,”)<>”
–OR
Select * from City where COALESCE(name,”)<>”
–OR
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
–OR
insert into test values(NULL)

NULL values can be remove by 2 ways

  1. By updating the NULL valued column with Update statement.
  2. 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.

Example

–To Get the log file size of all the Database
DBCC SQLPERF(logspace)

–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
FROM sys.master_files

To know the details about NOT NULL Constraint, please click here.

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Advertisements

Default keyword

In this blog, we’ll discuss about Default keyword.

Default keyword is different then Default constraint and below examples will help us to understand it clearly. Like Default Constraint , Default keyword does not create any constraint in the table or with column, thus we are free to change the datatype,modify or drop the table / column any time without any restriction.

–Example1: If the column is NULL-able, then using the “DEFAULT” keyword equals to  NULL in insert statement
create table test
(
Id int not null,
name varchar(50)
)

–Below statements will insert NULL in the name column for both the rows
insert into test values(1,Default),(2,NULL)

select * from test
/*
Id name
1 NULL
2 NULL
*/

–Example2: Insert statement for table with single IDENTITY column.
–We know that IDENTITY is an auto entry field, so we are not able to enter any explicit value for IDENTITY column until & unless it is SET TO Insert ON.
–Also we know that DEFAULT constraint is not applicable for column with IDENTITY property. Here again the “DEFAULT” will work as keyword instead of constraint.

create table test
(
Id int not null IDENTITY(1,1)
)

–It’ll insert value 1 in ID column and keep on increment it by 1 every time we run the same insert query
insert into test DEFAULT values

–OR
insert into test values (DEFAULT)

select * from test
–Output
/*
Id
1
*/

–Example3: Default constraint can be combine with Sequence database object. (Sequence type object was introduced with SQL 2012)
— Here create a SEQUENCE object first. For Detail discussion on SEQUENCE please visit the blog with Sequence object.
CREATE SEQUENCE Counter AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
CYCLE

GO

CREATE TABLE test
(
Sno int DEFAULT NEXT VALUE FOR Counter,
name varchar(50)
)

insert into test values(default,’ABC’),(default,’XYZ’)
select * from test
–Output
/*
Sno name
1 ABC
2 XYZ
*/

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.