SQL 2005 - Partitioning Tables

SQL 2005 - Partitioning Tables

• Alex Papadimoulis

  That was the official one. :-) We do not support partitioning on multiple columns directly. You have to use one of those approaches I outlined.
  
  

• KamranAmin

  Thanks for the reply.
  
  Still I cannot believe that we cannot partition on atleast two columns.
   
  I have asked the same question to guys who attended the training. They promised to get an "official" answer.
  
  If I find an answer, I will update you all.
  
  Baskar
  
  

• mk0991

  Hi,

  Can a Partition Function be used for this Or I am totally out of context here :-)

  I guess it is a new feature in 2005.

  Thanks

  Yogesh

  
  

• Chua Wen Ching

  Hi UmaChander ,

  Can We expect improvement in the performance in the query after partitioning the table

  I partitioned a table which is having 3 million records after that i dont see any change in the query performance,can you please give me some advice, i used 3 parttions on different filegroups and added index,please let me know soon as possible .

  Thansk in advance,

  Srinath Kasha

  
  

• Rattlerfxr

  There are couple of ways to do it:
  
  1. You can do this by using a computed column that combines the values from both columns and then using that as partitioning key
  2. You could vertically partition the table. For example,
  
  create table SalesOrder (
    SalesOrderId int not null primary key
  , SalesMan ...
  , OrderDate
  );
  -- this is your partitioned table and you partition based on SalesOrderId
  create table SalesOrder2 (
    SalesOrderId int not null references SalesOrder(SalesOrderId) primary key
  , ...
  );
  3. For completeness, you can also use CHECKSUM or BINARY_CHECKSUM in a computed column and use that as partitioning key. This method is best for random access and doesn't work that well for sequential or range type of queries since the rows in a range can be present in multiple partitions. Ex:
  
  create table SalesOrder (
  , SalesMan ...
  , OrderDate ...
  , (abs(binary_checksum(SalesMan, OrderDate))%13) as SalesOrderPartKey
  )
  
  
  Note that if you choose the first method, you can use a user-defined function in your partition function provide the partitioning key values. Lastly, partitioning shouldn't be considered as a performance improvement feature primarily. Partitioning in general requires lot of planning and schema design upfront especially given that we support only list based in SQL Server 2005.
  
  

• NewsReader

  Hi,

  We're facing with the same problem.

  We chose to implement it by combining two technics:

  1. Partition views.

  2. Partitions.

  We use the partition view on the date column, and each table in the partition view is partitioned by the integer column.

  A bit complicated, but should work (I hope).

  
  
  
• SQL 2005 - Partitioning Tables