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.
| Author |
Topic |
|
AskSQLTeam
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. |
|
|
ja928
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. |
 |
|
|
|
|
|
|
|
Article: Partitioning the Data in a Table