473,698 Members | 2,747 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 2425

"Thomas R. Hummel" <to********@hot mail.com> wrote in message
news:a2******** *************** ***@posting.goo gle.com...
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********@hot mail.com> wrote in message
news:a2******** *************** ***@posting.goo gle.com...
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
3231
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 based on a range of formula_id column. Test: Run this script to create the 5 partitioned tables and the partitioned view. Then
3
3804
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 month. Each month a stored procedure is scheduled that creates the new month's table, and alters the view to include it. Again... working like a charm for quite some time. This past weekend I moved some of the first tables onto a new file...
4
3498
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 view can hold? If this is true, does anybody have any suggestions or ideas to work around this max limit? TIA!
6
6905
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, segmented and partitioned) tablespaces. What is segmented tablespace ? Is it possible to span the the tables within multiple containers (but in some ordered manner) with no db partitions ?
3
2630
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 and user B viewing the same screen and user A submits his page that writes to the database. User B is now viewing a screen with invalid data. How do I prevent this scenario from happening with PHP? I thought about using a lock flag in a...
11
1885
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 spread over 10 partitions. Approximately 35% of the data is "inactive" (easily identifiable as such) and of limited (but some) use. We require 2 non-partitioning indexes on the data. The DBA group would like us to put the "inactive" data into a...
10
4289
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 application looks for existing rows in the table...if they already exist then it updates otherwise inserts them. The table is pretty large, around 6.5 million rows.
0
1668
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. Not that I care too much now about it :-) But what will happen when once upon a time in the future I execute the "attach partition" command, and so add another partition to the existing (partitioned) table. Will the indexes then be automatically...
2
13605
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 partitions and this is where I hit the wall the 2nd time. As per my study there are 3 types of indexed partitions: Global index (which is on an entire partitioned table) then there is Local Index (on just the partition of the table) and then there...
0
8683
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8611
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9031
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8876
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7741
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6531
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
3052
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2341
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2007
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.