Results 1 to 7 of 7

Thread: selective view

  1. #1
    Join Date
    Sep 2002
    Posts
    30

    Unanswered: selective view

    Hi.. sorry i have another problem..

    I have partitioned a table into 12 tables, each represent a month. Sales_1 to Sales_12. I joined all of them in a view using union all

    The problem is when I put a query asking for a specific date, e.g. 1 nov to 31 dec, the view will read all the data from Sales_1 to Sales_12. I want the view to union only Sales_11 and Sales_12. Or more specific, the SQL Server will only read Sales_11 and Sales_12 table, ignoring the other 10.

    Another question is, I create 1 database for each year, and each database consists of 12 months and i want to make a view procedure that can span through these years. i.e. I want the data from 1 Nov 2002 to 5 Jan 2003 then it will union Y2002.dbo.Sales_11 to Y2003.dbo.Sales_12. I want to perform some calculation on these result like group or having. How can I make it?

    Thanks for help
    Last edited by prd00; 01-04-03 at 01:59.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you will get this "effect" -- in fact you will get something a lot faster -- by placing all your entries into one table and making sure the search field has an index

    a lot simpler queries, too


    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Sep 2002
    Posts
    30
    I've tried those before, and it takes a LOT more of time compared to now it divided into some tables. Transaction reached hundreds of thousand records, and it took a long time to calculate daily sales. NOT to mention when I put the stock flow by joining Sales, Received and Stock Transfer tables.

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Views can be specified over multiple databases.
    These views can be indexed in MSSQL2K Enterprise/Developer ver.
    If you don't have MSSQL2K Enterprise and you need summaries,
    why are you splitting tables horizontaly? Vertical split would work better.

  5. #5
    Join Date
    Sep 2002
    Posts
    30
    er... sorry, but what do you mean by vertical split? How to do it?? thx..
    I am using SQL Enterprise right now. How to index the view?? Thank you.

  6. #6
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    I was a little mistaken, indexed view cannot have UNION. See BOL "Creating an Indexed View".
    It can be used to bind vertically splitted tables (with join) or for indexing aggregates (group by).
    Vertically splitted table (I don't know if this is the right term) are 2 or more tables
    with the same primary key (PK,A,B)=(PK,A)+(PK,B). It's something between indexed and nonindexed table
    (fast full table scan) and you can also add indexes.

    I have found something better, partitioned view( distributed view on checked subrange PK tables ).
    See BOL "Creating a Partitioned View".

  7. #7
    Join Date
    Sep 2002
    Posts
    30
    woww... thx.. that's a good one..
    thx a lot..

Posting Permissions

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