SQL Server Performance

Alternatives to Partitions in SQL Server 2000

Discussion in 'General Developer Questions' started by sqldev, Jan 21, 2009.

  1. sqldev New Member

    Hi,
    I would like to maintain the Current and Archival (few months old data) data separately.
    While maintaining Archival data, I could think of the following methods,
    1) Both current and archival tables can be maintained separately in the same database. The
    disadvantage in this method is that, database is grown very high.
    2) Current and archival tables can be maintained separately in two separate databases. The
    drawback in this method, data should be read and written into another database from the
    current one, which have affect the performance.
    3) Archival data can be split into 2 portions, latest archival in Current table's database
    and older archival data in another database. In this scenario, to read the entire archival
    contents, we need to read from two databases with the help of UNION clause in the SELECT statement. This could affect the performance.
    This can be achieved with the help of Partitions in other Databases. In SQL Server, what is the
    best way of handling this requirement in SQL Server 2000? Please give me all your inputs related
    to this topic, thank you.
    Regards,
    Dev
  2. ndinakar Member

    First off, Sql 2000 is out of support. So not sure why you are still in 2000. Once you move to 2005 or 2008, there is table partitioning available.
    Besides these, your archival strategy depends on your queries. How often do your users query data from both current and archived tables? Do they do it all the time? Or only few users do it? If only few need it, they can take the slight performance delay. you dont need to punish the rest of the users. its more of a business requirement that guides the strategy.
  3. satya Moderator

  4. Elisabeth Redei New Member

    Hi,
    As an aside, SQL Server 2000 is still supported - in Extended Support Phase - which means no non-security related hotfixes, no design or feature requests accepted and no no-charge incident support (still security hotfixes and paid support). This phase ends in 2013.
    http://support.microsoft.com/lifecycle/?p1=2852, "Microsoft Support Lifecycle".
    /Elisabeth
  5. moh_hassan20 New Member

    you can use partioned views in sql 2000

    you should create CHECK constraint on the partitioned fields of the table which is involved in the partitioned view
    in the following example , i create 2 tables in 2 different databases , with a partition based on the field c_date
    and created the partitioned view in third database
    that example :
    use AdventureWorks2008
    go
    drop table t_2007
    go

    create table t_2007
    (
    id int not null ,
    name varchar (50) ,
    c_date datetime NOT NULL
    CHECK (c_date BETWEEN '20070101' AND '20071231') -- partitioned key

    )
    go
    ALTER TABLE t_2007
    ADD PRIMARY KEY (id, c_date) --- partitioned key is part of PK
    go
    ------------------------------------
    -- second database
    use Northwind
    go

    -------------------
    drop table t_2008
    go

    create table t_2008
    (
    id int not null,
    name varchar (50) ,
    c_date datetime NOT NULL
    CHECK (c_date BETWEEN '20080101' AND '20081231')

    )

    go
    ALTER TABLE t_2008
    ADD PRIMARY KEY (id , c_date)
    go
    -----------------------------------
    -----third database
    use pubs
    go
    ----------
    drop view t_all
    go
    create view t_all
    as
    select * from AdventureWorks2008..t_2007
    union ALL
    select * from northwind..t_2008
    go
    -------
    insert into t_all values (1 , 'aaa' , '20081210') -- it is inserted into AdventureWorks2008..t_2007
    insert into t_all values (1 , 'bbb' , '20070510') -- it is inserted into northwind..t_2008

    -------------------------------
    --- check results
    select * from t_all
    select * from AdventureWorks2008..t_2007

    select * from northwind..t_2008
    ==============================
    (1 row(s) affected)

    (1 row(s) affected)
    id name c_date
    ----------- -------------------------------------------------- -----------------------
    1 bbb 2007-05-10 00:00:00.000

    (1 row(s) affected)

    id name c_date
    ----------- -------------------------------------------------- -----------------------
    1 aaa 2008-12-10 00:00:00.000

    (1 row(s) affected)

    id name c_date
    ----------- -------------------------------------------------- -----------------------
    1 bbb 2007-05-10 00:00:00.000
    1 aaa 2008-12-10 00:00:00.000

    (2 row(s) affected)


Share This Page