Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Partitioned View

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99_1 (
    	  Account	char(3)
    	, Ledger	char(4)
    	, PostDate	datetime
    	, PRIMARY KEY (Account, Ledger)
    	, CHECK(PostDate> '1/1/1999' and PostDate < '12/31/1999 23:59:59'))
    CREATE TABLE myTable99_2 (
    	  Account	char(3)
    	, Ledger	char(4)
    	, PostDate	datetime
    	, PRIMARY KEY (Account, Ledger)
    	, CHECK(PostDate> '1/1/2000' and PostDate < '12/31/2000 23:59:59'))
    CREATE TABLE myTable99_3 (
    	  Account	char(3)
    	, Ledger	char(4)
    	, PostDate	datetime
    	, PRIMARY KEY (Account, Ledger)
    	, CHECK(PostDate> '1/1/2001' and PostDate < '12/31/2001 23:59:59'))
    
    CREATE INDEX myTable99_1_IX ON MyTable99_1
    	(Account, Ledger)
    CREATE INDEX myTable99_2_IX ON MyTable99_2
    	(Account, Ledger)
    CREATE INDEX myTable99_3_IX ON MyTable99_3
    	(Account, Ledger)
    GO
    
    CREATE VIEW myView99
    AS
    SELECT 	  Account
    	, Ledger
    	, PostDate
      FROM myTable99_1
    UNION ALL
    SELECT 	  Account
    	, Ledger
    	, PostDate
      FROM myTable99_2
    UNION ALL
    SELECT 	  Account
    	, Ledger
    	, PostDate
      FROM myTable99_3
    GO
    
    SELECT * FROM myView99 WHERE Account = 1 AND Ledger = 1
    GO
    
    DROP VIEW myView99
    DROP TABLE myTable99_1,  myTable99_2,  myTable99_3
    GO
    OK, so I thought I knew this, but I'm looking for parallelism...not only am I no getting it, I'm getting an Index scan....is it becuse I didn't put any data in the table? I thought it would stil show my index seek with parallelism

    What up, homey?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, so here's a clue I do this

    INSERT INTO myView99 (Account, Ledger, PostDate)
    SELECT 1,1,'1/1/1999'


    and get this


    Server: Msg 4436, Level 16, State 12, Line 1
    UNION ALL view 'myView99' is not updatable because a partitioning column was not found.

    So I guess the contraint isn't correct...does the contraint have to be on the primary Key?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    there are some req's for this brett and there is one that is not in BOL. I tried to do this on a box with some pretty lame hardware a few months ago and I did not get much of a boost out of it. everyone was dissappointed. The order of the columns in the index has to be in a certain order I think.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ... is that all a partitioned view is? I thought there was more to it than that. Just a standard view definition where few tables are unioned? I guess I need to check BoL - I thought it was something that would require a bit of aclimatisation before I looked at using it. I just assumed it was related to a partitioned table....

    So really it is more about how SQL Server handles it behind the scenes than the definition itself?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In order for a partitioned view to actually handle the partitioning aspect, you need to have a constraint which formally defines what data belongs in each table. For example, you have to specify date ranges for one of the columns in a primary key constraint (an index isn't always good enough by itself).

    I'll cook up an example, but it will be a bit before I have a machine to test on... That may be tomorrow.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Posts
    88
    From BOL (and my practical experience)...

    "Partitioning columns must be a part of the primary key of the table."

    So your primary key constraint must include PostDate, even if that column is redundant from a uniqueness point of view.

Posting Permissions

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