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

 All Forums
 Site Related Forums
 Article Discussion
 Article: Partitioning the Data in a Table

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2000-09-14 : 11:49:33
Michael writes "1) Is it ever good database design practice (for speed sake, etc.) to essentially make copies of tables to hold a certain group of data?

For example, I have come across a database table that stores information for a housing subdivision; ie. lot number, lot size, lot price, etc. And the database to which this table belongs stores this data for many subdivisions. However, instead of having one table that stores the subdivision information for ALL subdivisions (and having some ID that represents the specific subdivision), this database has one table for each subdivision. For example, 'Clair Ridge Estates Subdivision Info' and another table 'Possum Bend Subdivision Info', etc, with each table having the exact same fields. And, if they needed another subdivision, they would make yet another copy and give it a unique name."

Yes, there are times this is a good idea. I like this question because it reminded me of one of my favorite features of SQL Server - partitioned views.

Article Link.

Starting Member

5 Posts

Posted - 2006-05-24 : 10:46:03
Hi SQLTeam.

I'm a long-time reader, but first time posting. The article is very good. I'm trying to apply this to a table with a child table. I have two tables for tracking customer transactions; tblTran(about 25million rows) and tblTranDetail(about 50 million rows). I want to archive the older transactions based on DateEffective in tblTran. I have created a view, vw_Tran to UNION the partitions as described in the article and the partition tables all have the appropriate constraints. It is still slow.

What types of actions should I use to optimize a JOINED Select between the view and the child table?
Should I also somehow partition the child table based on the foreign key? I would appreciate any suggestions.
Go to Top of Page

- Advertisement -