
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [TransactionItemAggPF1](smalldatetime) AS RANGE RIGHT FOR VALUES(
N'2019-01-01T00:00:00', N'2019-02-01T00:00:00', N'2019-03-01T00:00:00', 
N'2019-04-01T00:00:00', N'2019-05-01T00:00:00', N'2019-06-01T00:00:00', 
N'2019-07-01T00:00:00', N'2019-08-01T00:00:00', N'2019-09-01T00:00:00', 
N'2019-10-01T00:00:00', N'2019-11-01T00:00:00', N'2019-12-01T00:00:00', 
N'2020-01-01T00:00:00', N'2020-02-01T00:00:00', N'2020-03-01T00:00:00', 
N'2020-04-01T00:00:00', N'2020-05-01T00:00:00', N'2020-06-01T00:00:00', 
N'2020-07-01T00:00:00', N'2020-08-01T00:00:00', N'2020-09-01T00:00:00', 
N'2020-10-01T00:00:00', N'2020-11-01T00:00:00', N'2020-12-01T00:00:00', N'2021-01-01T00:00:00')

-- correct file group name shall be given

CREATE PARTITION SCHEME [TransactionItemAggPS1] AS PARTITION [TransactionItemAggPF1] TO (
[FG_TransactionItemAgg], [FG_TransactionItemAgg], [FG_TransactionItemAgg], 
[FG_TransactionItemAgg], [FG_TransactionItemAgg], [FG_TransactionItemAgg], 
[FG_TransactionItemAgg], [FG_TransactionItemAgg], [FG_TransactionItemAgg], 
[FG_TransactionItemAgg], [FG_TransactionItemAgg], [FG_TransactionItemAgg], 
[FG_TransactionItemAgg], [FG_TransactionItemAgg], [FG_TransactionItemAgg], 
[FG_TransactionItemAgg], [FG_TransactionItemAgg], [FG_TransactionItemAgg], 
[FG_TransactionItemAgg], [FG_TransactionItemAgg], [FG_TransactionItemAgg], 
[FG_TransactionItemAgg], [FG_TransactionItemAgg], [FG_TransactionItemAgg], [FG_TransactionItemAgg], [FG_TransactionItemAgg])



ALTER TABLE [dbo].[tbTransactionItem] DROP CONSTRAINT [PK_tbTransactionItem] WITH ( ONLINE = OFF )


ALTER TABLE [dbo].[tbTransactionItem] ADD  CONSTRAINT [PK_tbTransactionItem] PRIMARY KEY NONCLUSTERED 
(
	[TransactionItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [FG_TransactionItemAgg]


CREATE CLUSTERED INDEX [IX_BusinessDay_on_TransactionItemAggPS1] ON [dbo].[tbTransactionItem]([BusinessDay]) 
ON [TransactionItemAggPS1]([BusinessDay])
GO


CREATE NONCLUSTERED INDEX IX_EntityID ON tbTransactionItem(EntityID)
ON TransactionItemAggPS1(BusinessDay);
GO

CREATE NONCLUSTERED INDEX IX_IncludingInventoryCostQuantity ON tbTransactionItem(EntityID, ItemID, TransactionTime)
ON TransactionItemAggPS1(BusinessDay);
GO



COMMIT TRANSACTION