Default values

For a column, a default value can be specified, that is, a value the column will take in case the insert statement doesn’t provide any value for it. Generally, the most common value is used as the default one.

Let most models in our database be personal computers (desktops). Thus, we could make ‘PC’ the default value for the type column. This could be done by means of the ALTER TABLE statement. According to the SQL standard, the statement for our example would look as follows:

alter table Product
alter column "type" set default 'PC';

However, SQL Server doesn’t support the standard syntax for this case; in the T-SQL dialect, the same action can be carried out the following way:

alter table Product
add DEFAULT 'PC' for type;

Now, we don’t need to specify a type when adding a PC model to the Product table.

insert into Product(maker, model) values('A', '1124');

Note that not just a literal can be used as a default value but also a function without arguments. In particular, we can use the function CURRENT_TIMESTAMP, which returns the current value for date and time. Let’s add a column containing the time of insertion of model data into the database to the Product table.

alter table Product
add add_date DATETIME DEFAULT CURRENT_TIMESTAMP;

Now, when we add model 1125 by maker A

insert into Product(maker, model) values('A', '1125');

we get the following result:

select * from Product where model ='1125';

Notes

1.    If no default value is specified, NULL, that is, a NULL mark is implied. Of course, such a default value can only be used if no NOT NULL constraint is applied to the column.

2.    If a column is added to an existing table, this column, according to the standard, will be filled with default values for the records already existing. In SQL server, the column adding behavior deviates from the standard a bit. If you execute the statement

alter
table Product add available VARCHAR(20) default 'Yes';[[/CODE]which adds the column available with the default value ‘yes’ to the Product table, this column, strangely enough, will be filled with NULL marks. There are two possible ways to “force” the server to fill the column with yeses:
a). Forbid the usage of NULL, that is, rewrite the above command as follows:
[[CODE]]alter table Product add available VARCHAR(20) NOT NULL default 'Yes';

Obviously, this method is not applicable if the column allows NULLs.

b). Use the special WITH VALUES clause:

alter table Product add available VARCHAR(20) default 'Yes' with values;