Share via

partitioned view is not updatable because a partitioning column was not found

  • Article

I was testing partitioned views using the following script

USE DB1
GO
CREATE TABLE [dbo].[employee](
[Empid] [int] NOT NULL ,
[EmpName] [char](5) NOT NULL,
[AddressLine1] [varchar](8000) NULL,
[Addressline2] [varchar](8000) NULL,
 Constraint testing CHECK (Empid > 10),
Constraint PK_EmpID PRIMARY KEY(EmpID)
) ON [PRIMARY]

USE DB2
GO
CREATE TABLE [dbo].[employee](
[Empid] [int] NOT NULL ,
[EmpName] [char](5) NOT NULL,
[AddressLine1] [varchar](8000) NULL,
[Addressline2] [varchar](8000) NULL,
 Constraint testing CHECK (Empid < 11),
Constraint PK_EmpID PRIMARY KEY(EmpID)
) ON [PRIMARY]

I created the following view

create view vwDistributedEmp as
select empid,empname,AddressLine1,AddressLine2
from DB1.dbo.Employee
union all
select empid,empname,AddressLine1,AddressLine2
from DB2.dbo.Employee
go

while trying to populate the table using the view it threw the following error
"partitioned view is not updatable because a partitioning column was not found"

After some struggle, I suspected that it could be because of the value for the check constraint. Even though the values of EmpId would not overlap  with the above constraint the above tables were recreated as below and the insert using partitioned view succeeded.

USE DB1
GO
CREATE TABLE [dbo].[employee](
[Empid] [int] NOT NULL ,
[EmpName] [char](5) NOT NULL,
[AddressLine1] [varchar](8000) NULL,
[Addressline2] [varchar](8000) NULL,
 Constraint testing CHECK (Empid > 10),
Constraint PK_EmpID PRIMARY KEY(EmpID)
) ON [PRIMARY]

USE DB2
GO
CREATE TABLE [dbo].[employee](
[Empid] [int] NOT NULL ,
[EmpName] [char](5) NOT NULL,
[AddressLine1] [varchar](8000) NULL,
[Addressline2] [varchar](8000) NULL,
 Constraint testing CHECK (Empid <= 10),
Constraint PK_EmpID PRIMARY KEY(EmpID)
) ON [PRIMARY]

 

PS: Use the execution plan to check which table in the partitioned view is  used.