SQL Server 2005 Table Paritioning.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Server 2005 Table Paritioning.

Author

Topic

Hariarul
Posting Yak Master

160 Posts

Posted - 2006-11-02 : 00:24:08

Hi all,

We are planning to use the SQL Server table paritioning in our SQL Server 2005 database.

There is a very large table storing 12 years of data and it is frequently used by the application. This table would have 25 million records with approx 2 million records per year.

We are creating a index with below mentioned keys

PK_ID (INT)
Rec_CreateDate (Datetime)

If we create horizontal partition based on the column Rec_CreateDate, I have the following questions.

1. Will this improve the performance of the DML queries on the table?

2. Should I have the partitions on the same file groups or place in different file groups?

3. Will the query performance improve only if I include the column Rec_CreateDate in the DML
queries or the Pk_ID column itself is enough ?

Also , please give me if you have any other valuable suggestions for this scenario.

Thanks & Regards,

HHA

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-11-02 : 02:52:23

There was just recently a topic about this issue and if I'm not mistaken all you needed was a clustered index across all tables. As long as you query the clustered index for data from one of the partitions the other ones woun't get hit and you'll probably see a substantial performance gain. Let me see if I can find this topic...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"