SQL Server Performance

Partioned Views !DEPRECATED!

Discussion in 'SQL Server 2005 General Developer Questions' started by SIMONBA, Aug 31, 2007.

  1. SIMONBA New Member

    Advice in SQL On-Line help, informs that Partioned Views are now Deprecated in SQL 2005 and use of Partioned Tables Advised.
    Our 'Enterprise' Software Supplier has Client-Server JAVA + Server-Side JAVA [ajax] - forget about their 'C' apps! They are moving to S-S JAVA, slowly leaving C-S-JAVA as legacy.
    C-S-JAVA -> We, manually, create Custom Tables [_x] against Main Tables eg. [dbo].[traders] and [dbo].[traders_x] with one to one row match using keys/constraints, and add table into CustomFieldDefinitions.xml.
    S-S-JAVA -> They have retained Main Table [traders] and supplied 2 custom tables where we can add our custom fields: [dbo].[customer_custom] and [dbo].[supplier_custom], though an automatic DataEntity application, there is no entry required in CustomFieldDefinitions. Split based is on traders.tradertype field.
    Because of our heavy 'bespoke' of C-S-JAVA, some applications cannot, yet, be upgraded to preferred S-S-JAVA.
    Our software supplier's solution to running both JAVA methodsi tandem using custom tables is:
    Drop [_x] table eg. traders_x and replace with:
    Create VIEW traders_x [we must use same name to work with CustomFieldDefinitions]
    as
    select id,tradertype,UDFcol1, UDFcol2,.....FROM customers_custom
    UNION ALL
    select id,tradertype,UDFcol1, UDFcol2,.....FROM suppliers_custom
    GO
    And USE 'INSTEAD OF' TRIGGERS on VIEW to maintain new customers/suppliers_custom tables. I don't need any TRIGGERS on [_custom]
    Apart from NO Indexing which slows C-S-J apps down even more, this also us to migrate to S-S-J as our 'bespoke' is applied!
    QUESTION: HOW DOES A PARTIONED TABLE HELP ME if I can't use a Partioned VIEW ??
    Yes, I can write a plethoral of triggers to synchronise 'old' _x as a TABLE and 'new' _custom(s), Also Indexing will be allowed!
  2. alzdba Member

    can you point to your source for this anouncement ? [:^)]
    I cannot find this anouncement at http://msdn2.microsoft.com/en-us/library/ms143729.aspx
    There is still a huge diffence between the concept of a partitioned view and a partitioned table ! (federated databases vs split tables)
  3. satya Moderator

    To addup what Alzdba refers:
    Take the example where a partition arrangement covers a year of data split up according to months, where the date is used as a primary key. Partitioning the index this way speeds up date lookups since SQL Server can quickly determine where a given key may be in the index.
    If you're partitioning data that has a unique index, the column used for partitioning be the same used for the unique index key. If your unique partition index is a client ID number, for instance, that will be the same column used to partition the index key as well.
    In both the cases of partitioned view & partitioned table you should be considered about TEMPDB, as these indexes that are partitioned differently are built using different memory allocation schemes: a partition-aligned index is built with one sort table at a time, while a nonaligned index is built with all its sort tables at the same time.
  4. alzdba Member

  5. SIMONBA New Member

    It is a NOTE in SQL-SERVER Books On-Line [Transact SQL] under Create Views/Partioned Views. I would be happy if this was an error!
    Because we have FOUR Enterprise Databases on a Server [Archive/Live/Whatif/Training] the Software has a pointer for users to choose! I am stuck with having FOUR sets of Partioned Views, with Instead-Of-Triggers!
  6. alzdba Member

    Are you using the May 2007 version of books online ?
    (BOL is being updated with separate download !)
    With "create view" I can only find the note :
    The preferred method for partitioning data local to one server is through partitioned tables. For more information, see /msdn.microsoft.com/mshelp" />Partitioned Tables and Indexes.No trace of deprication [*-)]
  7. satya Moderator

    True, even on updated BOL July 2007 under deprecated features this Partitioned View is not mentioned.
  8. alzdba Member

  9. satya Moderator

    DOH, messed with SQL 2008 BOL here.... sorry.. my mistake [;)]
  10. alzdba Member

    so even in the beta bol for sql2008 they are not mentioning a deprication of partitioned views.
    That's a good sign [Y]
  11. SIMONBA New Member

    I HOPE I HAVE WRONG BOL version **
    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/aecc2f73-2ab5-4db9-b1e6-2f9e3c601fb9.htm
    ** Corporate Policy sets my Computer 'UP' with 8, yes eight, Giga-Byes on C:drive for O/S- all PROGRAMMES must go on D:drive, DATA on E:drive
    I cannot upgrade SQL-Server, and other large MicroSoft products, because they insist on using C:DRIVE. last week I got down to 8KB free on C:! I have to have PC-Laptop RE-BUILT EVERY TIME I need to upgrade [:mad:]
  12. satya Moderator

    You can try upgrading the BOL itself and it will not much space though.
  13. alzdba Member

  14. Expansion New Member

    MSDN tells in Sql Server 2005 BooksOnLine about CreateView ( http://msdn2.microsoft.com/en-us/library/ms187956.aspx )
    in Change History: 17 July 2006 Changed Content
    • Removed the statement that local partitioned views are available for backward compatibility only and are in the process of being deprecated.
    So in earlier versions was mentioned that local partitioned views were going to be deprecated.
    Obviously Microsoft changed their mind, probably because they realized that local Partitioned Views also are used in other ways then to partion data that belongs in one table.
    Now BooksOnline says:
    Partitioned Views

    A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers.
    Note:
    The preferred method for partitioning data local to one server is through partitioned tables. For more information, see Partitioned Tables and Indexes.

Share This Page