Using the Transact-SQL CREATE DEFAULT Statement to Set a Column Default

MS-SQL Server lets you create named, default values you can bind to columns and user-defined data types. Once you bind a default to a table column, the DBMS will supply the default value for the column (instead of NULL) if you insert a row that includes the column without specifying its value. The advantages of creating a default outside the CREATE TABLE statement are that you can use a descriptive name for the default, apply the same default to multiple columns in the same or different tables, and change or drop the default at any time.

The syntax of the Transact-SQL CREATE DEFAULT statement is:

 CREATE DEFAULT [.]
AS 

Note

Transact-SQL consists of Microsoft's additions to standard SQL. No commercial DBMS product fully supports everything in the SQL-92 standard. Conversely, every vendor adds its own SQL extensions (such as CREATE DEFAULT) and provides procedural language constructs. Microsoft calls its SQL and extensions and procedural language additions Transact-SQL. Oracle uses PL/SQL and SQL *Plus. While most standard SQL-92 code is transportable across DBMS products, specific product extensions (such as Transact-SQL statements) are not. If you need to use a Transact-SQL statement in an Oracle DBMS, check your system manual. You will probably find a PL/SQL statement that performs the same function but has a different name syntax.

Defaults you create must comply with the following rules:

• You do not have to supply the for the default. However, if you do not, the DBMS will supply your login name as the default for

• The name you use for the default () must be unique by owner.

• The must contain only constant values such as numbers, character strings, built-in functions, or mathematical expressions. The cannot include any columns or other database objects.

• After creating a default, you must use the stored procedure sp_bindefault to bind the default value to a column before the DBMS will supply the value for the column when inserting a row.

• The default must be compatible with the column to which you bind it. If you bind a character string to a numeric column, for example, the DBMS will generate an error message and not insert the row each time it has to supply the default value for the column.

• If you supply a character string default for a character column and the default is longer than the column length, the DBMS will truncate the default value to fit into the column.

• If a column has both a default and a constraint, the default value cannot violate the constraint. If a column's default value violates a column constraint, the DBMS will generate an error message and not insert the row each time it has to supply the default value for the column.

Suppose, for example, you had a table defined by

 CREATE TABLE employee
  (employee_ID            INTEGER,
   first_name             VARCHAR (20),
   last_name              VARCHAR(30),
   social_security_number CHAR(11),
   street_address         VARCHAR(35),
   health_card_number     CHAR(15),
   sheriff_card_number    CHAR(15)
   PRIMARY KEY (employee_ID))

and you want to supply "applied for" and "unknown" in place of NULL values if you don't know the Social Security number, health card number, or sheriff card number when adding a new employee to the EMPLOYEE table. You can create the defaults you need by executing the Transact-SQL statements:

 CREATE DEFAULT ud_value_unknown AS "Unknown"
CREATE DEFAULT ud_applied_for AS "Applied for"

Note

You can only enter one CREATE DEFAULT statement at a time into the Query Analyzer's Query Pane, or into the ISOQL (or OSOQL) command buffer.

Before the DBMS will use a default, you must execute the sp_bindefault stored procedure to bind the default value to a user-defined data type or a table column. You will learn how to bind default values to a table column in Tip 50 and how to bind a default to a user-defined data type in Tip 594.

You can use the stored procedure sp_help to display a list of user and system-defined defaults. Since sp_help will display all defaults, not just the ones you create, you may want to group all of your defaults together in the list. To do so, use the same first one or two characters for the names of the defaults (such as UD_, short for USER DEFAULTS). Then, when you use sp_help to list the database defaults, the stored procedure will group all of the defaults you create together in its semi-alphabetized list of all defaults.