Post reply

partitioned tables

  • July 31, 2003 at 9:29 am

    #106790

    Is it possible to implement partioned tables as in Oracle (using views or something) in SQL Server ?

    For the record: a partitioned table is a table that is "broken up" in function of one of his values (fi: time). A query or DML including time in the WHERE clause only has to search through the pieces within the requested range.

    Example: partitioning on time, 1 month / partition.

    The table (which is in fact is the addition of all partitions) appears in one part, but internally only the needed parts are taking into account. So a select on a range of two months only runs through 2 partitions.

    This approach is a huge advandage for datawarehouses.

    Thanks,

    Edited by - hbkdba on 07/31/2003 1:14:49 PM

  • July 31, 2003 at 9:51 am

    #468035

    Shure.

    Have a look in the BOL under "Creating a Partitioned View"

    It's easy if you have SQL2K Ent. Edition



    Bye
    Gabor

  • July 31, 2003 at 10:32 am

    #468048

    thanks, nyulg, do you have any experience with this on OLAP databases ?

    Edited by - hbkdba on 08/01/2003 05:10:25 AM

  • August 1, 2003 at 2:20 am

    #468145

    No unfortunatly only on OLTP.

    But on OLTP you can even use a so called distributed partitioned views where you can distribute your data betwenn several servers completly transparently to the application



    Bye
    Gabor

  • June 23, 2005 at 3:21 am

    #567808

    Does a partioned view work only in SQL ENT or will it work the same way in SQL Developer Edition.

    I mean I tried to create two tables with the required constraints and did a UNION ALL to create a view. Then i inserted some rows in the tables and did a select * from the union-view with a where clause which specified the value of the column on which the constraint was applied.

    The query plan should have denoted that the table which the data comes form should be the only one which is referenced but the query optimizer hit scanned both the tables for the data. This clearly doesn’t go with the way Microsoft describes the working of a partioned view.

    Heres my sample code

    CREATE TABLE aa1 (aa int, bb int,fname varchar(100) )

    CREATE TABLE aa2 (aa int, bb int,fname varchar(100))

    ALTER TABLE aa1 ADD CONSTRAINT CCaa1 CHECK (bb = 1)

    ALTER TABLE aa2 ADD CONSTRAINT CCaa2 CHECK (bb = 2)

    CREATE VIEW Subdivision

    AS    SELECT aa,bb,fname FROM aa1 UNION all   SELECT aa,bb,fname FROM aa2

    ==========================

    Is it because I am using a developer edition of SQL SERVER 2000?

    What I hear I forget, what I see I remember, what I do I understand

  • June 23, 2005 at 10:05 pm

    #568170

    nileshsane,

    My understanding is that the only "physical" difference between Developer and Enterprise editions is the "Edition" value you get from @@Version and the like. ("Logical" differences include price and limitations on where and how you may use it.)

    As for the partitioned view, I heard once that you have to create the CHECK constraints "with check"; if generated "with nocheck", SQL notes the fact and disregards them when figuring out the execution plan across views. What's the default for this when issued in an ALTER TABLE statement? (Me, I'd toss it in with the original CREATE TABLE statement, just to be sure.) A last lame guess, if there's very very little data in the tables (say 1 page each), SQL may just go ahead and read them both, figuring that working out fancy optimization routines garners no benefit.

    (These are all guesses. I'm familiar with partitioned views, but made the mistake of trying to explain them to developers and managers; the complexity scared them, and they wouldn't let me do this to "their" database.)

       Philip

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply