|
0
|
1 DROP TABLE DataTypeTest
|
|
|
2 GO
|
|
|
3 DROP TABLE BinaryData
|
|
|
4 GO
|
|
|
5 DROP TABLE Patient
|
|
|
6 GO
|
|
|
7 DROP TABLE Doctor
|
|
|
8 GO
|
|
|
9 DROP TABLE Person
|
|
|
10 GO
|
|
|
11
|
|
|
12 -- Person Table
|
|
|
13
|
|
|
14 CREATE TABLE Person
|
|
|
15 (
|
|
|
16 PersonID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_Person PRIMARY KEY,
|
|
|
17 FirstName nvarchar(50) NOT NULL,
|
|
|
18 LastName nvarchar(50) NOT NULL,
|
|
|
19 MiddleName nvarchar(50) NULL,
|
|
|
20 Gender nchar(1) NOT NULL
|
|
|
21 )
|
|
|
22 GO
|
|
|
23
|
|
|
24 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('John', 'Pupkin', 'M')
|
|
|
25 GO
|
|
|
26 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('Tester', 'Testerson', 'M')
|
|
|
27 GO
|
|
|
28
|
|
|
29 -- Doctor Table Extension
|
|
|
30
|
|
|
31 CREATE TABLE Doctor
|
|
|
32 (
|
|
|
33 PersonID int NOT NULL
|
|
|
34 CONSTRAINT PK_Doctor PRIMARY KEY
|
|
|
35 CONSTRAINT FK_Doctor_Person --FOREIGN KEY
|
|
|
36 REFERENCES Person ([PersonID])
|
|
|
37 ON UPDATE CASCADE
|
|
|
38 ON DELETE CASCADE,
|
|
|
39 Taxonomy nvarchar(50) NOT NULL
|
|
|
40 )
|
|
|
41 GO
|
|
|
42
|
|
|
43 INSERT INTO Doctor (PersonID, Taxonomy) VALUES (1, 'Psychiatry')
|
|
|
44 GO
|
|
|
45
|
|
|
46 -- Patient Table Extension
|
|
|
47
|
|
|
48 CREATE TABLE Patient
|
|
|
49 (
|
|
|
50 PersonID int NOT NULL
|
|
|
51 CONSTRAINT PK_Patient PRIMARY KEY
|
|
|
52 CONSTRAINT FK_Patient_Person --FOREIGN KEY
|
|
|
53 REFERENCES Person ([PersonID])
|
|
|
54 ON UPDATE CASCADE
|
|
|
55 ON DELETE CASCADE,
|
|
|
56 Diagnosis nvarchar(256) NOT NULL
|
|
|
57 )
|
|
|
58 GO
|
|
|
59
|
|
|
60 INSERT INTO Patient (PersonID, Diagnosis) VALUES (2, 'Hallucination with Paranoid Bugs'' Delirium of Persecution')
|
|
|
61 GO
|
|
|
62
|
|
|
63 -- BinaryData Table
|
|
|
64
|
|
|
65 CREATE TABLE BinaryData
|
|
|
66 (
|
|
|
67 BinaryDataID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_BinaryData PRIMARY KEY,
|
|
|
68 Data varbinary(1024) NOT NULL)
|
|
|
69 GO
|
|
|
70
|
|
|
71 CREATE TABLE DataTypeTest
|
|
|
72 (
|
|
|
73 DataTypeID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_DataType PRIMARY KEY,
|
|
|
74 Binary_ binary(50) NULL,
|
|
|
75 Boolean_ bit NULL,
|
|
|
76 Byte_ tinyint NULL,
|
|
|
77 Bytes_ varbinary(50) NULL,
|
|
|
78 Char_ nchar(1) NULL,
|
|
|
79 DateTime_ datetime NULL,
|
|
|
80 Decimal_ numeric(20,2) NULL,
|
|
|
81 Double_ float NULL,
|
|
|
82 Guid_ uniqueidentifier NULL,
|
|
|
83 Int16_ smallint NULL,
|
|
|
84 Int32_ int NULL,
|
|
|
85 Int64_ bigint NULL,
|
|
|
86 Money_ money NULL,
|
|
|
87 SByte_ tinyint NULL,
|
|
|
88 Single_ real NULL,
|
|
|
89 Stream_ varbinary(50) NULL,
|
|
|
90 String_ nvarchar(50) NULL,
|
|
|
91 UInt16_ smallint NULL,
|
|
|
92 UInt32_ int NULL,
|
|
|
93 UInt64_ bigint NULL,
|
|
|
94 Xml_ ntext NULL
|
|
|
95 )
|
|
|
96 GO
|
|
|
97
|
|
|
98 INSERT INTO DataTypeTest
|
|
|
99 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
|
|
|
100 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
|
|
|
101 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_, Xml_)
|
|
|
102 VALUES
|
|
|
103 ( NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
|
|
104 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
|
|
105 NULL, NULL, NULL, NULL, NULL, NULL, NULL)
|
|
|
106 GO
|
|
|
107
|
|
|
108 INSERT INTO DataTypeTest
|
|
|
109 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
|
|
|
110 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
|
|
|
111 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_,
|
|
|
112 Xml_)
|
|
|
113 VALUES
|
|
|
114 (NewID(), 1, 255, NewID(), 'B', GetDate(), 12345.67,
|
|
|
115 1234.567, NewID(), 32767, 32768, 1000000, 12.3456, 127,
|
|
|
116 1234.123, NewID(), 'string', 32767, 32768, 200000000,
|
|
|
117 '<root><element strattr="strvalue" intattr="12345"/></root>')
|
|
|
118 GO
|
|
|
119
|
|
|
120
|
|
|
121
|
|
|
122 DROP TABLE Parent
|
|
|
123 GO
|
|
|
124 DROP TABLE Child
|
|
|
125 GO
|
|
|
126 DROP TABLE GrandChild
|
|
|
127 GO
|
|
|
128
|
|
|
129 CREATE TABLE Parent (ParentID int, Value1 int)
|
|
|
130 GO
|
|
|
131 CREATE TABLE Child (ParentID int, ChildID int)
|
|
|
132 GO
|
|
|
133 CREATE TABLE GrandChild (ParentID int, ChildID int, GrandChildID int)
|
|
|
134 GO
|
|
|
135
|
|
|
136
|
|
|
137 DROP TABLE LinqDataTypes
|
|
|
138 GO
|
|
|
139
|
|
|
140 CREATE TABLE LinqDataTypes
|
|
|
141 (
|
|
|
142 ID int,
|
|
|
143 MoneyValue decimal(10,4),
|
|
|
144 DateTimeValue datetime,
|
|
|
145 DateTimeValue2 datetime,
|
|
|
146 BoolValue bit,
|
|
|
147 GuidValue uniqueidentifier,
|
|
|
148 BinaryValue varbinary(5000) NULL,
|
|
|
149 SmallIntValue smallint,
|
|
|
150 IntValue int NULL,
|
|
|
151 BigIntValue bigint NULL
|
|
|
152 )
|
|
|
153 GO
|
|
|
154
|
|
|
155
|
|
|
156 DROP TABLE TestIdentity
|
|
|
157 GO
|
|
|
158
|
|
|
159 CREATE TABLE TestIdentity (
|
|
|
160 ID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_TestIdentity PRIMARY KEY
|
|
|
161 )
|
|
|
162 GO
|
