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.

Advertisements