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

Home
SQL Server / T-SQL
1.Aggregate Functions
2.Analytical Functions
3.Constraints
4.Cursor
5.Data Set
6.Data Type
7.Database
8.Date Timezone
9.Index
10.Insert Delete Update
11.Math Functions
12.Select Query
13.Sequence
14.Store Procedure Function
15.String Functions
16.Subquery
17.System
18.Table
19.Table Joins
20.Transact SQL
21.Transaction
22.Trigger
23.View
24.XML
SQL Server / T-SQL » Constraints » Default Value 




Set default value for column


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

(rows affected)
1SELECT int1, bit1, usr1,
2>     CONVERT(int, rvr1'Timestamp as int',
3>     createtime
4FROM 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

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

(rows affected)
1>
2SELECT int1, bit1, usr1,
3>     CONVERT(int, rvr1'Timestamp as int',
4>     createtime
5FROM 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

(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
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.