· 7 years ago · Oct 16, 2018, 02:58 AM
1
2IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Src].[AllDataTypes]') AND type in (N'U'))
3 DROP TABLE [Src].[AllDataTypes]
4
5CREATE TABLE [Src].[AllDataTypes](
6 [charTest] [char](1) NULL,
7 [decimalMin] [decimal](38, 10) NULL,
8 [decimalMax] [decimal](38, 10) NULL,
9 [floatMin] [float] NULL,
10 [floatMax] [float] NULL,
11 [geographyNotNull] [geography] NOT NULL,
12 [geographyNull] [geography] NULL,
13 [geometryNotNull] [geometry] NOT NULL,
14 [geometryNull] [geometry] NULL,
15 [hierarchyidNotNull] [hierarchyid] NOT NULL,
16 [hierarchyidNull] [hierarchyid] NULL,
17 [imageNotNull] [image] NOT NULL,
18 [imageNull] [image] NULL,
19 [moneyMin] [money] NULL,
20 [moneyMax] [money] NULL,
21 [numericMin] [numeric](38, 10) NULL,
22 [numericMax] [numeric](38, 10) NULL,
23 [realMin] [real] NULL,
24 [realMax] [real] NULL,
25 [smallmoneyMin] [smallmoney] NULL,
26 [smallmoneyMax] [smallmoney] NULL,
27 [sql_variantTest] [sql_variant] NULL,
28 [sysnameTest] [sysname] NULL,
29 [xmlTest] [xml] NULL
30)
31
32INSERT [Src].[AllDataTypes]
33([charTest]
34, [decimalMin]
35, [decimalMax]
36, [floatMin]
37, [floatMax]
38, [geographyNotNull]
39, [geographyNull]
40, [geometryNotNull]
41, [geometryNull]
42, [hierarchyidNotNull]
43, [hierarchyidNull]
44, [imageNotNull]
45, [imageNull]
46, [moneyMin]
47, [moneyMax]
48, [numericMin]
49, [numericMax]
50, [realMin]
51, [realMax]
52, [smallmoneyMin]
53, [smallmoneyMax]
54, [sql_variantTest]
55, [sysnameTest]
56, [xmlTest])
57VALUES (N'z'
58, CAST(-9999999999999999999999999999.9999999999 AS Decimal(38, 10))
59, CAST(9999999999999999999999999999.9999999999 AS Decimal(38, 10))
60, -1.79E+308
61, -2.23E-308
62, 0xE6100000010C01008000B5DF07C00700ECFAD03A4C40
63, 0xE6100000010C00008C521FBB49400400006E72A7EDBF
64, 0xE6100000010C65F547B878FD364069F2F7DF1AE65540
65, 0xE6100000010C65F547B878FD364069F2F7DF1AE65540
66, N'/3/'
67, N'/1/'
68, 0xFFD8FFE000104A46494600010100000100010000FFDB0043000604050
69, 0xFFD8FFE000104A46494600010100000100010000FFDB0043000604050
70, 922337203685477.5807
71, -922337203685477.5808
72, CAST(-9999999999999999999999999999.9999999999 AS Numeric(38, 10))
73, CAST(9999999999999999999999999999.9999999999 AS Numeric(38, 10))
74, -3.4E+38
75, -1.18E-38
76, -214748.3648
77, 214748.3647
78, CAST(N'<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--This is a test XML file--><filemeta filetype="Audio"><Comments /><AlbumTitle /><TrackNumber /><ArtistName /><Year /><Genre /><TrackTitle /></filemeta>' AS nvarchar(210))
79, N'DG6'
80, N'<!--This is a test XML file--><filemeta filetype="Audio"><Comments /><AlbumTitle /><TrackNumber /><ArtistName /><Year /><Genre /><TrackTitle /></filemeta>'
81)
82
83INSERT [Src].[AllDataTypes] (
84 [charTest]
85 , [decimalMin], [decimalMax], [floatMin], [floatMax], [geographyNotNull], [geographyNull], [geometryNotNull], [geometryNull], [hierarchyidNotNull], [hierarchyidNull], [imageNotNull], [imageNull], [moneyMin], [moneyMax], [numericMin], [numericMax], [realMin], [realMax], [smallmoneyMin], [smallmoneyMax], [sql_variantTest], [sysnameTest], [xmlTest]) VALUES (NULL, CAST(-9999999999999999999999999999.9999999999 AS Decimal(38, 10)), CAST(9999999999999999999999999999.9999999999 AS Decimal(38, 10)), 2.23E-308, 1.79E+308, 0xE6100000010C01008000B5DF07C00700ECFAD03A4C40, 0xE6100000010C00008C521FBB49400400006E72A7EDBF, 0xE6100000010C65F547B878FD364069F2F7DF1AE65540, 0xE6100000010C65F547B878FD364069F2F7DF1AE65540, N'/3/', N'/1/', 0xFFD8FFE000104A464946000101, 922337203685477.5807, -922337203685477.5808, CAST(-9999999999999999999999999999.9999999999 AS Numeric(38, 10)), CAST(9999999999999999999999999999.9999999999 AS Numeric(38, 10)), 1.18E-38, 3.4E+38, -214748.3648, 214748.3647, CAST(N'<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--This is a test XML file--><filemeta filetype="Audio"><Comments /><AlbumTitle /><TrackNumber /><ArtistName /><Year /><Genre /><TrackTitle /></filemeta>' AS nvarchar(210)), N'DG6', N'<!--This is a test XML file--><filemeta filetype="Audio"><Comments /><AlbumTitle /><TrackNumber /><ArtistName /><Year /><Genre /><TrackTitle /></filemeta>')
86
87
88--[AllIntDataTypes]
89IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Src].[AllIntDataTypes]') AND type in (N'U'))
90 DROP TABLE [Src].[AllIntDataTypes]
91
92CREATE TABLE [Src].[AllIntDataTypes](
93 [coltinyint] [tinyint] NULL,
94 [colsmallint] [smallint] NULL,
95 [colint] [int] NULL,
96 [colbigint] [bigint] NULL
97);
98INSERT INTO [Src].[AllIntDataTypes]
99 ([coltinyint]
100 ,[colsmallint]
101 ,[colint]
102 ,[colbigint])
103 VALUES
104 (255,32767,2147483647,9223372036854775807),
105 (0,-32767,-2147483647,-9223372036854775807)
106--[AllNonIntDataTypes]
107IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Src].[AllNonIntDataTypes]') AND type in (N'U'))
108 DROP TABLE [Src].[AllNonIntDataTypes]
109
110CREATE TABLE [Src].[AllNonIntDataTypes](
111 [SSDataTypeID] [int] NOT NULL,
112 [TestBit] [bit] NULL,
113 [TestBin] [binary](50) NULL,
114 [TestVarBin] [varbinary](30) NULL,
115 [TestVarbinMax] [varbinary](max) NULL,
116 [TestMoney] [money] NULL,
117 [TestSmallMoney] [smallmoney] NULL,
118 [TestBigDecimal] [decimal](38, 30) NULL,
119 [TestFloat] [float] NULL,
120 [TestReal] [real] NULL,
121 [TestGUID] [uniqueidentifier] NULL,
122 [TestnNVarcharMax] [nvarchar](max) NULL,
123 [TestImage] [image] NULL,
124 [TestText] [text] NULL,
125 [TestNText] [ntext] NULL,
126 [TestVariant] [sql_variant] NULL,
127 [TestXML] [xml] NULL
128);
129
130INSERT [Src].[AllNonIntDataTypes] ([SSDataTypeID], [TestBit], [TestBin], [TestVarBin], [TestVarbinMax], [TestMoney], [TestSmallMoney], [TestBigDecimal], [TestFloat], [TestReal], [TestGUID], [TestnNVarcharMax], [TestImage], [TestText], [TestNText], [TestVariant], [TestXML]) VALUES (1, 0, 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005F7680E, 0x00000457, 0x00000457, 1.0001, 1.0000, CAST(12345678.012345678901235678901234567890 AS Decimal(38, 30)), 111111111, 111111112, N'0bcf6804-78ec-4e64-9cf2-b900b5c30c8c', N'Campbellfield', NULL, N'Campbellfield', N'Campbellfield', NULL, NULL)
131INSERT [Src].[AllNonIntDataTypes] ([SSDataTypeID], [TestBit], [TestBin], [TestVarBin], [TestVarbinMax], [TestMoney], [TestSmallMoney], [TestBigDecimal], [TestFloat], [TestReal], [TestGUID], [TestnNVarcharMax], [TestImage], [TestText], [TestNText], [TestVariant], [TestXML]) VALUES (2, 0, 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000068EA2E7, 0x00000456, 0x00000456, 2.0002, 2.0000, CAST(22345678.012345678901235678901234567890 AS Decimal(38, 30)), 222222222, 222222224, N'0bcf6804-78ec-4e64-9cf2-b900b5c30c8c', N'Somerton', NULL, N'Somerton', N'Somerton', NULL, NULL)
132INSERT [Src].[AllNonIntDataTypes] ([SSDataTypeID], [TestBit], [TestBin], [TestVarBin], [TestVarbinMax], [TestMoney], [TestSmallMoney], [TestBigDecimal], [TestFloat], [TestReal], [TestGUID], [TestnNVarcharMax], [TestImage], [TestText], [TestNText], [TestVariant], [TestXML]) VALUES (3, 0, 0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000B, 0x0000044D, 0x0000044D, 3.0003, 3.0000, CAST(32345678.012345678901235678901234567890 AS Decimal(38, 30)), 333333333, 333333344, N'0bcf6804-78ec-4e64-9cf2-b900b5c30c8c', N'Mickleham', NULL, N'Mickleham', N'Mickleham', NULL, NULL)
133INSERT [Src].[AllNonIntDataTypes] ([SSDataTypeID], [TestBit], [TestBin], [TestVarBin], [TestVarbinMax], [TestMoney], [TestSmallMoney], [TestBigDecimal], [TestFloat], [TestReal], [TestGUID], [TestnNVarcharMax], [TestImage], [TestText], [TestNText], [TestVariant], [TestXML]) VALUES (4, 0, 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000064, 0x0000044C, 0x0000044C, 4.0004, 4.0000, CAST(42345678.012345678901235678901234567890 AS Decimal(38, 30)), 444444444, 444444448, N'0bcf6804-78ec-4e64-9cf2-b900b5c30c8c', N'Fitzroy', NULL, N'Fitzroy', N'Fitzroy', NULL, NULL)
134INSERT [Src].[AllNonIntDataTypes] ([SSDataTypeID], [TestBit], [TestBin], [TestVarBin], [TestVarbinMax], [TestMoney], [TestSmallMoney], [TestBigDecimal], [TestFloat], [TestReal], [TestGUID], [TestnNVarcharMax], [TestImage], [TestText], [TestNText], [TestVariant], [TestXML]) VALUES (5, 0, 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000065, 0x000003F3, 0x000003F3, 5.0005, 5.0000, CAST(52345678.012345678901235678901234567890 AS Decimal(38, 30)), 555555555, 5.555556E+08, N'0bcf6804-78ec-4e64-9cf2-b900b5c30c8c', N'Collingwood', NULL, N'Collingwood', N'Collingwood', NULL, NULL)
135INSERT [Src].[AllNonIntDataTypes] ([SSDataTypeID], [TestBit], [TestBin], [TestVarBin], [TestVarbinMax], [TestMoney], [TestSmallMoney], [TestBigDecimal], [TestFloat], [TestReal], [TestGUID], [TestnNVarcharMax], [TestImage], [TestText], [TestNText], [TestVariant], [TestXML]) VALUES (6, 1, 0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006E, 0x000003F2, 0x000003F2, 6.0006, 6.0000, CAST(62345678.012345678901235678901234567890 AS Decimal(38, 30)), 666666666, 6.666667E+08, N'0bcf6804-78ec-4e64-9cf2-b900b5c30c8c', N'Abbotsford', NULL, N'Abbotsford', N'Abbotsford', NULL, NULL)
136INSERT [Src].[AllNonIntDataTypes] ([SSDataTypeID], [TestBit], [TestBin], [TestVarBin], [TestVarbinMax], [TestMoney], [TestSmallMoney], [TestBigDecimal], [TestFloat], [TestReal], [TestGUID], [TestnNVarcharMax], [TestImage], [TestText], [TestNText], [TestVariant], [TestXML]) VALUES (7, 1, 0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006F, 0x000003E9, 0x000003E9, 7.0007, 7.0000, CAST(72345678.012345678901235678901234567890 AS Decimal(38, 30)), 777777777, 7.777778E+08, N'0bcf6804-78ec-4e64-9cf2-b900b5c30c8c', N'Fitzroy North', NULL, N'Fitzroy North', N'Fitzroy North', NULL, NULL)
137INSERT [Src].[AllNonIntDataTypes] ([SSDataTypeID], [TestBit], [TestBin], [TestVarBin], [TestVarbinMax], [TestMoney], [TestSmallMoney], [TestBigDecimal], [TestFloat], [TestReal], [TestGUID], [TestnNVarcharMax], [TestImage], [TestText], [TestNText], [TestVariant], [TestXML]) VALUES (8, 1, 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003E8, 0x000003E8, 0x000003E8, 8.0008, 8.0000, CAST(82345678.012345678901235678901234567890 AS Decimal(38, 30)), 888888888, 8.888889E+08, N'0bcf6804-78ec-4e64-9cf2-b900b5c30c8c', N'Northcote', NULL, N'Northcote', N'Northcote', NULL, NULL)
138INSERT [Src].[AllNonIntDataTypes] ([SSDataTypeID], [TestBit], [TestBin], [TestVarBin], [TestVarbinMax], [TestMoney], [TestSmallMoney], [TestBigDecimal], [TestFloat], [TestReal], [TestGUID], [TestnNVarcharMax], [TestImage], [TestText], [TestNText], [TestVariant], [TestXML]) VALUES (9, 1, 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003E9, 0x0000006F, 0x0000006F, 9.0009, 9.0000, CAST(92345678.012345678901235678901234567890 AS Decimal(38, 30)), 999999999, 1E+09, N'0bcf6804-78ec-4e64-9cf2-b900b5c30c8c', N'Thornbury', NULL, N'Thornbury', N'Thornbury', NULL, NULL)
139
140
141--[AllDateTimeDataTypes]
142
143IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Src].[AllDateTimeDataTypes]') AND type in (N'U'))
144 DROP TABLE [Src].[AllDateTimeDataTypes]
145
146CREATE TABLE [Src].[AllDateTimeDataTypes](
147 [SSDateTimeTypeID] [int] NOT NULL,
148 [TestDate] [date] NULL,
149 [TestTime] [time](7) NULL,
150 [TestSmallDateTime] [smalldatetime] NULL,
151 [TestDateTime] [datetime] NULL,
152 [TestDateTime2] [datetime2](7) NULL,
153 [TestOffset] [datetimeoffset](7) NULL,
154 [TestTimeStamp] [timestamp] NOT NULL
155) ON [PRIMARY]
156
157INSERT INTO [Src].[AllDateTimeDataTypes]
158 ([SSDateTimeTypeID]
159 ,[TestDate]
160 ,[TestTime]
161 ,[TestSmallDateTime]
162 ,[TestDateTime]
163 ,[TestDateTime2]
164 ,[TestOffset])
165 VALUES
166 (1
167 ,'2000-04-01'
168 ,'15:25:35.0110000'
169 ,'2000-04-01 15:26:00'
170 ,'2000-04-01 15:25:35.010'
171 ,'2000-04-01 15:25:35.0110000'
172 ,'2000-04-01 15:25:35.0110000 +11:00')