Partitioning tasks

Before you partition a table or index, you must prepare the disk devices and the segments or other storages devices that you will use for the partitions.

You can assign multiple partitions to a segment, but a partition can be assigned to only one segment. Assigning a single partition to each segment, with devices bound to individual segments, ensures the most benefit from parallelization and partitioning.

A typical order of partitioning tasks is:

Use disk init to initialize a new database device. disk init maps a physical disk device or operating system file to a logical database device name. For example:
```
use master
```
```
disk init
name ="pubs_dev1",
physname = "SYB_DEV01/pubs_dev",
size = "50M"
```
See Chapter 7, “Initializing Database Devices,” in the System Administration Guide: Volume 1.
Use alter database to assign the new device to the database containing the table or index to partition. For example:
```
use master
```
```
alter database pubs2 on pubs_dev1
```
(Optional) Use sp_addsegment to define the segments in the database. This example assumes that pubs_dev2, pubs_dev3, and pubs_dev4 have been created in a similar manner to pubs_dev1.
```
use pubs2
```
```
sp_addsegment seg1, pubs2, pubs_dev1
sp_addsegment seg2, pubs2, pubs_dev2
sp_addsegment seg3, pubs2, pubs_dev3
sp_addsegment seg4, pubs2, pubs_dev4
```

Drop all indexes from the table to partition. For example:

use pubs2

drop index salesdetail.titleidind,
		salesdetail.salesdetailind

Use sp_dboption to enable the bulk-copy of table or index data to the new partitions. For example:
```
use master
```
```
sp_dboption pubs2,"select into", true
```
Use alter table to repartition a table or create table to create a new table with partitions; use create index to create a new, partitioned index; or use select into to create a new, partitioned table from an existing table.

For example, to repartition the salesdetail table in pubs2:
```
use pubs2
```
```
alter table salesdetail partition by range (qty)
		(smsales values <= (1000) on seg1,
		medsales values <= (5000) on seg2,
		lgsales values <= (10000) on seg3)
```

Re-create indexes on the partitioned table. For example, on the salesdetail table:

use pubs2

create nonclustered index titleidind
		on salesdetail (title_id)

create nonclustered index salesdetailind
		on salesdetail (stor_id)