> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
script instead of graphical tools to make major database changes so that you
can see exactly what actions are being made to implement the change.
You can workaround the error by selecting Index-->Properties-->Storage and
then click the script button to generate the clustered index create script
to a new window. Then change the partition scheme as desired and add
"DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is the
most efficient way to move the clustered index since it avoids the sort.
Hope this helps.
Dan Guzman
SQL Server MVP
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy
> The error looks like a SMO/SSMS bug to me.
I submitted feedback on this along with the workaround:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=319924
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C3E3DEFA-79E3-40A5-9FC4-44D7C3C57A75@.microsoft.com...
> The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
> script instead of graphical tools to make major database changes so that
> you can see exactly what actions are being made to implement the change.
> You can workaround the error by selecting Index-->Properties-->Storage and
> then click the script button to generate the clustered index create script
> to a new window. Then change the partition scheme as desired and add
> "DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is
> the most efficient way to move the clustered index since it avoids the
> sort.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <randykilmer@.hotmail.com> wrote in message
> news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
>
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Friday, March 23, 2012
Moving a clustered index to a different partition scheme
Hello,
I am trying to use SSMS to move a clusted index to a new partitioned
scheme. I have done everything msdn said to do:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
When I change the partition scheme in the index properties (storage
tab) and hit ok or try to script it I get this error:
You cannot perform operation Remove on an object in state Existing.
(Microsoft.SqlServer.Smo)
I can not find this error anywhere.
We are trying to move the clustered index from a partitioned scheme on
a small sized drive to a new partitioned scheme on a larger drive.
Hoping this is will keep the partitions and move the data quickly, as
it a large table.
Any help would be greatly appreciated,
RandyTry just writing your own script. There are things that cannot be done
using SSMS. I don't know if this is one of them but writing your own script
is guaranteed to work (if you write it correctly using BOL as a guide).
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
script instead of graphical tools to make major database changes so that you
can see exactly what actions are being made to implement the change.
You can workaround the error by selecting Index-->Properties-->Storage and
then click the script button to generate the clustered index create script
to a new window. Then change the partition scheme as desired and add
"DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is the
most efficient way to move the clustered index since it avoids the sort.
Hope this helps.
Dan Guzman
SQL Server MVP
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> The error looks like a SMO/SSMS bug to me.
I submitted feedback on this along with the workaround:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=319924
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C3E3DEFA-79E3-40A5-9FC4-44D7C3C57A75@.microsoft.com...
>> We are trying to move the clustered index from a partitioned scheme on
>> a small sized drive to a new partitioned scheme on a larger drive.
>> Hoping this is will keep the partitions and move the data quickly, as
>> it a large table.
> The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
> script instead of graphical tools to make major database changes so that
> you can see exactly what actions are being made to implement the change.
> You can workaround the error by selecting Index-->Properties-->Storage and
> then click the script button to generate the clustered index create script
> to a new window. Then change the partition scheme as desired and add
> "DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is
> the most efficient way to move the clustered index since it avoids the
> sort.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <randykilmer@.hotmail.com> wrote in message
> news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
>> Hello,
>> I am trying to use SSMS to move a clusted index to a new partitioned
>> scheme. I have done everything msdn said to do:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
>> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
>> When I change the partition scheme in the index properties (storage
>> tab) and hit ok or try to script it I get this error:
>> You cannot perform operation Remove on an object in state Existing.
>> (Microsoft.SqlServer.Smo)
>> I can not find this error anywhere.
>> We are trying to move the clustered index from a partitioned scheme on
>> a small sized drive to a new partitioned scheme on a larger drive.
>> Hoping this is will keep the partitions and move the data quickly, as
>> it a large table.
>> Any help would be greatly appreciated,
>> Randy
>|||Hi,
Thank you for all the help, it is appreciated. We did write out the
script ourselves and learned more about it becasue of it.
This is the test script to setup the db, table, partitions and then
move the data:
use master
go
create database [test] on primary
( name = N'test', filename = N'C:\mssql\data\test.mdf' , size =26624kb , maxsize = unlimited, filegrowth = 1024kb )
log on
( name = N'test_log', filename = N'C:\mssql\data\test_log.ldf' , size
= 24632kb , maxsize = 1048576kb , filegrowth = 10%)
go
use test
go
alter database test
add filegroup test_d
go
alter database test
add file
(
name = test_d1,
filename = 'C:\mssql\data\test_d1.ndf',
size = 512 KB,
maxsize = 5MB,
filegrowth = 5%
) to filegroup test_d
--
--alter database test
--modify filegroup test_d DEFAULT
create table dbo.tst(fld int, fld2 char(1), fld3 datetime)
insert dbo.tst values(3,'Y','20071031')
insert dbo.tst values(3,'Y','20071130')
insert dbo.tst values(3,'Y','20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3) with( drop_existing
= on)
on old_pscheme(fld3)
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction()
split range('20071231')
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction()
split range('20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
dbo.tst
create table dbo.tst_new(fld int, fld2 char(1), fld3 datetime) on
old_pscheme(fld3)
insert dbo.tst_new values(3,'Y','20071231')
insert dbo.tst_new values(3,'Y','20071031')
insert dbo.tst_new values(3,'Y','20071130')
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction() split range('20071130')
--drop index dbo_tst_cx on dbo.tst_new
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( drop_existing = on)
on old_pscheme(fld3)
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071130');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071231');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071031');
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
sp_help 'dbo.tst_new'
select object_name(object_id) o_id, * from sys.partitions where
object_name(object_id) = 'tst_new';sql
I am trying to use SSMS to move a clusted index to a new partitioned
scheme. I have done everything msdn said to do:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
When I change the partition scheme in the index properties (storage
tab) and hit ok or try to script it I get this error:
You cannot perform operation Remove on an object in state Existing.
(Microsoft.SqlServer.Smo)
I can not find this error anywhere.
We are trying to move the clustered index from a partitioned scheme on
a small sized drive to a new partitioned scheme on a larger drive.
Hoping this is will keep the partitions and move the data quickly, as
it a large table.
Any help would be greatly appreciated,
RandyTry just writing your own script. There are things that cannot be done
using SSMS. I don't know if this is one of them but writing your own script
is guaranteed to work (if you write it correctly using BOL as a guide).
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
script instead of graphical tools to make major database changes so that you
can see exactly what actions are being made to implement the change.
You can workaround the error by selecting Index-->Properties-->Storage and
then click the script button to generate the clustered index create script
to a new window. Then change the partition scheme as desired and add
"DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is the
most efficient way to move the clustered index since it avoids the sort.
Hope this helps.
Dan Guzman
SQL Server MVP
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> The error looks like a SMO/SSMS bug to me.
I submitted feedback on this along with the workaround:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=319924
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C3E3DEFA-79E3-40A5-9FC4-44D7C3C57A75@.microsoft.com...
>> We are trying to move the clustered index from a partitioned scheme on
>> a small sized drive to a new partitioned scheme on a larger drive.
>> Hoping this is will keep the partitions and move the data quickly, as
>> it a large table.
> The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
> script instead of graphical tools to make major database changes so that
> you can see exactly what actions are being made to implement the change.
> You can workaround the error by selecting Index-->Properties-->Storage and
> then click the script button to generate the clustered index create script
> to a new window. Then change the partition scheme as desired and add
> "DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is
> the most efficient way to move the clustered index since it avoids the
> sort.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <randykilmer@.hotmail.com> wrote in message
> news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
>> Hello,
>> I am trying to use SSMS to move a clusted index to a new partitioned
>> scheme. I have done everything msdn said to do:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
>> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
>> When I change the partition scheme in the index properties (storage
>> tab) and hit ok or try to script it I get this error:
>> You cannot perform operation Remove on an object in state Existing.
>> (Microsoft.SqlServer.Smo)
>> I can not find this error anywhere.
>> We are trying to move the clustered index from a partitioned scheme on
>> a small sized drive to a new partitioned scheme on a larger drive.
>> Hoping this is will keep the partitions and move the data quickly, as
>> it a large table.
>> Any help would be greatly appreciated,
>> Randy
>|||Hi,
Thank you for all the help, it is appreciated. We did write out the
script ourselves and learned more about it becasue of it.
This is the test script to setup the db, table, partitions and then
move the data:
use master
go
create database [test] on primary
( name = N'test', filename = N'C:\mssql\data\test.mdf' , size =26624kb , maxsize = unlimited, filegrowth = 1024kb )
log on
( name = N'test_log', filename = N'C:\mssql\data\test_log.ldf' , size
= 24632kb , maxsize = 1048576kb , filegrowth = 10%)
go
use test
go
alter database test
add filegroup test_d
go
alter database test
add file
(
name = test_d1,
filename = 'C:\mssql\data\test_d1.ndf',
size = 512 KB,
maxsize = 5MB,
filegrowth = 5%
) to filegroup test_d
--
--alter database test
--modify filegroup test_d DEFAULT
create table dbo.tst(fld int, fld2 char(1), fld3 datetime)
insert dbo.tst values(3,'Y','20071031')
insert dbo.tst values(3,'Y','20071130')
insert dbo.tst values(3,'Y','20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3) with( drop_existing
= on)
on old_pscheme(fld3)
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction()
split range('20071231')
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction()
split range('20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
dbo.tst
create table dbo.tst_new(fld int, fld2 char(1), fld3 datetime) on
old_pscheme(fld3)
insert dbo.tst_new values(3,'Y','20071231')
insert dbo.tst_new values(3,'Y','20071031')
insert dbo.tst_new values(3,'Y','20071130')
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction() split range('20071130')
--drop index dbo_tst_cx on dbo.tst_new
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( drop_existing = on)
on old_pscheme(fld3)
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071130');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071231');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071031');
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
sp_help 'dbo.tst_new'
select object_name(object_id) o_id, * from sys.partitions where
object_name(object_id) = 'tst_new';sql
Moving a clustered index to a different partition scheme
Hello,
I am trying to use SSMS to move a clusted index to a new partitioned
scheme. I have done everything msdn said to do:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
When I change the partition scheme in the index properties (storage
tab) and hit ok or try to script it I get this error:
You cannot perform operation Remove on an object in state Existing.
(Microsoft.SqlServer.Smo)
I can not find this error anywhere.
We are trying to move the clustered index from a partitioned scheme on
a small sized drive to a new partitioned scheme on a larger drive.
Hoping this is will keep the partitions and move the data quickly, as
it a large table.
Any help would be greatly appreciated,
RandyTry just writing your own script. There are things that cannot be done
using SSMS. I don't know if this is one of them but writing your own script
is guaranteed to work (if you write it correctly using BOL as a guide).
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
script instead of graphical tools to make major database changes so that you
can see exactly what actions are being made to implement the change.
You can workaround the error by selecting Index-->Properties-->Storage and
then click the script button to generate the clustered index create script
to a new window. Then change the partition scheme as desired and add
"DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is the
most efficient way to move the clustered index since it avoids the sort.
Hope this helps.
Dan Guzman
SQL Server MVP
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> The error looks like a SMO/SSMS bug to me.
I submitted feedback on this along with the workaround:
https://connect.microsoft.com/SQLSe...=3199
24
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C3E3DEFA-79E3-40A5-9FC4-44D7C3C57A75@.microsoft.com...
> The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
> script instead of graphical tools to make major database changes so that
> you can see exactly what actions are being made to implement the change.
> You can workaround the error by selecting Index-->Properties-->Storage and
> then click the script button to generate the clustered index create script
> to a new window. Then change the partition scheme as desired and add
> "DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is
> the most efficient way to move the clustered index since it avoids the
> sort.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <randykilmer@.hotmail.com> wrote in message
> news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
>|||Hi,
Thank you for all the help, it is appreciated. We did write out the
script ourselves and learned more about it becasue of it.
This is the test script to setup the db, table, partitions and then
move the data:
use master
go
create database [test] on primary
( name = N'test', filename = N'C:\mssql\data\test.mdf' , size =
26624kb , maxsize = unlimited, filegrowth = 1024kb )
log on
( name = N'test_log', filename = N'C:\mssql\data\test_log.ldf' , size
= 24632kb , maxsize = 1048576kb , filegrowth = 10%)
go
use test
go
alter database test
add filegroup test_d
go
alter database test
add file
(
name = test_d1,
filename = 'C:\mssql\data\test_d1.ndf',
size = 512 KB,
maxsize = 5MB,
filegrowth = 5%
) to filegroup test_d
--
--alter database test
--modify filegroup test_d DEFAULT
create table dbo.tst(fld int, fld2 char(1), fld3 datetime)
insert dbo.tst values(3,'Y','20071031')
insert dbo.tst values(3,'Y','20071130')
insert dbo.tst values(3,'Y','20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3) with( drop_existing
= on)
on old_pscheme(fld3)
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction()
split range('20071231')
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction()
split range('20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
dbo.tst
create table dbo.tst_new(fld int, fld2 char(1), fld3 datetime) on
old_pscheme(fld3)
insert dbo.tst_new values(3,'Y','20071231')
insert dbo.tst_new values(3,'Y','20071031')
insert dbo.tst_new values(3,'Y','20071130')
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction() split range('20071130')
--drop index dbo_tst_cx on dbo.tst_new
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( drop_existing = on)
on old_pscheme(fld3)
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071130');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071231');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071031');
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
sp_help 'dbo.tst_new'
select object_name(object_id) o_id, * from sys.partitions where
object_name(object_id) = 'tst_new';
I am trying to use SSMS to move a clusted index to a new partitioned
scheme. I have done everything msdn said to do:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
When I change the partition scheme in the index properties (storage
tab) and hit ok or try to script it I get this error:
You cannot perform operation Remove on an object in state Existing.
(Microsoft.SqlServer.Smo)
I can not find this error anywhere.
We are trying to move the clustered index from a partitioned scheme on
a small sized drive to a new partitioned scheme on a larger drive.
Hoping this is will keep the partitions and move the data quickly, as
it a large table.
Any help would be greatly appreciated,
RandyTry just writing your own script. There are things that cannot be done
using SSMS. I don't know if this is one of them but writing your own script
is guaranteed to work (if you write it correctly using BOL as a guide).
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
script instead of graphical tools to make major database changes so that you
can see exactly what actions are being made to implement the change.
You can workaround the error by selecting Index-->Properties-->Storage and
then click the script button to generate the clustered index create script
to a new window. Then change the partition scheme as desired and add
"DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is the
most efficient way to move the clustered index since it avoids the sort.
Hope this helps.
Dan Guzman
SQL Server MVP
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> The error looks like a SMO/SSMS bug to me.
I submitted feedback on this along with the workaround:
https://connect.microsoft.com/SQLSe...=3199
24
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C3E3DEFA-79E3-40A5-9FC4-44D7C3C57A75@.microsoft.com...
> The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
> script instead of graphical tools to make major database changes so that
> you can see exactly what actions are being made to implement the change.
> You can workaround the error by selecting Index-->Properties-->Storage and
> then click the script button to generate the clustered index create script
> to a new window. Then change the partition scheme as desired and add
> "DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is
> the most efficient way to move the clustered index since it avoids the
> sort.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <randykilmer@.hotmail.com> wrote in message
> news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
>|||Hi,
Thank you for all the help, it is appreciated. We did write out the
script ourselves and learned more about it becasue of it.
This is the test script to setup the db, table, partitions and then
move the data:
use master
go
create database [test] on primary
( name = N'test', filename = N'C:\mssql\data\test.mdf' , size =
26624kb , maxsize = unlimited, filegrowth = 1024kb )
log on
( name = N'test_log', filename = N'C:\mssql\data\test_log.ldf' , size
= 24632kb , maxsize = 1048576kb , filegrowth = 10%)
go
use test
go
alter database test
add filegroup test_d
go
alter database test
add file
(
name = test_d1,
filename = 'C:\mssql\data\test_d1.ndf',
size = 512 KB,
maxsize = 5MB,
filegrowth = 5%
) to filegroup test_d
--
--alter database test
--modify filegroup test_d DEFAULT
create table dbo.tst(fld int, fld2 char(1), fld3 datetime)
insert dbo.tst values(3,'Y','20071031')
insert dbo.tst values(3,'Y','20071130')
insert dbo.tst values(3,'Y','20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3) with( drop_existing
= on)
on old_pscheme(fld3)
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction()
split range('20071231')
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction()
split range('20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
dbo.tst
create table dbo.tst_new(fld int, fld2 char(1), fld3 datetime) on
old_pscheme(fld3)
insert dbo.tst_new values(3,'Y','20071231')
insert dbo.tst_new values(3,'Y','20071031')
insert dbo.tst_new values(3,'Y','20071130')
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction() split range('20071130')
--drop index dbo_tst_cx on dbo.tst_new
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( drop_existing = on)
on old_pscheme(fld3)
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071130');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071231');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071031');
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
sp_help 'dbo.tst_new'
select object_name(object_id) o_id, * from sys.partitions where
object_name(object_id) = 'tst_new';
Monday, March 19, 2012
move table that has no index from one Filegroup to another
Is there a better way to move a huge table that has no index on it from one
Filegroup to another ? Using SQL 2000The best way is to create a clustered index. Otherwise, you have to create a
new table, copy all of the data, drop the old table, rename the new one, and
I did not even mention any referential integrity issues.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#MS1ZX#VDHA.2168@.TK2MSFTNGP09.phx.gbl...
> Is there a better way to move a huge table that has no index on it from
one
> Filegroup to another ? Using SQL 2000
>|||And what if you had a clustered index created that had references to it ?
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uxzo5T$VDHA.2360@.TK2MSFTNGP12.phx.gbl...
> The best way is to create a clustered index. Otherwise, you have to create
a
> new table, copy all of the data, drop the old table, rename the new one,
and
> I did not even mention any referential integrity issues.
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:#MS1ZX#VDHA.2168@.TK2MSFTNGP09.phx.gbl...
> > Is there a better way to move a huge table that has no index on it from
> one
> > Filegroup to another ? Using SQL 2000
> >
> >
>|||Well, the table is avaiable for reading, but not for writing during the
index rebuild, thus the RI is maintained.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u#At6p$VDHA.1280@.tk2msftngp13.phx.gbl...
> And what if you had a clustered index created that had references to it ?
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uxzo5T$VDHA.2360@.TK2MSFTNGP12.phx.gbl...
> > The best way is to create a clustered index. Otherwise, you have to
create
> a
> > new table, copy all of the data, drop the old table, rename the new one,
> and
> > I did not even mention any referential integrity issues.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > Please reply only to the newsgroups.
> > PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:#MS1ZX#VDHA.2168@.TK2MSFTNGP09.phx.gbl...
> > > Is there a better way to move a huge table that has no index on it
from
> > one
> > > Filegroup to another ? Using SQL 2000
> > >
> > >
> >
> >
>
Filegroup to another ? Using SQL 2000The best way is to create a clustered index. Otherwise, you have to create a
new table, copy all of the data, drop the old table, rename the new one, and
I did not even mention any referential integrity issues.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#MS1ZX#VDHA.2168@.TK2MSFTNGP09.phx.gbl...
> Is there a better way to move a huge table that has no index on it from
one
> Filegroup to another ? Using SQL 2000
>|||And what if you had a clustered index created that had references to it ?
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uxzo5T$VDHA.2360@.TK2MSFTNGP12.phx.gbl...
> The best way is to create a clustered index. Otherwise, you have to create
a
> new table, copy all of the data, drop the old table, rename the new one,
and
> I did not even mention any referential integrity issues.
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:#MS1ZX#VDHA.2168@.TK2MSFTNGP09.phx.gbl...
> > Is there a better way to move a huge table that has no index on it from
> one
> > Filegroup to another ? Using SQL 2000
> >
> >
>|||Well, the table is avaiable for reading, but not for writing during the
index rebuild, thus the RI is maintained.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u#At6p$VDHA.1280@.tk2msftngp13.phx.gbl...
> And what if you had a clustered index created that had references to it ?
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uxzo5T$VDHA.2360@.TK2MSFTNGP12.phx.gbl...
> > The best way is to create a clustered index. Otherwise, you have to
create
> a
> > new table, copy all of the data, drop the old table, rename the new one,
> and
> > I did not even mention any referential integrity issues.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > Please reply only to the newsgroups.
> > PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:#MS1ZX#VDHA.2168@.TK2MSFTNGP09.phx.gbl...
> > > Is there a better way to move a huge table that has no index on it
from
> > one
> > > Filegroup to another ? Using SQL 2000
> > >
> > >
> >
> >
>
Move table from one file group to another
can I do it by just recreating the clustered index by
specifying the different file group on the statement?
Basically, I need to
1) drop all nonclustered index
2) drop the clustered index
3) create the clustered index in different group
4) create all the nonclustered index in differeng group
Right?
Any simpler approach?This is a multi-part message in MIME format.
--=_NextPart_000_04DF_01C377A2.8F3183A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Steps 2 and 3 can be merged by creating the index with the DROP_EXISTING =option.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"JZHU" <hzhua16@.hotmail.com> wrote in message =news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
can I do it by just recreating the clustered index by specifying the different file group on the statement?
Basically, I need to 1) drop all nonclustered index
2) drop the clustered index
3) create the clustered index in different group
4) create all the nonclustered index in differeng group
Right? Any simpler approach?
--=_NextPart_000_04DF_01C377A2.8F3183A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Steps 2 and 3 can be merged by =creating the index with the DROP_EXISTING option.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"JZHU" wrote in message news:66cf01c377c3$66=886a10$a501280a@.phx.gbl...can I do it by just recreating the clustered index by specifying the =different file group on the statement?Basically, I need to 1) drop all nonclustered index2) drop the clustered index3) create the =clustered index in different group4) create all the nonclustered index in =differeng groupRight? Any simpler approach?
--=_NextPart_000_04DF_01C377A2.8F3183A0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0521_01C377A5.C1E5ADF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You don't have to drop the reference. Consider the following example:
create table t1
(
id int constraint PK_t1 primary key
)
create table t2
(
id int references t1
)
go
create unique clustered index PK_t1 on t1 (id) with drop_existing
go
drop table t2, t1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"JZHU" <hzhua16@.hotmail.com> wrote in message =news:015d01c377c5$b48e1960$a401280a@.phx.gbl...
Thanks, Tom.
If the table is referenced by any other table, I will
have to drop the reference and then recreate it right?
Because I have to move the primary key index to different
file group too.
>--Original Message--
>Steps 2 and 3 can be merged by creating the index with the DROP_EXISTING option.
>-- >Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"JZHU" <hzhua16@.hotmail.com> wrote in message news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
>can I do it by just recreating the clustered index by >specifying the different file group on the statement?
>Basically, I need to >1) drop all nonclustered index
>2) drop the clustered index
>3) create the clustered index in different group
>4) create all the nonclustered index in differeng group
>Right? >Any simpler approach?
--=_NextPart_000_0521_01C377A5.C1E5ADF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You don't have to drop the =reference. Consider the following example:
create table t1( id int constraint PK_t1 primary =key)
create table t2( id int references t1)go
create unique clustered index =PK_t1 on t1 (id) with drop_existinggo
drop table t2, =t1
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"JZHU" wrote in message news:015d01c377c5$b4=8e1960$a401280a@.phx.gbl...Thanks, Tom.If the table is referenced by any other table, I =willhave to drop the reference and then recreate it right?Because I have to move =the primary key index to differentfile group =too.>--Original Message-->Steps 2 and 3 can be merged by creating the index =with the DROP_EXISTING option.>>-- >Tom>>---=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"JZHU=" =wrote in message news:66cf01c377c3$66=886a10$a501280a@.phx.gbl...>can I do it by just recreating the clustered index by >specifying the =different file group on the statement?>Basically, I need to =>1) drop all nonclustered index>2) drop the clustered index>3) =create the clustered index in different group>4) create all the =nonclustered index in differeng group>Right? >Any simpler approach?
--=_NextPart_000_0521_01C377A5.C1E5ADF0--|||Thanks, Tom.
But I can not use drop existing on the primary key index
because it is not clustered. If I do so, the recreate
clustered index will recreate the primary key index too.
It's a waste of time because I have to recreate
the primary key index again to move it to a different
group. Looks like it's a trade off here, right?
One more question ( I hope it is the last one:):
If the table has field text/image which need to move
to the different filegroup too, how can I do it?
>--Original Message--
>You don't have to drop the reference. Consider the
following example:
>create table t1
>(
> id int constraint PK_t1 primary key
>)
>create table t2
>(
> id int references t1
>)
>go
>create unique clustered index PK_t1 on t1 (id) with
drop_existing
>go
>drop table t2, t1
>
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"JZHU" <hzhua16@.hotmail.com> wrote in message
news:015d01c377c5$b48e1960$a401280a@.phx.gbl...
>Thanks, Tom.
>If the table is referenced by any other table, I will
>have to drop the reference and then recreate it right?
>Because I have to move the primary key index to different
>file group too.
>>--Original Message--
>>Steps 2 and 3 can be merged by creating the index with
>the DROP_EXISTING option.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"JZHU" <hzhua16@.hotmail.com> wrote in message
>news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
>>can I do it by just recreating the clustered index by
>>specifying the different file group on the statement?
>>Basically, I need to
>>1) drop all nonclustered index
>>2) drop the clustered index
>>3) create the clustered index in different group
>>4) create all the nonclustered index in differeng group
>>Right?
>>Any simpler approach?|||This is a multi-part message in MIME format.
--=_NextPart_000_0026_01C3783F.CB8BDEC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just following up, if you want to move the text/image data, you will =have to drop and recreate.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:ebEjUG9dDHA.1880@.TK2MSFTNGP10.phx.gbl...
You can use DROP_EXISTING on the PK:
create table t1
(
id int constraint PK_t1 primary key nonclustered
,junk char (5) not null
)
create clustered index idx on t1 (junk)
go
create table t2
(
id int references t1
)
go
create clustered index idx on t1 (junk)
with drop_existing
go
create unique nonclustered index PK_t1 on t1 (id)
with drop_existing
go
drop table t2, t1
As for text/image, I'll have to look further.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"JZHU" <hzhua16@.hotmail.com> wrote in message =news:0d7701c377cd$ebd17680$a601280a@.phx.gbl...
Thanks, Tom.
But I can not use drop existing on the primary key index
because it is not clustered. If I do so, the recreate
clustered index will recreate the primary key index too.
It's a waste of time because I have to recreate
the primary key index again to move it to a different group. Looks like it's a trade off here, right?
One more question ( I hope it is the last one:):
If the table has field text/image which need to move
to the different filegroup too, how can I do it?
>--Original Message--
>You don't have to drop the reference. Consider the following example:
>create table t1
>(
> id int constraint PK_t1 primary key
>)
>create table t2
>(
> id int references t1
>)
>go
>create unique clustered index PK_t1 on t1 (id) with drop_existing
>go
>drop table t2, t1
>
>-- >Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"JZHU" <hzhua16@.hotmail.com> wrote in message news:015d01c377c5$b48e1960$a401280a@.phx.gbl...
>Thanks, Tom.
>If the table is referenced by any other table, I will
>have to drop the reference and then recreate it right?
>Because I have to move the primary key index to different
>file group too.
>>--Original Message--
>>Steps 2 and 3 can be merged by creating the index with >the DROP_EXISTING option.
>>-- >>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"JZHU" <hzhua16@.hotmail.com> wrote in message >news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
>>can I do it by just recreating the clustered index by >>specifying the different file group on the statement?
>>Basically, I need to >>1) drop all nonclustered index
>>2) drop the clustered index
>>3) create the clustered index in different group
>>4) create all the nonclustered index in differeng group
>>Right? >>Any simpler approach?
--=_NextPart_000_0026_01C3783F.CB8BDEC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Just following up, if you want to move =the text/image data, you will have to drop and recreate.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Tom Moreau"= wrote in message news:ebEjUG9dDHA.1880=@.TK2MSFTNGP10.phx.gbl...
You can use DROP_EXISTING on the =PK:
create table =t1( id int constraint PK_t1 primary key nonclustered,junk char (5) not null)create clustered index idx on t1 (junk)go
create table =t2( id int references t1)go
create clustered index idx on =t1 (junk)with drop_existinggocreate unique nonclustered index =PK_t1 on t1 (id)with drop_existinggo
drop table t2, =t1
As for text/image, I'll have to look further.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"JZHU" wrote in message news:0d7701c377cd$eb=d17680$a601280a@.phx.gbl...Thanks, Tom.But I can not use drop existing on the primary key =indexbecause it is not clustered. If I do so, the recreateclustered index will =recreate the primary key index too.It's a waste of time because I have to recreatethe primary key index again to move it to a different =group. Looks like it's a trade off here, right?One more question ( I =hope it is the last one:):If the table has field text/image which need to =moveto the different filegroup too, how can I do it?>--Original Message-->You don't have to drop the reference. Consider =the following example:>>create table t1>(> =id int constraint PK_t1 primary key>)>>create table t2>(> id int references t1>)>go>>create unique clustered index PK_t1 =on t1 (id) with drop_existing>go>>drop table t2, t1>>>-- >Tom>>---=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"JZHU=" =wrote in message news:015d01c377c5$b4=8e1960$a401280a@.phx.gbl...>Thanks, Tom.>>If the table is referenced by any other table, I will>have to drop the reference and then recreate it right?>Because I have to move the primary key index to different>file group too.>>--Original Message-->Steps 2 and 3 can be merged by creating the =index with >the DROP_EXISTING option.>>-- >Tom>>--=--->-->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server =MVP>Columnist, SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"JZHU" =wrote in message =>news:66cf01c377c3$66886a10$a501280a@.phx.gbl...>can I do it by just recreating the clustered index by >specifying =the different file group on the statement?>Basically, I need to >1) drop all nonclustered index>2) drop the =clustered index>3) create the clustered index in different =group>4) create all the nonclustered index in differeng group>Right? >Any simpler approach?
--=_NextPart_000_0026_01C3783F.CB8BDEC0--
specifying the different file group on the statement?
Basically, I need to
1) drop all nonclustered index
2) drop the clustered index
3) create the clustered index in different group
4) create all the nonclustered index in differeng group
Right?
Any simpler approach?This is a multi-part message in MIME format.
--=_NextPart_000_04DF_01C377A2.8F3183A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Steps 2 and 3 can be merged by creating the index with the DROP_EXISTING =option.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"JZHU" <hzhua16@.hotmail.com> wrote in message =news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
can I do it by just recreating the clustered index by specifying the different file group on the statement?
Basically, I need to 1) drop all nonclustered index
2) drop the clustered index
3) create the clustered index in different group
4) create all the nonclustered index in differeng group
Right? Any simpler approach?
--=_NextPart_000_04DF_01C377A2.8F3183A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Steps 2 and 3 can be merged by =creating the index with the DROP_EXISTING option.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"JZHU"
--=_NextPart_000_04DF_01C377A2.8F3183A0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0521_01C377A5.C1E5ADF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You don't have to drop the reference. Consider the following example:
create table t1
(
id int constraint PK_t1 primary key
)
create table t2
(
id int references t1
)
go
create unique clustered index PK_t1 on t1 (id) with drop_existing
go
drop table t2, t1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"JZHU" <hzhua16@.hotmail.com> wrote in message =news:015d01c377c5$b48e1960$a401280a@.phx.gbl...
Thanks, Tom.
If the table is referenced by any other table, I will
have to drop the reference and then recreate it right?
Because I have to move the primary key index to different
file group too.
>--Original Message--
>Steps 2 and 3 can be merged by creating the index with the DROP_EXISTING option.
>-- >Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"JZHU" <hzhua16@.hotmail.com> wrote in message news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
>can I do it by just recreating the clustered index by >specifying the different file group on the statement?
>Basically, I need to >1) drop all nonclustered index
>2) drop the clustered index
>3) create the clustered index in different group
>4) create all the nonclustered index in differeng group
>Right? >Any simpler approach?
--=_NextPart_000_0521_01C377A5.C1E5ADF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You don't have to drop the =reference. Consider the following example:
create table t1( id int constraint PK_t1 primary =key)
create table t2( id int references t1)go
create unique clustered index =PK_t1 on t1 (id) with drop_existinggo
drop table t2, =t1
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"JZHU"
--=_NextPart_000_0521_01C377A5.C1E5ADF0--|||Thanks, Tom.
But I can not use drop existing on the primary key index
because it is not clustered. If I do so, the recreate
clustered index will recreate the primary key index too.
It's a waste of time because I have to recreate
the primary key index again to move it to a different
group. Looks like it's a trade off here, right?
One more question ( I hope it is the last one:):
If the table has field text/image which need to move
to the different filegroup too, how can I do it?
>--Original Message--
>You don't have to drop the reference. Consider the
following example:
>create table t1
>(
> id int constraint PK_t1 primary key
>)
>create table t2
>(
> id int references t1
>)
>go
>create unique clustered index PK_t1 on t1 (id) with
drop_existing
>go
>drop table t2, t1
>
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"JZHU" <hzhua16@.hotmail.com> wrote in message
news:015d01c377c5$b48e1960$a401280a@.phx.gbl...
>Thanks, Tom.
>If the table is referenced by any other table, I will
>have to drop the reference and then recreate it right?
>Because I have to move the primary key index to different
>file group too.
>>--Original Message--
>>Steps 2 and 3 can be merged by creating the index with
>the DROP_EXISTING option.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"JZHU" <hzhua16@.hotmail.com> wrote in message
>news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
>>can I do it by just recreating the clustered index by
>>specifying the different file group on the statement?
>>Basically, I need to
>>1) drop all nonclustered index
>>2) drop the clustered index
>>3) create the clustered index in different group
>>4) create all the nonclustered index in differeng group
>>Right?
>>Any simpler approach?|||This is a multi-part message in MIME format.
--=_NextPart_000_0026_01C3783F.CB8BDEC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just following up, if you want to move the text/image data, you will =have to drop and recreate.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:ebEjUG9dDHA.1880@.TK2MSFTNGP10.phx.gbl...
You can use DROP_EXISTING on the PK:
create table t1
(
id int constraint PK_t1 primary key nonclustered
,junk char (5) not null
)
create clustered index idx on t1 (junk)
go
create table t2
(
id int references t1
)
go
create clustered index idx on t1 (junk)
with drop_existing
go
create unique nonclustered index PK_t1 on t1 (id)
with drop_existing
go
drop table t2, t1
As for text/image, I'll have to look further.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"JZHU" <hzhua16@.hotmail.com> wrote in message =news:0d7701c377cd$ebd17680$a601280a@.phx.gbl...
Thanks, Tom.
But I can not use drop existing on the primary key index
because it is not clustered. If I do so, the recreate
clustered index will recreate the primary key index too.
It's a waste of time because I have to recreate
the primary key index again to move it to a different group. Looks like it's a trade off here, right?
One more question ( I hope it is the last one:):
If the table has field text/image which need to move
to the different filegroup too, how can I do it?
>--Original Message--
>You don't have to drop the reference. Consider the following example:
>create table t1
>(
> id int constraint PK_t1 primary key
>)
>create table t2
>(
> id int references t1
>)
>go
>create unique clustered index PK_t1 on t1 (id) with drop_existing
>go
>drop table t2, t1
>
>-- >Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"JZHU" <hzhua16@.hotmail.com> wrote in message news:015d01c377c5$b48e1960$a401280a@.phx.gbl...
>Thanks, Tom.
>If the table is referenced by any other table, I will
>have to drop the reference and then recreate it right?
>Because I have to move the primary key index to different
>file group too.
>>--Original Message--
>>Steps 2 and 3 can be merged by creating the index with >the DROP_EXISTING option.
>>-- >>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"JZHU" <hzhua16@.hotmail.com> wrote in message >news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
>>can I do it by just recreating the clustered index by >>specifying the different file group on the statement?
>>Basically, I need to >>1) drop all nonclustered index
>>2) drop the clustered index
>>3) create the clustered index in different group
>>4) create all the nonclustered index in differeng group
>>Right? >>Any simpler approach?
--=_NextPart_000_0026_01C3783F.CB8BDEC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Just following up, if you want to move =the text/image data, you will have to drop and recreate.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Tom Moreau"
You can use DROP_EXISTING on the =PK:
create table =t1( id int constraint PK_t1 primary key nonclustered,junk char (5) not null)create clustered index idx on t1 (junk)go
create table =t2( id int references t1)go
create clustered index idx on =t1 (junk)with drop_existinggocreate unique nonclustered index =PK_t1 on t1 (id)with drop_existinggo
drop table t2, =t1
As for text/image, I'll have to look further.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"JZHU"
--=_NextPart_000_0026_01C3783F.CB8BDEC0--
Friday, March 9, 2012
move indexes via ALTER INDEX
I was checking out an indexes property via SQL 2005 Management Studio
and it looks like I can move the index to other filegroups via the
drop down.
I checked the BOL ALTER INDEX and I didnt see an argument for this
action. I see stuff about PARTITION, but I think that is for row
partitioning??
I want to move existing indexes from the PRIMARY filegroup to a new
file group just for indexes, which is called INDEXES.
Can this be done via ALTER INDEX or some other way?
TIA
RobCheck out the CREATE INDEX ... WITH DROP_EXISTING syntax where you specify
the filegroup to create the new index on.
and it looks like I can move the index to other filegroups via the
drop down.
I checked the BOL ALTER INDEX and I didnt see an argument for this
action. I see stuff about PARTITION, but I think that is for row
partitioning??
I want to move existing indexes from the PRIMARY filegroup to a new
file group just for indexes, which is called INDEXES.
Can this be done via ALTER INDEX or some other way?
TIA
RobCheck out the CREATE INDEX ... WITH DROP_EXISTING syntax where you specify
the filegroup to create the new index on.
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"rcamarda" <robert.a.camarda@.gmail.comwrote in message
news:1173752583.908449.87100@.30g2000cwc.googlegrou ps.com...
Quote:
Originally Posted by
>I was checking out an indexes property via SQL 2005 Management Studio
and it looks like I can move the index to other filegroups via the
drop down.
I checked the BOL ALTER INDEX and I didnt see an argument for this
action. I see stuff about PARTITION, but I think that is for row
partitioning??
I want to move existing indexes from the PRIMARY filegroup to a new
file group just for indexes, which is called INDEXES.
Can this be done via ALTER INDEX or some other way?
TIA
Rob
>
Move indexes to new filegroup
If a new filegroup is created on a new disk drive, is there a way to migrate
a specific clustered index to the new filegroup?
Regards,
Jamie
I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH =
> 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>
a specific clustered index to the new filegroup?
Regards,
Jamie
I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH =
> 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>
Move indexes to new filegroup
If a new filegroup is created on a new disk drive, is there a way to migrate
a specific clustered index to the new filegroup?
--
Regards,
JamieUse CREATE INDEX with the DROP EXISTING clause. Example:
USE master
GO
DROP DATABASE FgTest
GO
CREATE DATABASE FgTest
ON PRIMARY
( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH =1024KB ),
FILEGROUP [ForData]
( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
USE FgTest
CREATE TABLE t(c1 int identity, c2 char(5))
CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
INSERT INTO t (c2)
SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
GO
EXEC sp_helpindex t
CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
EXEC sp_helpindex t
USE master
DROP DATABASE FgTest
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> If a new filegroup is created on a new disk drive, is there a way to migrate
> a specific clustered index to the new filegroup?
> --
> Regards,
> Jamie|||I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH => 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> > If a new filegroup is created on a new disk drive, is there a way to migrate
> > a specific clustered index to the new filegroup?
> > --
> > Regards,
> > Jamie
>|||Try adding UNIQUE to the index definition, since it comes from a primary key constraint:
CREATE UNIQUE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:6FF49395-56FF-453F-8E67-34AEFD91408D@.microsoft.com...
> I'm having trouble with the syntax. Index is created and dropped with
> ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
> [PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
> (
> [SupplierXrefKey] ASC
> ) ON [PRIMARY]
>
> IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id => OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
> ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
> WHEN I RUN:
> CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
> (SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
> I get an error:
> Server: Msg 1907, Level 16, State 1, Line 1
> Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
> does not match the constraint being enforced by the existing index.
> DDRIVE is the additional filegroup I created for this table and which exists
> on a drive other than the primary filegroup drive.
> --
> Regards,
> Jamie
>
> "Tibor Karaszi" wrote:
>> Use CREATE INDEX with the DROP EXISTING clause. Example:
>> USE master
>> GO
>> DROP DATABASE FgTest
>> GO
>> CREATE DATABASE FgTest
>> ON PRIMARY
>> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH
>> =>> 1024KB ),
>> FILEGROUP [ForData]
>> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
>> FILEGROWTH = 1024KB )
>> LOG ON
>> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
>> FILEGROWTH = 10%)
>> GO
>> USE FgTest
>> CREATE TABLE t(c1 int identity, c2 char(5))
>> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
>> INSERT INTO t (c2)
>> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
>> GO
>> EXEC sp_helpindex t
>> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
>> EXEC sp_helpindex t
>> USE master
>> DROP DATABASE FgTest
>> GO
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>> > If a new filegroup is created on a new disk drive, is there a way to migrate
>> > a specific clustered index to the new filegroup?
>> > --
>> > Regards,
>> > Jamie
>>
a specific clustered index to the new filegroup?
--
Regards,
JamieUse CREATE INDEX with the DROP EXISTING clause. Example:
USE master
GO
DROP DATABASE FgTest
GO
CREATE DATABASE FgTest
ON PRIMARY
( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH =1024KB ),
FILEGROUP [ForData]
( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
USE FgTest
CREATE TABLE t(c1 int identity, c2 char(5))
CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
INSERT INTO t (c2)
SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
GO
EXEC sp_helpindex t
CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
EXEC sp_helpindex t
USE master
DROP DATABASE FgTest
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> If a new filegroup is created on a new disk drive, is there a way to migrate
> a specific clustered index to the new filegroup?
> --
> Regards,
> Jamie|||I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH => 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> > If a new filegroup is created on a new disk drive, is there a way to migrate
> > a specific clustered index to the new filegroup?
> > --
> > Regards,
> > Jamie
>|||Try adding UNIQUE to the index definition, since it comes from a primary key constraint:
CREATE UNIQUE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:6FF49395-56FF-453F-8E67-34AEFD91408D@.microsoft.com...
> I'm having trouble with the syntax. Index is created and dropped with
> ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
> [PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
> (
> [SupplierXrefKey] ASC
> ) ON [PRIMARY]
>
> IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id => OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
> ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
> WHEN I RUN:
> CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
> (SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
> I get an error:
> Server: Msg 1907, Level 16, State 1, Line 1
> Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
> does not match the constraint being enforced by the existing index.
> DDRIVE is the additional filegroup I created for this table and which exists
> on a drive other than the primary filegroup drive.
> --
> Regards,
> Jamie
>
> "Tibor Karaszi" wrote:
>> Use CREATE INDEX with the DROP EXISTING clause. Example:
>> USE master
>> GO
>> DROP DATABASE FgTest
>> GO
>> CREATE DATABASE FgTest
>> ON PRIMARY
>> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH
>> =>> 1024KB ),
>> FILEGROUP [ForData]
>> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
>> FILEGROWTH = 1024KB )
>> LOG ON
>> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
>> FILEGROWTH = 10%)
>> GO
>> USE FgTest
>> CREATE TABLE t(c1 int identity, c2 char(5))
>> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
>> INSERT INTO t (c2)
>> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
>> GO
>> EXEC sp_helpindex t
>> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
>> EXEC sp_helpindex t
>> USE master
>> DROP DATABASE FgTest
>> GO
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>> > If a new filegroup is created on a new disk drive, is there a way to migrate
>> > a specific clustered index to the new filegroup?
>> > --
>> > Regards,
>> > Jamie
>>
Move indexes to new filegroup
If a new filegroup is created on a new disk drive, is there a way to migrate
a specific clustered index to the new filegroup?
--
Regards,
JamieUse CREATE INDEX with the DROP EXISTING clause. Example:
USE master
GO
DROP DATABASE FgTest
GO
CREATE DATABASE FgTest
ON PRIMARY
( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH =
1024KB ),
FILEGROUP [ForData]
( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXS
IZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MA
XSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
USE FgTest
CREATE TABLE t(c1 int identity, c2 char(5))
CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
INSERT INTO t (c2)
SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
GO
EXEC sp_helpindex t
CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
EXEC sp_helpindex t
USE master
DROP DATABASE FgTest
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> If a new filegroup is created on a new disk drive, is there a way to migra
te
> a specific clustered index to the new filegroup?
> --
> Regards,
> Jamie|||I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierIt
emXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupp
lierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE
= UNLIMITED, FILEGROWTH =
> 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MA
XSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB ,
MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>|||Try adding UNIQUE to the index definition, since it comes from a primary key
constraint:
CREATE UNIQUE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:6FF49395-56FF-453F-8E67-34AEFD91408D@.microsoft.com...[vbcol=seagreen]
> I'm having trouble with the syntax. Index is created and dropped with
> ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
> [PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
> (
> [SupplierXrefKey] ASC
> ) ON [PRIMARY]
>
> IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
> OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplier
ItemXrefs')
> ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSu
pplierItemXrefs]
> WHEN I RUN:
> CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
> (SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
> I get an error:
> Server: Msg 1907, Level 16, State 1, Line 1
> Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
> does not match the constraint being enforced by the existing index.
> DDRIVE is the additional filegroup I created for this table and which exis
ts
> on a drive other than the primary filegroup drive.
> --
> Regards,
> Jamie
>
> "Tibor Karaszi" wrote:
>
a specific clustered index to the new filegroup?
--
Regards,
JamieUse CREATE INDEX with the DROP EXISTING clause. Example:
USE master
GO
DROP DATABASE FgTest
GO
CREATE DATABASE FgTest
ON PRIMARY
( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH =
1024KB ),
FILEGROUP [ForData]
( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXS
IZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MA
XSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
USE FgTest
CREATE TABLE t(c1 int identity, c2 char(5))
CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
INSERT INTO t (c2)
SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
GO
EXEC sp_helpindex t
CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
EXEC sp_helpindex t
USE master
DROP DATABASE FgTest
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> If a new filegroup is created on a new disk drive, is there a way to migra
te
> a specific clustered index to the new filegroup?
> --
> Regards,
> Jamie|||I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierIt
emXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupp
lierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE
= UNLIMITED, FILEGROWTH =
> 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MA
XSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB ,
MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>|||Try adding UNIQUE to the index definition, since it comes from a primary key
constraint:
CREATE UNIQUE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:6FF49395-56FF-453F-8E67-34AEFD91408D@.microsoft.com...[vbcol=seagreen]
> I'm having trouble with the syntax. Index is created and dropped with
> ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
> [PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
> (
> [SupplierXrefKey] ASC
> ) ON [PRIMARY]
>
> IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
> OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplier
ItemXrefs')
> ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSu
pplierItemXrefs]
> WHEN I RUN:
> CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
> (SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
> I get an error:
> Server: Msg 1907, Level 16, State 1, Line 1
> Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
> does not match the constraint being enforced by the existing index.
> DDRIVE is the additional filegroup I created for this table and which exis
ts
> on a drive other than the primary filegroup drive.
> --
> Regards,
> Jamie
>
> "Tibor Karaszi" wrote:
>
Monday, February 20, 2012
Moving indexes to another filegroup trouble...
I am trying to place all the clustered indexes to de data filegroup of my database and de non clustered indexes to de index filegroup of my database.
But in some databases I cannot change the location in the storage tab of the index property window because it is greyed out. The strange thing is that in some other databases it is possible, and some other partially. I even found a table with two non clustered indexes, of which one was possible to change, and the other not! Changing the compatibility mode of the database does not help.
So I will not get too happy about this.
1) Does anyone know why this behaviour is?
2) Does anyone has a script or tool (preferably free) which I can use to automate this task?
This all is in SQL Server 2005 Developer and using Management Studio.
Thanks in advance!
You can move an index by using the DROP_EXISTING clause.
Code Snippet
CREATE TABLE myTable
(ID int, NC varchar(3))
GO
CREATE CLUSTERED INDEX myCIX ON myTable (ID)
GO
CREATE NONCLUSTERED INDEX myIX ON myTable (NC)
GO
CREATE CLUSTERED INDEX myCIX ON myTable (ID)
WITH (DROP_EXISTING=ON) ON [OtherFG]
GO
CREATE NONCLUSTERED INDEX myIX ON myTable (NC)
WITH (DROP_EXISTING=ON) ON [OtherFG]
GO
sp_help myTable
DROP TABLE myTable
GO
For constraints check out the MOVE TO clause for the DROP constraint statement.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||See this http://www.mssqltips.com/tip.asp?tip=1112 too|||Thanks, but wat I wanted to know, if somewone has an script that looks for all the indexes and creates move scripts or moves the indexes itself. I already knew how to do it, but with a database with 50-100 tables, handlabour is not cheap.
Moving indexes to another filegroup trouble...
I am trying to place all the clustered indexes to de data filegroup of my database and de non clustered indexes to de index filegroup of my database.
But in some databases I cannot change the location in the storage tab of the index property window because it is greyed out. The strange thing is that in some other databases it is possible, and some other partially. I even found a table with two non clustered indexes, of which one was possible to change, and the other not! Changing the compatibility mode of the database does not help.
So I will not get too happy about this.
1) Does anyone know why this behaviour is?
2) Does anyone has a script or tool (preferably free) which I can use to automate this task?
This all is in SQL Server 2005 Developer and using Management Studio.
Thanks in advance!
You can move an index by using the DROP_EXISTING clause.
Code Snippet
CREATE TABLE myTable
(ID int, NC varchar(3))
GO
CREATE CLUSTERED INDEX myCIX ON myTable (ID)
GO
CREATE NONCLUSTERED INDEX myIX ON myTable (NC)
GO
CREATE CLUSTERED INDEX myCIX ON myTable (ID)
WITH (DROP_EXISTING=ON) ON [OtherFG]
GO
CREATE NONCLUSTERED INDEX myIX ON myTable (NC)
WITH (DROP_EXISTING=ON) ON [OtherFG]
GO
sp_help myTable
DROP TABLE myTable
GO
For constraints check out the MOVE TO clause for the DROP constraint statement.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||See this http://www.mssqltips.com/tip.asp?tip=1112 too|||Thanks, but wat I wanted to know, if somewone has an script that looks for all the indexes and creates move scripts or moves the indexes itself. I already knew how to do it, but with a database with 50-100 tables, handlabour is not cheap.
Moving Indexes a different partition
Hi,
Does SQL Server has it own Index file?
How do we move the index from one partition to another, say, C TO D.
Thank You
Indexes are stored in the data files. If you just want to move a particular
index from drive C to D, you need to make sure that it's the only thing in
the data file(s). You can control what is in which data file(s) by specifying
which filegroup an index should be created on. Note that a filegroup has one
or more data files, and a data file must be in one and only one filegroup.
So one way to move an index from C to D is create a new filegroup with its
files on D, and then drop the index and re-create it on the new filegroup.
Look up CREATE INDEX in the Books Online for its detailed syntax.
Linchi
"loufuki@.gmail.com" wrote:
> Hi,
> Does SQL Server has it own Index file?
> How do we move the index from one partition to another, say, C TO D.
> Thank You
>
|||In addition to Linchi's answer , Indexes have pages not files. There are
data pages and index pages stored in data files
<loufuki@.gmail.com> wrote in message
news:b84a8468-2353-4e2e-9bb7-ac89db91c7b3@.q21g2000hsa.googlegroups.com...
> Hi,
> Does SQL Server has it own Index file?
> How do we move the index from one partition to another, say, C TO D.
> Thank You
Does SQL Server has it own Index file?
How do we move the index from one partition to another, say, C TO D.
Thank You
Indexes are stored in the data files. If you just want to move a particular
index from drive C to D, you need to make sure that it's the only thing in
the data file(s). You can control what is in which data file(s) by specifying
which filegroup an index should be created on. Note that a filegroup has one
or more data files, and a data file must be in one and only one filegroup.
So one way to move an index from C to D is create a new filegroup with its
files on D, and then drop the index and re-create it on the new filegroup.
Look up CREATE INDEX in the Books Online for its detailed syntax.
Linchi
"loufuki@.gmail.com" wrote:
> Hi,
> Does SQL Server has it own Index file?
> How do we move the index from one partition to another, say, C TO D.
> Thank You
>
|||In addition to Linchi's answer , Indexes have pages not files. There are
data pages and index pages stored in data files
<loufuki@.gmail.com> wrote in message
news:b84a8468-2353-4e2e-9bb7-ac89db91c7b3@.q21g2000hsa.googlegroups.com...
> Hi,
> Does SQL Server has it own Index file?
> How do we move the index from one partition to another, say, C TO D.
> Thank You
Moving Indexes a different partition
Hi,
Does SQL Server has it own Index file?
How do we move the index from one partition to another, say, C TO D.
Thank YouIndexes are stored in the data files. If you just want to move a particular
index from drive C to D, you need to make sure that it's the only thing in
the data file(s). You can control what is in which data file(s) by specifying
which filegroup an index should be created on. Note that a filegroup has one
or more data files, and a data file must be in one and only one filegroup.
So one way to move an index from C to D is create a new filegroup with its
files on D, and then drop the index and re-create it on the new filegroup.
Look up CREATE INDEX in the Books Online for its detailed syntax.
Linchi
"loufuki@.gmail.com" wrote:
> Hi,
> Does SQL Server has it own Index file?
> How do we move the index from one partition to another, say, C TO D.
> Thank You
>|||In addition to Linchi's answer , Indexes have pages not files. There are
data pages and index pages stored in data files
<loufuki@.gmail.com> wrote in message
news:b84a8468-2353-4e2e-9bb7-ac89db91c7b3@.q21g2000hsa.googlegroups.com...
> Hi,
> Does SQL Server has it own Index file?
> How do we move the index from one partition to another, say, C TO D.
> Thank You
Does SQL Server has it own Index file?
How do we move the index from one partition to another, say, C TO D.
Thank YouIndexes are stored in the data files. If you just want to move a particular
index from drive C to D, you need to make sure that it's the only thing in
the data file(s). You can control what is in which data file(s) by specifying
which filegroup an index should be created on. Note that a filegroup has one
or more data files, and a data file must be in one and only one filegroup.
So one way to move an index from C to D is create a new filegroup with its
files on D, and then drop the index and re-create it on the new filegroup.
Look up CREATE INDEX in the Books Online for its detailed syntax.
Linchi
"loufuki@.gmail.com" wrote:
> Hi,
> Does SQL Server has it own Index file?
> How do we move the index from one partition to another, say, C TO D.
> Thank You
>|||In addition to Linchi's answer , Indexes have pages not files. There are
data pages and index pages stored in data files
<loufuki@.gmail.com> wrote in message
news:b84a8468-2353-4e2e-9bb7-ac89db91c7b3@.q21g2000hsa.googlegroups.com...
> Hi,
> Does SQL Server has it own Index file?
> How do we move the index from one partition to another, say, C TO D.
> Thank You
Subscribe to:
Comments (Atom)