468,321 Members | 1,820 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,321 developers. It's quick & easy.

Maintaining partitioned views

Hello,

I have a large set of data that I have set up as a partitioned view.
The view is partitioned by a datetime column and the individual tables
each represent one month's worth of data. I need to keep at least two
year's worth of data at all times, but after two years I can archive
the data. A sample of the code used is below. It is simplified for
space reasons.

My question is, how do other people maintain the database in this type
of scenario? I could create all of the tables necessary for the next
year and then go through that at the end of each year (archive tables
over two years, add new tables, and change the view), but I was also
thinking that I might be able to write a stored procedure that runs
once a month and does all three of those tasks automatically. It seems
like a lot of dynamic SQL code though for something like that.
Alternatively, I could write VB code to handle it in a DTS package.
So, my question again is, how are others doing it? Any suggestions?

Thanks!
-Tom.

CREATE TABLE [dbo].[Station_Events_200401] (
[event_time] [datetime] NOT NULL ,
[another_column] [char] (8) NOT NULL )
GO

CREATE TABLE [dbo].[Station_Events_200402] (
[event_time] [datetime] NOT NULL ,
[another_column] [char] (8) NOT NULL )
GO

CREATE VIEW Station_Events
AS
SELECT event_time,
another_column
FROM Station_Events_200401
UNION ALL
SELECT event_time,
another_column
FROM Station_Events_200402
GO
Jul 20 '05 #1
3 2231

"Thomas R. Hummel" <to********@hotmail.com> wrote in message
news:a2**************************@posting.google.c om...
Hello,

I have a large set of data that I have set up as a partitioned view.
The view is partitioned by a datetime column and the individual tables
each represent one month's worth of data. I need to keep at least two
year's worth of data at all times, but after two years I can archive
the data. A sample of the code used is below. It is simplified for
space reasons.

My question is, how do other people maintain the database in this type
of scenario? I could create all of the tables necessary for the next
year and then go through that at the end of each year (archive tables
over two years, add new tables, and change the view), but I was also
thinking that I might be able to write a stored procedure that runs
once a month and does all three of those tasks automatically. It seems
like a lot of dynamic SQL code though for something like that.
Alternatively, I could write VB code to handle it in a DTS package.
So, my question again is, how are others doing it? Any suggestions?

Thanks!
-Tom.

CREATE TABLE [dbo].[Station_Events_200401] (
[event_time] [datetime] NOT NULL ,
[another_column] [char] (8) NOT NULL )
GO

CREATE TABLE [dbo].[Station_Events_200402] (
[event_time] [datetime] NOT NULL ,
[another_column] [char] (8) NOT NULL )
GO

CREATE VIEW Station_Events
AS
SELECT event_time,
another_column
FROM Station_Events_200401
UNION ALL
SELECT event_time,
another_column
FROM Station_Events_200402
GO


I would use a DTS package - the first steps to create the objects
dynamically, then later steps to call stored procedures to actually move the
data. But that's more or less a personal preference, and you could do
everything in a stored procedure as well. Assuming there are no major
reasons to use one solution or another, it probably depends on what is most
transparent and easiest to maintain for you and your organization.

Simon
Jul 20 '05 #2
Without dynamic SQL the view will be a problem, but I think you can easily
handle the archiving.

Within a stored procedure you can create a new copy of your table "template"
like this:

CREATE TABLE [dbo].[Station_Events_Template] ( [event_time] [datetime] NOT
NULL , [another_column] [char] (8) NOT NULL )

Then insert the rows into this table for the appropriate time period.

INSERT INTO Station_Events_Template
SELECT * FROM [your "active" table] WHERE event_time BETWEEN @thisdate AND
@thatdate

After the insert you can rename the template using the sp_rename procedure.
You will need a variable for the new table name first:
DECLARE @NewName varchar(25)

SELECT @NewName = 'Station_Events_' + CONVERT(char(6), @MonthEndDate,
112) -- returns date in format "YYYYMM"

EXEC sp_rename 'Station_Events_Template', @NewName

Hope this is helpful.

"Thomas R. Hummel" <to********@hotmail.com> wrote in message
news:a2**************************@posting.google.c om...
Hello,

I have a large set of data that I have set up as a partitioned view.
The view is partitioned by a datetime column and the individual tables
each represent one month's worth of data. I need to keep at least two
year's worth of data at all times, but after two years I can archive
the data. A sample of the code used is below. It is simplified for
space reasons.

My question is, how do other people maintain the database in this type
of scenario? I could create all of the tables necessary for the next
year and then go through that at the end of each year (archive tables
over two years, add new tables, and change the view), but I was also
thinking that I might be able to write a stored procedure that runs
once a month and does all three of those tasks automatically. It seems
like a lot of dynamic SQL code though for something like that.
Alternatively, I could write VB code to handle it in a DTS package.
So, my question again is, how are others doing it? Any suggestions?

Thanks!
-Tom.

CREATE TABLE [dbo].[Station_Events_200401] (
[event_time] [datetime] NOT NULL ,
[another_column] [char] (8) NOT NULL )
GO

CREATE TABLE [dbo].[Station_Events_200402] (
[event_time] [datetime] NOT NULL ,
[another_column] [char] (8) NOT NULL )
GO

CREATE VIEW Station_Events
AS
SELECT event_time,
another_column
FROM Station_Events_200401
UNION ALL
SELECT event_time,
another_column
FROM Station_Events_200402
GO

Jul 20 '05 #3
Thank you both for your suggestions! I always seem to overlook using
sp_rename as part of my bag of tricks. I'll give that some
consideration, but right now I'm leaning towards a DTS package using
VB to generate the SQL code.

Thanks,
-Tom.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by ~john | last post: by
11 posts views Thread by steve.keanie | last post: by
10 posts views Thread by shsandeep | last post: by
reply views Thread by Damir | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by howard w | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.