Sql Server Interview Question: Default

What is the CREATE DEFAULT definition?
Creates an object called a default. When bound to a column or a user-defined data type, a default specifies a value to be inserted into the column to which the object is bound (or into all columns, in the case of a user-defined data type) when no value is explicitly supplied during an insert.

How do you attach a default definition to column?
When a default is created, use sp_bindefault to bind it to a column or to a user-defined data type.

Notes about binding the rule?
If the default is not compatible with the column to which it is bound, Microsoft® SQL Server™ generates an error message when trying to insert the default value. For example, N/A cannot be used as a default for a numeric column.
If the default value is too long for the column to which it is bound, the value is truncated.
CREATE DEFAULT statements cannot be combined with other Transact-SQL statements in a single batch.

What you need to do before dropping the default?
A default must be dropped before creating a new one of the same name, and the default must be unbound by executing sp_unbindefault before it is dropped.

What will happen when you have both rule and default in a column?
If a column has both a default and a rule associated with it, the default value must not violate the rule. A default that conflicts with a rule is never inserted, and SQL Server generates an error message each time it attempts to insert the default.

When are the time default inserted to the table?
A value is not explicitly inserted.
Either the DEFAULT VALUES or DEFAULT keywords are used with INSERT to insert default values.
If NOT NULL is specified when creating a column and a default is not created for it, an error message is generated when a user fails to make an entry in that column. This table illustrates the relationship between the existence of a default and the definition of a column as NULL or NOT NULL. The entries in the table show the result.
Column definition No entry, no default No entry, default Enter NULL, no default Enter NULL, default
NULL NULL default NULL NULL
NOT NULL Error default error error

How do you rename a default?
To rename a default, use sp_rename

How do get report on default?
For a report on a default, use sp_help.

How do you drop default?
DROP DEFAULT
Removes one or more user-defined defaults from the current database.
The DROP DEFAULT statement does not apply to DEFAULT constraints. For more information about dropping DEFAULT constraints (created by using the DEFAULT option of either the CREATE TABLE or ALTER TABLE statements), see "ALTER TABLE" in this volume.
Syntax
DROP DEFAULT { default } [ ,...n ]