Selecting from different tables

[manning_news@hotmail.com]

Using SQL2000. How do I format my select statement to choose one out
of 24 different tables? Each table is slightly different and I was
hoping I could use one select statement and format it on-the-fly
instead of using 24 different ones. I had in mind using a case
statement, something like this:

select * from
case when <input parameter> = 'something1' then tblSomething1
case when <input parameter> = 'something2' then tblSomething2
...and so on...

Thanks for any help.

[Erland Sommarskog]

Re: Selecting from different tables

(manning_news@h otmail.com) writes:[color=blue]
> Using SQL2000. How do I format my select statement to choose one out
> of 24 different tables? Each table is slightly different and I was
> hoping I could use one select statement and format it on-the-fly
> instead of using 24 different ones. I had in mind using a case
> statement, something like this:
>
> select * from
> case when <input parameter> = 'something1' then tblSomething1
> case when <input parameter> = 'something2' then tblSomething2
> ...and so on...[/color]

You can use dynamic SQL, but it's not sure that is a good idea.

One has to understand that a table definition is a little different from
a record or struct definition in a traditional language. A table comes
with indexes and statistics. Two table can look identical, yet the
optimizer may choose two different ways to compute:

SELECT typecol, COUNT(*)
FROM tbl1
WHERE grouptype = 'ABC'
GROUP BY typecol

SELECT typecol, COUNT(*)
FROM tbl2
WHERE grouptype = 'ABC'
GROUP BY typecol

Say both have a non-clustered index on grouptype and typecol is not
in the index. But in tbl1 only 3 values (of 4000) has grouptype = 'ABC'
whereas in tbl2 there are 3000 (of 4000) with that value. The optimizer
will pick different plans for these.

This is why there is no built-in construct in the language for choosing
one of many similar tables. When it comes to building the query plan,
each table is unique.

As I said, you could use dynamic SQL, and I have longer article on this
topic on my web site. http://www.sommarskog.se/dynamic_sql.html. The
section that is likely apply directly to your situation is
http://www.sommarskog.se/dynamic_sql.html#Dyn_table and the following
section.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[manning_news@hotmail.com]

Re: Selecting from different tables

Thanks for your reply. I think I'll stick with separate select
statements for all 24 tables.



Erland Sommarskog wrote:[color=blue]
> (manning_news@h otmail.com) writes:[color=green]
> > Using SQL2000. How do I format my select statement to choose one out
> > of 24 different tables? Each table is slightly different and I was
> > hoping I could use one select statement and format it on-the-fly
> > instead of using 24 different ones. I had in mind using a case
> > statement, something like this:
> >
> > select * from
> > case when <input parameter> = 'something1' then tblSomething1
> > case when <input parameter> = 'something2' then tblSomething2
> > ...and so on...[/color]
>
> You can use dynamic SQL, but it's not sure that is a good idea.
>
> One has to understand that a table definition is a little different from
> a record or struct definition in a traditional language. A table comes
> with indexes and statistics. Two table can look identical, yet the
> optimizer may choose two different ways to compute:
>
> SELECT typecol, COUNT(*)
> FROM tbl1
> WHERE grouptype = 'ABC'
> GROUP BY typecol
>
> SELECT typecol, COUNT(*)
> FROM tbl2
> WHERE grouptype = 'ABC'
> GROUP BY typecol
>
> Say both have a non-clustered index on grouptype and typecol is not
> in the index. But in tbl1 only 3 values (of 4000) has grouptype = 'ABC'
> whereas in tbl2 there are 3000 (of 4000) with that value. The optimizer
> will pick different plans for these.
>
> This is why there is no built-in construct in the language for choosing
> one of many similar tables. When it comes to building the query plan,
> each table is unique.
>
> As I said, you could use dynamic SQL, and I have longer article on this
> topic on my web site. http://www.sommarskog.se/dynamic_sql.html. The
> section that is likely apply directly to your situation is
> http://www.sommarskog.se/dynamic_sql.html#Dyn_table and the following
> section.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Ford Desperado]

Re: Selecting from different tables

let's say I have an SP like this:
if <input parameter> = 'something1' then
SELECT typecol, COUNT(*)
FROM tbl1
WHERE grouptype = 'ABC'
GROUP BY typecol
else
if <input parameter> = 'something2' then
SELECT typecol, COUNT(*)
FROM tbl2
WHERE grouptype = 'ABC'
GROUP BY typecol

in the body of the stored procedure. How could I look up the execution
plans for these 2 selects?

TIA

[Erland Sommarskog]

Re: Selecting from different tables

Ford Desperado (ford_desperado @yahoo.com) writes:[color=blue]
> let's say I have an SP like this:
> if <input parameter> = 'something1' then
> SELECT typecol, COUNT(*)
> FROM tbl1
> WHERE grouptype = 'ABC'
> GROUP BY typecol
> else
> if <input parameter> = 'something2' then
> SELECT typecol, COUNT(*)
> FROM tbl2
> WHERE grouptype = 'ABC'
> GROUP BY typecol
>
> in the body of the stored procedure. How could I look up the execution
> plans for these 2 selects?[/color]

The simplest is to run the procedure from Query Analyzer, and prior to
that press CTRL/K. That will give you a tab with the execution plans.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp