Parameterized Expression: tables

Showing posts with label tables. Show all posts

Friday, March 30, 2012

particulat table in Filegroup

Dear Friends
I have a small database of 1 gb i have created some
Secondary data files for same. in the same database some
tables are extensively used for the Modification hence i
want to separte them form other table datafiles can i do
the same.
please suggest.
Best regards
NiitMaladWhat you can do is create multiple filegroups and assign a datafile to a
filegroup.
You can specify the create table statement with the filegroup it must be
created on
CREATE Table tbl_demo (COL1 INT)
on FILEGROUP
where filegroup is the logical name of the filegroup.
If you have an existing table and you want to place it on another filegroup
you can only create a new table with the ON filegroup keywords, insert from
the first table, delete the first table and rename the new table, what means
: there is no alter table statement to move a table to another filegroup.
You first need to create the filegroups before you create the datafiles and
assign them onto a filegroup
--
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"niitmalad" <niitmalad@.yahoo.co.in> wrote in message
news:006601c3dd18$6132f930$a101280a@.phx.gbl...
> Dear Friends
> I have a small database of 1 gb i have created some
> Secondary data files for same. in the same database some
> tables are extensively used for the Modification hence i
> want to separte them form other table datafiles can i do
> the same.
> please suggest.
> Best regards
> NiitMalad|||> If you have an existing table and you want to place it on another
filegroup
> you can only create a new table with the ON filegroup keywords
Or you could create a clustered index using the ON filegroup clause, and it
will move the data to the filegroup. Of course, if there's already a
clustered index, you'll have to drop it first. Which complicates things if
it's a referenced primary key.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Partial and Full Replication

Hi,
Is it possible to setup full replication of a database to run nightly
as well as partial replication of only a couple of tables on an hourly
basis?
Thanks,
Jason
Hello Jason,
There might be conflits because there are two replicaitons for the same
tables. You may consider set up a replicaiton of some tables to run nightly
and the rest tables to run on a hourly basis.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.
| From: jason.hill@.pearsoned.com.au
| Newsgroups: microsoft.public.sqlserver.replication
| Subject: Partial and Full Replication
| Date: 9 Aug 2005 20:25:54 -0700
| Organization: http://groups.google.com
| Lines: 10
| Message-ID: <1123644354.337678.325910@.g44g2000cwa.googlegroups .com>
| NNTP-Posting-Host: 203.143.94.10
| Mime-Version: 1.0
| Content-Type: text/plain; charset="iso-8859-1"
| X-Trace: posting.google.com 1123644360 22422 127.0.0.1 (10 Aug 2005
03:26:00 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Wed, 10 Aug 2005 03:26:00 +0000 (UTC)
| User-Agent: G2/0.2
| Complaints-To: groups-abuse@.google.com
| Injection-Info: g44g2000cwa.googlegroups.com; posting-host=203.143.94.10;
| posting-account=vUxf9g0AAAC_Tbj8TfB8dQv9VE03ynWf
| Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfee d00.sul.t-online.de!t-onli
ne.de!news.glorb.com!postnews.google.com!g44g2000c wa.googlegroups.com!not-fo
r-mail
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:15717
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| Hi,
|
| Is it possible to setup full replication of a database to run nightly
| as well as partial replication of only a couple of tables on an hourly
| basis?
|
| Thanks,
|
| Jason
|
|
|||Jason,
you'll need 2 publications for this - one containing the 2 tables and the
other publication containing the rest of the tables. This is often done to
separate lookup tables from transactional tables. However, if there are
PK-FK relationships across the publications, there can obviously be issues
arising.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the prompt replies. Sounds like 2 publications is the way
to go, splitting the tables out separately, which will work perfectly.
Cheers,
Jason

Wednesday, March 28, 2012

parsing varchar fields

InvoiceNumber@.VendorAcronym

There'd be a lot of vendors.

Additional issue: sometimes these values would be referred to in the
description field, and I'd need to distinguish them as referrals rather
than as original recorded instances of the values. For that, I imagined
either:

InvoiceNumber@.@.VendorAcronym

InvoiceNumber&VendorAcronym
InvoiceNumber//VendorAcronym

etc. -- something like that.

I'm just wondering if there's best practice for doing anything this stupid
(hey, I'm stuck with this as our only option just now; hopefully it's only
temporary). How to parse out whatever I end up implementing -- well, it
needs to be tractable.

Thoughts?

ScottScott,

Instead of changing the existing product's tables why not create an
additional table(s) to store these values and then write your queries using
joins.

HTH

Jerry
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539.cb0c208b60e605e1a298926bee12e52f@.t eranews...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi

It is not clear what you are trying to achieve, but creating a concanenated
field is probably not a good idea. Separate fields and a status will be alot
easier to maintain and understand.

John

"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539.cb0c208b60e605e1a298926bee12e52f@.t eranews...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi

It is not clear what you are trying to do, but creating a concatenated field
is probably not a good idea. Separate fields and a status will be far easier
to maintain and understand.

John

Jerry, John -- sorry for the lack of clarity.

We have a web app from a vendor, and in this particular case I don't want
to hack anything (I've done some hacks where practical, and will be doing
more, but there's some intractable stuff in view of upgrade management and
my available time).

Our people need to reference invoice numbers and vendors in a description
field, so I'm wanting to accommodate them until such time comes along that
the software supports something like that. But they're wanting to be able
to generate reports on the vendor and invoice numbers, so I'm wanting to do
this kludgey thing as elegantly as kludges can be. I figured if I specified
that they enter the two fields in some string format that was simple enough
for them this would be practical from their standpoint, and if I could
parse it it'd be practical from mine. The concatenation John's concerned
about is mostly as a way of avoiding separate ways of identifying the
"fields". That would be a bit onerous for the staff entering the
information, even if it did make parsing easier.

So imagine a lot of records with brief descriptions of invoices, with
vendor and invoice #s embedded in the descriptions. Oh, yeah, almost forgot
-- no chance of their being more than one of these per description (per
record).

Scott|||Hi

I am not sure why you think the users would want to enter some cryptic code
rather than exact values in two separate fields. That is more of a usability
with the UI, for instance if your parts were both numeric you may be slowing
your users down forcing them to use non-keypad characters that require
shifting. You should also look at the business processes, for instance if
you processed everything for one vendor together then retaining the vendor
information would remove the need to type it in again.

If you need to concatenate the values for display purposes then you can do
that in the code for your stored procedure or create a view.

John

"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127546922.53434faa9cedec41603cbc091e9f06a8@.t eranews...
> Jerry Spivey opined thusly on Sep 24:
>> Scott,
>>
>> Instead of changing the existing product's tables why not create an
>> additional table(s) to store these values and then write your queries
>> using
>> joins.
> Jerry, John -- sorry for the lack of clarity.
> We have a web app from a vendor, and in this particular case I don't want
> to hack anything (I've done some hacks where practical, and will be doing
> more, but there's some intractable stuff in view of upgrade management and
> my available time).
> Our people need to reference invoice numbers and vendors in a description
> field, so I'm wanting to accommodate them until such time comes along that
> the software supports something like that. But they're wanting to be able
> to generate reports on the vendor and invoice numbers, so I'm wanting to
> do
> this kludgey thing as elegantly as kludges can be. I figured if I
> specified
> that they enter the two fields in some string format that was simple
> enough
> for them this would be practical from their standpoint, and if I could
> parse it it'd be practical from mine. The concatenation John's concerned
> about is mostly as a way of avoiding separate ways of identifying the
> "fields". That would be a bit onerous for the staff entering the
> information, even if it did make parsing easier.
> So imagine a lot of records with brief descriptions of invoices, with
> vendor and invoice #s embedded in the descriptions. Oh, yeah, almost
> forgot
> -- no chance of their being more than one of these per description (per
> record).
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> I am not sure why you think the users would want to enter some cryptic code
> rather than exact values in two separate fields.

Developing more fields in this N-tier application whose upgrades are
controlled by a vendor whose dev I'm not privy to, isn't an option. If I
could do that, I would. I'm not posting to inquire about better
alternatives; I'm wanting to wring juice out of an unfortunate turnip.

> That is more of a usability
> with the UI, for instance if your parts were both numeric you may be slowing
> your users down forcing them to use non-keypad characters that require
> shifting. You should also look at the business processes, for instance if
> you processed everything for one vendor together then retaining the vendor
> information would remove the need to type it in again.
> If you need to concatenate the values for display purposes then you can do
> that in the code for your stored procedure or create a view.

Again, beyond my control. I've done a few necessary hacks of the software
(both the asp and some sprocs), but I can't expand the number of such edits
beyond reason. Next vendor upgrade, I'd be flailing about.

The users are more worried about being able to have reports on data they're
unfortunately left to enter in a free-form description field, type varchar.
My posting is to inquire whether a string formatting convention I'm
considering is the best I can do under that circumstance. I'd be glad of
any hints at how to most efficiently query the data as well.

Scott|||Hi

If you are not writing a new interface then you are probably better using a
fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
a fixed length then you don't need a separator, but it may help the users
differentiate the different parts. I would have expect that you can
determine the fact that an invoice is a referral by a different means.

John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127575738.f429cee989a6daeb985244c70f0bb497@.t eranews...
> John Bell opined thusly on Sep 24:
>> Hi
>>
>> I am not sure why you think the users would want to enter some cryptic
>> code
>> rather than exact values in two separate fields.
> Developing more fields in this N-tier application whose upgrades are
> controlled by a vendor whose dev I'm not privy to, isn't an option. If I
> could do that, I would. I'm not posting to inquire about better
> alternatives; I'm wanting to wring juice out of an unfortunate turnip.
>> That is more of a usability
>> with the UI, for instance if your parts were both numeric you may be
>> slowing
>> your users down forcing them to use non-keypad characters that require
>> shifting. You should also look at the business processes, for instance if
>> you processed everything for one vendor together then retaining the
>> vendor
>> information would remove the need to type it in again.
>>
>> If you need to concatenate the values for display purposes then you can
>> do
>> that in the code for your stored procedure or create a view.
> Again, beyond my control. I've done a few necessary hacks of the software
> (both the asp and some sprocs), but I can't expand the number of such
> edits
> beyond reason. Next vendor upgrade, I'd be flailing about.
> The users are more worried about being able to have reports on data
> they're
> unfortunately left to enter in a free-form description field, type
> varchar.
> My posting is to inquire whether a string formatting convention I'm
> considering is the best I can do under that circumstance. I'd be glad of
> any hints at how to most efficiently query the data as well.
>
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> If you are not writing a new interface then you are probably better using a
> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
> a fixed length then you don't need a separator, but it may help the users
> differentiate the different parts. I would have expect that you can
> determine the fact that an invoice is a referral by a different means.

Well, we could get into "I would have expected" exchanges, or we can skip
the stuff I already know -- and posted in the original post. With all due
respect, "duh."

Good grief. Outta here.

Scott|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you are not writing a new interface then you are probably better
> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
> each part is a fixed length then you don't need a separator, but it may
> help the users differentiate the different parts. I would have expect
> that you can determine the fact that an invoice is a referral by a
> different means.

Now, how is that for a user interface? This is free text, and users will
mangle it anyway, but they have better odds with a separator than fixed
length.

I have not intervened into this thread before, because I don't have
much advice to offer Steve in this dire situation.

Hm, possibly could have a trigger on the table that loaded a new table
with the fields split in pieces. Still iffy with regards to the vendor,
but at least no existing object is changed. (Then again, being on the
vendor side of the fence, I know what I would think if I found that one
of our customers had added a trigger to one of our tables.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Scott Marquardt opined thusly on Sep 24:
> John Bell opined thusly on Sep 24:
>> Hi
>>
>> If you are not writing a new interface then you are probably better using a
>> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
>> a fixed length then you don't need a separator, but it may help the users
>> differentiate the different parts. I would have expect that you can
>> determine the fact that an invoice is a referral by a different means.
> Well, we could get into "I would have expected" exchanges, or we can skip
> the stuff I already know -- and posted in the original post. With all due
> respect, "duh."
> Good grief. Outta here.

John., I'm going to apologize for a short temper there. It was born of
impatience.

My principal interest was in hearing how anyone else might PARSE the field
to recover the data. Erland's trigger idea isn't bad for WHAT I do with it.

"Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart"

"Refund for can of beans, 100-3123&WidgetMart"

The second one would need to be tagged as a referral to the first one. But
if that's my basic technique, they're parse out the same way.

Scott|||>> My principal interest was in hearing how anyone else might PARSE the field to recover the data. Erland's trigger idea isn't bad for WHAT I do with it. "Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart" <<

Since free text requires human intelligence, we get people to encode
it. If the text is short and styled the same way, you can find AI
programs that are pretty good these days. Of course this has nothing
to do with RDBMS and nobody in their right mind would try to do it in
SQL.|||Scott,

Have you considered an ActiveX script in a DTS package to shred the
data periodically (you can use the SQL Scheduler to run it once a
minute). We do this to parse out syslog tables; it's kludgy, but its
consistent. While not as immediate as a trigger to populate a table,
it does keep you from modifying the vendor database (which, if your
vendor has the ability to update their database, could be a big
benefit).

Obviously, your users would have to be trained on how to enter data
consistently, and you'd probably want some sort of validation to pick
up on coded entries that don't meant your
data-followed-by-a-comma-followed-by-more-data rules. You may even
look at Regular Expressions inside your DTS package to help with that.

Just thinking aloud,
Stu|||Hi Erland/Steve

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96DBF14D14D5AYazorman@.127.0.0.1...
> John Bell (jbellnewsposts@.hotmail.com) writes:
>> If you are not writing a new interface then you are probably better
>> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
>> each part is a fixed length then you don't need a separator, but it may
>> help the users differentiate the different parts. I would have expect
>> that you can determine the fact that an invoice is a referral by a
>> different means.
>
> Now, how is that for a user interface? This is free text, and users will
> mangle it anyway, but they have better odds with a separator than fixed
> length.

That was my point, guess I should have said it that way! The users will
(probably) not like having to enter a coded string, even if you keep it as
simple! They may even deliberately mangle it if there is no value in it for
themselves.

> I have not intervened into this thread before, because I don't have
> much advice to offer Steve in this dire situation.
It seems to me, that if this an off the shelf package the benefits has been
lost some time ago, if this is a bespoke development then it has not been
specified thoroughly enough.

> Hm, possibly could have a trigger on the table that loaded a new table
> with the fields split in pieces. Still iffy with regards to the vendor,
> but at least no existing object is changed. (Then again, being on the
> vendor side of the fence, I know what I would think if I found that one
> of our customers had added a trigger to one of our tables.)

Writing a trigger may still invalidate the contract as the transactions will
be effected, the vendor may have the right to charge improportate fees to
fix a problem even if a problem is not related to this table. But if Steve
has already tweeked things that could already be the case! A trigger would
be useful for validation even if the replication route is not taken. Then
again Steve was worried about upgrades breaking things.

Dire may be an understatement!

Steve has my sympathy.

John

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Nothing seems to be going right in this thread!

Who is Steve?

Sorry Scott!

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:43364302$0$14057$da0feed9@.news.zen.co.uk...
> Hi Erland/Steve
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96DBF14D14D5AYazorman@.127.0.0.1...
>> John Bell (jbellnewsposts@.hotmail.com) writes:
>>> If you are not writing a new interface then you are probably better
>>> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
>>> each part is a fixed length then you don't need a separator, but it may
>>> help the users differentiate the different parts. I would have expect
>>> that you can determine the fact that an invoice is a referral by a
>>> different means.
>>
>>
>> Now, how is that for a user interface? This is free text, and users will
>> mangle it anyway, but they have better odds with a separator than fixed
>> length.
> That was my point, guess I should have said it that way! The users will
> (probably) not like having to enter a coded string, even if you keep it as
> simple! They may even deliberately mangle it if there is no value in it
> for themselves.
>>
>> I have not intervened into this thread before, because I don't have
>> much advice to offer Steve in this dire situation.
>>
> It seems to me, that if this an off the shelf package the benefits has
> been lost some time ago, if this is a bespoke development then it has not
> been specified thoroughly enough.
>> Hm, possibly could have a trigger on the table that loaded a new table
>> with the fields split in pieces. Still iffy with regards to the vendor,
>> but at least no existing object is changed. (Then again, being on the
>> vendor side of the fence, I know what I would think if I found that one
>> of our customers had added a trigger to one of our tables.)
> Writing a trigger may still invalidate the contract as the transactions
> will be effected, the vendor may have the right to charge improportate
> fees to fix a problem even if a problem is not related to this table. But
> if Steve has already tweeked things that could already be the case! A
> trigger would be useful for validation even if the replication route is
> not taken. Then again Steve was worried about upgrades breaking things.
> Dire may be an understatement!
> Steve has my sympathy.
> John
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>> Books Online for SQL Server SP3 at
>> http://www.microsoft.com/sql/techin.../2000/books.asp
>>|||Hi Scott

Non taken!

You can't always give good advice, sometimes the best you can do is throw
ideas into the pot.

Using a scheduled job may be less of an preblem when considering
invalidating the contract! Although running it once a minute may cause
issues such as blocking.

I have assumed that the information you are entering is not available on
this system, therefore it is not possible to pick it up at report time from
other fields.

John

"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127610853.841b341db0737c3d0f8b5850b289ecae@.t eranews...
> Scott Marquardt opined thusly on Sep 24:
>> John Bell opined thusly on Sep 24:
>>> Hi
>>>
>>> If you are not writing a new interface then you are probably better
>>> using a
>>> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part
>>> is
>>> a fixed length then you don't need a separator, but it may help the
>>> users
>>> differentiate the different parts. I would have expect that you can
>>> determine the fact that an invoice is a referral by a different means.
>>
>> Well, we could get into "I would have expected" exchanges, or we can skip
>> the stuff I already know -- and posted in the original post. With all due
>> respect, "duh."
>>
>> Good grief. Outta here.
> John., I'm going to apologize for a short temper there. It was born of
> impatience.
> My principal interest was in hearing how anyone else might PARSE the field
> to recover the data. Erland's trigger idea isn't bad for WHAT I do with
> it.
> "Invoice for 1/2 inch puce widgets and a can of beans,
> 100-3123@.WidgetMart"
> "Refund for can of beans, 100-3123&WidgetMart"
> The second one would need to be tagged as a referral to the first one. But
> if that's my basic technique, they're parse out the same way.
> --
> Scott|||--CELKO-- opined thusly on Sep 24:
>>> My principal interest was in hearing how anyone else might PARSE the field to recover the data. Erland's trigger idea isn't bad for WHAT I do with it. "Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart" <<
> Since free text requires human intelligence, we get people to encode
> it. If the text is short and styled the same way, you can find AI
> programs that are pretty good these days. Of course this has nothing
> to do with RDBMS and nobody in their right mind would try to do it in
> SQL.

Hey! How'd you find out about my condition?

;-)

Scott|||Stu opined thusly on Sep 24:
> Scott,
> Have you considered an ActiveX script in a DTS package to shred the
> data periodically (you can use the SQL Scheduler to run it once a
> minute). We do this to parse out syslog tables; it's kludgy, but its
> consistent. While not as immediate as a trigger to populate a table,
> it does keep you from modifying the vendor database (which, if your
> vendor has the ability to update their database, could be a big
> benefit).
> Obviously, your users would have to be trained on how to enter data
> consistently, and you'd probably want some sort of validation to pick
> up on coded entries that don't meant your
> data-followed-by-a-comma-followed-by-more-data rules. You may even
> look at Regular Expressions inside your DTS package to help with that.
> Just thinking aloud,
> Stu

Less intrusive, yeah. I'll be needing to roll my own shredder, though --
unless you'd care to name some good controls. Haven't tried any, ever,
really. It's been nice to have never needed to!

I anticipate we'll see now more than 3000 such records a year, so this is
probably something that could be done at runtime. Users won't be running
anything that queries a result of this, so parsing it all out when running
reports (not sure where to implement that, yet, but it won't be in the
application itself) is an inelegant but tenable option.

Scott|||John Bell opined thusly on Sep 25:

> That was my point, guess I should have said it that way! The users will
> (probably) not like having to enter a coded string, even if you keep it as
> simple! They may even deliberately mangle it if there is no value in it for
> themselves.

In this case, the only users needing to enter these have the only stake in
these particular data points. This isn't something they'll grumble about;
in this case, they're the ones demanding it. ;-)

The vendor's likely to be cool with this. I'm documenting things pretty
well, and a few of my other hacks to the system may end up as features.
They have an aggressive development cycle and are responsive. Best of
worlds for trying stuff.

Steve ;-)|||Scott Marquardt at wit's-end:

> Less intrusive, yeah. I'll be needing to roll my own shredder, though --
> unless you'd care to name some good controls. Haven't tried any, ever,
> really. It's been nice to have never needed to!

> I anticipate we'll see now more than 3000 such records a year, so this is
> probably something that could be done at runtime. Users won't be running
> anything that queries a result of this, so parsing it all out when running
> reports (not sure where to implement that, yet, but it won't be in the
> application itself) is an inelegant but tenable option.

More stuff to drive you even crazier - Log Parser from Microsoft:
http://www.microsoft.com/technet/sc...er/default.mspx

It'll either drive you over the edge, or restore your sanity.

GeoSynch|||GeoSynch opined thusly on Sep 27:
> Scott Marquardt at wit's-end:
>> Less intrusive, yeah. I'll be needing to roll my own shredder, though --
>> unless you'd care to name some good controls. Haven't tried any, ever,
>> really. It's been nice to have never needed to!
>> I anticipate we'll see now more than 3000 such records a year, so this is
>> probably something that could be done at runtime. Users won't be running
>> anything that queries a result of this, so parsing it all out when running
>> reports (not sure where to implement that, yet, but it won't be in the
>> application itself) is an inelegant but tenable option.
> More stuff to drive you even crazier - Log Parser from Microsoft:
> http://www.microsoft.com/technet/sc...er/default.mspx
> It'll either drive you over the edge, or restore your sanity.

No. You don't understand. We're so understaffed and overbooked that I could
have this operating right now somewhere, in a scheduled task or a script,
and I might not even know about it. I can't begin to count the number of
things we've been grateful for and implemented without documenting it.

That's the world of education, folks! ;-)

Hey! Download to the usual folder asks whether to overwrite. Guess I've got
it already! ;-)

Scott|||Scott Marquardt opined thusly on Sep 25:
> John Bell opined thusly on Sep 25:
>> That was my point, guess I should have said it that way! The users will
>> (probably) not like having to enter a coded string, even if you keep it as
>> simple! They may even deliberately mangle it if there is no value in it for
>> themselves.
> In this case, the only users needing to enter these have the only stake in
> these particular data points. This isn't something they'll grumble about;
> in this case, they're the ones demanding it. ;-)

OK, this is maybe (maybe) the kludgiest thing I've done in a while. If
anyone has any ideas for cleaning it up, I'm all ears. It works, though.

| select
| case
| when charindex(' ',ltrim(rtrim(comment))) < charindex('@.',ltrim(rtrim(comment))) and charindex(' ',ltrim(rtrim(comment))) <> 0 then
| right(left(comment, charindex('@.',ltrim(rtrim(comment)))-1) ,charindex(' ', reverse(left( ltrim(rtrim(comment)), charindex('@.',ltrim(rtrim(comment)))-2))))
| else
| left( ltrim(rtrim(comment)), charindex('@.',ltrim(rtrim(comment)))-1)
| end
| as InvoiceNumber,
| case
| when charindex(' ',ltrim(rtrim(reverse(comment)))) < charindex('@.',ltrim(rtrim(reverse(comment)))) and charindex(' ',ltrim(rtrim(comment))) <> 0 then
| left(right(comment, charindex('@.',ltrim(rtrim(reverse(comment))))-1),charindex(' ',ltrim(rtrim(reverse(comment)))))
| else
| reverse(left( ltrim(rtrim(reverse(comment))), charindex('@.',ltrim(rtrim(reverse(comment))))-1))
| end
| as VendorID, case when charindex('@.@.',comment) > 0 then 'True' else 'False' end as Reference, comment
| from bill_lineitems
| where comment like '%@.%'

That translates into this:

This allows for putting an invoice@.vendor (or invoice@.@.vendor) anywhere in
a description field, so long as it's set off on either side by spaces or
appears at the very beginning or end of the string. You can't, for example,
use it at the end of a grammatical sentence and append a period.

These work:

invoice@.vendor yada yada yada
yada yada yada invoice@.vendor
yada yada yada invoice@.vendor yada yada yada
yada yada yada invoice@.vendor yada yada yada
invoice@.vendor
invoice@.vendor [with spaces at the end as well]

These will not work:

There are problems with this invoice@.vendor!
Use the invoice that was not paid (invoice@.vendor)

Also, you can't use more than one such thing in a single record's
description.

Scott

parsing varchar fields

What are some good strategic approaches to using freeform text fields for
data that needs to be queried? We have a product whose tables we can't
change, and I need to count on a "description" field for storing a value.
Two, actually. I'm thinking of adopting this convention:
InvoiceNumber@.VendorAcronym
There'd be a lot of vendors.
Additional issue: sometimes these values would be referred to in the
description field, and I'd need to distinguish them as referrals rather
than as original recorded instances of the values. For that, I imagined
either:
InvoiceNumber@.@.VendorAcronym
or
InvoiceNumber&VendorAcronym
InvoiceNumber//VendorAcronym
etc. -- something like that.
I'm just wondering if there's best practice for doing anything this stupid
(hey, I'm stuck with this as our only option just now; hopefully it's only
temporary). How to parse out whatever I end up implementing -- well, it
needs to be tractable.
Thoughts?
ScottScott,
Instead of changing the existing product's tables why not create an
additional table(s) to store these values and then write your queries using
joins.
HTH
Jerry
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to do, but creating a concatenated field
is probably not a good idea. Separate fields and a status will be far easier
to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to achieve, but creating a concanenated
field is probably not a good idea. Separate fields and a status will be alot
easier to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Jerry Spivey opined thusly on Sep 24:
> Scott,
> Instead of changing the existing product's tables why not create an
> additional table(s) to store these values and then write your queries usin
g
> joins.
Jerry, John -- sorry for the lack of clarity.
We have a web app from a vendor, and in this particular case I don't want
to hack anything (I've done some hacks where practical, and will be doing
more, but there's some intractable stuff in view of upgrade management and
my available time).
Our people need to reference invoice numbers and vendors in a description
field, so I'm wanting to accommodate them until such time comes along that
the software supports something like that. But they're wanting to be able
to generate reports on the vendor and invoice numbers, so I'm wanting to do
this kludgey thing as elegantly as kludges can be. I figured if I specified
that they enter the two fields in some string format that was simple enough
for them this would be practical from their standpoint, and if I could
parse it it'd be practical from mine. The concatenation John's concerned
about is mostly as a way of avoiding separate ways of identifying the
"fields". That would be a bit onerous for the staff entering the
information, even if it did make parsing easier.
So imagine a lot of records with brief descriptions of invoices, with
vendor and invoice #s embedded in the descriptions. Oh, yeah, almost forgot
-- no chance of their being more than one of these per description (per
record).
Scott|||Hi
I am not sure why you think the users would want to enter some cryptic code
rather than exact values in two separate fields. That is more of a usability
with the UI, for instance if your parts were both numeric you may be slowing
your users down forcing them to use non-keypad characters that require
shifting. You should also look at the business processes, for instance if
you processed everything for one vendor together then retaining the vendor
information would remove the need to type it in again.
If you need to concatenate the values for display purposes then you can do
that in the code for your stored procedure or create a view.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127546922. 53434faa9cedec41603cbc091e9f06a8@.teranew
s...
> Jerry Spivey opined thusly on Sep 24:
> Jerry, John -- sorry for the lack of clarity.
> We have a web app from a vendor, and in this particular case I don't want
> to hack anything (I've done some hacks where practical, and will be doing
> more, but there's some intractable stuff in view of upgrade management and
> my available time).
> Our people need to reference invoice numbers and vendors in a description
> field, so I'm wanting to accommodate them until such time comes along that
> the software supports something like that. But they're wanting to be able
> to generate reports on the vendor and invoice numbers, so I'm wanting to
> do
> this kludgey thing as elegantly as kludges can be. I figured if I
> specified
> that they enter the two fields in some string format that was simple
> enough
> for them this would be practical from their standpoint, and if I could
> parse it it'd be practical from mine. The concatenation John's concerned
> about is mostly as a way of avoiding separate ways of identifying the
> "fields". That would be a bit onerous for the staff entering the
> information, even if it did make parsing easier.
> So imagine a lot of records with brief descriptions of invoices, with
> vendor and invoice #s embedded in the descriptions. Oh, yeah, almost
> forgot
> -- no chance of their being more than one of these per description (per
> record).
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> I am not sure why you think the users would want to enter some cryptic cod
e
> rather than exact values in two separate fields.
Developing more fields in this N-tier application whose upgrades are
controlled by a vendor whose dev I'm not privy to, isn't an option. If I
could do that, I would. I'm not posting to inquire about better
alternatives; I'm wanting to wring juice out of an unfortunate turnip.

> That is more of a usability
> with the UI, for instance if your parts were both numeric you may be slowi
ng
> your users down forcing them to use non-keypad characters that require
> shifting. You should also look at the business processes, for instance if
> you processed everything for one vendor together then retaining the vendor
> information would remove the need to type it in again.
> If you need to concatenate the values for display purposes then you can do
> that in the code for your stored procedure or create a view.
Again, beyond my control. I've done a few necessary hacks of the software
(both the asp and some sprocs), but I can't expand the number of such edits
beyond reason. Next vendor upgrade, I'd be flailing about.
The users are more worried about being able to have reports on data they're
unfortunately left to enter in a free-form description field, type varchar.
My posting is to inquire whether a string formatting convention I'm
considering is the best I can do under that circumstance. I'd be glad of
any hints at how to most efficiently query the data as well.
Scott|||Hi
If you are not writing a new interface then you are probably better using a
fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
a fixed length then you don't need a separator, but it may help the users
differentiate the different parts. I would have expect that you can
determine the fact that an invoice is a referral by a different means.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127575738. f429cee989a6daeb985244c70f0bb497@.teranew
s...
> John Bell opined thusly on Sep 24:
> Developing more fields in this N-tier application whose upgrades are
> controlled by a vendor whose dev I'm not privy to, isn't an option. If I
> could do that, I would. I'm not posting to inquire about better
> alternatives; I'm wanting to wring juice out of an unfortunate turnip.
>
> Again, beyond my control. I've done a few necessary hacks of the software
> (both the asp and some sprocs), but I can't expand the number of such
> edits
> beyond reason. Next vendor upgrade, I'd be flailing about.
> The users are more worried about being able to have reports on data
> they're
> unfortunately left to enter in a free-form description field, type
> varchar.
> My posting is to inquire whether a string formatting convention I'm
> considering is the best I can do under that circumstance. I'd be glad of
> any hints at how to most efficiently query the data as well.
>
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> If you are not writing a new interface then you are probably better using
a
> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part i
s
> a fixed length then you don't need a separator, but it may help the users
> differentiate the different parts. I would have expect that you can
> determine the fact that an invoice is a referral by a different means.
Well, we could get into "I would have expected" exchanges, or we can skip
the stuff I already know -- and posted in the original post. With all due
respect, "duh."
Good grief. Outta here.
Scott|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you are not writing a new interface then you are probably better
> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
> each part is a fixed length then you don't need a separator, but it may
> help the users differentiate the different parts. I would have expect
> that you can determine the fact that an invoice is a referral by a
> different means.
Now, how is that for a user interface? This is free text, and users will
mangle it anyway, but they have better odds with a separator than fixed
length.
I have not intervened into this thread before, because I don't have
much advice to offer Steve in this dire situation.
Hm, possibly could have a trigger on the table that loaded a new table
with the fields split in pieces. Still iffy with regards to the vendor,
but at least no existing object is changed. (Then again, being on the
vendor side of the fence, I know what I would think if I found that one
of our customers had added a trigger to one of our tables.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Parse SQL statement for list of tables

Hello,
I would like to make a procedure of sorts that accepts as input a full
sql statment and then is able to return a list (or print) of only the tables
referenced in the sql statement. Is this kind of code available?
Thanks.
Bentweak this
-- Create our Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @.intLoopCounter INT
SELECT @.intLoopCounter =0
WHILE @.intLoopCounter <=999 BEGIN
INSERT INTO NumberPivot
VALUES (@.intLoopCounter)
SELECT @.intLoopCounter = @.intLoopCounter +1
END
GO
Create table #tempTables (SplitString varchar(50))
DECLARE @.chvGroupNumbers VARCHAR(1000)
SELECT @.chvGroupNumbers ='select * from authors join publishers on bla bla
bla...'
insert into #tempTables
SELECT SUBSTRING(' ' + @.chvGroupNumbers + ' ', NumberID + 1,
CHARINDEX(' ', ' ' + @.chvGroupNumbers + ' ', NumberID + 1) - NumberID -1)AS
Value
FROM NumberPivot
WHERE NumberID <= LEN(' ' + @.chvGroupNumbers + ' ') - 1
AND SUBSTRING(' ' + @.chvGroupNumbers + ' ', NumberID, 1) = ' '
GO
select * from #tempTables where Splitstring in (SELECT table_name FROM
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE')
Use Pubs for this example
http://sqlservercode.blogspot.com/
"Ben" wrote:

> Hello,
> I would like to make a procedure of sorts that accepts as input a full
> sql statment and then is able to return a list (or print) of only the tabl
es
> referenced in the sql statement. Is this kind of code available?
> Thanks.
> Ben

Parse a character seperated list of items

Hey,
Just spent WAY to long trying to parse out a item from a comma
seperated list without using temp tables. I bet there is something out
there already, but I couldn't find it. Here's my code for anyone that
wants it. The function parameters are the list of items, the seperator
you are looking for and the index of the item you want. NOTE: The list
is one based. If you enter 0 for the first item it will return NULL. If
the index higher than the number of items, the function also returns 0.
I'm sure the code could be better but I don't have any more time. If
someone improves the code, please post the new stuff so I can have a
look.

Cheers
Russ
CREATE FUNCTION get_list_item
(
@.string_list VARCHAR(8000),
@.seperator CHAR(1) = ',',
@.index_number INT = 1
)
RETURNS VARCHAR(1000)
AS
BEGIN
IF(@.index_number IS NULL)
BEGIN
SET @.index_number = 1
END
DECLARE @.seperator_index INT
DECLARE @.start_position INT
DECLARE @.next_index INT
DECLARE @.item_count INT
DECLARE @.return_item VARCHAR(1000)
DECLARE @.error_occured BIT
SET @.error_occured = 0
SET @.start_position = 0
SET @.item_count = 0
SET @.seperator_index = CHARINDEX(@.seperator, @.string_list)
IF(@.seperator_index > 0)
BEGIN
SET @.item_count = @.item_count + 1
--print 'COUNT = ' + CAST(@.item_count AS VARCHAR) + ' START: ' +
CAST(@.start_position AS VARCHAR) +
--' - END: ' + CAST(@.seperator_index AS VARCHAR)
WHILE(@.item_count < @.index_number)
BEGIN
SET @.start_position = @.seperator_index + 1
SET @.next_index = CHARINDEX(@.seperator, @.string_list,
@.start_position)
IF(@.next_index > 0)
BEGIN
SET @.seperator_index = @.next_index
SET @.item_count = @.item_count + 1
END
ELSE
BEGIN
--RAISERROR('The index requested was greater than the number of
items in the list', 16, 1)
--SET @.error_occured = 1
BREAK
END
--print 'COUNT = ' + CAST(@.item_count AS VARCHAR) + ' START: ' +
CAST(@.start_position AS VARCHAR) +
--' - END: ' + CAST(@.seperator_index AS VARCHAR)
END
IF(@.item_count = @.index_number - 1)
BEGIN
SET @.return_item = SUBSTRING(@.string_list,
@.start_position, (LEN(@.string_list) - @.start_position) + 1)
END
ELSE
BEGIN
IF(@.item_count = @.index_number)
BEGIN
SET @.return_item = SUBSTRING(@.string_list,
@.start_position, @.seperator_index - @.start_position)
END
END
END
ELSE
BEGIN
--DECLARE @.error_message VARCHAR(250)
--SET @.error_message = 'Seperator "' + @.seperator + '" not found.'
--RAISERROR(@.error_message,16,1)
SET @.return_item = NULL
END
RETURN @.return_item
END
P.S. This was tested with SQL Server 2000.
Russ
|||(russ.haley@.gmail.com) writes:
> Just spent WAY to long trying to parse out a item from a comma
> seperated list without using temp tables. I bet there is something out
> there already, but I couldn't find it.
Have a look at http://www.sommarskog.se/arrays-in-sql.html, and
particularly the core function for "Using a Table of Number". Further
down the page, there is also an example with fixed-length input.
I'm not really sure that I see the point with your function. You avoid
creating a table, but since a table is faster for lookup than a list,
I can't see any significant gain with it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||On 20 Mar 2006 12:07:45 -0800, russ.haley@.gmail.com wrote:

>Hey,
>Just spent WAY to long trying to parse out a item from a comma
>seperated list without using temp tables. I bet there is something out
>there already, but I couldn't find it.
Hi Russ,
http://www.sommarskog.se/arrays-in-sql.html
Hugo Kornelis, SQL Server MVP
|||Hi, some time ago I wrote a function for that:
CREATE FUNCTION dbo.Split
(
@.String VARCHAR(200),
@.Delimiter VARCHAR(5)
)
RETURNS @.SplittedValues TABLE
(
OccurenceId SMALLINT IDENTITY(1,1),
SplitValue VARCHAR(200)
)
AS
BEGIN
DECLARE @.SplitLength INT
WHILE LEN(@.String) > 0
BEGIN
SELECT @.SplitLength = (CASE CHARINDEX(@.Delimiter,@.String) WHEN 0 THEN
LEN(@.String) ELSE CHARINDEX(@.Delimiter,@.String) -1 END)
INSERT INTO @.SplittedValues
SELECT SUBSTRING(@.String,1,@.SplitLength)
SELECT @.String = (CASE (LEN(@.String) - @.SplitLength) WHEN 0 THEN ''
ELSE RIGHT(@.String, LEN(@.String) - @.SplitLength - 1) END)
END
RETURN
END
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||The point was I couldn't find an easy way to parse a character
seperated list. There are times in an application I am working on where
we get such data that is not parsed on the application side. Look
forward to reading the article you suggested.
Russ
|||Wow, very cool function. (I'm such a geek for saying that!) Thanks for
the feedback.
Russ

Monday, March 26, 2012

Parent-Child View without using Cursors

I would like to create a View (we'll call it FamilyView) using two tables, that I can then query against.
For example:

Parent
{
ID_PK,
Name,
PhoneNum,
Address
}

Child
{
ID_PK,
ParentID_FK,
Name
}

The view would return a dataset like this:

Parent.Name, Parent.PhoneNum, Parent.Address, Child.Name1, Child.Name2, Child.Name3... Child.NameN

William Smith, (555)555-5555, 123 Main Street, Susie, Peter, Bill Jr, Fred
Jason Jones, (666)666-6666, 54332 South Ave, Brian, Steven
Kay McPeak, (777)777-7777, 9876 Division NW, Kathy, Sally, Karen, Deb, Becky, Kendra, Ann, Edward

with an unknown number of children for each parent.

Then I would like to be able to query against this view with something like this:

SELECT * FROM FamilyView Where Child2 = 'Peter'

I have no idea how to write the SQL for this View. Is it possible?
Is this possible without using a cursor?

Thanks for any advice you all can give me.
BrianWhat Version of SQL Server?

If it's 2005, you can use CTE (Common Table Expreassions)

If it's 2000, you probably need to use a udf that returns a table|||It's SQL Server 2000.

Could you give me an example of how a UDF would be used to solve this please?|||So you want to find where the family tree for a child somewhere in the middle?|||I want to display each "family" in a single row in a result set and then be able to filter those families where the second child listed is 'Peter' (for example) and view only the families where Peter is the name of the second child. Does that make sense?

My real problem is a little more complex, but I thought that if I used this example it would eliminate a lot of explanation of the problem domain.|||Here you go, either a sproc, or a udf for set based stuff

CREATE TABLE Parent (
ID_PK int IDENTITY(1,1)
, [Name] varchar(20)
, PhoneNum varchar(20)
, Address varchar(30))

CREATE TABLE Child (
ID_PK int
, ParentID_FK int)
GO

INSERT INTO Parent([Name],PhoneNum, Address)
SELECT 'Annie', '111-111-1111', '1st Street' UNION ALL
SELECT 'Bob', '222-222-2222', '2nd Street' UNION ALL
SELECT 'Cathy', '333-333-3333', '3rd Street' UNION ALL
SELECT 'Don', '444-444-4444', '4th Street' UNION ALL
SELECT 'Emily', '555-555-5555', '5th Street' UNION ALL
SELECT 'Frank', '666-666-6666', '6th Street' UNION ALL
SELECT 'Georgette', '777-777-7777', '7th Street' UNION ALL
SELECT 'Harry', '888-888-8888', '8th Street'

INSERT INTO Child(ID_PK, ParentID_FK)
SELECT 1, null UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 4, 3 UNION ALL
SELECT 5, null UNION ALL
SELECT 6, 5 UNION ALL
SELECT 7, 6 UNION ALL
SELECT 8, 7
GO

SELECT * FROM Parent p LEFT JOIN Child c ON p.ID_PK = c.ID_PK
GO

CREATE FUNCTION udf_FindTree (@.Child varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @.p int, @.p_save int, @.rs varchar(8000)
SELECT @.p = 0, @.p_save = 0
SELECT @.p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @.Child
--Loop Until @.@.rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @.p)
BEGIN
SELECT @.p_save = @.p
SELECT @.p = ParentID_FK FROM Child c WHERE ID_PK = @.p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @.@.rowcount = 0
SELECT @.p = @.p_save
SELECT @.rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @.p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @.p)
BEGIN
SELECT @.rs = @.rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @.p
SELECT @.p = ID_PK FROM Child WHERE ParentID_FK = @.p
END
RETURN @.rs
END
GO

SELECT dbo.udf_FindTree('Cathy')
GO

SELECT * FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = 'Cathy'
GO

CREATE PROC usp_FindTree @.Child varchar(20)
AS
SET NOCOUNT ON
DECLARE @.p int, @.p_save int, @.rs varchar(8000)
SELECT @.p = 0, @.p_save = 0
SELECT @.p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @.Child
--Loop Until @.@.rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @.p)
BEGIN
SELECT @.p_save = @.p
SELECT @.p = ParentID_FK FROM Child c WHERE ID_PK = @.p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @.@.rowcount = 0
SELECT @.p = @.p_save
SELECT @.rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @.p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @.p)
BEGIN
SELECT @.rs = @.rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @.p
SELECT @.p = ID_PK FROM Child WHERE ParentID_FK = @.p
END
SELECT @.rs AS rs
SET NOCOUNT OFF
GO

EXEC usp_FindTree 'Cathy'
GO

DROP PROC usp_FindTree
DROP Function udf_FindTree
DROP TABLE Parent, Child
GO|||You could even do

SELECT DISTINCT dbo.udf_FindTree([name]) FROM Parent
GO|||Your work here has actually taught me quite a bit about UDFs and I appreciate that very much, Thank you!

But what I'm looking for is something closer to what this SQL generates.

USE Northwind
GO

SELECT OrderID,
coalesce(MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END), '') AS Product1,
coalesce(MAX(CASE OD.rowno WHEN 2 THEN P.ProductName END), '') AS Product2,
coalesce(MAX(CASE OD.rowno WHEN 3 THEN P.ProductName END), '') AS Product3,
coalesce(MAX(CASE OD.rowno WHEN 4 THEN P.ProductName END), '') AS Product4,
coalesce(MAX(CASE OD.rowno WHEN 5 THEN P.ProductName END), '') AS Product5,
coalesce(MAX(CASE OD.rowno WHEN 6 THEN P.ProductName END), '') AS Product6,
coalesce(MAX(CASE OD.rowno WHEN 7 THEN P.ProductName END), '') AS Product7
FROM (SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD
JOIN Products P ON P.ProductID = OD.ProductID
GROUP BY OD.OrderID
ORDER BY OD.OrderID

Use Northwind database and assume [Order Details] as parent and [Products] as the child. See how all the data between the two tables are displayed in one row (but separate NAMED columns: Product1, Product2, ... etc.)? That's what I'm looking for. If I could write this code into a View (Which I can't) then I could query against the returned dataset like this.

SELECT * FROM OrderProductView WHERE Product1 = 'Chang'

and I would get all the same columns, but only including the rows with OrderIDs: {10255, 10258, 10264, etc}.

The problem with the above code is that I HAVE to know the number of "child" (Product) elements expected per order at design time. Also, the CASE construct is not valid in a View.|||So you don't care about a tree, just a key and all it's attributive rows?

Maybe something like

http://weblogs.sqlteam.com/brettk/archive/2005/02/23/4171.aspx|||Yes! This appears to be exactly what I've been looking for. Thank you, thank you, thank you.
I was beginning to think this could only be executed in code outside the SQL.
I need to play with this a little to fully understand it all, but I think this will give me the results I need.
Thank you Brett for your patience and all your help!|||Just cut and paste the code example to see how it works

Good Luck

...oh, and you can buy me a margarita and we'll call it even|||Next time I'm in the Jersey area I might do just that. I really appreciate it.
And if you're ever in Grand Rapids...|||One more question...
My query is now too big to store in a local variable... I've managed to write the generated query to a file. Is there anyway I can execute this query from a text file?

Parent-child variable issues that may impact deployment

We are using SSIS for the first time. My team is working on a project that involves putting a date time stamp into a series of tables. These tables are being assembled in a series of child packages being executed by the parent. When the parent runs, we evaluate our timestamp variable as a GETDATE() expression and pass it to the children to be included as a derived column. We don't want the actual runtime of each step to be the timestamp, just the start of the batch (parent).

In order to get the variable to pass over to the child, we needed to set the package location to "file system"instead of "SQL Server". It seems unusual that this would be so. Are we doing something wrong?

What implications does this have for deployment? Will we need to customize the packages for each instance we plan to run this on? Can you have a parent run a child package on a different instance? This would be a performance plus since we have really huge source databases and would like to distribute the processing.

Hmmm, my boss just told me to scratch the whole idea of parent-child and go with a control table to store the variable for all the packages to access. Oh well, I'm still interested in why this is so cumbersome when really its just passing a parameter from one procedure to another.

Oh, and I think you could use a spellchecker on this message box. At least I could use one.

In the child package, create a variable to hold the parent variable. Same name is fine. Then in the control flow, right click on the background and select "package configurations".

Enable package configurations. Then add a new one. Change the configuration type to "parent package variable." Then, in the specify configuration settings entry, enter the name of the variable in the parent package. Next, on the following screen, select the "value" property of the variable created in your child package. (Expand the child variable until you can select the value property.) Click next. Give the configuration a name and hit finish. Done.

Phil|||

Phil,

Thanks, but we already have that functionality. The problem is that we don't like it. We want something more robust, sort of like passing a parameter from one function to another. And we can't even pass a value parameter with this thingy. This parent-child functionality is just plain ugly. We are using a control table instead, but we don't like doing that either.

Parent Table Control

Hi All,
I Have 2 tables, and when I DELETE or UPDATE 'TPARENT' the 'CLILD' also
UPDATED, DELETED.
If I DELETE in CLILD, the row in CLILD is DELETED, but in 'TPARENT' no.
I would like know if have way to I specifique that DELETE can be used only
in 'TPARENT'
If user try use DELETE in CLILD he receive a error, user can use DELETE
only in TPARENT.
I did try use triger, but if I try DELET of TPARENT I also receive the
error, I want receive erro on;y if I try use DELETE on CLILD
IF EXISTS(SELECT NAME
FROM sysobjects
WHERE NAME = 'BlockDeleteOnDomiciliosBancarios'
AND type = 'TR')
DROP TRIGGER BlockDeleteOnDomiciliosBancarios
GO
CREATE TRIGGER BlockDeleteOnDomiciliosBancarios
ON DomiciliosBancarios
FOR DELETE
AS
BEGIN
ROLLBACK TRANSACTION
PRINT ('No possvel apagar de DomiciliosBancarios')
END
can anyone help-me -- Thanks
CREATE TABLE TPARENT
(
CONSTRAINT pk_TPARENT
PRIMARY KEY(TPARENT),
TPARENT CHAR(30)
NOT NULL
)
INSERT INTO TPAI VALUES ('Test 01')
INSERT INTO TPAI VALUES ('Test 02')
INSERT INTO TPAI VALUES ('Test 03')
---
CREATE TABLE CLILD
(
CONSTRAINT fk_CLILD
FOREIGN KEY(TPARENT )
References TPARENT (TPARENT )
ON UPDATE CASCADE
ON DELETE CASCADE,
TPARENT CHAR(30)
NOT NULL
)Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to know how to delete
rows in TPARENT table. However, I am not sure what's the exact error
message when "If user try use DELETE in CLILD he receive a error, user can
use DELETE only in TPARENT." Would you please help describe it further? If
I have misunderstood your concern, please feel free to point it out.
Based on my knowlegde, when you are specifing ON DELETE/UPDATE CASCADE,
CLILD table's related rows will also be deleted when you are deleting
TPARENT rows.
Since you have specificed FOREIGN KEY, you cannot delete rows in TPARENT
while leave the rows in CLILD. The related rows in CLILD will be also
deleted. If you want to delete rows in TPARENT and not delete rows in
CLILD, you must specify your won trigger to accomplish this instead of
using FOREIGN KEY.
Sincerely yours,
Michael Cheng
Microsoft Online Partner 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.|||Here is a suggestion: Use both a hidden table and a public view for
CLILD. Put the referential integrity on the hidden table, and put
an INSTEAD OF trigger on the view that generates an error and
does not delete anything. If you need to, roll back the transaction
in the trigger also, but since it is an INSTEAD OF trigger, there is
no DELETE to roll back. Here is a repro:
CREATE TABLE TPARENT
(
CONSTRAINT pk_TPARENT
PRIMARY KEY(TPARENT),
TPARENT CHAR(30)
NOT NULL
)
INSERT INTO TPARENT VALUES ('Test 01')
INSERT INTO TPARENT VALUES ('Test 02')
INSERT INTO TPARENT VALUES ('Test 03')
-- Hidden table with foreign key constraint
CREATE TABLE CLILD_hidden
(
CONSTRAINT fk_CLILD
FOREIGN KEY(TPARENT )
References TPARENT (TPARENT )
ON UPDATE CASCADE
ON DELETE CASCADE,
TPARENT CHAR(30)
NOT NULL
)
go
-- view that is used externally as the table
create view CLILD as
select TPARENT from CLILD_hidden
go
insert into CLILD VALUES ('Test 01')
insert into CLILD VALUES ('Test 02')
insert into CLILD VALUES ('Test 03')
go
-- do not allow delete from the view
CREATE TRIGGER BlockDeleteOnCLILD
ON CLILD INSTEAD OF DELETE
AS
-- ROLLBACK TRANSACTION -- if necessary for other reasons
PRINT ('No possvel apagar de CLILD')
go
select * from CLILD
go
delete from TPARENT
where TPARENT = 'Test 01'
go
select * from CLILD
go
delete from CLILD
where TPARENT = 'Test 02'
go
delete from TPARENT
where TPARENT = 'Test 03'
go
select * from CLILD
go
-- drop view CLILD
-- drop table CLILD_hidden, TPARENT
-- Steve Kass
-- Drew University
ReTF wrote:

>Hi All,
>I Have 2 tables, and when I DELETE or UPDATE 'TPARENT' the 'CLILD' also
>UPDATED, DELETED.
>If I DELETE in CLILD, the row in CLILD is DELETED, but in 'TPARENT' no.
>I would like know if have way to I specifique that DELETE can be used only
>in 'TPARENT'
>If user try use DELETE in CLILD he receive a error, user can use DELETE
>only in TPARENT.
>I did try use triger, but if I try DELET of TPARENT I also receive the
>error, I want receive erro on;y if I try use DELETE on CLILD
>IF EXISTS(SELECT NAME
> FROM sysobjects
> WHERE NAME = 'BlockDeleteOnDomiciliosBancarios'
> AND type = 'TR')
> DROP TRIGGER BlockDeleteOnDomiciliosBancarios
>GO
>CREATE TRIGGER BlockDeleteOnDomiciliosBancarios
>ON DomiciliosBancarios
>FOR DELETE
>AS
>BEGIN
> ROLLBACK TRANSACTION
> PRINT ('No possvel apagar de DomiciliosBancarios')
>END
>can anyone help-me -- Thanks
>CREATE TABLE TPARENT
>(
> CONSTRAINT pk_TPARENT
> PRIMARY KEY(TPARENT),
> TPARENT CHAR(30)
> NOT NULL
> )
>INSERT INTO TPAI VALUES ('Test 01')
>INSERT INTO TPAI VALUES ('Test 02')
>INSERT INTO TPAI VALUES ('Test 03')
>---
>CREATE TABLE CLILD
>(
> CONSTRAINT fk_CLILD
> FOREIGN KEY(TPARENT )
> References TPARENT (TPARENT )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> TPARENT CHAR(30)
> NOT NULL
> )
>
>|||Hi,
I want block if the user try DELETE of CLILD, the user can DELETE only of
TPARENT .
Because CLILD has ON DELETE/UPDATE CASCADE, when TPARENT (row) is deleted in
CLILD this also deleted.
But if user try use DELETE direct in CLILD the user must receive a error,
the user can only use DELETE in TPARENT no in childs.
sorry about my english, this is not my native language, if you don't
understand let-me know and I will explain again. Thanks
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> escreveu na mensagem
news:azlw3bxlFHA.3672@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you would like to know how to delete
> rows in TPARENT table. However, I am not sure what's the exact error
> message when "If user try use DELETE in CLILD he receive a error, user
> can
> use DELETE only in TPARENT." Would you please help describe it further? If
> I have misunderstood your concern, please feel free to point it out.
> Based on my knowlegde, when you are specifing ON DELETE/UPDATE CASCADE,
> CLILD table's related rows will also be deleted when you are deleting
> TPARENT rows.
> Since you have specificed FOREIGN KEY, you cannot delete rows in TPARENT
> while leave the rows in CLILD. The related rows in CLILD will be also
> deleted. If you want to delete rows in TPARENT and not delete rows in
> CLILD, you must specify your won trigger to accomplish this instead of
> using FOREIGN KEY.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner 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,
Thanks for your reply.
I understood you request as below
1) User is not able to delete from CLILD table
2) User could delete from TPARENT table
3) When rows in TPARENT table is deleted, remain the related rows in CLILD
table.
If I have misunderstood your concern, please feel free to point it out.
To accomplish this, you cannot use Foreign Key in your tables. As I have
said before, you'd better create your own triggers to do so.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner 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.

Parent Expression

I have created a Report that has many Datasets and they each link to an individual table in the Report. I need the tables to only show the Data that relates to a Parent field on the Report.

E.G.

John Smith (parent)
Tasks 3
Task1
Task2
Task3
Appointments 10
... etc

Joe Bloggs (parent field)
Tasks 2
Task1
Task2
Appointments 5
... etc

I dont know how to make all the Datasets for the individual tables look at the name field. I can only do this if I use one Dataset or use a Parameter. I have tried using Lists but they dont seem to do what I need to do.

Thanks
NJA

What you are describing is a cross-dataset join. This is not natively supported in Reporting Services. You will either need to do the join in your query or use subreports (which are less efficient).

Friday, March 23, 2012

Parent Child Tables

In our database we have a list of devices in a "Device" Table, each
having one or more IP's located in the "IP" Table linked through a
forein key on the DeviceID Column.

I would like to retrieve this information as Such

DeviceID IpAddress
1 10.0.0.1, 10.0.0.2, 10.0.0.3
2 ...
3
4
5
etc.

Is it possible to do that without using cursors? Through a query?"Mark" <markoueis@.hotmail.com> wrote in message
news:b1800bd3.0402231034.7facd986@.posting.google.c om...
> In our database we have a list of devices in a "Device" Table, each
> having one or more IP's located in the "IP" Table linked through a
> forein key on the DeviceID Column.
> I would like to retrieve this information as Such
> DeviceID IpAddress
> 1 10.0.0.1, 10.0.0.2, 10.0.0.3
> 2 ...
> 3
> 4
> 5
> etc.
> Is it possible to do that without using cursors? Through a query?

There are several ways to do this (see the link below for one solution) but
in general it's easier to do this in a client application - formatting data
for presentation shouldn't usually be done in SQL code.

http://www.aspfaq.com/show.asp?id=2279

Note that this solution cannot guarantee to sort the data in any particular
order (see the KB article referenced from the link), so if that is a
requirement then you will need to use a cursor or client code.

Simon

Partitioning Strategery

Hi all,

I've got several large fact tables that I want to partition by time. What's the best way to strategy to manage this? I have a C# app that uses AMO to manage the re-processing currently, but right now I'm fairly naive and just re-process every measure group.

What I'd like is a way to automatically partition by month / day [depending on measure group], and then enhance my re-process app to only process the latest partition.

My current idea is to establish a naming structure for the existing MGs/partitions, [i.e. "Foo 12-2006"], parse the date, adjust the named query, create a new partition if needed, and re-process if appropriate. Though it's quite doable, it seems a bit complex for something that should be a more of a mainstream scenario.

How have you guys implemented this functionality? I saw something about specifying the "slice" dimension on partitions, but I'm not sure if that can be used to help this.

Cheers,

Arjun

The follwoing is my experience for different cases when refreshing data in a partitioned cube.

C1. New data for a new time period

Incrementally processing the time dimension, creating the new partition for the new time period and fully process it.

C2. New data for existing time period

Create meta data in RDBMS such as batch etc, merge the new data into existing partition

C3. Updated or deleted data for existing period

Fully process the partition

In SSAS2005, slice is automatically set for msolap partition, and it is only used for querying, not for processing, so you maybe need not to consider it at all.

|||

CJ - thanks for your insight.

Typically my cases fall under (C1) - new data for a new time period. What's the best way to manage new partition creation? Does SSAS provide any functionality to make this easier, or do I need to build my own infrastructure?

|||

If a partition strategery works for you, it is a good one.

I always build the partitions logic by myself.

Partitioning performance on one disk?

Hi,

I know partitioning improves the performance on large tables.

We have a table of app. 100.000.000 records.
We want to partition the table, but we were wondering if there is still an increase in performance if all the partitions are on the same disk. We want to partition the data per month, but we don't have any disks left were we can spread the partitions.

So my main question is:
Is there still a performance increase when you use partitioning on one disk in stead of multiple disks.

Thx!

Regards Tony

sure. though the gain is maximized when you partition across multiple disks and the system is running on multi cpu.

Partitioning in SQL 2000

Is partitioning fact tables in SQL 2000 really inadequate compared to other
RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
of datawarehouse have partitioning of data and I dont believe the
partitioned views are robust enough. Is that a true statement ?
Robust enough for what? Your requirements are not necessarily the same as
others. In 2000 your only choice really is a Partitioned View which does
have limitations just like any other feature has limitations. Only you can
decide if it meets your needs or not. Yes 2005 has new partitioning
features and will fill in the voids for instances that the Partitioned Views
don't fit. But that does not mean that feature will be right for everyone
either. It all boils down to what you need to do and how you do it.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QS$1jrfFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Is partitioning fact tables in SQL 2000 really inadequate compared to
> other
> RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
> of datawarehouse have partitioning of data and I dont believe the
> partitioned views are robust enough. Is that a true statement ?
>
|||partitionning works fine in SQL 2000.
you have to use the right partition and create good queries to insure that
you'll ask for a limited number of partition only.
if your partition is by year, but you never filter on the year, then the
advantage of the partition is not used.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QS$1jrfFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Is partitioning fact tables in SQL 2000 really inadequate compared to
> other
> RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
> of datawarehouse have partitioning of data and I dont believe the
> partitioned views are robust enough. Is that a true statement ?
>

Partitioning in SQL 2000

Is partitioning fact tables in SQL 2000 really inadequate compared to other
RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
of datawarehouse have partitioning of data and I dont believe the
partitioned views are robust enough. Is that a true statement ?Robust enough for what? Your requirements are not necessarily the same as
others. In 2000 your only choice really is a Partitioned View which does
have limitations just like any other feature has limitations. Only you can
decide if it meets your needs or not. Yes 2005 has new partitioning
features and will fill in the voids for instances that the Partitioned Views
don't fit. But that does not mean that feature will be right for everyone
either. It all boils down to what you need to do and how you do it.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QS$1jrfFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Is partitioning fact tables in SQL 2000 really inadequate compared to
> other
> RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
> of datawarehouse have partitioning of data and I dont believe the
> partitioned views are robust enough. Is that a true statement ?
>|||partitionning works fine in SQL 2000.
you have to use the right partition and create good queries to insure that
you'll ask for a limited number of partition only.
if your partition is by year, but you never filter on the year, then the
advantage of the partition is not used.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QS$1jrfFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Is partitioning fact tables in SQL 2000 really inadequate compared to
> other
> RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
> of datawarehouse have partitioning of data and I dont believe the
> partitioned views are robust enough. Is that a true statement ?
>

Partitioning in SQL 2000

Is partitioning fact tables in SQL 2000 really inadequate compared to other
RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
of datawarehouse have partitioning of data and I dont believe the
partitioned views are robust enough. Is that a true statement ?Robust enough for what? Your requirements are not necessarily the same as
others. In 2000 your only choice really is a Partitioned View which does
have limitations just like any other feature has limitations. Only you can
decide if it meets your needs or not. Yes 2005 has new partitioning
features and will fill in the voids for instances that the Partitioned Views
don't fit. But that does not mean that feature will be right for everyone
either. It all boils down to what you need to do and how you do it.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QS$1jrfFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Is partitioning fact tables in SQL 2000 really inadequate compared to
> other
> RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
> of datawarehouse have partitioning of data and I dont believe the
> partitioned views are robust enough. Is that a true statement ?
>|||partitionning works fine in SQL 2000.
you have to use the right partition and create good queries to insure that
you'll ask for a limited number of partition only.
if your partition is by year, but you never filter on the year, then the
advantage of the partition is not used.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QS$1jrfFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Is partitioning fact tables in SQL 2000 really inadequate compared to
> other
> RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
> of datawarehouse have partitioning of data and I dont believe the
> partitioned views are robust enough. Is that a true statement ?
>

Wednesday, March 21, 2012

Partitioned Views in Sql 2000

Been trying to start a new model with Partitioned Views. Its basically
Monthly data in Fact Tables with Time_Sk as Chk Constraint and the a View on
Top of 12-24 tables. Thing i notice is that the query with Time_Sk In (1,2)
gets me the data faster than Time_Sk In (Select Value from Table where yrmnth
between '200701' and '200702') or a Join with TimeDim.
I did look up a few notes in here, where in it says that the plan would say
that all the tables are scanned but in truth only the wanted table is
scanned. But how would the time taken to run these 2 queries varry so much...
Any ideas ? Options ?
Sr Apps Developer
> I did look up a few notes in here, where in it says that the plan would
> say
> that all the tables are scanned but in truth only the wanted table is
> scanned.
As long as your view and underlying tables are setup correctly (primary key
and check constraint), the execution plan will contain a startup expression
to eliminate unneeded tables during execution.

> But how would the time taken to run these 2 queries varry so much...
I expect this is due to different execution plans. Can you post DDL
(simplified CREATE TABLE and VIEW) that shows the issue? I recall that
there might be cases where elimination doesn't occur via joins but indexes
can still be used to efficiently return data.
Hope this helps.
Dan Guzman
SQL Server MVP
"sabin" <sabin@.discussions.microsoft.com> wrote in message
news:D9C7FF35-961C-481E-BF88-9D8933CD0332@.microsoft.com...
> Been trying to start a new model with Partitioned Views. Its basically
> Monthly data in Fact Tables with Time_Sk as Chk Constraint and the a View
> on
> Top of 12-24 tables. Thing i notice is that the query with Time_Sk In
> (1,2)
> gets me the data faster than Time_Sk In (Select Value from Table where
> yrmnth
> between '200701' and '200702') or a Join with TimeDim.
> I did look up a few notes in here, where in it says that the plan would
> say
> that all the tables are scanned but in truth only the wanted table is
> scanned. But how would the time taken to run these 2 queries varry so
> much...
> Any ideas ? Options ?
> --
> Sr Apps Developer
|||/****** Object: Table [dbo].[Product_Fact_TmpTmSk88] Script Date:
7/27/2007 11:20:25 AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Product_Fact_TmpTmSk88]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[Product_Fact_TmpTmSk88]
GO
/****** Object: Table [dbo].[Usage_Fact_TmSk88] Script Date: 7/27/2007
11:20:25 AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Usage_Fact_TmSk88]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[Usage_Fact_TmSk88]
GO
/****** Object: Table [dbo].[Product_Fact_TmpTmSk88] Script Date:
7/27/2007 11:20:27 AM ******/
CREATE TABLE [dbo].[Product_Fact_TmpTmSk88] (
[Fact_Sk] [numeric](18, 0) IDENTITY (150000000, 1) NOT NULL ,
[Time_Sk] [numeric](18, 0) NOT NULL ,
[Main_Btn_Sk] [numeric](18, 0) NOT NULL ,
[Entity_Sk] [numeric](18, 0) NOT NULL ,
[AssocChgCd_Sk] [numeric](18, 0) NOT NULL ,
[Product_Sk] [numeric](18, 0) NOT NULL ,
[Unit_Rate] [varchar] (13) NOT NULL ,
[Prod_Qty] [int] NULL ,
[Rev_Amt] [decimal](38, 2) NULL ,
[DateStamp] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Usage_Fact_TmSk88] Script Date: 7/27/2007
11:20:29 AM ******/
CREATE TABLE [dbo].[Usage_Fact_TmSk88] (
[Fact_Sk] [numeric](18, 0) IDENTITY (170000000, 1) NOT NULL ,
[Time_Sk] [numeric](18, 0) NOT NULL ,
[Main_Btn_Sk] [numeric](18, 0) NOT NULL ,
[Usage_Sk] [numeric](18, 0) NOT NULL ,
[Billable_Calls] [decimal](11, 0) NULL ,
[Billable_MOU] [decimal](11, 0) NULL ,
[Billable_Revenue] [decimal](13, 2) NULL ,
[Billed_Calls] [decimal](18, 0) NULL ,
[Billed_MOU] [decimal](11, 0) NULL ,
[Billed_Revenue] [decimal](13, 2) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product_Fact_TmpTmSk88] WITH NOCHECK ADD
CONSTRAINT [PK_Product_Fact_TmpTmSk88] PRIMARY KEY CLUSTERED
(
[Time_Sk],
[Fact_Sk]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Usage_Fact_TmSk88] WITH NOCHECK ADD
CONSTRAINT [PK_Usage_Fact_TmpTmSk88] PRIMARY KEY CLUSTERED
(
[Time_Sk],
[Fact_Sk]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product_Fact_TmpTmSk88] WITH NOCHECK ADD
CHECK ([Time_Sk] = 88)
GO
ALTER TABLE [dbo].[Usage_Fact_TmSk88] WITH NOCHECK ADD
CHECK ([Time_Sk] = 88)
GO
CREATE UNIQUE INDEX [UK_Product_Fact_TmpTmSk88] ON
[dbo].[Product_Fact_TmpTmSk88]([Main_Btn_Sk], [Entity_Sk], [AssocChgCd_Sk],
[Product_Sk], [Unit_Rate]) ON [PRD_INDXGRP]
GO
CREATE INDEX [Idx_PfTmsk88_TimeSK] ON
[dbo].[Product_Fact_TmpTmSk88]([Time_Sk]) ON [PRD_INDXGRP]
GO
CREATE INDEX [Idx_PfTmsk88_MainBTNSK] ON
[dbo].[Product_Fact_TmpTmSk88]([Main_Btn_Sk]) ON [PRD_INDXGRP]
GO
CREATE INDEX [Idx_PfTmsk88_ProductSK] ON
[dbo].[Product_Fact_TmpTmSk88]([Product_Sk]) ON [PRD_INDXGRP]
GO
CREATE UNIQUE INDEX [UK_Usage_Fact_TmpTmSk88] ON
[dbo].[Usage_Fact_TmSk88]([Main_Btn_Sk], [Usage_Sk]) ON [PRD_INDXGRP]
GO
CREATE INDEX [Idx_UfTmsk88_MainBtnSk] ON
[dbo].[Usage_Fact_TmSk88]([Main_Btn_Sk]) ON [PRD_INDXGRP]
GO
CREATE INDEX [Idx_UfTmsk88_UsageSk] ON
[dbo].[Usage_Fact_TmSk88]([Usage_Sk]) ON [PRD_INDXGRP]
GO
I did some simple checks as to run the Fact table with a join to just the
dimension table...If i were to use a join or a subquery, it runs a scan on
all the fact tables...BUt if i run the query on usage_fact with a where
condition as time_sk in (81,82), it just looks at the 81 and 82 table
Sr Apps Developer
"Dan Guzman" wrote:

> As long as your view and underlying tables are setup correctly (primary key
> and check constraint), the execution plan will contain a startup expression
> to eliminate unneeded tables during execution.
>
> I expect this is due to different execution plans. Can you post DDL
> (simplified CREATE TABLE and VIEW) that shows the issue? I recall that
> there might be cases where elimination doesn't occur via joins but indexes
> can still be used to efficiently return data.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sabin" <sabin@.discussions.microsoft.com> wrote in message
> news:D9C7FF35-961C-481E-BF88-9D8933CD0332@.microsoft.com...
>
|||the view creation ddl...
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: View dbo.Product_Fact Script Date: 7/27/2007 11:21:18 AM
******/
CREATE View [dbo].[Product_Fact] With SchemaBinding
AS
Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Produc t_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk88]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Produc t_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk87]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Produc t_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk86]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Produc t_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk85]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Produc t_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk84]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Produc t_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk83]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Produc t_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk82]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: View dbo.Usage_Fact Script Date: 7/27/2007 11:21:18 AM
******/
CREATE View [dbo].[Usage_Fact] With SchemaBinding
As
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[Usage_Fact_TmSk88]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[Usage_Fact_TmSk87]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[Usage_Fact_TmSk86]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[Usage_Fact_TmSk85]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[Usage_Fact_TmSk84]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[Usage_Fact_TmSk83]
/*
Union All
Select * From [dbo].[Usage_Fact_TmSk82]
Union All
Select * From [dbo].[Usage_Fact_TmSk81]
Union All
Select * From [dbo].[Usage_Fact_TmSk80]
Union All
Select * From [dbo].[Usage_Fact_TmSk79]
Union All
Select * From [dbo].[Usage_Fact_TmSk78]
Union All
Select * From [dbo].[Usage_Fact_TmSk77]
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Sr Apps Developer
"sabin" wrote:
[vbcol=seagreen]
> /****** Object: Table [dbo].[Product_Fact_TmpTmSk88] Script Date:
> 7/27/2007 11:20:25 AM ******/
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Product_Fact_TmpTmSk88]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Product_Fact_TmpTmSk88]
> GO
> /****** Object: Table [dbo].[Usage_Fact_TmSk88] Script Date: 7/27/2007
> 11:20:25 AM ******/
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Usage_Fact_TmSk88]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Usage_Fact_TmSk88]
> GO
> /****** Object: Table [dbo].[Product_Fact_TmpTmSk88] Script Date:
> 7/27/2007 11:20:27 AM ******/
> CREATE TABLE [dbo].[Product_Fact_TmpTmSk88] (
> [Fact_Sk] [numeric](18, 0) IDENTITY (150000000, 1) NOT NULL ,
> [Time_Sk] [numeric](18, 0) NOT NULL ,
> [Main_Btn_Sk] [numeric](18, 0) NOT NULL ,
> [Entity_Sk] [numeric](18, 0) NOT NULL ,
> [AssocChgCd_Sk] [numeric](18, 0) NOT NULL ,
> [Product_Sk] [numeric](18, 0) NOT NULL ,
> [Unit_Rate] [varchar] (13) NOT NULL ,
> [Prod_Qty] [int] NULL ,
> [Rev_Amt] [decimal](38, 2) NULL ,
> [DateStamp] [datetime] NULL
> ) ON [PRIMARY]
> GO
> /****** Object: Table [dbo].[Usage_Fact_TmSk88] Script Date: 7/27/2007
> 11:20:29 AM ******/
> CREATE TABLE [dbo].[Usage_Fact_TmSk88] (
> [Fact_Sk] [numeric](18, 0) IDENTITY (170000000, 1) NOT NULL ,
> [Time_Sk] [numeric](18, 0) NOT NULL ,
> [Main_Btn_Sk] [numeric](18, 0) NOT NULL ,
> [Usage_Sk] [numeric](18, 0) NOT NULL ,
> [Billable_Calls] [decimal](11, 0) NULL ,
> [Billable_MOU] [decimal](11, 0) NULL ,
> [Billable_Revenue] [decimal](13, 2) NULL ,
> [Billed_Calls] [decimal](18, 0) NULL ,
> [Billed_MOU] [decimal](11, 0) NULL ,
> [Billed_Revenue] [decimal](13, 2) NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Product_Fact_TmpTmSk88] WITH NOCHECK ADD
> CONSTRAINT [PK_Product_Fact_TmpTmSk88] PRIMARY KEY CLUSTERED
> (
> [Time_Sk],
> [Fact_Sk]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Usage_Fact_TmSk88] WITH NOCHECK ADD
> CONSTRAINT [PK_Usage_Fact_TmpTmSk88] PRIMARY KEY CLUSTERED
> (
> [Time_Sk],
> [Fact_Sk]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Product_Fact_TmpTmSk88] WITH NOCHECK ADD
> CHECK ([Time_Sk] = 88)
> GO
> ALTER TABLE [dbo].[Usage_Fact_TmSk88] WITH NOCHECK ADD
> CHECK ([Time_Sk] = 88)
> GO
> CREATE UNIQUE INDEX [UK_Product_Fact_TmpTmSk88] ON
> [dbo].[Product_Fact_TmpTmSk88]([Main_Btn_Sk], [Entity_Sk], [AssocChgCd_Sk],
> [Product_Sk], [Unit_Rate]) ON [PRD_INDXGRP]
> GO
> CREATE INDEX [Idx_PfTmsk88_TimeSK] ON
> [dbo].[Product_Fact_TmpTmSk88]([Time_Sk]) ON [PRD_INDXGRP]
> GO
> CREATE INDEX [Idx_PfTmsk88_MainBTNSK] ON
> [dbo].[Product_Fact_TmpTmSk88]([Main_Btn_Sk]) ON [PRD_INDXGRP]
> GO
> CREATE INDEX [Idx_PfTmsk88_ProductSK] ON
> [dbo].[Product_Fact_TmpTmSk88]([Product_Sk]) ON [PRD_INDXGRP]
> GO
> CREATE UNIQUE INDEX [UK_Usage_Fact_TmpTmSk88] ON
> [dbo].[Usage_Fact_TmSk88]([Main_Btn_Sk], [Usage_Sk]) ON [PRD_INDXGRP]
> GO
> CREATE INDEX [Idx_UfTmsk88_MainBtnSk] ON
> [dbo].[Usage_Fact_TmSk88]([Main_Btn_Sk]) ON [PRD_INDXGRP]
> GO
> CREATE INDEX [Idx_UfTmsk88_UsageSk] ON
> [dbo].[Usage_Fact_TmSk88]([Usage_Sk]) ON [PRD_INDXGRP]
> GO
>
>
> I did some simple checks as to run the Fact table with a join to just the
> dimension table...If i were to use a join or a subquery, it runs a scan on
> all the fact tables...BUt if i run the query on usage_fact with a where
> condition as time_sk in (81,82), it just looks at the 81 and 82 table
>
> --
> Sr Apps Developer
>
> "Dan Guzman" wrote:
|||I went ahead a created a sample with just 1000 rows in each table...2 tables.
-- TimeSk 88
CREATE TABLE [dbo].[UsgFact_TmSk88] (
[Fact_Sk] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Time_Sk] [numeric](18, 0) NOT NULL ,
[Main_Btn_Sk] [numeric](18, 0) NOT NULL ,
[Usage_Sk] [numeric](18, 0) NOT NULL ,
[Billable_Calls] [decimal](11, 0) NULL ,
[Billable_MOU] [decimal](11, 0) NULL ,
[Billable_Revenue] [decimal](13, 2) NULL ,
[Billed_Calls] [decimal](18, 0) NULL ,
[Billed_MOU] [decimal](11, 0) NULL ,
[Billed_Revenue] [decimal](13, 2) NULL
) ON [PRIMARY]
Insert Into UsgFact_TmSk88
(Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billa ble_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,B illed_Revenue)
Select Top 1000
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From Usage_Fact_Tmsk88 ;
ALTER TABLE [dbo].[UsgFact_TmSk88] WITH CHECK ADD CONSTRAINT
[PK_UsgFact_TmpTmSk88] PRIMARY KEY CLUSTERED ([Time_Sk],[Fact_Sk]) ON
[PRIMARY]
ALTER TABLE [dbo].[UsgFact_TmSk88] WITH CHECK ADD CHECK ([Time_Sk] = 88)
-- TimeSk 87
CREATE TABLE [dbo].[UsgFact_TmSk87] (
[Fact_Sk] [numeric](18, 0) IDENTITY (20000000, 1) NOT NULL ,
[Time_Sk] [numeric](18, 0) NOT NULL ,
[Main_Btn_Sk] [numeric](18, 0) NOT NULL ,
[Usage_Sk] [numeric](18, 0) NOT NULL ,
[Billable_Calls] [decimal](11, 0) NULL ,
[Billable_MOU] [decimal](11, 0) NULL ,
[Billable_Revenue] [decimal](13, 2) NULL ,
[Billed_Calls] [decimal](18, 0) NULL ,
[Billed_MOU] [decimal](11, 0) NULL ,
[Billed_Revenue] [decimal](13, 2) NULL
) ON [PRIMARY]
--sp_help Usage_Fact_Tmsk87
Insert Into UsgFact_TmSk87
(Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billa ble_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,B illed_Revenue)
Select Top 1000
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From Usage_Fact_Tmsk87 ;
ALTER TABLE [dbo].[UsgFact_TmSk87] WITH CHECK ADD CONSTRAINT
[PK_UsgFact_TmpTmSk87] PRIMARY KEY CLUSTERED ([Time_Sk],[Fact_Sk]) ON
[PRIMARY]
ALTER TABLE [dbo].[UsgFact_TmSk87] WITH CHECK ADD CHECK ([Time_Sk] = 87)
Create View [dbo].[UsgFact] With SchemaBinding
As
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[UsgFact_TmSk88]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[UsgFact_TmSk87]
SET SHOWPLAN_ALL On
Select a.* From UsgFact a Where Time_Sk In (88,83);
Select a.* From UsgFact a Where Time_Sk In (88,83);
|--Compute
Scalar(DEFINE

[UsgFact_TmSk88].[Time_Sk]=[UsgFact_TmSk88].[Time_Sk],
[UsgFact_TmSk88].[Main_Btn_Sk]=[UsgFact_TmSk88].[Main_Btn_Sk],
[UsgFact_TmSk88].[Usage_Sk]=[UsgFact_TmSk88].[Usage_Sk],
[UsgFact_TmSk88].[Billable_Calls]=[UsgFact_TmSk88].[B
|--Clustered Index
Scan(OBJECT

[CVPT_B3].[dbo].[UsgFact_TmSk88].[PK_UsgFact_TmpTmSk88]))
Select a.* From UsgFact a Inner Join Time_Dimension b On b.Time_Sk =
a.Time_Sk and b.Year_Month Between '200704' and '200704';
Select a.* From UsgFact a Inner Join Time_Dimension b On b.Time_Sk =
a.Time_Sk and b.Year_Month Between '200704' and '200704';
|--Nested Loops(Inner Join, OUTER REFERENCES

[b].[Time_SK]))
|--Bookmark Lookup(BOOKMARK

[Bmk1000]),
OBJECT

[CVPT_B3].[dbo].[Time_Dimension] AS [b]))
| |--Index
Seek(OBJECT

[CVPT_B3].[dbo].[Time_Dimension].[Idx_TimeDim_YrMnth] AS [b]),
SEEK

[b].[Year_Month]='200704') ORDERED FORWARD)
|--Concatenation
|--Filter(WHERE

STARTUP EXPR([b].[Time_SK]=88)))
| |--Clustered Index
Seek(OBJECT

[CVPT_B3].[dbo].[UsgFact_TmSk88].[PK_UsgFact_TmpTmSk88]),
SEEK

[UsgFact_TmSk88].[Time_Sk]=[b].[Time_SK]) ORDERED FORWARD)
|--Filter(WHERE

STARTUP EXPR([b].[Time_SK]=87)))
|--Clustered Index
Seek(OBJECT

[CVPT_B3].[dbo].[UsgFact_TmSk87].[PK_UsgFact_TmpTmSk87]),
SEEK

[UsgFact_TmSk87].[Time_Sk]=[b].[Time_SK]) ORDERED FORWARD)
Select a.* From UsgFact a Inner Join Time_Dimension b On b.Time_Sk =
a.Time_Sk and b.Year_Month = '200704';
Select a.* From UsgFact a Inner Join Time_Dimension b On b.Time_Sk =
a.Time_Sk and b.Year_Month = '200704';
|--Nested Loops(Inner Join, OUTER REFERENCES