3DtJnZh0

1USE [master];
2GO
3
4CREATE DATABASE [TaiwanBioBank]
5CONTAINMENT = NONE
6ON PRIMARY (
7      NAME = N'TaiwanBioBank'
8    , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TaiwanBioBank.mdf'
9    , SIZE = 8192 KB
10    , FILEGROWTH = 65536 KB
11)
12LOG ON (
13      NAME = N'TaiwanBioBank_log'
14    , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TaiwanBioBank_log.ldf'
15    , SIZE = 8192 KB
16    , FILEGROWTH = 65536 KB
17);
18GO
19
20ALTER DATABASE [TaiwanBioBank] SET COMPATIBILITY_LEVEL = 130;
21GO
22ALTER DATABASE [TaiwanBioBank] SET ANSI_NULL_DEFAULT OFF;
23GO
24ALTER DATABASE [TaiwanBioBank] SET ANSI_NULLS OFF;
25GO
26ALTER DATABASE [TaiwanBioBank] SET ANSI_PADDING OFF;
27GO
28ALTER DATABASE [TaiwanBioBank] SET ANSI_WARNINGS OFF;
29GO
30ALTER DATABASE [TaiwanBioBank] SET ARITHABORT OFF;
31GO
32ALTER DATABASE [TaiwanBioBank] SET AUTO_CLOSE OFF;
33GO
34ALTER DATABASE [TaiwanBioBank] SET AUTO_SHRINK OFF;
35GO
36ALTER DATABASE [TaiwanBioBank] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
37GO
38ALTER DATABASE [TaiwanBioBank] SET AUTO_UPDATE_STATISTICS ON;
39GO
40ALTER DATABASE [TaiwanBioBank] SET CURSOR_CLOSE_ON_COMMIT OFF;
41GO
42ALTER DATABASE [TaiwanBioBank] SET CURSOR_DEFAULT GLOBAL;
43GO
44ALTER DATABASE [TaiwanBioBank] SET CONCAT_NULL_YIELDS_NULL OFF;
45GO
46ALTER DATABASE [TaiwanBioBank] SET NUMERIC_ROUNDABORT OFF;
47GO
48ALTER DATABASE [TaiwanBioBank] SET QUOTED_IDENTIFIER OFF;
49GO
50ALTER DATABASE [TaiwanBioBank] SET RECURSIVE_TRIGGERS OFF;
51GO
52ALTER DATABASE [TaiwanBioBank] SET DISABLE_BROKER;
53GO
54ALTER DATABASE [TaiwanBioBank] SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
55GO
56ALTER DATABASE [TaiwanBioBank] SET DATE_CORRELATION_OPTIMIZATION OFF;
57GO
58ALTER DATABASE [TaiwanBioBank] SET PARAMETERIZATION SIMPLE;
59GO
60ALTER DATABASE [TaiwanBioBank] SET READ_COMMITTED_SNAPSHOT OFF;
61GO
62ALTER DATABASE [TaiwanBioBank] SET READ_WRITE;
63GO
64ALTER DATABASE [TaiwanBioBank] SET RECOVERY FULL;
65GO
66ALTER DATABASE [TaiwanBioBank] SET MULTI_USER;
67GO
68ALTER DATABASE [TaiwanBioBank] SET PAGE_VERIFY CHECKSUM;
69GO
70ALTER DATABASE [TaiwanBioBank] SET TARGET_RECOVERY_TIME = 60 SECONDS;
71GO
72ALTER DATABASE [TaiwanBioBank] SET DELAYED_DURABILITY = DISABLED;
73GO
74USE [TaiwanBioBank];
75GO
76ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
77GO
78ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
79GO
80ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
81GO
82ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
83GO
84ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
85GO
86ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
87GO
88ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
89GO
90ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
91GO
92
93USE [TaiwanBioBank];
94GO
95
96IF NOT EXISTS (
97    SELECT [name]
98    FROM [sys].[filegroups]
99    WHERE [is_default] = 1
100          AND [name] = N'PRIMARY'
101)
102BEGIN
103    ALTER DATABASE [TaiwanBioBank] MODIFY FILEGROUP [PRIMARY] DEFAULT;
104END;
105GO
106
107ALTER DATABASE [TaiwanBioBank] SET RECOVERY SIMPLE;
108GO
109
110CREATE TABLE [dbo].[TaiwanBioBank_population_allelefreq.1517] (
111      [Chrom]      NVARCHAR(5) NOT NULL
112    , [Position]   [INT] NOT NULL
113    , [dbSNPid]    NVARCHAR(256) NOT NULL
114    , [Ref ]       NVARCHAR(512) NOT NULL
115    , [AA]         [INT] NOT NULL
116    , [AC]         [INT] NOT NULL
117    , [AG]         [INT] NOT NULL
118    , [AT]         [INT] NOT NULL
119    , [CC]         [INT] NOT NULL
120    , [CG]         [INT] NOT NULL
121    , [CT]         [INT] NOT NULL
122    , [GG]         [INT] NOT NULL
123    , [GT]         [INT] NOT NULL
124    , [TT]         [INT] NOT NULL
125    , [Others]     [INT] NOT NULL
126    , [AlleleFreq] NVARCHAR(MAX) NOT NULL
127    , [VarType]    NVARCHAR(4) NOT NULL
128)
129ON [PRIMARY];
130GO
131
132DECLARE @bulk_cmd NVARCHAR(4000);
133SET @bulk_cmd = 'BULK INSERT [TaiwanBioBank_population_allelefreq.1517]
134FROM ''C:\Users\User\Downloads\TaiwanBioBank_population_allelefreq.1517.tsv''
135WITH (
136FIRSTROW = 2,
137FIELDTERMINATOR = ''\t'',
138ROWTERMINATOR = '''+CHAR(10)+'''
139)';
140EXEC (@bulk_cmd);
141GO
142
143CREATE NONCLUSTERED INDEX [IX_TaiwanBioBank_population_allelefreq.1517]
144ON [dbo].[TaiwanBioBank_population_allelefreq.1517] (
145      [Position] ASC
146    , [Chrom] ASC
147    , [Ref ] ASC
148)
149INCLUDE (
150    [AlleleFreq]
151)
152WITH (
153      PAD_INDEX = OFF
154    , STATISTICS_NORECOMPUTE = OFF
155    , SORT_IN_TEMPDB = OFF
156    , DROP_EXISTING = OFF
157    , ONLINE = OFF
158    , ALLOW_ROW_LOCKS = ON
159    , ALLOW_PAGE_LOCKS = ON
160)
161ON [PRIMARY];
162GO
163
164CREATE TABLE [dbo].[Whole exome-UCSC_Main_on_Human__refGene_(genome)] (
165      [chrom]      NVARCHAR(32) NOT NULL
166    , [chromStart] [INT] NOT NULL
167    , [chromEnd]   [INT] NOT NULL
168    , [name]       NVARCHAR(64) NOT NULL
169    , [score]      [INT] NOT NULL
170    , [strand]     NVARCHAR(2) NOT NULL
171)
172ON [PRIMARY];
173GO
174
175DECLARE @bulk_cmd NVARCHAR(4000);
176SET @bulk_cmd = 'BULK INSERT [Whole exome-UCSC_Main_on_Human__refGene_(genome)]
177FROM ''C:\Users\User\Downloads\Whole exome-[UCSC_Main_on_Human__refGene_(genome)].bed''
178WITH (
179FIRSTROW = 1,
180FIELDTERMINATOR = ''\t'',
181ROWTERMINATOR = '''+CHAR(10)+'''
182)';
183EXEC (@bulk_cmd);
184GO
185
186CREATE NONCLUSTERED INDEX [IX_Whole exome-UCSC_Main_on_Human__refGene_(genome)]
187ON [dbo].[Whole exome-UCSC_Main_on_Human__refGene_(genome)] (
188      [chromStart] ASC
189    , [chromEnd] ASC
190    , [chrom] ASC
191)
192WITH (
193      PAD_INDEX = OFF
194    , STATISTICS_NORECOMPUTE = OFF
195    , SORT_IN_TEMPDB = OFF
196    , DROP_EXISTING = OFF
197    , ONLINE = OFF
198    , ALLOW_ROW_LOCKS = ON
199    , ALLOW_PAGE_LOCKS = ON
200)
201ON [PRIMARY];
202GO
203
204CREATE TABLE [dbo].[TaiwanBioBankWholeExome] (
205      [Chrom]    NVARCHAR(5) NOT NULL
206    , [Position] [INT] NOT NULL
207    , [Ref]      NVARCHAR(512) NOT NULL
208    , [Alt]      NVARCHAR(MAX) NOT NULL
209    , [VarType]    NVARCHAR(3) NOT NULL
210    , [Rate]     FLOAT NOT NULL
211)
212ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
213GO
214
215CREATE CLUSTERED INDEX [CX_TaiwanBioBankWholeExome]
216ON [dbo].[TaiwanBioBankWholeExome] (
217      [Chrom] ASC
218    , [Position] ASC
219    , [VarType] ASC
220    , [Rate] DESC
221)
222WITH (
223      PAD_INDEX = OFF
224    , STATISTICS_NORECOMPUTE = OFF
225    , SORT_IN_TEMPDB = OFF
226    , DROP_EXISTING = OFF
227    , ONLINE = OFF
228    , ALLOW_ROW_LOCKS = ON
229    , ALLOW_PAGE_LOCKS = ON
230)
231ON [PRIMARY];
232GO
233
234INSERT INTO [dbo].[TaiwanBioBankWholeExome]
235SELECT DISTINCT
236       [a].[Chrom]
237     , [a].[Position]
238     , [a].[Ref]
239     , SUBSTRING(value, 1, CHARINDEX(':', value)-1) AS [Alt]
240     , [a].[VarType]
241     , CAST(SUBSTRING(value, CHARINDEX(':', value)+1, LEN(value)-CHARINDEX(':', value)) AS FLOAT) AS [Rate]
242FROM [dbo].[TaiwanBioBank_population_allelefreq.1517] AS [a]
243     CROSS APPLY [STRING_SPLIT]([a].[AlleleFreq], ' ')
244     INNER JOIN [dbo].[Whole exome-UCSC_Main_on_Human__refGene_(genome)] AS [b]
245     ON [a].[Chrom] = [b].[chrom]
246        AND [a].[Position] >= [b].[chromStart]
247        AND [a].[Position] < [b].[chromEnd];
248GO
249
250DBCC SHRINKFILE(TaiwanBioBank, 1);
251DBCC SHRINKFILE(TaiwanBioBank_log, 1);
252GO