Skip to content

Commit 7a3d906

Browse files
edarchisedarchis
authored
OP-1467 Remove refs to file groups + unsafe update (#169)
* OP-1467 Remove refs to file groups + unsafe update IndexesFG is specific to one setup and OPTIMIZE_FOR_SEQUENTIAL_KEY is not available on some versions of MS SQL. * OP-1467 Some more fixes on SQL script
1 parent 93640a8 commit 7a3d906

1 file changed

Lines changed: 27 additions & 22 deletions

File tree

‎sql/migrations/1_migration_latest.sql‎

Lines changed: 27 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -150,9 +150,7 @@ BEGIN
150150
END
151151
GO
152152

153-
ALTER TABLE tblFamilySMS DROP CONSTRAINT IF EXISTS DF_tblFamilies_LanguageOfSMS;
154-
GO
155-
DROP FUNCTION IF EXISTS [dbo].[udfDefaultLanguageCode]
153+
DROP FUNCTION IF EXISTS [dbo].[udfDefaultLanguageCode]
156154
GO
157155

158156
CREATE FUNCTION [dbo].[udfDefaultLanguageCode]()
@@ -2472,7 +2470,7 @@ IF OBJECT_ID('[uspGetPolicyRenewals]', 'P') IS NOT NULL
24722470
DROP PROCEDURE uspGetPolicyRenewals
24732471
GO
24742472

2475-
CREATE PROCEDURE [dbo].[uspGetPolicyRenewals]
2473+
CREATE PROCEDURE [dbo].[uspGetPolicyRenewals]
24762474
(
24772475
@OfficerCode NVARCHAR(8)
24782476
)
@@ -3237,9 +3235,16 @@ IF OBJECT_ID('FK_tblPaymentDetails_tblPayment') IS NULL
32373235

32383236
--OTC-511
32393237
IF COL_LENGTH(N'tblPremium', N'CreatedDate') IS NULL
3240-
ALTER TABLE tblPremium
3241-
ADD [CreatedDate] DATETIME NOT NULL DEFAULT GETDATE()
3242-
UPDATE tblPremium SET CreatedDate = ValidityFrom
3238+
ALTER TABLE tblPremium
3239+
ADD [CreatedDate] DATETIME NULL DEFAULT GETDATE()
3240+
-- We cannot put the not null and update here because the alter will be defered and the update will fail
3241+
GO
3242+
3243+
UPDATE tblPremium SET [CreatedDate] = ValidityFrom WHERE [CreatedDate] is null
3244+
GO
3245+
3246+
ALTER TABLE tblPremium
3247+
ALTER COLUMN [CreatedDate] DATETIME NOT NULL
32433248
GO
32443249

32453250
--OTC-520
@@ -3498,7 +3503,7 @@ BEGIN
34983503
[InsureeId] ASC,
34993504
[PolicyId] ASC
35003505
)
3501-
INCLUDE([EffectiveDate],[ExpiryDate],[ValidityTo]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3506+
INCLUDE([EffectiveDate],[ExpiryDate],[ValidityTo]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
35023507
END
35033508
GO
35043509

@@ -3510,7 +3515,7 @@ BEGIN
35103515
[ValidityTo] ASC,
35113516
[EffectiveDate] ASC,
35123517
[ExpiryDate] ASC
3513-
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3518+
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
35143519
END
35153520
GO
35163521

@@ -3519,7 +3524,7 @@ BEGIN
35193524
CREATE NONCLUSTERED INDEX [NCI_tblInsureePolicy_InsureeID] ON [dbo].[tblInsureePolicy]
35203525
(
35213526
[InsureeId] ASC
3522-
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3527+
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
35233528
END
35243529
GO
35253530

@@ -3531,7 +3536,7 @@ BEGIN
35313536
[EffectiveDate] ASC,
35323537
[ExpiryDate] ASC
35333538
)
3534-
INCLUDE([InsureeId],[PolicyId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
3539+
INCLUDE([InsureeId],[PolicyId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
35353540
END
35363541
GO
35373542

@@ -3542,7 +3547,7 @@ BEGIN
35423547
[EffectiveDate] ASC,
35433548
[ValidityTo] ASC
35443549
)
3545-
INCLUDE([PolicyId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3550+
INCLUDE([PolicyId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
35463551
END
35473552
GO
35483553

@@ -3554,7 +3559,7 @@ BEGIN
35543559
[ValidityTo] ASC,
35553560
[EffectiveDate] ASC,
35563561
[ExpiryDate] ASC
3557-
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3562+
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
35583563
END
35593564
GO
35603565

@@ -3563,7 +3568,7 @@ BEGIN
35633568
CREATE NONCLUSTERED INDEX [NCI_tblInsureePolicy_PolicyID] ON [dbo].[tblInsureePolicy]
35643569
(
35653570
[PolicyId] ASC
3566-
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3571+
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
35673572
END
35683573
GO
35693574

@@ -3644,7 +3649,7 @@ IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = N'missing_index_215' AND ob
36443649
[ValidityTo] ASC,
36453650
[DateProcessed] ASC
36463651
)
3647-
INCLUDE([ClaimCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3652+
INCLUDE([ClaimCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
36483653
GO
36493654

36503655
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = N'missing_index_218' AND object_id = OBJECT_ID('tblClaim'))
@@ -3655,7 +3660,7 @@ IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = N'missing_index_218' AND ob
36553660
[ValidityTo] ASC,
36563661
[DateProcessed] ASC
36573662
)
3658-
INCLUDE([ClaimCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3663+
INCLUDE([ClaimCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
36593664
GO
36603665

36613666
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = N'missing_index_242' AND object_id = OBJECT_ID('tblClaim'))
@@ -3666,7 +3671,7 @@ IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = N'missing_index_242' AND ob
36663671
[HFID] ASC,
36673672
[DateProcessed] ASC
36683673
)
3669-
INCLUDE([ClaimCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3674+
INCLUDE([ClaimCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
36703675
GO
36713676

36723677
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = N'missing_index_245' AND object_id = OBJECT_ID('tblClaim'))
@@ -3677,7 +3682,7 @@ IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = N'missing_index_245' AND ob
36773682
[HFID] ASC,
36783683
[DateProcessed] ASC
36793684
)
3680-
INCLUDE([ClaimCode],[Claimed]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3685+
INCLUDE([ClaimCode],[Claimed]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
36813686
GO
36823687

36833688
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = N'missing_index_306' AND object_id = OBJECT_ID('tblClaim'))
@@ -3686,15 +3691,15 @@ IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = N'missing_index_306' AND ob
36863691
[ClaimStatus] ASC,
36873692
[ValidityTo] ASC,
36883693
[DateProcessed] ASC
3689-
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3694+
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
36903695
GO
36913696

36923697
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = N'missing_index_4896' AND object_id = OBJECT_ID('tblClaim'))
36933698
CREATE NONCLUSTERED INDEX [missing_index_4896] ON [dbo].[tblClaim]
36943699
(
36953700
[ClaimStatus] ASC,
36963701
[DateProcessed] ASC
3697-
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3702+
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
36983703
GO
36993704

37003705
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = N'missing_index_50' AND object_id = OBJECT_ID('tblClaim'))
@@ -3704,14 +3709,14 @@ IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = N'missing_index_50' AND obj
37043709
[ValidityTo] ASC,
37053710
[DateProcessed] ASC
37063711
)
3707-
INCLUDE([ICDID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3712+
INCLUDE([ICDID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
37083713
GO
37093714

37103715
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = N'NCI_tblClaim_DateProcessed' AND object_id = OBJECT_ID('tblClaim'))
37113716
CREATE NONCLUSTERED INDEX [NCI_tblClaim_DateProcessed] ON [dbo].[tblClaim]
37123717
(
37133718
[DateProcessed] ASC
3714-
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [IndexesFG]
3719+
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
37153720
GO
37163721

37173722
--OTC-941

0 commit comments

Comments
 (0)