create default

Syntax

create default [or replace] [owner.]default_name 
	as constant_expression

or replace – if the default already exists, replaces the default definition with the new definition.
default_name – is the name of the default, which must conform to the rules for identifiers and cannot be a variable. Specify the owner’s name to create another default of the same name owned by a different user in the current database. The default value for owner is the current user.
When you use or replace, if the specified default name already exists, then it is replaced with the new default definition. The object name and ID remain the same.
constant_expression – is an expression that does not include the names of any columns or other database objects. It can include global variables and built-in functions that do not reference database objects. Enclose character and date constants in quotes and use a “0x” prefix for binary constants.
When you use or replace, the definition of the default can be changed when the default is replaced. The new default value overrides the old default.

Example 1 – Creates a default called D1 that uses the @@spid global variable:
```
create default D1 as @@spid
```
Example 2 – Defines a default value, then binds it to the appropriate column or user-defined datatype:
```
create default phonedflt as "UNKNOWN"
```
Example 3 – The default takes effect only if there is no entry in the phone column of the authors table:
```
sp_bindefault phonedflt, "authors.phone"
```
No entry is different from a null value entry. To get the default, issue an insert command with a column list that does not include the column that has the default.
Example 4 – Creates a default value, todays_date, that inserts the current date into the columns to which it is bound:
```
create default todays_date as getdate ()
```
Example 5 – This example first creates a default with a phone number defined as UNKNOWN:
```
create default phonedflt as "UNKNOWN"       

select object_id("phonedflt")      
-----------    
1001051571  
```
This default replaces the previously created default using the or replace clause. The phone number is changed, but the object ID of the default remains the same:
```
create or replace default phonedflt as "999-999-9999"       

select object_id("phonedflt")      
-----------    
1001051571  
```

Bind a default to a column or user-defined datatype—but not an SAP ASE server-supplied datatype—with sp_bindefault.
You can bind a new default to a datatype without unbinding the old one. The new default overrides and unbinds the old one.
create default performs error checking for check constraints before it creates the default.
To hide the source test of a default, use sp_hidetext.

See also sp_bindefault, sp_help, sp_helptext, sp_rename, sp_unbindefault in Reference Manual: Procedures.

ANSI SQL – Compliance level: Transact-SQL extension.

Use the default clause of the create table statement to create ANSI SQL-compliant defaults.

Any user who impersonates the default owner through an alias or setuser cannot replace the default.

The permission checks for create default differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must have the `create default` privilege to create a default. To create a default for another user, you must have the `create any default` privilege. To replace the default, you must be the default owner.
Disabled	With granular permissions disabled, you must be the database owner, a user with sa_role, or have the `create default` privilege to create a default. To create a default for another user, you must have sa_role. To replace the default, you must be the default owner.

Values in event and extrainfo columns of sysaudits are:

Information	Values
Event	14
Audit option	create
Command or access audited	create default
Information in `extrainfo`	Roles – current active roles Keywords or options – NULL Previous value – NULL Other information – NULL Current value – NULL Proxy information – original login name, if set proxy is in effect or replace – for create or replace