Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    3

    Unanswered: SQL SERVER 2000 Partitioned Views Bug

    Thanks in advance in reading this post ! I'm facing a situation in sql server 2000 sp4 with partitioned views.

    I have a partition views that joins about 10 tables, in each table there is a check constraint.

    For example, if a exec a select count(*) from VIEW where col1 = '20080101' , it goes for the table that has data for '20080101' .

    If I exec a select col1,col2,col3,col4 from VIEW where col1='20080101', it goes to all tables and make an index seek.

    I want the beaviour of query 1, beause it is just looking on 1 table and not one the 10.



    Thanks in advance !

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    How do you determine that the plan includes only the table you're after when you do a COUNT(*)? I just wrote a quick test against SQL2Ksp4, and in both queries the plan shows all participating tables being affected.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Apr 2008
    Posts
    3
    rdjabarov,

    For spectacular performance, if you use check constraints in the tables envolved in the view, when you execute a query for a specific value of the checked column, it will only check in the tables that have that value !

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yup, you're right, forgot about the check constraints. But if you look at statistics io, in both cases only the table that contains the actual data incurs any IO.
    What you were looking at was the query cost in the actual execution plan. The real physical cost of the query can be seen either with STATISTICS IO or with Profiler. On large resultsets COUNT(*) will be noticeably cheaper, because only slot array elements are counted, as opposed to returning all the fields that qualify the filtering condition.
    Last edited by rdjabarov; 05-12-08 at 12:02.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •