SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Partitioning the Data in a Table		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts
Posted - 09/14/2000 :  11:49:33  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

USA
5 Posts
Posted - 05/24/2006 :  10:46:03  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000