Create Defaults

Define a default using create default, then bind the default to the appropriate table column or user-defined datatype using sp_bindefault.

You can test the bound default by inserting data.

You can drop defaults using drop default and remove their association using sp_unbinddefault.

When you create and bind defaults:

Make sure the column is large enough for the default. For example, a char (2) column will not hold a 17-byte string like “Nobody knows yet.”
Be careful when you put a default on a user-defined datatype and a different default on an individual column of that type. If you bind the datatype default first and then the column default, the column default replaces the user-defined datatype default for the named column only. The user-defined datatype default is bound to all the other columns having that datatype.

However, once you bind another default to a column that has a default because of its type, that column ceases to be influenced by defaults bound to its datatype.
Watch for conflicts between defaults and rules. Be sure the default value is allowed by the rule; otherwise, the default may be eliminated by the rule.

For example, if a rule allows entries between 1 and 100, and the default is set to 0, the rule rejects the default entry. Either change the default or change the rule.
The syntax is:
```
create default [owner.]default_name 
     as constant_expression
```
Default names must follow the rules for identifiers.

Within a database, default names must be unique for each user. For example, you cannot create two defaults called phonedflt. However, as “guest,” you can create a phonedflt even if dbo.phonedflt already exists because the owner name makes each one distinct.

Another example: suppose you want to create a default value of “Oakland” that can be used with the city column of friends_etc and possibly with other columns or user datatypes. To create the default, enter:
```
create default citydflt 
as "Oakland" 
```
As you continue to follow this example, you can use any city name that works for the people you are going to enter in your personal table.

Enclose character and date constants in quotes; money, integer, and floating point constants do not require them. Binary data must be preceded by “0x”, and money data should be preceded by a dollar sign ($), or whatever monetary sign is the logical default currency for the area where you are working. The default value must be compatible with the datatype of the column, for example, you cannot use “none” as a default for a numeric column, but 0 is appropriate.

Usually, you enter default values when you create a table. However, during a session in which you want to enter many rows having the same values in one or more columns, you may want to, before you begin, create a default tailored to that session.

Note: You cannot issue create table with a declarative default and then insert data into the table in the same batch or procedure. Either separate the create and insert statements into two different batches or procedures, or use execute to perform the actions separately.