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)

Partitioned Views

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-12 : 10:50:40

We are trying to create partitioning without using Enterprise version of sql server 2005. We have split the tables according to the date:

Header_1 (getdate())
Header_2 (dateadd(day,-1,getdate())
Header_3 (dateadd(day,-2,getdate())
etc..............

We want to use a view to union all the tables into a single view:

create view vwGetHeader
as
select * from Header_1
union all
select * from Header_2
union all
select * from Header_3

When trying to insert into a table through the view, we get the error:

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

This makes sense, as how would the optimiser know which table to insert into. My question is can we create partitioned views in standard version of sql server?

If not, then how can we mimic the concept?

Thanks

Hearty head pats

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-12 : 11:30:52

Why not Table Partition If You have EnterPrise Edition?

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-12 : 11:33:19

We don't want to use enterprise edition (to save the pennies), hence trying to create our own 'partioned' implementation.

If we can get it working, and its still performant, then the company can host systems internally and save a lot of money (then perhaps give the extra cash to its employees

Hearty head pats

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-12 : 11:38:13

Sorry

. I have to get coffee now.

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-12 : 11:41:57

No worries, needs must!!! I've already had 3 strong coffees this afternoon to try and keep me awake (work christmas party yesterday and am seriously struggling... though only less than an hour to go, yay)

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources