Aligning 2 charts

I have a report with 2 charts as subreports. The X axis is time and
the charts show different data over the same time period.
I would like to position the charts so that corresponding X axis
values are aligned vertically. This seems to work if the Y axis scales
are the same ie. the labels are the same length, but if the scales are
different the charts don't quite align. The chart types might also be
causing problems i.e. Area vs. Line - it looks like they may be drawn
with different margins as well as different values on the Y scale.
Essentially, I need to get the left hand edges of the charts to line
up.
Is there a way to align these charts the way I want?
--
Andrew RowleyOn Aug 21, 8:50 pm, Andrew Rowley <aj...@.newsgroup.nospam> wrote:
> I have a report with 2 charts as subreports. The X axis is time and
> the charts show different data over the same time period.
> I would like to position the charts so that corresponding X axis
> values are aligned vertically. This seems to work if the Y axis scales
> are the same ie. the labels are the same length, but if the scales are
> different the charts don't quite align. The chart types might also be
> causing problems i.e. Area vs. Line - it looks like they may be drawn
> with different margins as well as different values on the Y scale.
> Essentially, I need to get the left hand edges of the charts to line
> up.
> Is there a way to align these charts the way I want?
> --
> Andrew Rowley
I'm not sure if this will help, but you might try putting the 2 charts
in the same subreport or in the main report and/or inside a rectangle
control. Normally, subreports are a little difficult to align. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You could also try setting the scale (interval) of the y axis yourself.
"EMartinez" wrote:
> On Aug 21, 8:50 pm, Andrew Rowley <aj...@.newsgroup.nospam> wrote:
> > I have a report with 2 charts as subreports. The X axis is time and
> > the charts show different data over the same time period.
> >
> > I would like to position the charts so that corresponding X axis
> > values are aligned vertically. This seems to work if the Y axis scales
> > are the same ie. the labels are the same length, but if the scales are
> > different the charts don't quite align. The chart types might also be
> > causing problems i.e. Area vs. Line - it looks like they may be drawn
> > with different margins as well as different values on the Y scale.
> >
> > Essentially, I need to get the left hand edges of the charts to line
> > up.
> >
> > Is there a way to align these charts the way I want?
> > --
> > Andrew Rowley
>
> I'm not sure if this will help, but you might try putting the 2 charts
> in the same subreport or in the main report and/or inside a rectangle
> control. Normally, subreports are a little difficult to align. Hope
> this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||David <David@.discussions.microsoft.com> wrote:
>You could also try setting the scale (interval) of the y axis yourself.
The problem is the scales need to be different, e.g. one is 0-100 and
the other is 0-5.
--
Andrew Rowley|||Hello Andrew,
You need to get the same font size for your X Axis label of both Charts.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Alias or Group SSAS Dimension at Query time.

In an MDX Query i am trying to alias (or group ) the returned dimension as shown below but i am getting the wrong result.I believe the issue is in the case statement logic.

Is there a way to alias (or group dynamically) dimension without creating a named column in DSV?

Any help will be appreciated.

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

SET [GroupedRatings] AS

CASE

WHEN [Curve Family].[SP Rating].&[AA-] THEN [Curve Family].[SP Rating].&[AA]

WHEN [Curve Family].[SP Rating].&[AA+] THEN [Curve Family].[SP Rating].&[AA]

WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]

WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]

WHEN [Curve Family].[SP Rating].&[BB-] THEN [Curve Family].[SP Rating].&[BB]

WHEN [Curve Family].[SP Rating].&[BB+] THEN [Curve Family].[SP Rating].&[BB]

WHEN [Curve Family].[SP Rating].&[BBB+] THEN [Curve Family].[SP Rating].&[BBB]

ELSE NULL

END

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

This is where the similarities between MDX and SQL can be confusing. What you really want to do is to create some calculated members to do your grouping and then create a set of these members.

eg.

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

MEMBER [Curve Family].[SP Rating].&[AA] AS Aggregate({[Curve Family].[SP Rating].&[AA-],[Curve Family].[SP Rating].&[AA+]})

MEMBER [Curve Family].[SP Rating].&[AAA] AS Aggregate({[Curve Family].[SP Rating].&[AAA-],[Curve Family].[SP Rating].&[AAA+]}

MEMBER [Curve Family].[SP Rating].&[BB] AS Aggregate({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]})

MEMBER [Curve Family].[SP Rating].&[BBB] AS Aggregate({[Curve Family].[SP Rating].&[BBB+]})

SET [GroupedRatings] AS

{[Curve Family].[SP Rating].&[AA]
,[Curve Family].[SP Rating].&[AAA]
,[Curve Family].[SP Rating].&[BB]
,[Curve Family].[SP Rating].&[BBB]}

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

|||

The case statement won't create new members dynamically, which it looks like you're trying to do. You could declare each member explicitly, like:

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

Member [Curve Family].[SP Rating].[AA] as

Sum({[Curve Family].[SP Rating].&[AA-], [Curve Family].[SP Rating].&[AA+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[AAA] as

Sum({[Curve Family].[SP Rating].&[AAA-], [Curve Family].[SP Rating].&[AAA+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[BB] as

Sum({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[BBB] as

Sum({[Curve Family].[SP Rating].&[BBB-], [Curve Family].[SP Rating].&[BBB+]}),

SOLVE_ORDER = 10

SET [GroupedRatings] AS

{[Curve Family].[SP Rating].[AA], [Curve Family].[SP Rating].[AAA],

[Curve Family].[SP Rating].[BB], [Curve Family].[SP Rating].[BBB]}

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

|||

Thanks Darren for pointing me in the right direction.I changed the code to the sample below to make it work properly.

WITH

MEMBER [Curve Family].[SP Rating].[AA] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[AA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AA+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[AAA] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[AAA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AAA+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[BB] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[BB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BB+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[BBB]AS

Aggregate({FILTER([Curve Family].[SP Rating].&[BBB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BBB+],([Measures].[Risk Value])<0)})

SET [GroupedRatings] AS

{

[Curve Family].[SP Rating].[AA]

,[Curve Family].[SP Rating].[AAA]

,[Curve Family].[SP Rating].[BB]

,[Curve Family].[SP Rating].[BBB]

}

SELECT

NON EMPTY { [Measures].[Risk Value]} ON COLUMNS,

NON EMPTY {([GroupedRatings]*[Vdim Book].[Desk].[Desk].Members)} ON ROWS

FROM

[DM]

Aggregation Problem with Hours and Minutes in Time Dimension

For the first time, I have created a time dimension that includes hours and minutes, in addition to the Year, Quarter, Month, and Date that I am familiar with.

I want to display the average of my measures at whatever level of Time the user chooses. I thus chose "AverageOfChildren" as the aggregation method for these measures.

Unfortunately, SSAS aggregates these measures by Sum over Hours and Minutes, and then by AverageOfChildren over Date, Month, Quarter, and Year.

I am confused.

How can I force it to perform an "AverageOfChildren" at all levels ?

First, have a look at this document(http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx) and think about your design a second time(on page 34-35 in the word version)

Next. This is only a guess but how are your attribute relations between minutes, hours, date,quarter and year defined, if they are included in the same user hierarchy?

HTH

Thomas Ivarsson

aggregate question

In the below structure, if I wanted to get the Id of the comment for
each Generic record having the latest comment time, how would I do that
not using a subquery?

Table: Generic
Id
Description

Table: Comment
Id
GenericId
CommentTime

Currently I have something like the following:

Select
Generic.Id, Max(Comment.CommentTime) /*,Comment.Id for max comment
time comment record*/
From
Generic
INNER JOIN Comment ON Generic.Id = Comment.GenericId
Group By
Generic.Id

To get it, I could do a sub query, using the above query as its source
and joining on the max comment time, but I was wondering if there was a
way to do it without a sub query. Keep in mind that I am looking for a
set of Generic records and not looking for only a single record (so
select top top 1 with order by won't work)You need to take what you have and use it as a subquery as you've
suggested. Other than the subquery, there's no way in SQL to say "give
me the id column from the right table where some other column in the
right table has it's max value".

The reason you can't do that is simple: what id would you get back
from the right table if the maximum value occured in more than one row?|||On 3 Nov 2005 08:41:10 -0800, pb648174 wrote:

>In the below structure, if I wanted to get the Id of the comment for
>each Generic record having the latest comment time, how would I do that
>not using a subquery?
>Table: Generic
>Id
>Description
>Table: Comment
>Id
>GenericId
>CommentTime
>Currently I have something like the following:
>Select
> Generic.Id, Max(Comment.CommentTime) /*,Comment.Id for max comment
>time comment record*/
>From
> Generic
> INNER JOIN Comment ON Generic.Id = Comment.GenericId
>Group By
> Generic.Id
>To get it, I could do a sub query, using the above query as its source
>and joining on the max comment time, but I was wondering if there was a
>way to do it without a sub query. Keep in mind that I am looking for a
>set of Generic records and not looking for only a single record (so
>select top top 1 with order by won't work)

Hi pb648174,

You can do this in two ways.

1. Using a correlated subquery (probably the solution you already had in
mind, since you write: "if there was a way to do it without a sub
query", but I'll give it anyway)

SELECT g.Id, c.CommentTime, c.Id
FROM Generic AS g
INNER JOIN Comment AS c
ON c.GenericId = g.Id
WHERE c.CommentTime = (SELECT MAX(c2.CommentTime)
FROM Comment AS c2
WHERE c2.GenericId = c.GenericId)

2. Using a derived table. This is a subquery as well, but it's not
correlated, and it's used in the FROM clause, in place of a table or
view name:

SELECT g.Id, c.CommentTime, c.Id
FROM Generic AS g
INNER JOIN (SELECT GenericId, MAX(CommentTime) AS MaxCommentTime
FROM Comment
GROUP BY GenericId) AS c2
ON c2.GenericId = g.Id
INNER JOIN Comment AS c
ON c.GenericId = g.Id
AND c.CommentTime = c2.MaxCommentTime

(Note: both queries are untested - see www.aspfaq.com/5006 if you prefer
a tested reply)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you had done this right and realized tht ther are
no magical, universal "id' things in RDBMS, would the schema look like
this?

CREATE TABLE Generic
(generic_id INTEGER NOT NULL PRIMARY KEY,
description VARCHAR(30) NOT NULL,
..);

CREATE TABLE Comments
(generic_id INTEGER NOT NULL
REFERENCES Generic (generic_id)
comment_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
-- where is the comment??
PRIMARY KEY (generic_id, comment_time));

>> but I was wondering if there was a way to do it without a subquery. <<

No, not unless you move comment time into Gerneric.|||Thanks, Celko. Helpful and on-topic as always.

Aggregate Function to Concatenate Columns Data into a single Row

Hi all,

I have a scenario which I am not able to figure out how to do it better for quite some time.

Assume I have a few rows of data :

RunningID Date WOid

1234 1/23/2007 23

1236 1/24/2007 23

1239 1/2/2007 24

1222 1/4/2007 23

1321 2/4/2007 22

My objective is to merge all RunningID into a single cell when WOid is the same (this will most probably use a "group by" to get the different WOid out). Maybe some aggregate function that can do it as:

select ReturnConca(RunningID, "#") as RunningID_str, max(Date) as MaxDate, max(WOid) as WO from tableXXX

group by WOid

Results:

RunningID_str MaxDate WO

1234#1236#1222 1/24/2007 23

1239 1/2/2007 24

1321 2/4/2007 22

Any advise would be much appreciated.

If you use SQL server 2005,

Code Snippet

Create Table #data (

[RunningID] int ,

[Date] datetime ,

[WOid] int

);

Insert Into #data Values('1234','1/23/2007','23');

Insert Into #data Values('1236','1/24/2007','23');

Insert Into #data Values('1239','1/2/2007','24');

Insert Into #data Values('1222','1/4/2007','23');

Insert Into #data Values('1321','2/4/2007','22');

Select

[RunningIDs],

Max([Date]) [Date],

[WoId]

From

(

select

Substring((Select '#' + cast([RunningID] as varchar) as [text()] from #data sub

where sub.[Woid] = main.[Woid] for xml path('')

),2,8000) as [RunningIDs],

[Date],

[WoId]

from

#data main

) as data

Group By

[RunningIDs],[WoId]

Order By

[RunningIDs]

|||

Thanks for your code.

However, this must be done on the fly and there are many similar rows in a single selection and how do we encapsulate the above code into a function. If not, how do we insert the dynamic data into the temp table on the fly?

|||

Post your query.. I didn't understand the dynamic data / on the fly.. You can achive this without function.|||

Here is my query:

SELECT P.running as PRunning, P.[date], P.refWO, P.addWO,
W.id,W.running as WRunning,W.[date] as WO_Date
FROM WorkOrder W
RIGHT JOIN PurchaseOrder P ON (P.refWO=W.id)
where not W.id is null
UNION ALL

SELECT P.running as PRunning, P.[date], P.refWO, P.addWO,
W.id,W.running as WRunning, W.[date] as WO_Date
FROM WorkOrder W
RIGHT JOIN PurchaseOrder P ON (P.addWO like ('%#' + cast(W.id as varchar) + ':%'))
where not W.id is null
order by W.id, W.[date], W.running

The PRunning and P.date will have a few rows to one P.refWO. his might be occuring a few times over the result.

|||

May be something like this,

Code Snippet

SELECT P.running as PRunning, P.[date], P.refWO, P.addWO,

W.id,W.running as WRunning,W.[date] as WO_Date into #temp

FROM WorkOrder W

RIGHT JOIN PurchaseOrder P ON (P.refWO=W.id)

where not W.id is null

UNION ALL

SELECT P.running as PRunning, P.[date], P.refWO, P.addWO,

W.id,W.running as WRunning, W.[date] as WO_Date

FROM WorkOrder W

RIGHT JOIN PurchaseOrder P ON (P.addWO like ('%#' + cast(W.id as varchar) + ':%'))

where not W.id is null

order byW.id, W.[date], W.running

Select

PRunning

,Max([date])

,refWO

,addWO

,WRunning

,max(WO_Date)

From

(

Select

(Select '#' + PRunning as [text()] from #temp sub where sub.id = main.id For xml path('')) as PRunning

,[date]

,(Select '#' + refWO as [text()] from #temp sub where sub.id = main.id For xml path('')) as refWO

,(Select '#' + addWO as [text()] from #temp sub where sub.id = main.id For xml path('')) as addWO

,(Select '#' + WRunning as [text()] from #temp sub where sub.id = main.id For xml path('')) as WRunning

,WO_Date

from

#temp

) as Data

Group By

PRunning

,refWO

,addWO

,WRunning

|||

Hi I am using SQL 2000 and I suppose i need some minor tweating to the code. When i run the code, It reported invalid for "For XML Path('')'. So i took those out.

Another issue is where does the alias "main" referring to?

Agents failed to load

Using SQL2005 EE SP1
I'm trying to set up a simple transactional replication with initial
snapshot (1st time on this server). I create the publisher and it says it's
successful, however the error log shows:
Job cannot run because LogReader subsystem failed to load. Job has been
suspended.
Job cannot run because Snapshot subsystem failed to load. Job has been
suspended.
Did I not install the proper components during the SQL2005 install?
Thanks
Ron
Is it possible that you have Express Edition on this box and this is the one
you are using? This is installed with Visual Studion and sometimes people
have got the editions confused. Pls can you run select SERVERPROPERTY (
'edition') to double check, and just in case you are connected to the EE in
SSMS, have a look at your list of services to see what services SQL server
appears under. If this is not the case then I'll see what other things we
could look for.
HTH,
Paul Ibison
|||Paul, it's definitely SQLServer2005 "Enterprise Edition" SP1.
"Paul Ibison" wrote:

> Is it possible that you have Express Edition on this box and this is the one
> you are using? This is installed with Visual Studion and sometimes people
> have got the editions confused. Pls can you run select SERVERPROPERTY (
> 'edition') to double check, and just in case you are connected to the EE in
> SSMS, have a look at your list of services to see what services SQL server
> appears under. If this is not the case then I'll see what other things we
> could look for.
> HTH,
> Paul Ibison
>
|||I'm all set now, I've got it working. Turns out there were earlier errors
from a couple of months ago that stated: Subsystem "LogReader" could not be
loaded
as well as the subsystems.
I googled that and the fix was to update the msdb.dbo.syssubsystems table to
point to the correct DLL and executable.
Thanks anyway.
"Ron" wrote:
[vbcol=seagreen]
> Paul, it's definitely SQLServer2005 "Enterprise Edition" SP1.
> "Paul Ibison" wrote:
|||Hi Ron - thanks for the update. I've found a potential cause of this issue
here: http://support.microsoft.com/kb/914171.
I'll add this to my website.
Cheers,
Paul Ibison

Agent Service Fails to Start

For some time now, I've been unable to start SQL Server
Agent service. I can run agent in cmd window via:
sqlagent.exe -c -v. The error on failed startup is
something like, sqlagent failed to start in a timely
manner.Have you tried starting the agent from Service Manager? If so, what error
message do you get? What user account does the Agent run with? Does the
account belong to a Win2K domain or the local machine?
The answers to these questions will help to diagnose the problem.
"Clark" <anonymous@.discussions.microsoft.com> wrote in message
news:4a2e01c3ab7d$eebde570$a601280a@.phx.gbl...
> For some time now, I've been unable to start SQL Server
> Agent service. I can run agent in cmd window via:
> sqlagent.exe -c -v. The error on failed startup is
> something like, sqlagent failed to start in a timely
> manner.|||Oh yes, I've tried that.
1. With Service Manager, it fails to start with something
like, sqlagent failed to start in a timely manner.
2. Agent uses the sama account as SQL Server service.
I plan to dink with this on Monday morning.
Question: Can the time to load a service be extended,
for example, to load a service on a very busy server?
>--Original Message--
>Have you tried starting the agent from Service Manager?
If so, what error
>message do you get? What user account does the Agent run
with? Does the
>account belong to a Win2K domain or the local machine?
>The answers to these questions will help to diagnose the
problem.
>
>"Clark" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4a2e01c3ab7d$eebde570$a601280a@.phx.gbl...
>> For some time now, I've been unable to start SQL Server
>> Agent service. I can run agent in cmd window via:
>> sqlagent.exe -c -v. The error on failed startup is
>> something like, sqlagent failed to start in a timely
>> manner.
>
>.
>|||I am having a similar problem too. I am trying to start the service from
Enterprise Manager and get the following error:
A error 5 - (Access is denied) occured while performing the service opeation
on the SQL ServerAgent service
I tried changeing the account info to the SA login and password but to no
avail. Is it possibel that the service is needs the machine domain Admin
account info?
John.
"Clark" <anonymous@.discussions.microsoft.com> wrote in message
news:505301c3ac4e$be0c0070$a601280a@.phx.gbl...
> Oh yes, I've tried that.
> 1. With Service Manager, it fails to start with something
> like, sqlagent failed to start in a timely manner.
> 2. Agent uses the sama account as SQL Server service.
> I plan to dink with this on Monday morning.
> Question: Can the time to load a service be extended,
> for example, to load a service on a very busy server?
>
> >--Original Message--
> >Have you tried starting the agent from Service Manager?
> If so, what error
> >message do you get? What user account does the Agent run
> with? Does the
> >account belong to a Win2K domain or the local machine?
> >
> >The answers to these questions will help to diagnose the
> problem.
> >
> >
> >"Clark" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:4a2e01c3ab7d$eebde570$a601280a@.phx.gbl...
> >> For some time now, I've been unable to start SQL Server
> >> Agent service. I can run agent in cmd window via:
> >> sqlagent.exe -c -v. The error on failed startup is
> >> something like, sqlagent failed to start in a timely
> >> manner.
> >
> >
> >.
> >|||Hi John,
Sa login is for SQL server authentication, which is different from the account used for SQL server agent service. To run sql server agent, one must typically have a domain user account (as it is used for sql mail, replication, backup etc). Also this domain name should have administrative privs on the machine where sql server instance is running.
Sometimes I guess rebuilding registry can solve the problem. Use "regrebld" in the cmd line prompt. I haven't used this utility myself, so inquire with others before using it
Thank
GYK|||Thanks for you help.
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:CFD08193-68CB-48CA-A931-04BBA0EC21A2@.microsoft.com...
> Hi John,
> Sa login is for SQL server authentication, which is different from the
account used for SQL server agent service. To run sql server agent, one must
typically have a domain user account (as it is used for sql mail,
replication, backup etc). Also this domain name should have administrative
privs on the machine where sql server instance is running.
> Sometimes I guess rebuilding registry can solve the problem. Use
"regrebld" in the cmd line prompt. I haven't used this utility myself, so
inquire with others before using it.
> Thanks
> GYK

Ageing data using Sql Server 2000

Hi there,

I've only recently started a project using sql server 2000 for the
first time. One of the considerations we have to take is that we need
to continuously age/purge data from a couple of large tables (100
million rows+) - to keep the tablesize growth to a minimum. Coming
from an Oracle background, I've used partitions to help manage this
before.

Now with Sql Server 2000, I'm wondering what the best/recommended
approaches are for ageing data - I've been struggling to find out
enough inforamtion on the msdn site, so I'm hoping some gurus out
there can help me here.

Thanks![posted and mailed, please reply in news]

elpico (kevinmartinwalsh@.yahoo.co.uk) writes:
> I've only recently started a project using sql server 2000 for the
> first time. One of the considerations we have to take is that we need
> to continuously age/purge data from a couple of large tables (100
> million rows+) - to keep the tablesize growth to a minimum. Coming
> from an Oracle background, I've used partitions to help manage this
> before.
> Now with Sql Server 2000, I'm wondering what the best/recommended
> approaches are for ageing data - I've been struggling to find out
> enough inforamtion on the msdn site, so I'm hoping some gurus out
> there can help me here.

Partitioned views may be your best bet. You would have one table each
for each chunk you want to prune at a time. (You would have to know the
size of the chunk before you start inserting the data, obviously.) Each
table would have a check constraint on the primary key which constrains
the table to its partition, and then you combine all tables into a view
by means of UNION ALL.

Provided that you follow certain rules you can insert directly through
the view. You can also use a INSTEAD OF triggers that divert the data
into the approriate table.

Each time you need to add a new partition, you would need to alter
the view, and possibly also alter the constraint for the primary key
for the top-most table. But you could create partitions long before you
actually need them, to make this a swift operation.

I've only given you a brief introduction. Use the index in Books Online to
find "partitioned views" to get more information.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp