SQL Server Performance

View Problem

Discussion in 'T-SQL Performance Tuning for Developers' started by Linus Yeung, Jan 17, 2007.

  1. Linus Yeung New Member

    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
  2. Roji. P. Thomas New Member

    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
  3. Linus Yeung New Member

    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
  4. alzdba Member

    May I add ..
    Use UNION ALL so sqlserver skips the "sort unique" step of the union query !
  5. Linus Yeung New Member

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

  6. alzdba Member

    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 ???


  7. Linus Yeung New Member

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

Share This Page