SQL Server/T-SQL Tutorial/Constraints/Default

Column with default random value

3> CREATE TABLE random_data
4> (
5> col1    int      PRIMARY KEY IDENTITY(10,10) NOT NULL,
6> col2    int      NOT NULL DEFAULT CASE
7>                  -- Random integer between -9999 and 9999
8>                  WHEN CONVERT(int, RAND() * 1000) % 2 = 1
9>                  THEN (CONVERT(int, RAND() * 100000) % 10000 * -1 )
10>                  ELSE CONVERT(int, RAND() * 100000) % 10000
11>                  END,
12> col3    char(15) NOT NULL DEFAULT
13>                   CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65)
14>                  -- 65 is "A"
15>                  + CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65)
16>                  + CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65)
17>                  + CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65)
18>                  + REPLICATE(CHAR((CONVERT(int, RAND() * 1000)
19>                      % 26) + 65), 11)
20> )
21> GO
1>
2> DECLARE @counter int
3> SET @counter=1
4> WHILE (@counter <= 1000)
5>     BEGIN
6>     INSERT random_data DEFAULT VALUES
7>     SET @counter=@counter + 1
8>     END
9> GO
1>
2> drop table random_data;
3> GO

DEFAULT Constraints

5>    CREATE TABLE Shippers
6>    (
7>       ShipperID      int   IDENTITY   NOT NULL
8>          PRIMARY KEY,
9>       ShipperName    varchar(30)      NOT NULL,
10>       DateInSystem   smalldatetime    NOT NULL
11>          DEFAULT GETDATE ()
12>    )
13> GO
1>
2>    CREATE TABLE Customers
3>    (
4>       CustomerNo     int     IDENTITY   NOT NULL
5>          PRIMARY KEY,
6>       CustomerName   varchar(30)        NOT NULL,
7>       Address1       varchar(30)        NOT NULL,
8>       Address2       varchar(30)        NOT NULL,
9>      City            varchar(20)        NOT NULL,
10>      State           char(2)            NOT NULL,
11>      Zip             varchar(10)        NOT NULL,
12>      Contact         varchar(25)        NOT NULL,
13>      Phone           char(15)           NOT NULL,
14>      FedIDNo         varchar(9)         NOT NULL,
15>      DateInSystem    smalldatetime      NOT NULL
16>    )
17>    GO
1>
2>    ALTER TABLE Customers
3>       ADD CONSTRAINT CN_CustomerDefaultDateInSystem
4>          DEFAULT GETDATE() FOR DateInSystem
5> GO
1>
2> drop table Shippers;
3> drop table Customers;
4> GO

Default int type value and default char type value

2> CREATE TABLE xyz
3> (
4> col1    int            PRIMARY KEY IDENTITY(1, 1) NOT NULL,
5> col2    int            NOT NULL DEFAULT 999,
6> col3    char(10)       NOT NULL DEFAULT "ABCEFGHIJK"
7> )
8> GO
1>
2> SET NOCOUNT ON
3> DECLARE @counter int
4> SET @counter=1
5> WHILE (@counter <= 1000)
6>     BEGIN
7>     INSERT xyz DEFAULT VALUES
8>     SET @counter=@counter+1
9>     END
10> GO
1>
2>
3> drop table xyz;
4> GO

Designating Default Column Values: Assign Zeroes Instead of Null Values

6> CREATE TABLE T (
7>     int1 int,
8>     bit1 bit NOT NULL DEFAULT 0,
9>     varchar1 varchar(3),
10>     dec1 dec(5,2),
11>     cmp1 AS (int1 + bit1)
12> )
13> GO
1>
2> drop table t;
3> GO

Dropping Defaults

DROP DEFAULT <default name>
EXEC sp_depends <object name>

Insert to a table with default value

2> CREATE TABLE T (
3>     int1 int,
4>     bit1 bit NOT NULL DEFAULT 0,
5>     rvr1 timestamp,
6>     usr1 nvarchar(128) DEFAULT USER,
7>     createtime datetime DEFAULT CURRENT_TIMESTAMP
8> )
9> GO
1>
2> INSERT T (int1, bit1) VALUES (3, 1)
3> GO
(1 rows affected)
1>
2> drop table t;
3> GO

The syntax for defining a default

CREATE DEFAULT <default name>
AS <default value>

Using the system date as a default parameter.

5>
6>
7>     CREATE TABLE myusers(
8>         UserID        varchar(30)NOT NULL PRIMARY KEY,
9>         FirstName     varchar(30),
10>         LastName      varchar(30),
11>         EmployeeType  char(1) NOT NULL,
12>         DBAccess      varchar(30),
13>         StartDate     datetime,
14>         ExpDate       datetime
15>     )
16>     GO
1>
2>
3>     CREATE PROC pr_deluser (@TD datetime )
4>     AS
5>     DECLARE getuser_curs CURSOR
6>          FOR
7>               SELECT UserID
8>               FROM myusers
9>               WHERE ExpDate <= @TD
10>     DECLARE @HoldID varchar(30)
11>     DECLARE @MyCount int
12>     SELECT @MyCount = 0
13>     OPEN getuser_curs
14>     FETCH NEXT FROM getuser_curs into @HoldID
15>     WHILE @@FETCH_STATUS = 0 BEGIN
16>          EXEC sp_droplogin @HoldID
17>          EXEC pr_copyuser @HoldID
18>          SELECT @MyCount = @MyCount + 1
19>          FETCH NEXT FROM getuser_curs into @HoldID
20>     END
21>     DECLARE @MyDisp varchar(50)
22>     SELECT @MyDisp = "Number of Users Deleted is " + ltrim(str(@MyCount))
23>     PRINT @MyDisp
24>     CLOSE getuser_curs
25>     DEALLOCATE getuser_curs
26>     GO
Cannot add rows to sysdepends for the current object because it depends on the missing object "pr_copyuser". The object will still be created.
1>
2>
3>     DECLARE @myvalue varchar(12)
4>
5>     SELECT @myvalue=CONVERT(varchar(12), getdate())
6>
7>     EXEC pr_deluser @myvalue
8>     GO
Number of Users Deleted is 0
1>
2>     drop PROC pr_deluser;
3>     GO
1>
2>
3>     drop table myusers;
4>     GO