Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008

    Unanswered: Which way is better?

    Hello, one of my table (activityTable) will have big number of rows up to several hundreds of million.
    To handle these data in more slimmer way, I have two different ideas in my mind.
    #1. Divide the activityTable by Dept into many smaller tables which will hold serveral millions of rows such as activityTable_Dept001, activityTable_Dept002, ... and so on.

    #2. Keep all data in one activityTable and whenever retrieving I use stored procedure to get only rows for certain Dept and have a new table (virtual) in the Database side, so that it looks like that we have table only for the Dept from front side.

    I personally like the idea #2, but is it realistic?

    Since I am a beginer, I ask anyone who read this to understand my stupid question.

    Thank you in advance.

  2. #2
    Join Date
    Nov 2002
    What version of sql server

    Have you looked into partion views distributed across multiple drives?

    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
    May 2004
    I would keep it in one table.

    Rather than partitioned views, in SQL 2005, you can use a PartitionFunction and PartitionScheme on a single table to partition it. That way you don't have the extra headache of managing N different tables each with their own indexes. SQL Server treats it as N different tables which can each be in a different filegroup (and hence different drive), but from your perspective it's just one table, with one set of indexes. Easier to manage IMO.

Posting Permissions

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