Example of Splitting a Table Across Segments

You can improve performance for high-volume multiuser applications by splitting large tables across segments located on separate physical disks.

Note If the table is updated frequently, the balance of disk allocation can change over time. To guarantee that the speed advantages are maintained, it may be necessary to drop and re-create the table.

The following example shows how to split a table across two segments. This example uses a database called mydata and a table called riff to represent the user database and the table being split:

1. Select physical devices to use. In this example, the physical devices are C:\SQL60\DATA\MYDEV1.DAT and D:\DATA\MYDEV2.DAT.
2. Switch to the master database:

   use master

3. Add the devices mydisk1 and mydisk2. Allocate 4 MB for mydisk1 and 2 MB for mydisk2:

   DISK INIT
   NAME = 'mydisk1', 
   PHYSNAME = 'C:\sql60\data\mydev1.dat',
   VDEVNO = 7, 
   SIZE = 2048
   DISK INIT 
   
   NAME = 'mydisk2', 
   PHYSNAME = 'D:\sql60\data\mydev2.dat', 
   VDEVNO = 8, 
   SIZE = 1024 

4. Allocate additional storage space for the database by adding the mydisk1 and mydisk2 devices to mydata:

   alter database mydata
   on mydisk1 = 4, 
   mydisk2 = 2  

5. Switch to the mydata database:

   use mydata

6. Add a segment to mydisk1 and a segment to mydisk2:

   sp_addsegment seg_mydisk1, mydisk1 
   <execute>
   sp_addsegment seg_mydisk2, mydisk2
   <execute>

7. Create a third segment called seg_bothdisks and extend it to span both mydisk1 and mydisk2:

   sp_addsegment seg_bothdisks, mydisk1
   <execute>
   sp_extendsegment seg_bothdisks, mydisk2
   <execute>

8. Drop the SYSTEM and DEFAULT segments from the new devices:

   sp_dropsegment 'default', mydisk1 
   <execute>
   sp_dropsegment system, mydisk1
   <execute>
   sp_dropsegment 'default', mydisk2 
   <execute>
   sp_dropsegment system, mydisk2
   <execute>

9. Create the table and clustered index on the seg_mydisk1 segment.

   create table riff (ri_id id, ri_lname varchar(40), 
     ri_fname varchar(20), phone char(12), address varchar(40), 
     city varchar(20), state char(2), zip char(5), contract bit) 
     on seg_mydisk1 
   create clustered index ri_index 
     on riff(au_id) on seg_mydisk1

   In this case, the table is riff and the clustered index is ri_index.

10. Use the bcp utility to copy half of the rows into the riff table.
11. Place the riff table on the second segment (seg_mydisk):

    sp_placeobject seg_mydisk2, riff

12. Use bcp to copy the rest of the rows.
13. Place the riff table on the segment that spans both disks (seg_bothdisks):

    sp_placeobject seg_bothdisks, riff

    Important You must create the clustered index before the table is placed on the second segment. If you create the clustered index after the table is placed on the second segment, both the table and the index move to the second segment. If you create the index after creating seg_bothdisks, the allocation of disk space is unpredictable.