October 11, 2007 at 3:08 am
I have successfull implemented this in our production environment-- Create production tables
CREATE TABLE tOrderCurrent
(
ID INT,
Season VARCHAR(5),
IsCurrentSeason BIT,
PRIMARY KEY (ID, IsCurrentSeason),
CHECK(IsCurrentSeason = 1)
)
CREATE TABLE tOrderHistory
(
ID INT,
Season VARCHAR(5),
IsCurrentSeason BIT,
PRIMARY KEY (ID, IsCurrentSeason),
CHECK(IsCurrentSeason = 0)
)
GO
-- Create production view
CREATE VIEW tOrder
AS
SELECT ID,
Season,
IsCurrentSeason
FROM tOrderCurrent
UNION ALL
SELECT ID,
Season,
IsCurrentSeason
FROM tOrderHistory
GO
-- Insert an order
INSERT tOrder
(
ID,
Season,
IsCurrentSeason
)
SELECT 99,
'20079',
1
GO
-- Select and view storage for Order
SELECT 'tOrder',
ID,
Season,
IsCurrentSeason
FROM tOrder
SELECT 'tOrderCurrent',
ID,
Season,
IsCurrentSeason
FROM tOrderCurrent
UNION ALL
SELECT 'tOrderHistory',
ID,
Season,
IsCurrentSeason
FROM tOrderHistory
GO
-- Update Order and set as older Order
UPDATE tOrder
SET IsCurrentSeason = 0
WHERE Season IN ('20078', '20079')
GO
-- Select and view storage for Order
SELECT 'tOrder',
ID,
Season,
IsCurrentSeason
FROM tOrder
SELECT 'tOrderCurrent',
ID,
Season,
IsCurrentSeason
FROM tOrderCurrent
UNION ALL
SELECT 'tOrderHistory',
ID,
Season,
IsCurrentSeason
FROM tOrderHistory
GO
-- Clean up view
DROP VIEW tOrder
GO
-- Clean up tables
DROP TABLE tOrderCurrent,
tOrderHistory
GO
Now I would like to extend the partitioning logic but I have failed this far.
What I would like to have is more than two underlying tables.
1) tOrderTypeA
2) tOrderTypeB
3) tOrderTypeC
4) tOrderCancelled
Something similar to this:
CREATE TABLE tOrderTypeA
(
OrderID INT,
OrderType VARCHAR(2),
Cancelled BIT
)
1) tOrderTypeA - CHECK ( OrderType = 'A' AND Cancelled = 0)
2) tOrderTypeB - CHECK ( OrderType = 'B' AND Cancelled = 0)
3) tOrderTypeC - CHECK ( OrderType = 'C' AND Cancelled = 0)
4) tOrderCancelled - CHECK ( Cancelled = 1)
Can I do this is SQL Server 2000, or do I have to wait for SQL Server 2005?
N 56°04'39.16"
E 12°55'05.25"
October 11, 2007 at 3:25 am
Hi
Could tell the problems you faced when you ,emtiom you failed to do this.
In SQL 2000 you can use partioned views to do partioning.
In SQL 2005 you can partion a single table into multiple partions.
"Keep Trying"
October 11, 2007 at 4:10 am
Sure. The error I get is "UNION ALL view 'Test.dbo.tOrder' is not updatable because a partitioning column was not found.".
I know in SQL Server 2005, you can even put the different partitions on different filegroups, but this has to be solved in SQL Server 2000 for the moment.
I even tried
3.5) tOrderTypeOther - CHECK ( OrderType <> 'A' AND OrderType <> 'B' OrderType <> 'C' AND Cancelled = 0)
to no vail.
N 56°04'39.16"
E 12°55'05.25"
October 11, 2007 at 4:34 am
If you want to use partioned views you need to have a partioning column which identifies to which undelying table a record belongs. You when have to define Instead of Triggers on the view to manage any DML commands.
See here http://technet.microsoft.com/en-us/library/ms190019.aspx
The problem is that handling updates can become quite complex when the pertioning column gets updated, because when you might end up with an insert and delete on two different tables.
[font="Verdana"]Markus Bohse[/font]
October 11, 2007 at 4:41 am
Are instead-of-triggers available in SQL Server 2000?
N 56°04'39.16"
E 12°55'05.25"
October 11, 2007 at 4:52 am
you do have instead of triggers in sql 2000.
"Keep Trying"
October 11, 2007 at 7:18 am
Yes, instead of triggers are implemented in SQL 2000.
I didn't realize that the link I posted was for SQL 2005 but as far as I know, everything in it also applies to 2000.
Markus
[font="Verdana"]Markus Bohse[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply