Creating Partitioned Views - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Creating Partitioned Views

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-18 : 09:19:30

I have the following code:


create table test1 (ID int primary key, date datetime, number int)
create table test2 (ID int primary key, date datetime, number int)
create table test3 (ID int primary key, date datetime, number int)
go
create view vwtest
as
select * from test1
union all
select * from test2
union all
select * from test3
go
alter table test1
add constraint ck_test1a check (number = 1) 
alter table test2
add constraint ck_test2a check (number = 2) 
alter table test3
add constraint ck_test3a check (number = 3) 
go
insert into vwtest
select 2, current_timestamp, 1
go
drop table test1
drop table test2
drop table test3
drop view vwtest

I have created a partitioned column have I not? So why do I get the error:

UNION ALL view 'crdm_prototype.dbo.vwtest' is not updatable because a partitioning column was not found.

?????????

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 09:21:53

See http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Horizontal-partitioning.aspx

E 12°55'05.63"
N 56°04'39.26"

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-18 : 10:31:09

I've had a look at the link, but it still doesn't answer my question as to why what I've implemented does not work?

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-18 : 10:39:24

I have my answer:

'the partitioning column must be a part of the primary key of the base table'

Thanks

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-18 : 10:54:04

This works:

drop table test1
drop table test2
drop table test3
drop view vwtest

create table test1 (ID int not null, date datetime not null, number int not null)
create table test2 (ID int not null, date datetime not null, number int not null)
create table test3 (ID int not null, date datetime not null, number int not null)
go
create view vwtest
as
select * from test1
union all
select * from test2
union all
select * from test3
go
alter table test1
add constraint pk_test1a primary key (id,number) 
alter table test2
add constraint pk_test2a primary key (id,number) 
alter table test3
add constraint pk_test3a primary key (id,number) 
go
alter table test1
add constraint ck_test1a check (number = 1) 
alter table test2
add constraint ck_test2a check (number = 2) 
alter table test3
add constraint ck_test3a check (number = 3) 
go
insert into vwtest
select 2, current_timestamp, 1
go

When I change the CHECK to include a function, it doesn't work:

create table test1 (ID int not null, date datetime not null, number int not null)
create table test2 (ID int not null, date datetime not null, number int not null)
create table test3 (ID int not null, date datetime not null, number int not null)
go
create view vwtest
as
select * from test1
union all
select * from test2
union all
select * from test3
go
alter table test1
add constraint pk_test1a primary key (id,date) 
alter table test2
add constraint pk_test2a primary key (id,date) 
alter table test3
add constraint pk_test3a primary key (id,date) 
go
alter table test1
add constraint ck_test1 check (crdm.fnGetPartitionNumberForDate(Date) = 1) 
alter table test2
add constraint ck_test2 check (crdm.fnGetPartitionNumberForDate(Date) = 2) 
alter table test3
add constraint ck_test3 check (crdm.fnGetPartitionNumberForDate(Date) = 3) 
go
insert into vwtest
select 2, current_timestamp, 1
go

Can anyone explain why this is?

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 11:02:24

Then you must make the function part of the primary key too...

add constraint pk_test3a primary key (id,crdm.fnGetPartitionNumberForDate(Date))

E 12°55'05.63"
N 56°04'39.26"

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-18 : 11:07:59

I've added the function to the pk:

alter table test1
add constraint pk_test1a primary key (id,crdm.fnGetPartitionNumberForDate(Date))
alter table test2
add constraint pk_test2a primary key (id,crdm.fnGetPartitionNumberForDate(Date)) 
alter table test3
add constraint pk_test3a primary key (id,crdm.fnGetPartitionNumberForDate(Date))

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 11:09:27

Well, as you can see this is not possible.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 11:14:01

The reason is that crdm.fnGetPartitionNumberForDate(Date) function could return NULL value.
And the number column is defined as NOT NULL.

E 12°55'05.63"
N 56°04'39.26"

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-18 : 11:20:41

I seeeeee. That makes sense, as its part of the PK. Ok, I shall see if there is an alternatively method to achieve my goal. Thanks for your help on this.

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources