CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
dprice
dprice

Reputation: 13

Creating a partitioned view in SQL Server 2008 R2 Enterprise

I'm extending some legacy software that splits data up in to multiple schemas by company, for example CP1.ACCOUNTS, CP2.ACCOUNTS, CPN.ACCOUNTS. I'm attempting to create an updatable view of these tables using partitioning, but I'm getting the typical "not updatable because a partitioning column was not found" error. The column I'm trying to partition on is the primary key, and as far as I can tell, isn't any of the things it isn't allowed to be.

So, with table definitions like so:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [CP1].[ACCOUNTS](
    [ACCOUNTID] [char](10) NOT NULL,
    [LASTNAME] [varchar](60) NOT NULL,
    [FIRSTNAME] [varchar](35) NOT NULL,
    [MIDDLE] [varchar](26) NULL,
    [SUFFIX] [varchar](10) NULL,
    [ADDRESS1] [varchar](55) NULL,
    [ADDRESS2] [varchar](55) NULL,
    [SOME_FLAG] [tinyint] NULL,
    CONSTRAINT [ARM_CODE_KEY] PRIMARY KEY CLUSTERED 
        (
            [CODE_] ASC
        ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [CP1].[ACCOUNTS]  WITH CHECK ADD  CONSTRAINT [CK__ACCOUNTS__CODE___4DD705FF] CHECK  ((left([ACCOUNTID],(3))='CP1'))
GO

ALTER TABLE [CP1].[ACCOUNTS] CHECK CONSTRAINT [CK__ACCOUNTS__CODE___4DD705FF]
GO

ALTER TABLE [CP1].[ACCOUNTS] ADD  DEFAULT ((0)) FOR [SOME_FLAG]
GO

and the rest of the tables defined exactly as above, following the CP2, CP3, CPN pattern, and the view definition being a simple: 

CREATE VIEW [ALL].[ACCOUNTS] AS
    SELECT * FROM CP1.ACCOUNTS
    UNION ALL
    SELECT * FROM CP2.ACCOUNTS
    --UNION ALL etc...

Inserts would be like:

INSERT INTO [ALL].[ACCOUNTS]
           ([ACCOUNTID]
           ,[LASTNAME]
           ,[FIRSTNAME]
           ,[MIDDLE]
           ,[SUFFIX]
           ,[ADDRESS1]
           ,[ADDRESS2]
           ,[SOME_FLAG])
     VALUES
           ('CP1XYZ0001',
            'SMITH',
            'JOHN',
            'Q',
            '',
            '123 Fake St',
            'Apt 2',
            0,
GO

generates an error like:

Msg 4436, level 16, State 12, Line 1
UNION ALL view 'ALL.ACCOUNTS' is not updatable because a partitioning column was not found.

Am I missing something simple? Am I just way out in left field here?

Upvotes: 0

Views: 1016

Answers (2)

Ulfius
Ulfius

Reputation: 617

In case someone comes upon this, you can use a computed column for partitioning, just make sure to make it a persisted computed column.

In this case, the computed column should be left([ACCOUNTID],(3) and the partition constraint would be <computed column> = 'CP1'. Note: using left() in the constraint will cause it to still scan all partitions. The CHECK constraints can only use these operators: BETWEEN, AND, OR, <, <=, >, >=, =.

Also, since the question referenced enterprise edition, you'd get better performance using a partitioned table instead of a partitioned view.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You need a constraint that defines which column is used as a partitioning column. As the error suggests, you don't have one defined. As described in the documentation:

To perform updates on a partitioned view, the partitioning column must be a part of the primary key of the base table. If a view is not updatable, you can create an INSTEAD OF trigger on the view that allows updates. You should design error handling into the trigger to make sure that no duplicate rows are inserted. For an example of an INSTEAD OF trigger designed on a view, see Designing INSTEAD OF Triggers.

In other words, SQL Server needs to be able to figure out which table gets the update.

You might be able to alter the tables to contain a company name column, which is then used as part of the primary key. Something like this might work:

create table . . .
    CompanyName as 'CompanyA',
    primary key (AccountId, CompanyName)
    . . .

The alternative is to use an instead of trigger, as suggested in the documentation.

Upvotes: 2

Related Questions