· 8 years ago · May 24, 2018, 02:36 PM
1create database mdTestDB2 collate SQL_Latin1_General_CP1_CS_AS;
2
3GO
4
5use mdTestDB2; -- owner mduser
6GO
7
8
9-- 2. create test schema1
10-- TODO drop schema is not simple, first have to drop all objects within the schema.
11-- http://ranjithk.com/2010/01/31/script-to-drop-all-objects-of-a-schema/
12-- https://ruiromanoblog.wordpress.com/2011/01/27/drop-a-sql-server-schema-and-all-objects-related/
13-- if exists (select * from sys.schemas where name= 'mdTestSchema1') drop schema mdTestSchema1; -- drop if exists and recreate, unlike database
14create schema mdTestSchema1; -- dbName prefix not allowed.
15GO
16
17-- 3. create test tables
18-- all data type table
19create table mdTestDB2.mdTestSchema1.allDataTypesTable -- one column for each data type
20 (
21 -- Exact numerics
22 bigintCol bigint,
23 bitCol bit,
24 decimalCol decimal,
25 intCol int,
26 moneyCol money,
27 numericCol numeric,
28 smallintCol smallint,
29 smallmoneyCol smallmoney,
30 tinyintcol tinyint,
31
32 -- Approx numerics
33 floatCol float,
34 realCol real,
35
36 -- Date time
37 dateCol date,
38 datetime2Col datetime2,
39 datetimeCol datetime,
40 timeCol time,
41 datetimeoffsetCol datetimeoffset,
42 smalldatetimecol smalldatetime,
43
44 -- Character strings
45 charCol char,
46 varcharCol varchar,
47 textcol text,
48
49 -- Unicode character strings
50 ncharCol nchar,
51 nvarcharCol nvarchar,
52 ntextCol ntext,
53
54 -- Binary strings
55 binaryCol binary,
56 varbinaryCol varbinary,
57 imageCol image,
58
59 -- Others
60 hierarchyidCol hierarchyid,
61 sql_variantCol sql_variant,
62 timestampCol timestamp,
63 uniqueidentifierCol uniqueidentifier,
64 xmlCol xml
65
66 -- Spatial types ignored
67 )
68
69
70-- TABLE CONSTRAINT: Primary/Foreign key
71-- Primary key table
72create table mdTestDB2.mdTestSchema1.pkTable
73 (
74 pkCol int primary key
75 )
76
77-- PRIMARY KEY, UNIQUE, FOREIGN KEY, a CHECK or a DEFAULT
78create table mdTestDB2.mdTestSchema1.constrainedTable
79 (
80 uniqueColumnConstraintCol int unique,
81 uniqueTableConstraintCol int not null,
82 defaultColumnConstraintCol int constraint defaultColConstraint default 0,
83 fkColumnConstraintCol int constraint fkColConstraint foreign key references mdTestDB2.mdTestSchema1.pkTable(pkCol),
84 fkTableConstraintCol int not null,
85 constraint uniqueTableConstraint UNIQUE(uniqueTableConstraintCol),
86 constraint pkTableConstraint primary key nonclustered (uniqueColumnConstraintCol ASC, uniqueTableConstraintCol DESC),
87 constraint fkTableConstraint foreign key (fkTableConstraintCol) references mdTestDB2.mdTestSchema1.pkTable(pkCol),
88 constraint checkTableConstraint check(uniqueColumnConstraintCol != uniqueTableConstraintCol)
89 );
90GO
91-- VIEWS
92create view mdTestSchema1.nonEncryptedView -- dbNAme prefix not allowed
93 ( arithmeticExpressionCol,
94 functionCol,
95 constantCol,
96 tableCol
97 )
98with view_metadata, schemabinding
99as
100select
101 1+1 as colOne,
102 SYSDATETIME() as ColTwo,
103 'constant' as ColThree,
104 pkCol as ColFour
105from mdTestSchema1.pkTable -- dbNAme prefix invalid for schema binding
106with check option;
107
108GO
109create view mdTestSchema1.encryptedView (colOne)
110with encryption
111as select 1+1;
112GO
113
114create view mdTestSchema1.indexedView (colOne)
115with schemabinding
116as select pkCol*pkCol from mdTestSchema1.pkTable;
117
118GO
119
120create unique clustered index uniqueCluseteredIndex
121 ON mdTestSchema1.indexedView(colOne);
122
123GO
124
125-- USER DEFINED FUNCTIONS
126create function mdTestSchema1.scalarFunction(@arg1 int)
127returns int
128as begin
129 return @arg1
130end;
131GO
132create function mdTestSchema1.inLineTableValuedFunction(@arg1 int)
133returns table
134as
135 return (select * from mdTestDB2.mdTestSchema1.pkTable where pkCol < @arg1);
136
137GO
138create function mdTestSchema1.multiStatementTableValuedFunction(@arg1 int)
139returns @retTable table
140 (
141 colOne int
142 )
143as
144begin
145 insert into @retTable values (@arg1)
146 return
147end;
148GO
149-- PROCEDURES
150-- 1. temporary procedures - local and global
151-- 2. parameter qualifiers - input, output, readonly and default.
152create proc mdTestSchema1.what_db_is_this
153as
154select db_name() as ThisDB;
155GO
156-- TODO: Figure out how to know if a temporary stored procedure already exists.
157-- create proc mdTestSchema1.#localTempStoredProc -- dropped when that connection is closed
158-- as
159-- select db_name() as ThisDB;
160
161-- create proc mdTestSchema1.##globalTempStoredProc -- dropped at the end of the last session using the procedure
162-- as
163-- select db_name() as ThisDB;
164
165-- create proc mdTestSchema1.numberedProc; 1 -- dropped at the end of the last session using the procedure.
166-- as
167-- select db_name() as ThisDB; -- doesn't work
168
169create type mdTestSchema1.tableDataType as table
170(
171 colOne int
172);
173GO
174create proc mdTestSchema1.parameterizedProc
175 @mssqlDataTypeParam int,
176 @tableValueTypeParam mdTestSchema1.tableDataType READONLY,
177 @defaultValuedParam int = 0,
178 @outputParam int OUT
179as
180select db_name() as ThisDB;
181GO
182create proc mdTestSchema1.procedureOptionProc
183with encryption, recompile, execute as owner
184as
185select db_name() as ThisDB;
186GO
187-- INDEXES
188-- Types:
189-- Hash,
190-- memory-optimized nonclustered indexes,
191-- clustered,
192-- unique
193-- columnstore,
194-- index with included columns,
195-- indexed with computed columns,
196-- filtered,
197-- spatial,
198-- xml,
199-- full-text
200if exists ( select * from information_schema.tables where table_name = 'indexableTable' AND table_schema = 'mdTestSchema1' )
201 drop table mdTestSchema1.indexableTable;
202
203GO
204create table mdTestSchema1.indexableTable
205 (
206 nonClusteredIndexCol int,
207 clusteredIndexCol int,
208 uniqueIndexCol int,
209 filteredIndexCol int,
210 );
211GO
212create nonclustered index nonClusteredIndex -- indexes are not attached to a schema. schemaname.indexname throws error.
213 on mdTestSchema1.indexableTable (nonClusteredIndexCol);
214GO
215create clustered index clusteredIndex
216 on mdTestSchema1.indexableTable(clusteredIndexCol);
217GO
218create unique index uniqueIndex
219 on mdTestSchema1.indexableTable(uniqueIndexCol)
220GO
221create nonclustered index filteredIndex
222 on mdTestSchema1.indexableTable(filteredIndexCol)
223 where filteredIndexCol > 0;
224GO
225create nonclustered index includedColIndex
226 on mdTestSchema1.indexableTable(clusteredIndexCol)
227 include (filteredIndexCol);
228GO
229-- PARTITIONS
230-- table
231-- index
232-- need access to the server to create files for partition filegroups.
233-- this will also be required to be handled in terraform scripts.
234
235-- Partitioned view
236create table mdTestSchema1.tblForPViewOne (
237 colOne int,
238 constraint partionedViewRangeCheckOne check (colOne between 1 and 10)
239);
240GO
241create table mdTestSchema1.tblForPViewTwo (
242 colOne int,
243 constraint partionedViewRangeCheckTwo check (colOne between 11 and 20)
244);
245GO
246create view mdTestSchema1.partitionedView
247as
248select * from mdTestSchema1.tblForPViewOne
249 union all
250select * from mdTestSchema1.tblForPViewTwo;
251GO
252-- Schema2 to test filtering.
253create schema mdTestSchema2; -- dbName prefix not allowed.
254GO
255create table mdTestSchema2.pkTable
256(
257 pkCol int primary key
258);
259GO
260-- create schema MDTESTSCHEMA1; -- mssql12 is case insensitive.
261create schema mdTestScehm@3;
262GO
263
264create table mdTestScehm@3.pkTable
265(
266 pkCol int primary key
267);
268GO