Presents your  
SQL SERVER E-NEWSLETTER for January 27, 2004 
 
<-------------------------------------------> 
  
INCREASE PERFORMANCE WITH HORIZONTAL PARTITIONS   
  
Horizontal partitioning is the process of creating at least two
physical files for a database's tables. The larger a table, the longer
it takes to scan. So the general motive behind horizontal partitioning
is to move seldom-used data into a second file.   
  
A common way to do this is to assign date ranges to each partition. For 
example, suppose that in a given application, the data of interest is 
almost always from the current year. (Other data is occasionally 
examined, so it must be available, but it doesn't need to be in the main
physical file.) You could create just two horizontal partitions--perhaps
Current and Archive--or you could create a partition for each year's 
data. It depends on your requirements.   
  
Books Online's information on creating a horizontal partition is quite 
good, so I'll just mention the steps here.   
  
* Create a publication with a Publisher (the current database) and a 
Subscriber (the archive database).   
  
* For each article that you want to horizontally partition, select 
Provide Support For Horizontal DTS Transformation Partitions.   
  
* Build the DTS package using the Transform Data Wizard. For each table 
to be partitioned, write an ActiveX script that defines the partition. 
In general, you have to determine whether any new or changed rows in the
Publisher need to be moved to the Subscriber.   
  
Books Online also offers a nice example of the ActiveX script you have 
to write for each article you want to partition. You can copy and paste 
the example and, with just a few additions, you'll be ready to go. 
(Search for "Defining a Horizontal Partition" in Books Online.)   
  
HOW HORIZONTAL PARTITIONING HELPED ME   
  
I recently examined one of my databases to see how I might benefit from 
horizontal partitioning. There are three tables of particular interest. 
All three have a DateEntered column, whose default is GetDate(). I made 
two partitions, using the last year as Current and everything before
that as Archive.   
  
Sales were down last year, ironically resulting in even better 
performance--the principal interest data was about one-tenth of the total 
data. The performance gain wasn't quite as good but still obvious. On the
other hand, multiyear queries are noticeably slower but are executed so 
infrequently that it doesn't matter. We gained significantly using 
horizontal partitions. In our case, they were based on date.   
  
Your case may differ, but I encourage you to explore it.   
  
Arthur Fuller has been developing database applications for 20 years. 
His experience includes Access ADPs, Microsoft SQL 2000, MySQL, and
.NET.   

----------------------------------------   
  
SQL SERVER FORUM: SHARE YOUR EXPERTISE WITH YOUR PEERS
  
The SQL Server Forum is the place where members can talk technique, 
argue methods, ask questions, and share experiences. Offer your views 
in
the 
SQL Server Forum.   
http://ct.com.com/click?q=2c-DafOIc0O7hoT2K5iXl10p7wUruIw 

Or send us an e-mail with "SQL: Horizontal partitions" in the subject
line.  mailto:Enews2@cnet.com   
  
----------------------------------------