SQL Server Performance Forum – Threads Archive
View Problem
Hi I have a question about View in SQL Server Suppose I have two DBsDBA & 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
]]>