"Default" in ms SQL Server"

-- Note: There is no "Default Value Object" and "Default Value constraints" in the T-SQL, but collectively referred to as "default value ". this is only for the convenience of discussion. <br/> -- the so-called "Default Value Object" refers to the default value definition created as part of the create default statement. <br/> -- the so-called "Default Value constraint ", the default value definition created as part of the create table or alter table statement. <br/> -- Note: In later versions, Microsoft SQL server will delete the default value definition created by the create default statement, use <br/> -- default definition created by using the default keyword of alter table or create table. </P> <p> -- 1. create "Default object" <br/> create default df_test as 100; <br/> go </P> <p> -- 2. create a test table <B R/> Create Table t_tab1 <br/> (<br/> C1 smallint identity, <br/> C2 smallint null default 2, -- create an anonymous "Default Value constraint" as part of the create table statement <br/> C3 smallint null, <br/> C4 smallint null constraint df_t_tab1_c4 default 4, -- as part of the create table statement, create the "Default Value constraint" <br/> C5 smallint null, <br/> C6 smallint null, <br/> C7 smallint null <br/>); </P> <p> Create Table t_tab2 <br/> (<br/> C1 smallint identity, <br/> C2 Smallint null, <br/> C3 smallint null <br/>); <br/> go </P> <p> -- 3. create an anonymous and named "Default constraint" as part of the alter table statement <br/> alter table t_tab1 add constraint df_t_tab1_c6 default 6 for C6; <br/> alter table t_tab1 add default 7 For C7; <br/> go </P> <p> -- 4. bind the "Default object" df_test to the column <br/> --! The same "Default Value Object" can be bound to different columns of the same table at the same time, or to columns of different tables at the same time. <br/> execute sp_bindefault df_test, n't_ tab1.c3 '; <br/> execute sp_bindefault df_test, n't_tab1.c5'; <br/> execute sp_bindefault df_test, n' T _ tab2.c2 '; <br/> -- the "Default Value constraint" created as part of the create table statement cannot be bound to other tables. <br/> -- execute sp_bindefault df_t_tab1_c6, N 't_ tab2.c3 '; </P> <p> -- the "Default object" created as part of the create default statement ", it cannot be bound to a column through alter table. <br/> -- alter table t_tab1 add df_test For C3; <br/> -- alter table t_tab1 add df_test default for C3; <br/> -- alter table t_tab1 add constraint df_test default for C3; <br/> go </P> <p> -- 5. test default value <br/> insert into t_tab1 default values; <br/> insert into t_tab1 default values; <br/> insert into t_tab1 default values; <br/> select * From t_tab1; <br/> go </P> <p> insert into t_tab2 default values; <br/> insert into t_tab2 default values; <br/> insert into t_tab2 default values; <br/> select * From t_tab2; <br/> go </P> <p> -- 6. through sys. the default_constraints directory view does not detect the "Default Value Object" created as part of the create default statement ". <br/> select Sys. objects. name table name, <br/> sys. columns. column_id column ID, <br/> sys. columns. name column, <br/> sys. default_constraints.name default value constraint name <br/> from sys. default_constraints inner join sys. columns <br/> On sys. default_constraints.parent_object_id = sys. columns. object_id <br/> and SYS. default_constraints.parent_column_id = sys. columns. column_id <br/> Inner join sys. objects <br/> On sys. columns. [object_id] = sys. objects. [object_id] <br/> where sys. objects. name = n' T _ tab1'; <br/> go </P> <p> -- 7. "Default Value Object", named "Default Value constraint", and anonymous "Default Value constraint" are "objects within the framework ", and its type is d = default (constraint or independent ). <br/> select name into t_temp from sys. objects where type = 'D'; <br/> select name as [name of the database object whose type is d in the architecture Scope] From t_temp; <br/> go </P> <p> -- 8. "Default object" is "Default Value", but not "Default Value constraint"; "Default Value constraint" is not "Default Value", but "Default Value constraint ". <br/> select name as [name of the database object whose type is d in the schema range], <br/> objectproperty (object_id (name, N 'D '), N 'isdefault') as isdefault <br/> from t_temp; </P> <p> select name as [database object name with type D in the architecture Scope], <br/> objectproperty (object_id (name, n'd'), n'isultultcnst ') as isdefacncnst <br/> from t_temp; </P> <p> drop table t_temp; <br/> go </P> <p> -- 9. detaches (deletes) a column) default Value binding <br/> -- #1 cannot use sp_unbindefault for "table name" to unbind default values from all columns <br/> -- execute sp_unbindefault n't_ tab1 '; <br/> -- #2 correct method for resolving columns bound with the "Default object" <br/> execute sp_unbindefault n't_ tab1.c3 '; <br/> execute sp_unbindefault n't_ tab1.c5 '; <br/> execute sp_unbindefault n't_ tab2.c2 '; <br/> -- #3 for naming or anonymous "Default Value constraints", the sp_unbindefault method cannot be used to unbind; alter table drop constraint must be used to unbind. <br/> -- execute sp_unbindefault n't_ tab1.c2 '; <br/> -- execute sp_unbindefault n't_ tab1.c4 '; <br/> go </P> <p> -- 10. to delete a user-defined default value (that is, "Default Value Object"), you must first unbind it from the column. <br/> -- here, the release of df_test and Column Binding has been completed in 9, so you can delete it. <br/> drop default df_test; <br/> go </P> <p> -- 11. delete a column bound with a default value <br/> -- Note: If a column is bound with a default value, you must unbind the default value before deleting the column. <br/> -- if a "Default Value Object" is bound, sp_unbindefault is used to unbind it. <br/> -- if a "Default Value constraint" (named or anonymous) is bound ), remove the statement by using alter table drop constraint. </P> <p> -- 12. delete the database object created in the table <br/> drop table t_tab1; <br/> drop table t_tab2; <br/> go

Execution result:

The default value is bound to the column. <Br/> the default value is bound to the column. <Br/> the default value is bound to the column. </P> <p> (one row is affected) <br/> C1 C2 C3 C4 C5 C6 C7 <br/> ------ <br/> 1 2 100 4 100 6 7 <br/> 2 2 100 4 100 6 7 <br/> 3 2 100 4 100 6 7 </P> <p> (3 rows affected) </P> <p> (one row is affected) <br/> C1 C2 C3 <br/> ------ <br/> 1 100 null <br/> 2 100 null <br/> 3 100 null </P> <p> (3 rows affected) </P> <p> table name column ID column name default value constraint name <br/> -------------- -- --------- Partition <br/> t_tab1 2 C2 DF _ t_tab1 _ C2 _ 3ad78439 <br/> t_tab1 4 C4 df_t_tab1_c4 <br/> t_tab1 6 C6 df_t_tab1_c6 <br/> t_tab1 7 C7 DF _ t_tab1 _ C7 _ 3ea8151d </P> <p> (four rows affected) </P> <p> (five rows are affected) <br/> database object whose type is d in the architecture scope <br/> --------------------------------------- <br/> df_test <br/> DF _ t_tab1 _ C2 _ 3ad78439 <br /> df_t_tab1_c4 <br/> df_t_tab1_c6 <br/> DF _ t_tab1 _ C7 _ 3ea8151d </P> <p> (5 rows affected) </P> <p> database object whose type is d in the architecture scope is isdefault <br/> ------------------------------------- ----------- <br/> df_test 1 <br/> DF _ t_tab1 __ c2 _ 3ad78439 0 <br/> df_t_tab1_c4 0 <br/> df_t_tab1_c6 0 <br/> DF _ t_tab1 _ C7 _ 3ea8151d 0 </P> <p> (5 rows affected) </P> <p> database object name isdefaultcnst with type D in the architecture scope <br/> --------------------------------------------- <br/> df_test 0 <br/> DF _ t_tab1 __ c2 _ 3ad78439 1 <br/> df_t_tab1_c 4 1 <br/> df_t_tab1_c6 1 <br/> DF _ t_tab1 _ C7 _ 3ea8151d 1 </P> <p> (five rows are affected) </P> <p> the binding between the table column and its default value has been removed. <Br/> the binding between the table column and its default value has been removed. <Br/> the binding between the table column and its default value has been removed.