в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:22:30Z

1 версия

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

== A statement that creates a table with column attributes==



<source lang="sql">
4>
5> CREATE TABLE Billings
6> (BillingID INT NOT NULL IDENTITY PRIMARY KEY,
7> BankerID INT NOT NULL,
8> BillingDate SMALLDATETIME NULL,
9> BillingTotal MONEY NULL DEFAULT 0)
10> GO
1>
2> drop table Billings;
3> GO</source>

== CLUSTERED column==



<source lang="sql">
3> CREATE TABLE StudentsArchive (
4> AID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
5> type nvarchar(6) NOT NULL,
6> whenchanged smalldatetime NOT NULL DEFAULT Getdate(),
7>
8> StudentID int,
9> nFirstName nvarchar(30),
10> nLastName nvarchar(50),
11> oFirstName nvarchar(30),
12> oLastName nvarchar(50)
13> )
14> GO
1>
2> drop table StudentsArchive;
3> GO
1></source>

== Computational column==



<source lang="sql">
6> CREATE TABLE T (
7> int1 int,
8> bit1 bit,
9> varchar1 varchar(3),
10> dec1 dec(5,2),
11> cmp1 AS (int1 + bit1)
12> )
13> GO
1>
2> INSERT T (int1, bit1) VALUES (1, 0)
3> INSERT T (int1, varchar1) VALUES (2, "abc")
4> INSERT T (int1, dec1) VALUES (3, 5.25)
5> INSERT T (bit1, dec1) VALUES (1, 9.75)
6> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> drop table t;
3> GO</source>

== PRIMARY KEY column==



<source lang="sql">
4> CREATE TABLE employee(
5> emp_no INTEGER NOT NULL CONSTRAINT prim_empl PRIMARY KEY,
6> emp_fname CHAR(20) NOT NULL,
7> emp_lname CHAR(20) NOT NULL,
8> dept_no CHAR(4) NULL)
9> GO
1>
2> drop table employee;
3> GO
1></source>

== Table Creation Script for Parent and Child with FOREIGN KEY Constraint and INSTEAD OF Trigger==



<source lang="sql">
4> CREATE TABLE Parent(
5> ID int NOT NULL PRIMARY KEY
6> )
7> GO
1> CREATE TABLE Child(
2> ID int NOT NULL PRIMARY KEY REFERENCES Parent (ID) ON DELETE CASCADE
3> )
4> GO
1> CREATE TRIGGER trd_Parent ON Parent INSTEAD OF DELETE
2> AS
3> IF @@ROWCOUNT = 0
4> RETURN
5> PRINT "Inside Parent trigger."
6> GO
1> INSERT Parent VALUES (1)
2> INSERT Parent VALUES (2)
3> INSERT Parent VALUES (3)
4> INSERT Parent VALUES (4)
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> INSERT Child VALUES (1)
2> INSERT Child VALUES (2)
3> INSERT Child VALUES (3)
4> INSERT Child VALUES (4)
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> --INSTEAD OF Trigger that Duplicates DELETE Action
2> ALTER TRIGGER trd_Parent ON Parent INSTEAD OF DELETE
3> AS
4> IF @@ROWCOUNT = 0
5> RETURN
6> PRINT "Inside Parent trigger."
7> DELETE P
8> FROM
9> Parent P
10> JOIN
11> deleted D ON D.ID = P.ID
12> GO
1> -- AFTER Trigger on Child Table
2> CREATE TRIGGER trd_Child ON Child AFTER DELETE
3> AS
4> IF @@ROWCOUNT = 0
5> RETURN
6> PRINT "Inside Child trigger."
7> GO
1>
2> drop table child
3> drop table parent
4> GO
1></source>

== The basic syntax of the CREATE TABLE statement==



<source lang="sql">
CREATE TABLE table_name
(column_name_1 data_type [column_attributes]
[, column_name_2 data_type [column_attributes]]...
[, table_attributes])
Common column attributes
Attribute Description
NULL|NOT NULL Indicates whether or not the column can accept null values. If omitted, NULL is the default unless PRIMARY KEY is specified.
PRIMARY KEY|UNIQUE Identifies the primary key or a unique key for the table. If PRIMARY is specified, the NULL attribute isn"t allowed.
IDENTITY Identifies an identity column. Only one identity column can be created per table.
DEFAULT default_value Specifies a default value for the column.</source>

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

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

в 13:46, 26 мая 2010

Admin: 1 версия