I recently moved a SQL Server 2000 server to 2005 and I cannot drop
the user. One of the issues is that the user does not have a login. I
tried to do an Alter USer but it's not sticking.
How can I add a login to an already created user?Check out sp_change_users_login in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<axwack@.gmail.com> wrote in message
news:1177599478.863706.29390@.s33g2000prh.googlegroups.com...
I recently moved a SQL Server 2000 server to 2005 and I cannot drop
the user. One of the issues is that the user does not have a login. I
tried to do an Alter USer but it's not sticking.
How can I add a login to an already created user?|||ok do this.
use mydatabase
EXEC sp_change_users_login 'Report'
This outputs the users that are orpohaned.
Now run this to fix the user
Sp_change_users_login AUTO_FIX, myusername
This will get you going again.
John Vandervliet...
"axwack@.gmail.com" wrote:
> I recently moved a SQL Server 2000 server to 2005 and I cannot drop
> the user. One of the issues is that the user does not have a login. I
> tried to do an Alter USer but it's not sticking.
> How can I add a login to an already created user?
>
Showing posts with label drop. Show all posts
Showing posts with label drop. Show all posts
Friday, March 23, 2012
Monday, March 19, 2012
move table to another filegroup
alter table db.schme.table drop move to filegroup1?
this doesnot work
thanks
your syntax looks wrong
try:
altertable dbo.Gladiators
dropconstraint pk_Gladiators_int1
WITH(moveto'filegroup1')
|||In order to move a table to another filegroup, use the statement "create index" with the option "with drop_existing", to recreate the clustered index, and specify the new filegroup. See "create index" in BOL.
AMB
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--
Subscribe to:
Posts (Atom)