Set default value for column : Default Value « Constraints « SQL Server / T-SQL





Set default value for column


1>
2> CREATE TABLE T (
3>     int1 int,
4>     bit1 bit NOT NULL DEFAULT 0,
5>     rvr1 timestamp,
6>     usr1 nvarchar(28) DEFAULT USER,
7>     createtime datetime DEFAULT CURRENT_TIMESTAMP
8> )
9> GO
1>
2> INSERT T (int1) VALUES (1) WAITFOR DELAY '00:00:01'
3> GO
1> INSERT T (int1, bit1) VALUES (2, 0) WAITFOR DELAY '00:00:01'
2> GO
1> INSERT T (int1, bit1) VALUES (3, 1)
2> GO

(1 rows affected)
1> SELECT int1, bit1, usr1,
2>     CONVERT(int, rvr1) 'Timestamp as int',
3>     createtime
4> FROM T
5> GO
int1        bit1 usr1                         Timestamp as int createtime
----------- ---- ---------------------------- ---------------- -----------------------
          1    0 dbo                                      5007 2006-10-14 10:53:20.827
          2    0 dbo                                      5008 2006-10-14 10:53:21.827
          3    1 dbo                                      5009 2006-10-14 10:53:22.827

(3 rows affected)
1>
2> UPDATE T
3> set bit1 = 1
4> WHERE int1 = 2
5> GO

(1 rows affected)
1>
2> SELECT int1, bit1, usr1,
3>     CONVERT(int, rvr1) 'Timestamp as int',
4>     createtime
5> FROM T
6> GO
int1        bit1 usr1                         Timestamp as int createtime
----------- ---- ---------------------------- ---------------- -----------------------
          1    0 dbo                                      5007 2006-10-14 10:53:20.827
          2    1 dbo                                      5010 2006-10-14 10:53:21.827
          3    1 dbo                                      5009 2006-10-14 10:53:22.827

(3 rows affected)
1>
2> drop table t
3> GO
1>
           
       










Related examples in the same category

1.Add a default value of 0 if no value specified
2.Add Default value constraint