в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:25:02Z

1 версия

Новая страница

== Column with default random value==



<source lang="sql">
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</source>

== DEFAULT Constraints==



<source lang="sql">
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</source>

== Default int type value and default char type value==



<source lang="sql">
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</source>

== Designating Default Column Values: Assign Zeroes Instead of Null Values==



<source lang="sql">
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</source>

== Dropping Defaults==



<source lang="sql">
DROP DEFAULT <default name>
EXEC sp_depends <object name></source>

== Insert to a table with default value==



<source lang="sql">
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</source>

== The syntax for defining a default==



<source lang="sql">
CREATE DEFAULT <default name>
AS <default value></source>

== Using the system date as a default parameter.==



<source lang="sql">
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</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/Constraints/Default - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия