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:
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:
Now, we don’t need to specify a type when adding a PC model to the Product table.
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.
Now, when we add model 1125 by maker A
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
Obviously, this method is not applicable if the column allows NULLs.
b). Use the special WITH VALUES clause: