I ve problem with union

[Bpk. Adi Wira Kusuma]

I use Sql Server 2000. I've structure like it:

Create table T1(
RID int primary key,
A varchar(8),
...
...
)

Create table T2(
RID int primary key,
A varchar(8),
...
...
)

To make easy to view, so I make a view like it

Create view VUni as
Select RID, A From T1
union

Select RID, A From T2

Now, when I execute

Select * From VUNI Where RID between 15 and 25

So the process needs a lot of times alias Very Slow. How to make it best
performance?

Oh yeach, it's other question, I want to select data from table which its
name is name dimanically (variable)

IF @YEAR=2000 Select * FROM T2000
ELSE
IF @YEAR=2001 Select * FROM T2001
ELSE
IF @YEAR=2002 Select * FROM T2002
ELSE
...
...

To make it compact, So how to the code?

Next question, How to avoid error? I execute like it

Select a/b from T2

seldom the statement is error, because value of filed "a" is 0. I want if
a/b is error calculation, so it give value 0 automatically. Can I do it?
Without I ve to make a function? Or any function (built in) to handle it?

[rpresser]

On Jun 18, 11:40 am, "Bpk. Adi Wira Kusuma"

<adi_wira_kus...@yahoo.com.sg> wrote:
> I use Sql Server 2000. I've structure like it:
>
> Create table T1(
> RID int primary key,
> A varchar(8),
> ...
> ...
> )
>
> Create table T2(
> RID int primary key,
> A varchar(8),
> ...
> ...
> )
>
> To make easy to view, so I make a view like it
>
> Create view VUni as
> Select RID, A From T1
> union
>
> Select RID, A From T2
>
> Now, when I execute
>
> Select * From VUNI Where RID between 15 and 25
>
> So the process needs a lot of times alias Very Slow. How to make it best
> performance?

UNION will eliminate any rows that are duplicated between the tables,
and this will slow things down. Change your view definition to

CREATE VIEW VUni AS
SELECT RID,A FROM T1
UNION ALL
SELECT RID,A FROM T2

> Oh yeach, it's other question, I want to select data from table which its
> name is name dimanically (variable)
>
> IF @YEAR=2000 Select * FROM T2000
> ELSE
> IF @YEAR=2001 Select * FROM T2001
> ELSE
> IF @YEAR=2002 Select * FROM T2002
> ELSE
> ...
> ...
>
> To make it compact, So how to the code?

I will decline to give an answer to this because I think it represents
a bad database design.

> Next question, How to avoid error? I execute like it
>
> Select a/b from T2
>
> seldom the statement is error, because value of filed "a" is 0. I want if
> a/b is error calculation, so it give value 0 automatically. Can I do it?
> Without I ve to make a function? Or any function (built in) to handle it?

SELECT CASE WHEN b=0 THEN 0 ELSE a/b END FROM T2

[Roy Harvey]

Can you use UNION ALL rather than UNION? UNION requires the
performance of a DISTINCT operation, which is significant overhead. If
UNION ALL (which returns all rows from both queries without trying to
remove duplicate rows) is sufficient the performance improvement may
be significant.

As for querying tables T2000, T2001, T2002, this appears to be a
database design problem. If all the tables are structured the same
they should almost certainly be one table. The messy code you have
started to create will only get messier and messier as more problems
with this design become evident. If the design can be fixed I
strongly suggest doing it before going any further.

As far as avoiding zerodivide errorsL

SELECT CASE WHEN B <> 0 THEN a/b ELSE NULL END
FROM T2

Roy Harvey
Beacon Falls, CT

[--CELKO--]

>> I want to select data from table whose name is dynamic (variable) <<

IF @year=2000 SELECT * FROM T2000
ELSE
IF @year=2001 SELECT * FROM T2001
ELSE
IF @year=2002 SELECT * FROM T2002
ELSE
...
<<

This design flaw is so bad it has a name, like a disease: "Attribute
Splitting". Instead of separate tables, you need one table with a
"<something>_year" column in it. You then use VIEWs or queries.

You probably also split an attribute in the first part of this
posting, and are trying to fix it with a UNION [ALL] construct.

[Bpk. Adi Wira Kusuma]

Thx for your answer. But for my second question, Please answer. I know it is
bad db design. But I wanna know how to make it compact. Just add my
programming sql knowledge

thx

"rpresser" <rpre...@gmail.com> wrote in message
news:1182181631....@w5g2000hsg.googlegroups.com...

[Tony Rogerson]

> This design flaw is so bad it has a name, like a disease: "Attribute
> Splitting". Instead of separate tables, you need one table with a
> "<something>_year" column in it. You then use VIEWs or queries.

Obviously you've never worked with large volumes of data, or locking
contention; if you had you'd know about partitioned views.

Your ignorance is so bad it has a name, 'Classroom Coder'.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1182182711.7...@n60g2000hse.googlegroups.com...

[Tony Rogerson]

> IF @YEAR=2000 Select * FROM T2000
> ELSE
> IF @YEAR=2001 Select * FROM T2001
> ELSE
> IF @YEAR=2002 Select * FROM T2002
> ELSE

Look at partitioned views, put a CHECK constraint on the year and the
optimiser will do the work for you; it will also make your app simpler
because it would just query a view vw_sales.....

Tony.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

"Bpk. Adi Wira Kusuma" <adi_wir...@yahoo.com.sg> wrote in message
news:%23vCm03b...@TK2MSFTNGP06.phx.gbl...

[Bpk. Adi Wira Kusuma]

hi, ALL........ UNION ALL less make it best performance. it is still slow.
Any other solutions?

because if the view for like it

Update TB1
set A=(select A from VUni where RID=TB1.RID)

So its process is slow. Or other words, How to give value of TB1.A quickly?
because the value of TB1.A can be from one of tables (T1 and T2)

"Roy Harvey" <roy_h...@snet.net> wrote in message
news:3cad73t27r63i81sg...@4ax.com...

[Hugo Kornelis]

On Mon, 18 Jun 2007 15:47:11 -0000, rpresser wrote:

>> Next question, How to avoid error? I execute like it
>>
>> Select a/b from T2
>>
>> seldom the statement is error, because value of filed "a" is 0. I want if
>> a/b is error calculation, so it give value 0 automatically. Can I do it?
>> Without I ve to make a function? Or any function (built in) to handle it?
>
>SELECT CASE WHEN b=0 THEN 0 ELSE a/b END FROM T2

Hi rpresser,

Or the shorter version:

SELECT COALESCE(a/NULLIF(b,0),0) FROM T2;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[rpresser]

On Jun 18, 6:51 pm, Hugo Kornelis

<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On Mon, 18 Jun 2007 15:47:11 -0000, rpresser wrote:

> >SELECT CASE WHEN b=0 THEN 0 ELSE a/b END FROM T2
>
> Hi rpresser,
>
> Or the shorter version:
>
> SELECT COALESCE(a/NULLIF(b,0),0) FROM T2;

Well, yours is fewer characters, but it's harder for my brain to
parse. :-) I'm sure there's little if any difference in execution
speed...

[Hugo Kornelis]

Hi rpresser,

Yeah, you're probably right. I still feel that NULLIF is one of the more
underrated functions SQL Server offers. Many people don't know it,
because it's so little used - and people who do know it tend not to use
it because they fear that others might not know it. Death spiral <g>

Mayby I should finally try to find some time to write that blog post
about NULLIF that has been on my mind for months already...