473,216 Members | 1,508 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

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 2392

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: SM | last post by:
/* problem: Trying to get partitioned views to "prune" unneeded partitions from select statements against the partitioned view. There are 5 partitioned tables. Each with a check constraint...
3
by: Thomas R. Hummel | last post by:
I am using SQL Server 2000, SP3. I created an updatable partitioned view awhile ago and it has been running smoothly for some time. The partition is on a DATETIME column and it is partitioned by...
4
by: karthik | last post by:
I have a partitioned view sitting over several tables and I'm slowly approaching the 256 number. Can anybody confirm if there is such a limit for the maximum number of tables that a partitioned...
6
by: Smutny30 | last post by:
Hello, I consider partitioning a huge table. I am not sure wheather it is possioble only in partitioned databases. I found somewhere in docs ( http://tinyurl.com/4oara ) that there are (simple,...
3
by: ~john | last post by:
I'm working on a multi-user system that at times may result in 2 users accessing the same screen. These screens allow the users to both view and update data. The problem comes when you have user A...
11
by: steve.keanie | last post by:
Hi ... we're a mainframe V7 shop planning an imminent upgrade to V8. My application team is converting an IMS DB into a DB/2 table ... approximately 40GB of uncompressed (~20 GB compressed) data...
10
by: shsandeep | last post by:
The ETL application loaded around 3000 rows in 14 seconds in a Development database while it took 2 hours to load in a UAT database. UAT db is partitioned. Dev db is not partitioned. the...
0
by: Damir | last post by:
Hello all! I created a range-partitioned table, and noticed that indexes were created as "NOT PARTITIONED" (through db2look), even though I ran the index creating commands without this parameter....
2
by: eeriehunk | last post by:
Hi All, Is it possible to create a partitioned index on a table which is not partitioned? If so what is such a partition called and please explain? I have done some research on partitions and index...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.