solution please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

solution please

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-02 : 04:12:46

Dear Experts,
after 2years of my journey with sql server i came to know that "if i'm using union and distinct in a query, my database design is poor"

now i'm asking one thing that
creating a view based on the tables which are there in union statement can i create a view? so that it gives performance?

ex: select.....from table2
union
select ........from table3
union
select ........from table4

now i'l create one view (updatable) for these three statements.so that i'l use the view in place of this.

am i correct?

please guide me.

Vinod
Even you learn 1%, Learn it with 100% confidence.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-02 : 04:16:34

Yes you can create view and if your three tables doesn't contain overlapping data you should preferably use UNION ALL instead of UNION.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-02 : 04:53:48

so the statement changes like this...
select * from view1
union all
select * from view2
union all
select * from view3

am i correct?

Vinod
Even you learn 1%, Learn it with 100% confidence.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-01-02 : 05:54:46

create one view (updatable)...

Maybe I'm getting this wrong....but I think you will have a problem with the updatable part.
You cannot issue an update statement against more than 1 table at a time....and I would have thought this would extend to views as well. Your view may work to display records, but I think it may hit a hiccup at the updating bit.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-02 : 07:47:57

what will happen if i update one particular table? the view will automatically updated. so is there any problem with taht? or i need to recompile the view?

Andrew,Harsh i need suggesions.because i'm goint to change the entire application based on this concept.

Vinod
Even you learn 1%, Learn it with 100% confidence.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-02 : 08:00:53

If you are directly updating the underlying tables there is no problem with this approach. But if you are making updates throught view, SQL won't allow it.

For e.g.

if your view definition is like this:

create view v1
as
select * from table1
union all
select * from table2
union all
select * from table3

Then, this Update statement will work:

UPDATE table1
set....

But not this:

UPDATE v1
set...

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-02 : 11:06:20

You can only update a union view if it is a partitioned view (See example below).

You can read about the requirements for partitioned views in SQL Server Books Online.


create table T_1 (
Seq int not null primary key clustered,
X int not null,
constraint T_1_PK_Range check (Seq between 0 and 10000 )
)
go
create table T_2 (
Seq int not null  primary key clustered,
X int not null,
constraint T_2_PK_Range check (Seq between 10001 and 20000 )
)
go
create table T_3 (
Seq int not null  primary key clustered,
X int not null,
constraint T_3_PK_Range check (Seq between 20001 and 30000 )
)
go
create view V_123
as
select * from  T_1
union all
select * from  T_2
union all
select * from  T_3
go
insert into V_123 select 01000 , 1
insert into V_123 select 11000 , 2
insert into V_123 select 21000 , 3
go
select * from V_123
go
update V_123 set X = X+1
go
select * from V_123
go
drop view V_123
go
drop table T_1
go
drop table T_2
go
drop table T_3

CODO ERGO SUM

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-02 : 23:09:24

Very Very Great Help Dear Experts....
thanks a lot
thank you very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources