Moving data between Table Partitions

Hi,
I have a requirement of moving some rows in one partition to another
partition. I'm planning to move data which has become old to a partition
which acts as archive. The two partitions are on different hard drives. Can
anyone help me solving this problem ?

Thanks,
RK

> I have a requirement of moving some rows in one partition to another
> partition. I'm planning to move data which has become old to a partition
> which acts as archive. The two partitions are on different hard drives.
> Can anyone help me solving this problem ?

You mention that the archive table is on a different drive so the tables
must therefore be in different filegroups. Consequently, you cannot use
SWITCH to move data directly between the partitions.

One method is to simply copy data to the archive table with INSERT...SELECT
and then SWITCH the old partition to an intermediate table (same filegroup)
and truncate the intermediate table.

If you database is in the SIMPLE or BULK_LOGGED recovery model, another
approach that requires less logging is SELECT...INTO. Mark the archive
table file group as the default file group and use SELECT...INTO to copy the
old data to a new table. Then build indexes on the newly loaded table,
create a check constraint to match the target partition boundaries and
SWITCH the new table to the target archive table partition. Finally, SWITCH
the old partition to an intermediate table (same filegroup) and truncate the
intermediate table.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Hi Dan,
Thanks for the reply. I'm not maintaining a different table for archive.
There is just one table. I have two partitions , one hold holds the latest
two years data and another hold all other data. The first partition will be
on a SAS drive which is faster and the second would be on a slower hard
drive. So at the end of every year I have to move one year's data to second
partition. Iam trying to achieve this using SPLIT and MERGE partition
functions. Please tell me if this approach is not correct.
Also I have one general question on partition. When we split a partition,
doesn't the size of the file which is used by partition decrease , because
we are moving data to a different partition ?

Thanks,
RK


"Dan Guzman" wrote in message
news:erdfaDLCJHA.2476-at-TK2MSFTNGP06.phx.gbl...
>> I have a requirement of moving some rows in one partition to another
>> partition. I'm planning to move data which has become old to a partition
>> which acts as archive. The two partitions are on different hard drives.
>> Can anyone help me solving this problem ?
>
> You mention that the archive table is on a different drive so the tables
> must therefore be in different filegroups. Consequently, you cannot use
> SWITCH to move data directly between the partitions.
>
> One method is to simply copy data to the archive table with
> INSERT...SELECT and then SWITCH the old partition to an intermediate table
> (same filegroup) and truncate the intermediate table.
>
> If you database is in the SIMPLE or BULK_LOGGED recovery model, another
> approach that requires less logging is SELECT...INTO. Mark the archive
> table file group as the default file group and use SELECT...INTO to copy
> the old data to a new table. Then build indexes on the newly loaded
> table, create a check constraint to match the target partition boundaries
> and SWITCH the new table to the target archive table partition. Finally,
> SWITCH the old partition to an intermediate table (same filegroup) and
> truncate the intermediate table.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>

> Thanks for the reply. I'm not maintaining a different table for archive.
> There is just one table. I have two partitions , one hold holds the latest
> two years data and another hold all other data.

Yes, you can get away with SPLIT and MERGE (without SWITCH). Be aware that
both the SPLIT and MERGE of non-empty partitions will require significant
data movement and logging so consider data availability and log space
requirements for the duration of the operation.

I'll assume your partition function contains only the single 2-year boundary
you stated. The details of the 2-partition approach depend on whether you
have a RANGE LEFT or RANGE RIGHT partition function. If you have a RANGE
LEFT, alter the partition scheme to specify the old filegroup as NEXT USED.
In the case of RANGE RIGHT, specify the recent filegroup as NEXT USED. Then
SPLIT the partition function at the new boundary. This will move data to
the newly created partition (the one that includes the specified boundary)
as needed. After the SPLIT, MERGE the old 2-year boundary, which will move
data to the remaining old partition.

Note that you can avoid unneeded data movement by creating multiple
partitions (one per year) for recent data. Ideally, SPLIT the last empty
partition to make room for future data while the current partition is still
being. Then you need only MERGE the oldest boundary to move data to the
archive filegroup. For example, after the start of 2009, SPLIT an empty
2010 partition at the future 2011 boundary. Then MERGE the old 2006
boundary. This will keep 2-3 years of recent data on the recent filegroup.
The maintenance of the function and scheme is pretty much the same as
described above except that the new boundary is in the future instead of the
past.

> Also I have one general question on partition. When we split a partition,
> doesn't the size of the file which is used by partition decrease , because
> we are moving data to a different partition ?

If the SPLIT moves data to a different filegroup, space will be vacated from
the other filegroup. However, this will not reduce the physical size of the
underlying file(s). You need to run DBCC SHRINKFILE for that. I advise you
not to shrink the recent filegroup file in your scenario though. The space
file will only be needed again when recent data is added. This shrink/grow
cycle can hurt performance unnecessarily.

If you need additional help, please post DDL for your partition scheme,
function and table.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"S V Ramakrishna" wrote in message
news:e2oGc4MCJHA.4916-at-TK2MSFTNGP03.phx.gbl...
> Hi Dan,
> Thanks for the reply. I'm not maintaining a different table for archive.
> There is just one table. I have two partitions , one hold holds the latest
> two years data and another hold all other data. The first partition will
> be on a SAS drive which is faster and the second would be on a slower hard
> drive. So at the end of every year I have to move one year's data to
> second partition. Iam trying to achieve this using SPLIT and MERGE
> partition functions. Please tell me if this approach is not correct.
> Also I have one general question on partition. When we split a partition,
> doesn't the size of the file which is used by partition decrease , because
> we are moving data to a different partition ?
>
> Thanks,
> RK
>
>
> "Dan Guzman" wrote in message
> news:erdfaDLCJHA.2476-at-TK2MSFTNGP06.phx.gbl...
>>> I have a requirement of moving some rows in one partition to another
>>> partition. I'm planning to move data which has become old to a partition
>>> which acts as archive. The two partitions are on different hard drives.
>>> Can anyone help me solving this problem ?
>>
>> You mention that the archive table is on a different drive so the tables
>> must therefore be in different filegroups. Consequently, you cannot use
>> SWITCH to move data directly between the partitions.
>>
>> One method is to simply copy data to the archive table with
>> INSERT...SELECT and then SWITCH the old partition to an intermediate
>> table (same filegroup) and truncate the intermediate table.
>>
>> If you database is in the SIMPLE or BULK_LOGGED recovery model, another
>> approach that requires less logging is SELECT...INTO. Mark the archive
>> table file group as the default file group and use SELECT...INTO to copy
>> the old data to a new table. Then build indexes on the newly loaded
>> table, create a check constraint to match the target partition boundaries
>> and SWITCH the new table to the target archive table partition. Finally,
>> SWITCH the old partition to an intermediate table (same filegroup) and
>> truncate the intermediate table.
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>>
>
>

Hi Dan,
Thank you very much for the prompt reply. I am doing all this for a data
warehouse project whose initial size is 2 tera bytes. We are planning to
implement NearLine storage kind of design in our data warehouse because the
size will increase tremendously over time. Please let me know if Partitions
are fine on a terabyte database.

Thanks,
RK
"Dan Guzman" wrote in message
news:uc30ZrQCJHA.4884-at-TK2MSFTNGP02.phx.gbl...
>> Thanks for the reply. I'm not maintaining a different table for archive.
>> There is just one table. I have two partitions , one hold holds the
>> latest two years data and another hold all other data.
>
> Yes, you can get away with SPLIT and MERGE (without SWITCH). Be aware
> that both the SPLIT and MERGE of non-empty partitions will require
> significant data movement and logging so consider data availability and
> log space requirements for the duration of the operation.
>
> I'll assume your partition function contains only the single 2-year
> boundary you stated. The details of the 2-partition approach depend on
> whether you have a RANGE LEFT or RANGE RIGHT partition function. If you
> have a RANGE LEFT, alter the partition scheme to specify the old filegroup
> as NEXT USED. In the case of RANGE RIGHT, specify the recent filegroup as
> NEXT USED. Then SPLIT the partition function at the new boundary. This
> will move data to the newly created partition (the one that includes the
> specified boundary) as needed. After the SPLIT, MERGE the old 2-year
> boundary, which will move data to the remaining old partition.
>
> Note that you can avoid unneeded data movement by creating multiple
> partitions (one per year) for recent data. Ideally, SPLIT the last empty
> partition to make room for future data while the current partition is
> still being. Then you need only MERGE the oldest boundary to move data to
> the archive filegroup. For example, after the start of 2009, SPLIT an
> empty 2010 partition at the future 2011 boundary. Then MERGE the old 2006
> boundary. This will keep 2-3 years of recent data on the recent
> filegroup. The maintenance of the function and scheme is pretty much the
> same as described above except that the new boundary is in the future
> instead of the past.
>
>> Also I have one general question on partition. When we split a partition,
>> doesn't the size of the file which is used by partition decrease ,
>> because we are moving data to a different partition ?
>
> If the SPLIT moves data to a different filegroup, space will be vacated
> from the other filegroup. However, this will not reduce the physical size
> of the underlying file(s). You need to run DBCC SHRINKFILE for that. I
> advise you not to shrink the recent filegroup file in your scenario
> though. The space file will only be needed again when recent data is
> added. This shrink/grow cycle can hurt performance unnecessarily.
>
> If you need additional help, please post DDL for your partition scheme,
> function and table.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "S V Ramakrishna" wrote in message
> news:e2oGc4MCJHA.4916-at-TK2MSFTNGP03.phx.gbl...
>> Hi Dan,
>> Thanks for the reply. I'm not maintaining a different table for archive.
>> There is just one table. I have two partitions , one hold holds the
>> latest two years data and another hold all other data. The first
>> partition will be on a SAS drive which is faster and the second would be
>> on a slower hard drive. So at the end of every year I have to move one
>> year's data to second partition. Iam trying to achieve this using SPLIT
>> and MERGE partition functions. Please tell me if this approach is not
>> correct.
>> Also I have one general question on partition. When we split a partition,
>> doesn't the size of the file which is used by partition decrease ,
>> because we are moving data to a different partition ?
>>
>> Thanks,
>> RK
>>
>>
>> "Dan Guzman" wrote in message
>> news:erdfaDLCJHA.2476-at-TK2MSFTNGP06.phx.gbl...
>>>> I have a requirement of moving some rows in one partition to another
>>>> partition. I'm planning to move data which has become old to a
>>>> partition which acts as archive. The two partitions are on different
>>>> hard drives. Can anyone help me solving this problem ?
>>>
>>> You mention that the archive table is on a different drive so the tables
>>> must therefore be in different filegroups. Consequently, you cannot use
>>> SWITCH to move data directly between the partitions.
>>>
>>> One method is to simply copy data to the archive table with
>>> INSERT...SELECT and then SWITCH the old partition to an intermediate
>>> table (same filegroup) and truncate the intermediate table.
>>>
>>> If you database is in the SIMPLE or BULK_LOGGED recovery model, another
>>> approach that requires less logging is SELECT...INTO. Mark the archive
>>> table file group as the default file group and use SELECT...INTO to copy
>>> the old data to a new table. Then build indexes on the newly loaded
>>> table, create a check constraint to match the target partition
>>> boundaries and SWITCH the new table to the target archive table
>>> partition. Finally, SWITCH the old partition to an intermediate table
>>> (same filegroup) and truncate the intermediate table.
>>>
>>> --
>>> Hope this helps.
>>>
>>> Dan Guzman
>>> SQL Server MVP
>>> http://weblogs.sqlteam.com/dang/
>>>
>>
>>
>

> Please let me know if Partitions are fine on a terabyte database.

Partitions help make large tables more manageable. This is a good reason to
consider partitioning. However, you also need to keep in mind how the table
is queried since queries that to not include the partitioning key will need
to touch all partitions. Also, you might find it easier to manage multiple
partitions/filegroups for your old data rather than just one large one.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"S V Ramakrishna" wrote in message
news:ezX1pNRCJHA.528-at-TK2MSFTNGP06.phx.gbl...
> Hi Dan,
> Thank you very much for the prompt reply. I am doing all this for a data
> warehouse project whose initial size is 2 tera bytes. We are planning to
> implement NearLine storage kind of design in our data warehouse because
> the size will increase tremendously over time. Please let me know if
> Partitions are fine on a terabyte database.
>
> Thanks,
> RK
> "Dan Guzman" wrote in message
> news:uc30ZrQCJHA.4884-at-TK2MSFTNGP02.phx.gbl...
>>> Thanks for the reply. I'm not maintaining a different table for archive.
>>> There is just one table. I have two partitions , one hold holds the
>>> latest two years data and another hold all other data.
>>
>> Yes, you can get away with SPLIT and MERGE (without SWITCH). Be aware
>> that both the SPLIT and MERGE of non-empty partitions will require
>> significant data movement and logging so consider data availability and
>> log space requirements for the duration of the operation.
>>
>> I'll assume your partition function contains only the single 2-year
>> boundary you stated. The details of the 2-partition approach depend on
>> whether you have a RANGE LEFT or RANGE RIGHT partition function. If you
>> have a RANGE LEFT, alter the partition scheme to specify the old
>> filegroup as NEXT USED. In the case of RANGE RIGHT, specify the recent
>> filegroup as NEXT USED. Then SPLIT the partition function at the new
>> boundary. This will move data to the newly created partition (the one
>> that includes the specified boundary) as needed. After the SPLIT, MERGE
>> the old 2-year boundary, which will move data to the remaining old
>> partition.
>>
>> Note that you can avoid unneeded data movement by creating multiple
>> partitions (one per year) for recent data. Ideally, SPLIT the last empty
>> partition to make room for future data while the current partition is
>> still being. Then you need only MERGE the oldest boundary to move data
>> to the archive filegroup. For example, after the start of 2009, SPLIT an
>> empty 2010 partition at the future 2011 boundary. Then MERGE the old
>> 2006 boundary. This will keep 2-3 years of recent data on the recent
>> filegroup. The maintenance of the function and scheme is pretty much the
>> same as described above except that the new boundary is in the future
>> instead of the past.
>>
>>> Also I have one general question on partition. When we split a
>>> partition, doesn't the size of the file which is used by partition
>>> decrease , because we are moving data to a different partition ?
>>
>> If the SPLIT moves data to a different filegroup, space will be vacated
>> from the other filegroup. However, this will not reduce the physical
>> size of the underlying file(s). You need to run DBCC SHRINKFILE for
>> that. I advise you not to shrink the recent filegroup file in your
>> scenario though. The space file will only be needed again when recent
>> data is added. This shrink/grow cycle can hurt performance
>> unnecessarily.
>>
>> If you need additional help, please post DDL for your partition scheme,
>> function and table.
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>>
>> "S V Ramakrishna" wrote in message
>> news:e2oGc4MCJHA.4916-at-TK2MSFTNGP03.phx.gbl...
>>> Hi Dan,
>>> Thanks for the reply. I'm not maintaining a different table for archive.
>>> There is just one table. I have two partitions , one hold holds the
>>> latest two years data and another hold all other data. The first
>>> partition will be on a SAS drive which is faster and the second would be
>>> on a slower hard drive. So at the end of every year I have to move one
>>> year's data to second partition. Iam trying to achieve this using SPLIT
>>> and MERGE partition functions. Please tell me if this approach is not
>>> correct.
>>> Also I have one general question on partition. When we split a
>>> partition, doesn't the size of the file which is used by partition
>>> decrease , because we are moving data to a different partition ?
>>>
>>> Thanks,
>>> RK
>>>
>>>
>>> "Dan Guzman" wrote in message
>>> news:erdfaDLCJHA.2476-at-TK2MSFTNGP06.phx.gbl...
>>>>> I have a requirement of moving some rows in one partition to another
>>>>> partition. I'm planning to move data which has become old to a
>>>>> partition which acts as archive. The two partitions are on different
>>>>> hard drives. Can anyone help me solving this problem ?
>>>>
>>>> You mention that the archive table is on a different drive so the
>>>> tables must therefore be in different filegroups. Consequently, you
>>>> cannot use SWITCH to move data directly between the partitions.
>>>>
>>>> One method is to simply copy data to the archive table with
>>>> INSERT...SELECT and then SWITCH the old partition to an intermediate
>>>> table (same filegroup) and truncate the intermediate table.
>>>>
>>>> If you database is in the SIMPLE or BULK_LOGGED recovery model, another
>>>> approach that requires less logging is SELECT...INTO. Mark the archive
>>>> table file group as the default file group and use SELECT...INTO to
>>>> copy the old data to a new table. Then build indexes on the newly
>>>> loaded table, create a check constraint to match the target partition
>>>> boundaries and SWITCH the new table to the target archive table
>>>> partition. Finally, SWITCH the old partition to an intermediate table
>>>> (same filegroup) and truncate the intermediate table.
>>>>
>>>> --
>>>> Hope this helps.
>>>>
>>>> Dan Guzman
>>>> SQL Server MVP
>>>> http://weblogs.sqlteam.com/dang/
>>>>
>>>
>>>
>>
>
>

Thank you Dan for the support. This conversation has really helped me a lot.

--
RK

"Dan Guzman" wrote in message
news:u8lR%23RXCJHA.524-at-TK2MSFTNGP06.phx.gbl...
>> Please let me know if Partitions are fine on a terabyte database.
>
> Partitions help make large tables more manageable. This is a good reason
> to consider partitioning. However, you also need to keep in mind how the
> table is queried since queries that to not include the partitioning key
> will need to touch all partitions. Also, you might find it easier to
> manage multiple partitions/filegroups for your old data rather than just
> one large one.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "S V Ramakrishna" wrote in message
> news:ezX1pNRCJHA.528-at-TK2MSFTNGP06.phx.gbl...
>> Hi Dan,
>> Thank you very much for the prompt reply. I am doing all this for a data
>> warehouse project whose initial size is 2 tera bytes. We are planning to
>> implement NearLine storage kind of design in our data warehouse because
>> the size will increase tremendously over time. Please let me know if
>> Partitions are fine on a terabyte database.
>>
>> Thanks,
>> RK
>> "Dan Guzman" wrote in message
>> news:uc30ZrQCJHA.4884-at-TK2MSFTNGP02.phx.gbl...
>>>> Thanks for the reply. I'm not maintaining a different table for
>>>> archive. There is just one table. I have two partitions , one hold
>>>> holds the latest two years data and another hold all other data.
>>>
>>> Yes, you can get away with SPLIT and MERGE (without SWITCH). Be aware
>>> that both the SPLIT and MERGE of non-empty partitions will require
>>> significant data movement and logging so consider data availability and
>>> log space requirements for the duration of the operation.
>>>
>>> I'll assume your partition function contains only the single 2-year
>>> boundary you stated. The details of the 2-partition approach depend on
>>> whether you have a RANGE LEFT or RANGE RIGHT partition function. If you
>>> have a RANGE LEFT, alter the partition scheme to specify the old
>>> filegroup as NEXT USED. In the case of RANGE RIGHT, specify the recent
>>> filegroup as NEXT USED. Then SPLIT the partition function at the new
>>> boundary. This will move data to the newly created partition (the one
>>> that includes the specified boundary) as needed. After the SPLIT, MERGE
>>> the old 2-year boundary, which will move data to the remaining old
>>> partition.
>>>
>>> Note that you can avoid unneeded data movement by creating multiple
>>> partitions (one per year) for recent data. Ideally, SPLIT the last
>>> empty partition to make room for future data while the current partition
>>> is still being. Then you need only MERGE the oldest boundary to move
>>> data to the archive filegroup. For example, after the start of 2009,
>>> SPLIT an empty 2010 partition at the future 2011 boundary. Then MERGE
>>> the old 2006 boundary. This will keep 2-3 years of recent data on the
>>> recent filegroup. The maintenance of the function and scheme is pretty
>>> much the same as described above except that the new boundary is in the
>>> future instead of the past.
>>>
>>>> Also I have one general question on partition. When we split a
>>>> partition, doesn't the size of the file which is used by partition
>>>> decrease , because we are moving data to a different partition ?
>>>
>>> If the SPLIT moves data to a different filegroup, space will be vacated
>>> from the other filegroup. However, this will not reduce the physical
>>> size of the underlying file(s). You need to run DBCC SHRINKFILE for
>>> that. I advise you not to shrink the recent filegroup file in your
>>> scenario though. The space file will only be needed again when recent
>>> data is added. This shrink/grow cycle can hurt performance
>>> unnecessarily.
>>>
>>> If you need additional help, please post DDL for your partition scheme,
>>> function and table.
>>>
>>> --
>>> Hope this helps.
>>>
>>> Dan Guzman
>>> SQL Server MVP
>>> http://weblogs.sqlteam.com/dang/
>>>
>>> "S V Ramakrishna" wrote in message
>>> news:e2oGc4MCJHA.4916-at-TK2MSFTNGP03.phx.gbl...
>>>> Hi Dan,
>>>> Thanks for the reply. I'm not maintaining a different table for
>>>> archive. There is just one table. I have two partitions , one hold
>>>> holds the latest two years data and another hold all other data. The
>>>> first partition will be on a SAS drive which is faster and the second
>>>> would be on a slower hard drive. So at the end of every year I have to
>>>> move one year's data to second partition. Iam trying to achieve this
>>>> using SPLIT and MERGE partition functions. Please tell me if this
>>>> approach is not correct.
>>>> Also I have one general question on partition. When we split a
>>>> partition, doesn't the size of the file which is used by partition
>>>> decrease , because we are moving data to a different partition ?
>>>>
>>>> Thanks,
>>>> RK
>>>>
>>>>
>>>> "Dan Guzman" wrote in message
>>>> news:erdfaDLCJHA.2476-at-TK2MSFTNGP06.phx.gbl...
>>>>>> I have a requirement of moving some rows in one partition to another
>>>>>> partition. I'm planning to move data which has become old to a
>>>>>> partition which acts as archive. The two partitions are on different
>>>>>> hard drives. Can anyone help me solving this problem ?
>>>>>
>>>>> You mention that the archive table is on a different drive so the
>>>>> tables must therefore be in different filegroups. Consequently, you
>>>>> cannot use SWITCH to move data directly between the partitions.
>>>>>
>>>>> One method is to simply copy data to the archive table with
>>>>> INSERT...SELECT and then SWITCH the old partition to an intermediate
>>>>> table (same filegroup) and truncate the intermediate table.
>>>>>
>>>>> If you database is in the SIMPLE or BULK_LOGGED recovery model,
>>>>> another approach that requires less logging is SELECT...INTO. Mark
>>>>> the archive table file group as the default file group and use
>>>>> SELECT...INTO to copy the old data to a new table. Then build indexes
>>>>> on the newly loaded table, create a check constraint to match the
>>>>> target partition boundaries and SWITCH the new table to the target
>>>>> archive table partition. Finally, SWITCH the old partition to an
>>>>> intermediate table (same filegroup) and truncate the intermediate
>>>>> table.
>>>>>
>>>>> --
>>>>> Hope this helps.
>>>>>
>>>>> Dan Guzman
>>>>> SQL Server MVP
>>>>> http://weblogs.sqlteam.com/dang/
>>>>>
>>>>
>>>>
>>>
>>
>>
>