October 13, 2015 at 3:09 am
Hi,
I have a few databases that are using Partitioned Views in order to manage the table sizes and they all work well for our purposes.
Recently I noticed a table that had grown to 400+ million rows and want to partition it as well, so I went about creating new base tables based on the initial table's structure, just adding a column to both table and primary key to be able to build a Partitioned View on them.
The first time around, on a test system, everything worked flawlessly but when I put the same structure in place on the production system I get the dreaded "UNION ALL view 'DBName.dbo.RptReportData' is not updatable because the primary key of table '[DBName].[dbo].[RptReportData_201405]' is not included in the union result. [SQLSTATE 42000] (Error 4444)" error.
I have searched high and low and everything I see points to a few directives in order for a UNION ALL view to be updatable:
- Need a partitioning column that is part of the primary key
- Need a CHECK constraint that make the base tables exclusive, i.e. data cannot belong to more than one table
- Cannot have IDENTITY or calculated columns in the base tables
- The INSERT statement needs to specify all columns with actual values, i.e. not DEFAULT
Well, according to me, my structure fulfills these conditions but the INSERT fails anyway. I would really appreciate another set of eyes to look at where I have missed something - because I am sure that there is a little glitch somewhere.
CREATE scripts below scripted from SQL Server. I only modified them to be on a single row - it is easier to verify that they are identical in a text editor that way.
Regards
Daniel
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
--===================================
--=== CREATE TABLES
--===================================
CREATE TABLE [dbo].[RptReportData_201401]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201401] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201402]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201402] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201403]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201403] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201404]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201404] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201405]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201405] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201406]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201406] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201407]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201407] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201408]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201408] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201409]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201409] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201410]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201410] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201411]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201411] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201412]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201412] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201501]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201501] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201502]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201502] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201503]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201503] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201504]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201504] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201505]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201505] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201506]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201506] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201507]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201507] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201508]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201508] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201509]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201509] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201510]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201510] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201511]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201511] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Report_Data]) ON [Report_Data]
GO
--===================================
--=== CREATE CHECK Constraints
--===================================
ALTER TABLE [dbo].[RptReportData_201401] WITH CHECK ADD CONSTRAINT [chkRptReportData_201401_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-01-01' AND [PeriodEndDate]<'2014-02-01'))
ALTER TABLE [dbo].[RptReportData_201402] WITH CHECK ADD CONSTRAINT [chkRptReportData_201402_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-02-01' AND [PeriodEndDate]<'2014-03-01'))
ALTER TABLE [dbo].[RptReportData_201403] WITH CHECK ADD CONSTRAINT [chkRptReportData_201403_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-03-01' AND [PeriodEndDate]<'2014-04-01'))
ALTER TABLE [dbo].[RptReportData_201404] WITH CHECK ADD CONSTRAINT [chkRptReportData_201404_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-04-01' AND [PeriodEndDate]<'2014-05-01'))
ALTER TABLE [dbo].[RptReportData_201405] WITH CHECK ADD CONSTRAINT [chkRptReportData_201405_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-05-01' AND [PeriodEndDate]<'2014-06-01'))
ALTER TABLE [dbo].[RptReportData_201406] WITH CHECK ADD CONSTRAINT [chkRptReportData_201406_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-06-01' AND [PeriodEndDate]<'2014-07-01'))
ALTER TABLE [dbo].[RptReportData_201407] WITH CHECK ADD CONSTRAINT [chkRptReportData_201407_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-07-01' AND [PeriodEndDate]<'2014-08-01'))
ALTER TABLE [dbo].[RptReportData_201408] WITH CHECK ADD CONSTRAINT [chkRptReportData_201408_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-08-01' AND [PeriodEndDate]<'2014-09-01'))
ALTER TABLE [dbo].[RptReportData_201409] WITH CHECK ADD CONSTRAINT [chkRptReportData_201409_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-09-01' AND [PeriodEndDate]<'2014-10-01'))
ALTER TABLE [dbo].[RptReportData_201410] WITH CHECK ADD CONSTRAINT [chkRptReportData_201410_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-10-01' AND [PeriodEndDate]<'2014-11-01'))
ALTER TABLE [dbo].[RptReportData_201411] WITH CHECK ADD CONSTRAINT [chkRptReportData_201411_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-11-01' AND [PeriodEndDate]<'2014-12-01'))
ALTER TABLE [dbo].[RptReportData_201412] WITH CHECK ADD CONSTRAINT [chkRptReportData_201412_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-12-01' AND [PeriodEndDate]<'2015-01-01'))
ALTER TABLE [dbo].[RptReportData_201501] WITH CHECK ADD CONSTRAINT [chkRptReportData_201501_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-01-01' AND [PeriodEndDate]<'2015-02-01'))
ALTER TABLE [dbo].[RptReportData_201502] WITH CHECK ADD CONSTRAINT [chkRptReportData_201502_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-02-01' AND [PeriodEndDate]<'2015-03-01'))
ALTER TABLE [dbo].[RptReportData_201503] WITH CHECK ADD CONSTRAINT [chkRptReportData_201503_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-03-01' AND [PeriodEndDate]<'2015-04-01'))
ALTER TABLE [dbo].[RptReportData_201504] WITH CHECK ADD CONSTRAINT [chkRptReportData_201504_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-04-01' AND [PeriodEndDate]<'2015-05-01'))
ALTER TABLE [dbo].[RptReportData_201505] WITH CHECK ADD CONSTRAINT [chkRptReportData_201505_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-05-01' AND [PeriodEndDate]<'2015-06-01'))
ALTER TABLE [dbo].[RptReportData_201506] WITH CHECK ADD CONSTRAINT [chkRptReportData_201506_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-06-01' AND [PeriodEndDate]<'2015-07-01'))
ALTER TABLE [dbo].[RptReportData_201507] WITH CHECK ADD CONSTRAINT [chkRptReportData_201507_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-07-01' AND [PeriodEndDate]<'2015-08-01'))
ALTER TABLE [dbo].[RptReportData_201508] WITH CHECK ADD CONSTRAINT [chkRptReportData_201508_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-08-01' AND [PeriodEndDate]<'2015-09-01'))
ALTER TABLE [dbo].[RptReportData_201509] WITH CHECK ADD CONSTRAINT [chkRptReportData_201509_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-09-01' AND [PeriodEndDate]<'2015-10-01'))
ALTER TABLE [dbo].[RptReportData_201510] WITH CHECK ADD CONSTRAINT [chkRptReportData_201510_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-10-01' AND [PeriodEndDate]<'2015-11-01'))
ALTER TABLE [dbo].[RptReportData_201511] WITH CHECK ADD CONSTRAINT [chkRptReportData_201511_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-11-01' AND [PeriodEndDate]<'2015-12-01'))
GO
--===================================
--=== CHECK Constraints
--===================================
ALTER TABLE [dbo].[RptReportData_201401] CHECK CONSTRAINT [chkRptReportData_201401_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201402] CHECK CONSTRAINT [chkRptReportData_201402_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201403] CHECK CONSTRAINT [chkRptReportData_201403_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201404] CHECK CONSTRAINT [chkRptReportData_201404_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201405] CHECK CONSTRAINT [chkRptReportData_201405_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201406] CHECK CONSTRAINT [chkRptReportData_201406_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201407] CHECK CONSTRAINT [chkRptReportData_201407_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201408] CHECK CONSTRAINT [chkRptReportData_201408_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201409] CHECK CONSTRAINT [chkRptReportData_201409_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201410] CHECK CONSTRAINT [chkRptReportData_201410_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201411] CHECK CONSTRAINT [chkRptReportData_201411_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201412] CHECK CONSTRAINT [chkRptReportData_201412_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201501] CHECK CONSTRAINT [chkRptReportData_201501_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201502] CHECK CONSTRAINT [chkRptReportData_201502_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201503] CHECK CONSTRAINT [chkRptReportData_201503_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201504] CHECK CONSTRAINT [chkRptReportData_201504_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201505] CHECK CONSTRAINT [chkRptReportData_201505_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201506] CHECK CONSTRAINT [chkRptReportData_201506_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201507] CHECK CONSTRAINT [chkRptReportData_201507_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201508] CHECK CONSTRAINT [chkRptReportData_201508_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201509] CHECK CONSTRAINT [chkRptReportData_201509_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201510] CHECK CONSTRAINT [chkRptReportData_201510_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201511] CHECK CONSTRAINT [chkRptReportData_201511_PeriodEndDate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--===================================
--=== CREATE Partitioned View
--===================================
CREATE VIEW [dbo].[RptReportData] AS
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201401
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201402
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201403
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201404
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201405
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201406
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201407
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201408
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201409
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201410
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201411
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201412
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201501
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201502
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201503
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201504
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201505
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201506
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201507
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201508
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201509
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201510
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201511
GO
October 15, 2015 at 12:52 am
There is nothing wrong with this DDL, works perfectly. Just a hunch, have you run DBCC CHECKDB on that database recently? Also is this script generated from the table set that is failing the inserts?
😎
Quick test script modification of your DDL, ran it on 4 different instances without a glitch.
USE TEEST;
GO
SET NOCOUNT ON;
GO
/*
-- http://www.sqlservercentral.com/Forums/Topic1727429-2799-1.aspx
-- [SQLSTATE 42000] (Error 4444)
- Need a partitioning column that is part of the primary key
- Need a CHECK constraint that make the base tables exclusive, i.e. data cannot belong to more than one table
- Cannot have IDENTITY or calculated columns in the base tables
- The INSERT statement needs to specify all columns with actual values, i.e. not DEFAULT
*/
--===================================
--=== CREATE TABLES
--===================================
IF OBJECT_ID(N'dbo.RptReportData') IS NOT NULL DROP VIEW dbo.RptReportData;
IF OBJECT_ID(N'dbo.RptReportData_201401') IS NOT NULL DROP TABLE dbo.RptReportData_201401;
IF OBJECT_ID(N'dbo.RptReportData_201402') IS NOT NULL DROP TABLE dbo.RptReportData_201402;
IF OBJECT_ID(N'dbo.RptReportData_201403') IS NOT NULL DROP TABLE dbo.RptReportData_201403;
IF OBJECT_ID(N'dbo.RptReportData_201404') IS NOT NULL DROP TABLE dbo.RptReportData_201404;
IF OBJECT_ID(N'dbo.RptReportData_201405') IS NOT NULL DROP TABLE dbo.RptReportData_201405;
IF OBJECT_ID(N'dbo.RptReportData_201406') IS NOT NULL DROP TABLE dbo.RptReportData_201406;
IF OBJECT_ID(N'dbo.RptReportData_201407') IS NOT NULL DROP TABLE dbo.RptReportData_201407;
IF OBJECT_ID(N'dbo.RptReportData_201408') IS NOT NULL DROP TABLE dbo.RptReportData_201408;
IF OBJECT_ID(N'dbo.RptReportData_201409') IS NOT NULL DROP TABLE dbo.RptReportData_201409;
IF OBJECT_ID(N'dbo.RptReportData_201410') IS NOT NULL DROP TABLE dbo.RptReportData_201410;
IF OBJECT_ID(N'dbo.RptReportData_201411') IS NOT NULL DROP TABLE dbo.RptReportData_201411;
IF OBJECT_ID(N'dbo.RptReportData_201412') IS NOT NULL DROP TABLE dbo.RptReportData_201412;
IF OBJECT_ID(N'dbo.RptReportData_201501') IS NOT NULL DROP TABLE dbo.RptReportData_201501;
IF OBJECT_ID(N'dbo.RptReportData_201502') IS NOT NULL DROP TABLE dbo.RptReportData_201502;
IF OBJECT_ID(N'dbo.RptReportData_201503') IS NOT NULL DROP TABLE dbo.RptReportData_201503;
IF OBJECT_ID(N'dbo.RptReportData_201504') IS NOT NULL DROP TABLE dbo.RptReportData_201504;
IF OBJECT_ID(N'dbo.RptReportData_201505') IS NOT NULL DROP TABLE dbo.RptReportData_201505;
IF OBJECT_ID(N'dbo.RptReportData_201506') IS NOT NULL DROP TABLE dbo.RptReportData_201506;
IF OBJECT_ID(N'dbo.RptReportData_201507') IS NOT NULL DROP TABLE dbo.RptReportData_201507;
IF OBJECT_ID(N'dbo.RptReportData_201508') IS NOT NULL DROP TABLE dbo.RptReportData_201508;
IF OBJECT_ID(N'dbo.RptReportData_201509') IS NOT NULL DROP TABLE dbo.RptReportData_201509;
IF OBJECT_ID(N'dbo.RptReportData_201510') IS NOT NULL DROP TABLE dbo.RptReportData_201510;
IF OBJECT_ID(N'dbo.RptReportData_201511') IS NOT NULL DROP TABLE dbo.RptReportData_201511;
CREATE TABLE [dbo].[RptReportData_201401]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201401] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201402]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201402] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201403]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201403] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201404]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201404] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201405]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201405] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201406]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201406] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201407]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201407] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201408]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201408] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201409]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201409] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201410]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201410] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201411]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201411] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201412]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201412] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201501]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201501] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201502]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201502] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201503]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201503] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201504]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201504] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201505]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201505] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201506]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201506] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201507]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201507] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201508]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201508] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201509]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201509] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201510]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201510] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
CREATE TABLE [dbo].[RptReportData_201511]([RptPeriodId] [int] NOT NULL,[SystemAccountId] [int] NOT NULL,[ReferenceTypeId] [int] NOT NULL,[SubAccountId] [int] NOT NULL,[CurrencyCode] [char](3) NOT NULL,[VatCategoryId] [int] NOT NULL,[OrganizationId] [int] NOT NULL,[PeriodEndDate] [date] NOT NULL,[Amount] [decimal](38, 4) NOT NULL,[Deposits] [decimal](38, 4) NULL,[Credits] [decimal](38, 4) NULL,[NoOfRefs] [int] NULL,CONSTRAINT [PK_RptReportData_201511] PRIMARY KEY CLUSTERED ([RptPeriodId] ASC,[SystemAccountId] ASC,[ReferenceTypeId] ASC,[SubAccountId] ASC,[CurrencyCode] ASC,[VatCategoryId] ASC,[OrganizationId] ASC,[PeriodEndDate] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) ---ON [Report_Data]
GO
--===================================
--=== CREATE CHECK Constraints
--===================================
ALTER TABLE [dbo].[RptReportData_201401] WITH CHECK ADD CONSTRAINT [chkRptReportData_201401_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-01-01' AND [PeriodEndDate]<'2014-02-01'))
ALTER TABLE [dbo].[RptReportData_201402] WITH CHECK ADD CONSTRAINT [chkRptReportData_201402_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-02-01' AND [PeriodEndDate]<'2014-03-01'))
ALTER TABLE [dbo].[RptReportData_201403] WITH CHECK ADD CONSTRAINT [chkRptReportData_201403_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-03-01' AND [PeriodEndDate]<'2014-04-01'))
ALTER TABLE [dbo].[RptReportData_201404] WITH CHECK ADD CONSTRAINT [chkRptReportData_201404_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-04-01' AND [PeriodEndDate]<'2014-05-01'))
ALTER TABLE [dbo].[RptReportData_201405] WITH CHECK ADD CONSTRAINT [chkRptReportData_201405_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-05-01' AND [PeriodEndDate]<'2014-06-01'))
ALTER TABLE [dbo].[RptReportData_201406] WITH CHECK ADD CONSTRAINT [chkRptReportData_201406_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-06-01' AND [PeriodEndDate]<'2014-07-01'))
ALTER TABLE [dbo].[RptReportData_201407] WITH CHECK ADD CONSTRAINT [chkRptReportData_201407_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-07-01' AND [PeriodEndDate]<'2014-08-01'))
ALTER TABLE [dbo].[RptReportData_201408] WITH CHECK ADD CONSTRAINT [chkRptReportData_201408_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-08-01' AND [PeriodEndDate]<'2014-09-01'))
ALTER TABLE [dbo].[RptReportData_201409] WITH CHECK ADD CONSTRAINT [chkRptReportData_201409_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-09-01' AND [PeriodEndDate]<'2014-10-01'))
ALTER TABLE [dbo].[RptReportData_201410] WITH CHECK ADD CONSTRAINT [chkRptReportData_201410_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-10-01' AND [PeriodEndDate]<'2014-11-01'))
ALTER TABLE [dbo].[RptReportData_201411] WITH CHECK ADD CONSTRAINT [chkRptReportData_201411_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-11-01' AND [PeriodEndDate]<'2014-12-01'))
ALTER TABLE [dbo].[RptReportData_201412] WITH CHECK ADD CONSTRAINT [chkRptReportData_201412_PeriodEndDate] CHECK (([PeriodEndDate]>='2014-12-01' AND [PeriodEndDate]<'2015-01-01'))
ALTER TABLE [dbo].[RptReportData_201501] WITH CHECK ADD CONSTRAINT [chkRptReportData_201501_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-01-01' AND [PeriodEndDate]<'2015-02-01'))
ALTER TABLE [dbo].[RptReportData_201502] WITH CHECK ADD CONSTRAINT [chkRptReportData_201502_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-02-01' AND [PeriodEndDate]<'2015-03-01'))
ALTER TABLE [dbo].[RptReportData_201503] WITH CHECK ADD CONSTRAINT [chkRptReportData_201503_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-03-01' AND [PeriodEndDate]<'2015-04-01'))
ALTER TABLE [dbo].[RptReportData_201504] WITH CHECK ADD CONSTRAINT [chkRptReportData_201504_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-04-01' AND [PeriodEndDate]<'2015-05-01'))
ALTER TABLE [dbo].[RptReportData_201505] WITH CHECK ADD CONSTRAINT [chkRptReportData_201505_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-05-01' AND [PeriodEndDate]<'2015-06-01'))
ALTER TABLE [dbo].[RptReportData_201506] WITH CHECK ADD CONSTRAINT [chkRptReportData_201506_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-06-01' AND [PeriodEndDate]<'2015-07-01'))
ALTER TABLE [dbo].[RptReportData_201507] WITH CHECK ADD CONSTRAINT [chkRptReportData_201507_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-07-01' AND [PeriodEndDate]<'2015-08-01'))
ALTER TABLE [dbo].[RptReportData_201508] WITH CHECK ADD CONSTRAINT [chkRptReportData_201508_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-08-01' AND [PeriodEndDate]<'2015-09-01'))
ALTER TABLE [dbo].[RptReportData_201509] WITH CHECK ADD CONSTRAINT [chkRptReportData_201509_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-09-01' AND [PeriodEndDate]<'2015-10-01'))
ALTER TABLE [dbo].[RptReportData_201510] WITH CHECK ADD CONSTRAINT [chkRptReportData_201510_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-10-01' AND [PeriodEndDate]<'2015-11-01'))
ALTER TABLE [dbo].[RptReportData_201511] WITH CHECK ADD CONSTRAINT [chkRptReportData_201511_PeriodEndDate] CHECK (([PeriodEndDate]>='2015-11-01' AND [PeriodEndDate]<'2015-12-01'))
GO
--===================================
--=== CHECK Constraints
--===================================
ALTER TABLE [dbo].[RptReportData_201401] CHECK CONSTRAINT [chkRptReportData_201401_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201402] CHECK CONSTRAINT [chkRptReportData_201402_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201403] CHECK CONSTRAINT [chkRptReportData_201403_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201404] CHECK CONSTRAINT [chkRptReportData_201404_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201405] CHECK CONSTRAINT [chkRptReportData_201405_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201406] CHECK CONSTRAINT [chkRptReportData_201406_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201407] CHECK CONSTRAINT [chkRptReportData_201407_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201408] CHECK CONSTRAINT [chkRptReportData_201408_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201409] CHECK CONSTRAINT [chkRptReportData_201409_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201410] CHECK CONSTRAINT [chkRptReportData_201410_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201411] CHECK CONSTRAINT [chkRptReportData_201411_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201412] CHECK CONSTRAINT [chkRptReportData_201412_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201501] CHECK CONSTRAINT [chkRptReportData_201501_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201502] CHECK CONSTRAINT [chkRptReportData_201502_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201503] CHECK CONSTRAINT [chkRptReportData_201503_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201504] CHECK CONSTRAINT [chkRptReportData_201504_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201505] CHECK CONSTRAINT [chkRptReportData_201505_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201506] CHECK CONSTRAINT [chkRptReportData_201506_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201507] CHECK CONSTRAINT [chkRptReportData_201507_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201508] CHECK CONSTRAINT [chkRptReportData_201508_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201509] CHECK CONSTRAINT [chkRptReportData_201509_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201510] CHECK CONSTRAINT [chkRptReportData_201510_PeriodEndDate]
ALTER TABLE [dbo].[RptReportData_201511] CHECK CONSTRAINT [chkRptReportData_201511_PeriodEndDate]
GO
--===================================
--=== CREATE Partitioned View
--===================================
CREATE VIEW [dbo].[RptReportData] AS
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201401
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201402
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201403
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201404
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201405
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201406
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201407
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201408
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201409
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201410
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201411
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201412
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201501
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201502
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201503
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201504
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201505
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201506
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201507
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201508
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201509
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201510
UNION ALL
SELECT [Amount], [Credits], [CurrencyCode], [Deposits], [NoOfRefs], [OrganizationId], [PeriodEndDate], [ReferenceTypeId], [RptPeriodId], [SubAccountId], [SystemAccountId], [VatCategoryId] FROM RptReportData_201511
GO
DECLARE @FIRST_DATE DATE = '2014-01-01';
DECLARE @LAST_DATE DATE = '2015-12-01';
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) X(N))
, NUMS(N) AS (SELECT TOP(DATEDIFF(DAY,@FIRST_DATE,@LAST_DATE)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,FIVE_A_DAY(NX) AS (SELECT NX FROM (VALUES (1),(2),(3),(4),(5)) AS X(NX))
INSERT INTO [dbo].[RptReportData]
([Amount]
,[Credits]
,[CurrencyCode]
,[Deposits]
,[NoOfRefs]
,[OrganizationId]
,[PeriodEndDate]
,[ReferenceTypeId]
,[RptPeriodId]
,[SubAccountId]
,[SystemAccountId]
,[VatCategoryId])
SELECT
CONVERT(decimal(38,4),NM.N,0)
,CONVERT(decimal(38,4),NM.N,0)
,'USD'
,CONVERT(decimal(38,4),NM.N,0)
,FAD.NX
,FAD.NX
,DATEADD(DAY,NM.N,@FIRST_DATE)
,FAD.NX
,FAD.NX
,FAD.NX
,FAD.NX
,FAD.NX
FROM NUMS NM
CROSS APPLY FIVE_A_DAY FAD;
SELECT
RD.Amount
,RD.Credits
,RD.CurrencyCode
,RD.Deposits
,RD.NoOfRefs
,RD.OrganizationId
,RD.PeriodEndDate
,RD.ReferenceTypeId
,RD.RptPeriodId
,RD.SubAccountId
,RD.SystemAccountId
,RD.VatCategoryId
FROM dbo.RptReportData RD;
October 15, 2015 at 1:52 am
Thank you very much for taking the time to run the scripts and test the resulting structure. It feels good to know that it works on other systems even though it does not work here.
This is a brand new database so I didn't think of running DBCC CHECKDB on it before, however, I have now done that and it shows no errors.
"CHECKDB found 0 allocation errors and 0 consistency errors in database 'DBName'."
The scripts I attached were generated from the failing environment, yes.
I guess that I'll just have to tear down that structure and re-build it from the generated scripts. Seems like an exercise in futility but that's the only way out I can think of now.
I'll keep you posted on how it worked.
Daniel
October 15, 2015 at 7:00 am
What I ended up doing was to create the logic in the SP that inserts/deletes data to know which partition it should insert into or delete from. I did not have the time to find out why this would not work on my servers.
Thanks again for the help
Daniel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply