DDL : Moving an object to another segment

When databases contain more segments than the usual default, it is
often necessary to move tables between segments.


/* move a table, in its entirety, to the new segment */

drop index 'employee.idx_employee'
go
create clustered index on employee (emp_id) on new_seg
go


/* leave table where it is, but future allocations go to the new segment */

sp_placeobject new_seg , 'employee'
go


/* leave table where it is, but future allocations for the text column (employee_notes) go to the new segment*/

sp_placeobject new_seg , 'employee.temployee'
go


/* leave table where it is, but future allocations for the text column (resume) go to the new segment */

sp_placeobject new_seg , 'student.tstudent'
go