View Problem

Hi I have a question about View in SQL Server

Suppose I have two DBs
DBA & DBB

in two DBs, there is a table tableA with same schema

In DBA.dbo.TableA, the data period is from 20050101 to 20051231
In DBB.dbo.TableA, the data period is from 20060101 to 20061231

so I create a view named TableA in another DB named DBC

the definiion of the view is as below:

select * from DBA.dbo.TableA
where datadate between 20050101 and 20051231
union
select * from DBB.dbo.TableA
where datadate between 20060101 and 20061231

so if i have a sql like below:

select * from DBC.dbo.TableA
where datadate between 20060201 and 20060331

so the question is that will SQL Server process the sql by ignoring this part in the view?
select * from DBB.dbo.TableA
where datadate between 20060101 and 20061231

or SQL Server will execute both part in the view?

Many thanks

Yes, SQL Server WILL execute both the parts of the union, but the good thing is that the filters supplied will be pushed deep down the plan. So If you have proper indexes on the table (one on datadate in this case), there wont be much of a performance issue.

Roji. P. Thomas
http://toponewithties.blogspot.com

Thank you Thomas

that means if I create an index on datadate
then although SQL Server process both parts, it takes only little effort on second part


am i right?

Many thanks

May I add ..
Use UNION ALL so sqlserver skips the "sort unique" step of the union query !

However, if union all is used, duplicate data cannot be resolved.

So you have duplicate rows within a part of the union ???
e.g. select * from DBA.dbo.TableA where datadate between 20050101 and 20051231 has duplicate rows ???

oh.................if bounded by date.........no duplicate data exists