Changing Primary Key from nonclustered to clustered

Is it possible to modify a PrimaryKey Constraint from nonclustered to
clustered without dropping and redefining the Primary Key?
I'm 99% sure that I will need to drop and redefine but just thought that I
would ask since it is a major hassle to drop and redefine the Foreign Keys
that reference this Primary Key
Thanks in advance.
Nope, you have to drop and redefine it. You can use Enterprise Manager to
generate a script though. Make the change in the table designer and the Save
Script button is the third from the left.
Jacco Schalkwijk
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:ex%23E5FkUEHA.3540@.TK2MSFTNGP11.phx.gbl...
> Is it possible to modify a PrimaryKey Constraint from nonclustered to
> clustered without dropping and redefining the Primary Key?
> I'm 99% sure that I will need to drop and redefine but just thought that I
> would ask since it is a major hassle to drop and redefine the Foreign Keys
> that reference this Primary Key
> Thanks in advance.
>

Changing Primary Key from nonclustered to clustered

Is it possible to modify a PrimaryKey Constraint from nonclustered to
clustered without dropping and redefining the Primary Key?
I'm 99% sure that I will need to drop and redefine but just thought that I
would ask since it is a major hassle to drop and redefine the Foreign Keys
that reference this Primary Key
Thanks in advance.Nope, you have to drop and redefine it. You can use Enterprise Manager to
generate a script though. Make the change in the table designer and the Save
Script button is the third from the left.
Jacco Schalkwijk
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:ex%23E5FkUEHA.3540@.TK2MSFTNGP11.phx.gbl...
> Is it possible to modify a PrimaryKey Constraint from nonclustered to
> clustered without dropping and redefining the Primary Key?
> I'm 99% sure that I will need to drop and redefine but just thought that I
> would ask since it is a major hassle to drop and redefine the Foreign Keys
> that reference this Primary Key
> Thanks in advance.
>

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto Porter
On Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
--
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto PorterOn Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing constraintnames.

Although constraint names are of no consequence to a 'working'
database, they sometimes pose a problem when managing
databases.
So we try to keep control over het names of constraints.
Now we are changing the name of a table.
Should we alter al the constraintnames so that
the tablename in the constraintname reflects the
actual table, or should we leave the 'original' tablename.
Any thoughts about that ?
Is there an easy way to manage constraintnames, if the
database is implemented at several customers sites ?
Thanks for you attention,
ben brugmanAccording to the documentation, you should be able to rename a constraint us
ing sp_rename.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ben brugman" <ben@.niethier.nl> wrote in message news:eCo2wR%23pFHA.3160@.TK2MSFTNGP14.phx.gb
l...
> Although constraint names are of no consequence to a 'working'
> database, they sometimes pose a problem when managing
> databases.
> So we try to keep control over het names of constraints.
> Now we are changing the name of a table.
> Should we alter al the constraintnames so that
> the tablename in the constraintname reflects the
> actual table, or should we leave the 'original' tablename.
> Any thoughts about that ?
> Is there an easy way to manage constraintnames, if the
> database is implemented at several customers sites ?
> Thanks for you attention,
> ben brugman
>

Changing constraintnames.

Although constraint names are of no consequence to a 'working'
database, they sometimes pose a problem when managing
databases.
So we try to keep control over het names of constraints.
Now we are changing the name of a table.
Should we alter al the constraintnames so that
the tablename in the constraintname reflects the
actual table, or should we leave the 'original' tablename.
Any thoughts about that ?
Is there an easy way to manage constraintnames, if the
database is implemented at several customers sites ?
Thanks for you attention,
ben brugmanAccording to the documentation, you should be able to rename a constraint using sp_rename.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ben brugman" <ben@.niethier.nl> wrote in message news:eCo2wR%23pFHA.3160@.TK2MSFTNGP14.phx.gbl...
> Although constraint names are of no consequence to a 'working'
> database, they sometimes pose a problem when managing
> databases.
> So we try to keep control over het names of constraints.
> Now we are changing the name of a table.
> Should we alter al the constraintnames so that
> the tablename in the constraintname reflects the
> actual table, or should we leave the 'original' tablename.
> Any thoughts about that ?
> Is there an easy way to manage constraintnames, if the
> database is implemented at several customers sites ?
> Thanks for you attention,
> ben brugman
>

Changing constraintnames.

Although constraint names are of no consequence to a 'working'
database, they sometimes pose a problem when managing
databases.
So we try to keep control over het names of constraints.
Now we are changing the name of a table.
Should we alter al the constraintnames so that
the tablename in the constraintname reflects the
actual table, or should we leave the 'original' tablename.
Any thoughts about that ?
Is there an easy way to manage constraintnames, if the
database is implemented at several customers sites ?
Thanks for you attention,
ben brugman
According to the documentation, you should be able to rename a constraint using sp_rename.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ben brugman" <ben@.niethier.nl> wrote in message news:eCo2wR%23pFHA.3160@.TK2MSFTNGP14.phx.gbl...
> Although constraint names are of no consequence to a 'working'
> database, they sometimes pose a problem when managing
> databases.
> So we try to keep control over het names of constraints.
> Now we are changing the name of a table.
> Should we alter al the constraintnames so that
> the tablename in the constraintname reflects the
> actual table, or should we leave the 'original' tablename.
> Any thoughts about that ?
> Is there an easy way to manage constraintnames, if the
> database is implemented at several customers sites ?
> Thanks for you attention,
> ben brugman
>