I created a table with two primary keys. The table was been populated with
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.
> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated with
> numerous rows. This table should have been created with one primary and one
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:
>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and one
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label primary. Show all posts
Showing posts with label primary. Show all posts
Thursday, March 22, 2012
Changing a Primary Key to Foreign Key
I created a table with two primary keys. The table was been populated with
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,
> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.
> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated with
> numerous rows. This table should have been created with one primary and one
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,
|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:
>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and one
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,
> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.
> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated with
> numerous rows. This table should have been created with one primary and one
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,
|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:
>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and one
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Changing a Primary Key to Foreign Key
I created a table with two primary keys. The table was been populated with
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.
> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated wit
h
> numerous rows. This table should have been created with one primary and o
ne
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:
>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and on
e
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.
> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated wit
h
> numerous rows. This table should have been created with one primary and o
ne
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:
>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and on
e
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
changing a primary key in a replicated table
Hi
We have a merge publication - I want to change the primary key for one of the tables (add another column to the primary key)
How do I do it ?
Currently there is no data in the table - which I guess might help..
thanks
Bruce
you can try to run the snapshot agent after change the PK to generate the latest snapshot and reinitialize the subsciption.
I'm not sure if it will work.
Cheers,
Justin
|||After some testing I worked out that we need to:
a) remove it from the publication
b) recreate snapshot
c) change the primary key at our end
d) change the primary key at their end
e) add it back into the publication
f) recreate snapshot
Tuesday, March 20, 2012
Changind a Primary Key value
Hi,
I have a table with a Primary Key field that is an integer (int) data type
with auto increment.
Is there a way to change the value of the ID field (e.g. currently it is 100
and I want to change it to 20, is it possible?)
Thanks,
Shai,shalom
CREATE TABLE Test (col INT IDENTITY(1,1))
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--4 rows
GO
DBCC CHECKIDENT (Test, RESEED, 2)
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--6 rows
GO
DROP TABLE Test
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
|||Shai
You will get an error of primary key violention. This is one of many reasons
not to use an identity property as a primary key,
as you cannot update it.
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
|||On Thu, 5 Aug 2004 14:55:45 +0200, "Shai Goldberg"
<gshai(Remove-it)@.shamir.co.il> wrote:
>Hi,
>I have a table with a Primary Key field that is an integer (int) data type
>with auto increment.
>Is there a way to change the value of the ID field (e.g. currently it is 100
>and I want to change it to 20, is it possible?)
>Thanks,
>
Hi Shai,
Do you mean that after inserting data, you want to manually change the ID
for one of the rows inserted?
Unfortunately, that is not possible.
From Books Online:
Error 8102
Severity Level 16
Message Text
Cannot update identity column '%.*ls'.
Explanation
You have specifically attempted to alter the value of an identity column
in the SET portion of the UPDATE statement. You can only use the identity
column in the WHERE clause of the UPDATE statement.
Action
Updating of the identity column is not allowed. To update an identity
column, you can use the following techniques:
To reassign all identity values, bulk copy the data out, and then drop and
re-create the table with the proper seed and increment values. Then bulk
copy the data back into the newly created table. When bcp inserts the
values it will appropriately increase the values and redistribute the
identity values. You can also use the INSERT INTO and sp_rename commands
to accomplish the same action.
To reassign a single row, you must delete the row and insert it using the
SET IDENTITY_INSERT tblName ON clause.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Something like this will work
drop table t
go
create table t( id int identity(1,1) primary key, data varchar(8) not null)
go
insert into t values('HI')
go
select * from t
GO
set identity_insert t on
insert t (id, data) select 2, data from t where id = 1
delete from t where id = 1
GO
set identity_insert t off
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
sql
I have a table with a Primary Key field that is an integer (int) data type
with auto increment.
Is there a way to change the value of the ID field (e.g. currently it is 100
and I want to change it to 20, is it possible?)
Thanks,
Shai,shalom
CREATE TABLE Test (col INT IDENTITY(1,1))
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--4 rows
GO
DBCC CHECKIDENT (Test, RESEED, 2)
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--6 rows
GO
DROP TABLE Test
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
|||Shai
You will get an error of primary key violention. This is one of many reasons
not to use an identity property as a primary key,
as you cannot update it.
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
|||On Thu, 5 Aug 2004 14:55:45 +0200, "Shai Goldberg"
<gshai(Remove-it)@.shamir.co.il> wrote:
>Hi,
>I have a table with a Primary Key field that is an integer (int) data type
>with auto increment.
>Is there a way to change the value of the ID field (e.g. currently it is 100
>and I want to change it to 20, is it possible?)
>Thanks,
>
Hi Shai,
Do you mean that after inserting data, you want to manually change the ID
for one of the rows inserted?
Unfortunately, that is not possible.
From Books Online:
Error 8102
Severity Level 16
Message Text
Cannot update identity column '%.*ls'.
Explanation
You have specifically attempted to alter the value of an identity column
in the SET portion of the UPDATE statement. You can only use the identity
column in the WHERE clause of the UPDATE statement.
Action
Updating of the identity column is not allowed. To update an identity
column, you can use the following techniques:
To reassign all identity values, bulk copy the data out, and then drop and
re-create the table with the proper seed and increment values. Then bulk
copy the data back into the newly created table. When bcp inserts the
values it will appropriately increase the values and redistribute the
identity values. You can also use the INSERT INTO and sp_rename commands
to accomplish the same action.
To reassign a single row, you must delete the row and insert it using the
SET IDENTITY_INSERT tblName ON clause.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Something like this will work
drop table t
go
create table t( id int identity(1,1) primary key, data varchar(8) not null)
go
insert into t values('HI')
go
select * from t
GO
set identity_insert t on
insert t (id, data) select 2, data from t where id = 1
delete from t where id = 1
GO
set identity_insert t off
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
sql
Changind a Primary Key value
Hi,
I have a table with a Primary Key field that is an integer (int) data type
with auto increment.
Is there a way to change the value of the ID field (e.g. currently it is 100
and I want to change it to 20, is it possible?)
Thanks,Shai,shalom
CREATE TABLE Test (col INT IDENTITY(1,1))
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--4 rows
GO
DBCC CHECKIDENT (Test, RESEED, 2)
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--6 rows
GO
DROP TABLE Test
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>|||Shai
You will get an error of primary key violention. This is one of many reasons
not to use an identity property as a primary key,
as you cannot update it.
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>|||On Thu, 5 Aug 2004 14:55:45 +0200, "Shai Goldberg"
<gshai(Remove-it)@.shamir.co.il> wrote:
>Hi,
>I have a table with a Primary Key field that is an integer (int) data type
>with auto increment.
>Is there a way to change the value of the ID field (e.g. currently it is 100
>and I want to change it to 20, is it possible?)
>Thanks,
>
Hi Shai,
Do you mean that after inserting data, you want to manually change the ID
for one of the rows inserted?
Unfortunately, that is not possible.
From Books Online:
Error 8102
Severity Level 16
Message Text
Cannot update identity column '%.*ls'.
Explanation
You have specifically attempted to alter the value of an identity column
in the SET portion of the UPDATE statement. You can only use the identity
column in the WHERE clause of the UPDATE statement.
Action
Updating of the identity column is not allowed. To update an identity
column, you can use the following techniques:
To reassign all identity values, bulk copy the data out, and then drop and
re-create the table with the proper seed and increment values. Then bulk
copy the data back into the newly created table. When bcp inserts the
values it will appropriately increase the values and redistribute the
identity values. You can also use the INSERT INTO and sp_rename commands
to accomplish the same action.
To reassign a single row, you must delete the row and insert it using the
SET IDENTITY_INSERT tblName ON clause.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Something like this will work
drop table t
go
create table t( id int identity(1,1) primary key, data varchar(8) not null)
go
insert into t values('HI')
go
select * from t
GO
set identity_insert t on
insert t (id, data) select 2, data from t where id = 1
delete from t where id = 1
GO
set identity_insert t off
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
I have a table with a Primary Key field that is an integer (int) data type
with auto increment.
Is there a way to change the value of the ID field (e.g. currently it is 100
and I want to change it to 20, is it possible?)
Thanks,Shai,shalom
CREATE TABLE Test (col INT IDENTITY(1,1))
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--4 rows
GO
DBCC CHECKIDENT (Test, RESEED, 2)
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--6 rows
GO
DROP TABLE Test
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>|||Shai
You will get an error of primary key violention. This is one of many reasons
not to use an identity property as a primary key,
as you cannot update it.
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>|||On Thu, 5 Aug 2004 14:55:45 +0200, "Shai Goldberg"
<gshai(Remove-it)@.shamir.co.il> wrote:
>Hi,
>I have a table with a Primary Key field that is an integer (int) data type
>with auto increment.
>Is there a way to change the value of the ID field (e.g. currently it is 100
>and I want to change it to 20, is it possible?)
>Thanks,
>
Hi Shai,
Do you mean that after inserting data, you want to manually change the ID
for one of the rows inserted?
Unfortunately, that is not possible.
From Books Online:
Error 8102
Severity Level 16
Message Text
Cannot update identity column '%.*ls'.
Explanation
You have specifically attempted to alter the value of an identity column
in the SET portion of the UPDATE statement. You can only use the identity
column in the WHERE clause of the UPDATE statement.
Action
Updating of the identity column is not allowed. To update an identity
column, you can use the following techniques:
To reassign all identity values, bulk copy the data out, and then drop and
re-create the table with the proper seed and increment values. Then bulk
copy the data back into the newly created table. When bcp inserts the
values it will appropriately increase the values and redistribute the
identity values. You can also use the INSERT INTO and sp_rename commands
to accomplish the same action.
To reassign a single row, you must delete the row and insert it using the
SET IDENTITY_INSERT tblName ON clause.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Something like this will work
drop table t
go
create table t( id int identity(1,1) primary key, data varchar(8) not null)
go
insert into t values('HI')
go
select * from t
GO
set identity_insert t on
insert t (id, data) select 2, data from t where id = 1
delete from t where id = 1
GO
set identity_insert t off
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
Changind a Primary Key value
Hi,
I have a table with a Primary Key field that is an integer (int) data type
with auto increment.
Is there a way to change the value of the ID field (e.g. currently it is 100
and I want to change it to 20, is it possible?)
Thanks,Shai,shalom
CREATE TABLE Test (col INT IDENTITY(1,1))
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--4 rows
GO
DBCC CHECKIDENT (Test, RESEED, 2)
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--6 rows
GO
DROP TABLE Test
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>|||Shai
You will get an error of primary key violention. This is one of many reasons
not to use an identity property as a primary key,
as you cannot update it.
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>|||On Thu, 5 Aug 2004 14:55:45 +0200, "Shai Goldberg"
<gshai(Remove-it)@.shamir.co.il> wrote:
>Hi,
>I have a table with a Primary Key field that is an integer (int) data type
>with auto increment.
>Is there a way to change the value of the ID field (e.g. currently it is 10
0
>and I want to change it to 20, is it possible?)
>Thanks,
>
Hi Shai,
Do you mean that after inserting data, you want to manually change the ID
for one of the rows inserted?
Unfortunately, that is not possible.
From Books Online:
Error 8102
Severity Level 16
Message Text
Cannot update identity column '%.*ls'.
Explanation
You have specifically attempted to alter the value of an identity column
in the SET portion of the UPDATE statement. You can only use the identity
column in the WHERE clause of the UPDATE statement.
Action
Updating of the identity column is not allowed. To update an identity
column, you can use the following techniques:
To reassign all identity values, bulk copy the data out, and then drop and
re-create the table with the proper seed and increment values. Then bulk
copy the data back into the newly created table. When bcp inserts the
values it will appropriately increase the values and redistribute the
identity values. You can also use the INSERT INTO and sp_rename commands
to accomplish the same action.
To reassign a single row, you must delete the row and insert it using the
SET IDENTITY_INSERT tblName ON clause.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Something like this will work
drop table t
go
create table t( id int identity(1,1) primary key, data varchar(8) not null)
go
insert into t values('HI')
go
select * from t
GO
set identity_insert t on
insert t (id, data) select 2, data from t where id = 1
delete from t where id = 1
GO
set identity_insert t off
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
I have a table with a Primary Key field that is an integer (int) data type
with auto increment.
Is there a way to change the value of the ID field (e.g. currently it is 100
and I want to change it to 20, is it possible?)
Thanks,Shai,shalom
CREATE TABLE Test (col INT IDENTITY(1,1))
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--4 rows
GO
DBCC CHECKIDENT (Test, RESEED, 2)
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--6 rows
GO
DROP TABLE Test
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>|||Shai
You will get an error of primary key violention. This is one of many reasons
not to use an identity property as a primary key,
as you cannot update it.
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>|||On Thu, 5 Aug 2004 14:55:45 +0200, "Shai Goldberg"
<gshai(Remove-it)@.shamir.co.il> wrote:
>Hi,
>I have a table with a Primary Key field that is an integer (int) data type
>with auto increment.
>Is there a way to change the value of the ID field (e.g. currently it is 10
0
>and I want to change it to 20, is it possible?)
>Thanks,
>
Hi Shai,
Do you mean that after inserting data, you want to manually change the ID
for one of the rows inserted?
Unfortunately, that is not possible.
From Books Online:
Error 8102
Severity Level 16
Message Text
Cannot update identity column '%.*ls'.
Explanation
You have specifically attempted to alter the value of an identity column
in the SET portion of the UPDATE statement. You can only use the identity
column in the WHERE clause of the UPDATE statement.
Action
Updating of the identity column is not allowed. To update an identity
column, you can use the following techniques:
To reassign all identity values, bulk copy the data out, and then drop and
re-create the table with the proper seed and increment values. Then bulk
copy the data back into the newly created table. When bcp inserts the
values it will appropriately increase the values and redistribute the
identity values. You can also use the INSERT INTO and sp_rename commands
to accomplish the same action.
To reassign a single row, you must delete the row and insert it using the
SET IDENTITY_INSERT tblName ON clause.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Something like this will work
drop table t
go
create table t( id int identity(1,1) primary key, data varchar(8) not null)
go
insert into t values('HI')
go
select * from t
GO
set identity_insert t on
insert t (id, data) select 2, data from t where id = 1
delete from t where id = 1
GO
set identity_insert t off
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
Wednesday, March 7, 2012
Change the order of columns
By mistake I misplaced the order of my primary keys in some tables and I just dont find the way to move them to the top of each table. This must be something easy. and its ugly to see primary keys on the foot.If you are using the management studio, right click on the table in question and select "Modify" from the context menu. In the work area click the area to the left of the column name you want to move and drag it up or down to its new location.
|||
|||
Thanks, very easy. The problem was that I always make my tables in the Diagram view. and there is no posibility to do that. Only in the table view.
So easy answer.
Sunday, February 19, 2012
Change Self reference values in a data flow task
Hi,
We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).
When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.
We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.
Please help me to solve this problem.
Regards,
Gopi
Hi Gopi,Did you ever resolve this? If so, how? I am facing the same issue.
Thanks,
Dave
|||The way to solve this is by creating your own lookup component using a custom script transformation. Instead of looking up reference values from an external table, your script should cache each unique key translation pair in a memory-based structure like a dictionary. Each old/new EmployeeKey pair gets cached into memory, and each old ManagerKey gets looked up in that cache and replaced with the new value. You would need to make sure that your data is sorted appropriately so that you don't get a ManagerKey before the EmployeeKey.
|||
Hi Dave,
Good morning. We preferred to resolve this problem at SQL Server Engine instead at Integration Services Flow. We are using query built with a Common Table Expression of SQL Server 2005 to identify all the invalid self referencing records. The SSIS Components are proven as very poor performing to identify invalid self referencing records
Cheers,
Gopi
Change Self reference values in a data flow task
Hi,
We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).
When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.
We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.
Please help me to solve this problem.
Regards,
Gopi
Hi Gopi,
Did you ever resolve this? If so, how? I am facing the same issue.
Thanks,
Dave|||The way to solve this is by creating your own lookup component using a custom script transformation. Instead of looking up reference values from an external table, your script should cache each unique key translation pair in a memory-based structure like a dictionary. Each old/new EmployeeKey pair gets cached into memory, and each old ManagerKey gets looked up in that cache and replaced with the new value. You would need to make sure that your data is sorted appropriately so that you don't get a ManagerKey before the EmployeeKey.|||
Hi Dave,
Good morning. We preferred to resolve this problem at SQL Server Engine instead at Integration Services Flow. We are using query built with a Common Table Expression of SQL Server 2005 to identify all the invalid self referencing records. The SSIS Components are proven as very poor performing to identify invalid self referencing records
Cheers,
Gopi
Change results in column to display different
I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment.
Here goes..
SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE
FROM SAMPLE SAMPLE, SDIDATAITEM SDI
WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
(SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASCSELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID,
(CASE WHEN ISNUMERIC(SDI.DISPLAYVALUE) = 0 THEN Null
ELSE SDI.DISPLAYVALUE END) SDI_DISPLAYVALUE
FROM SAMPLE SAMPLE, SDIDATAITEM SDI
WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
(SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC
Here goes..
SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE
FROM SAMPLE SAMPLE, SDIDATAITEM SDI
WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
(SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASCSELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID,
(CASE WHEN ISNUMERIC(SDI.DISPLAYVALUE) = 0 THEN Null
ELSE SDI.DISPLAYVALUE END) SDI_DISPLAYVALUE
FROM SAMPLE SAMPLE, SDIDATAITEM SDI
WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
(SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC
Thursday, February 16, 2012
Change Primary Key Values
Hello,
Is there a way in SQL Server 2000 to reassign primary key
values automatically?
For example, I have a table that has a "Person ID" field
as its primary key that was started with an identity seed
of "98753162". The identity increment is "1".
Now, there are hundreds of records in this table. I want
to reassign these PK values with much shorter numbers,
i.e. subtract "98753160" from all existing values. In
addition, I would need all corresponding foreign key
values in other tables to be updated automatically with
the new values.
Thanks,
Mike1)Distable the foreign key constraint with ALTER TABLE command.
look for alter table command in BOL "alter table... nocheck ..." (look for
nocheck clause) which will stop validation of future inserts or updates to
the column.
2) add a column to primary key table and populate the values of PK to this
column with the statement as
update <table> set new_col=PK_col
3)you can update the existing primary key values with the statement such as
update table set PK_col= pk_col - 98753160 --required subtraction.(make sure
primary key constraint is not violated)
4) once this is done you will have to fire an update statement on the FK
tables to update the new FK values replacing the old one.
--
-Vishal
"Mike Rogan" <mrogan@.carolinawebdev.com> wrote in message
news:038e01c3555a$95864c50$a601280a@.phx.gbl...
> Hello,
> Is there a way in SQL Server 2000 to reassign primary key
> values automatically?
> For example, I have a table that has a "Person ID" field
> as its primary key that was started with an identity seed
> of "98753162". The identity increment is "1".
> Now, there are hundreds of records in this table. I want
> to reassign these PK values with much shorter numbers,
> i.e. subtract "98753160" from all existing values. In
> addition, I would need all corresponding foreign key
> values in other tables to be updated automatically with
> the new values.
> Thanks,
> Mike
Is there a way in SQL Server 2000 to reassign primary key
values automatically?
For example, I have a table that has a "Person ID" field
as its primary key that was started with an identity seed
of "98753162". The identity increment is "1".
Now, there are hundreds of records in this table. I want
to reassign these PK values with much shorter numbers,
i.e. subtract "98753160" from all existing values. In
addition, I would need all corresponding foreign key
values in other tables to be updated automatically with
the new values.
Thanks,
Mike1)Distable the foreign key constraint with ALTER TABLE command.
look for alter table command in BOL "alter table... nocheck ..." (look for
nocheck clause) which will stop validation of future inserts or updates to
the column.
2) add a column to primary key table and populate the values of PK to this
column with the statement as
update <table> set new_col=PK_col
3)you can update the existing primary key values with the statement such as
update table set PK_col= pk_col - 98753160 --required subtraction.(make sure
primary key constraint is not violated)
4) once this is done you will have to fire an update statement on the FK
tables to update the new FK values replacing the old one.
--
-Vishal
"Mike Rogan" <mrogan@.carolinawebdev.com> wrote in message
news:038e01c3555a$95864c50$a601280a@.phx.gbl...
> Hello,
> Is there a way in SQL Server 2000 to reassign primary key
> values automatically?
> For example, I have a table that has a "Person ID" field
> as its primary key that was started with an identity seed
> of "98753162". The identity increment is "1".
> Now, there are hundreds of records in this table. I want
> to reassign these PK values with much shorter numbers,
> i.e. subtract "98753160" from all existing values. In
> addition, I would need all corresponding foreign key
> values in other tables to be updated automatically with
> the new values.
> Thanks,
> Mike
change primary index value
Is there any way to change the value of a primary key value?Is there any way to change the value of a primary key value?
Use cascading Update to change a primary key and its related foreign key.|||is cascading update slow?
It just dawned on me that if I changed a primary key I would have to change every PK in all rows.
Will there be a performance hit if this is done?|||in all rows of which table? you wouldn't need to do it if you're talking about ON UPDATE CASCADE
UPDATE takes as long as UPDATE takes, but if there are cascading updates, those will obviously take time too
could you give a more specific example, please|||OK for example:
PK Product Price
1 SQL Book $99.99
2 ASP Book $89.99
3 C# Intro $99.99
4 Computer $500.00
5 Hard Drive $150.00
For example, asp book has matched one of my queries and I want to switch ASP book and SQL book PKs. So the table now looks like this:
PK Product Price
1 ASP Book $89.99
2 SQL Book $99.99
3 C# Intro $99.99
4 Computer $500.00
5 Hard Drive $150.00
is this possible?|||i suppose it's possible, but by far the more important question is: why would you want to do this!!!!
no offence, but do you understand what the purpose of the PK is?|||well..I am guessing that if the PK s are already in order either by alphabet or price,etc i would not have to use the SORT BY in the SELECT command which I can only conclude slows the query down.|||use ORDER BY if you want a specific sequence
manipulating a numeric surrogate primary key to maintain a sequence is like trying to empty a swimming pool with a teaspoon
add an index to the table on the price column, and ORDER BY will be efficient|||So, ORDER BY carries little overhead even if I have over 5000 rows? Right now, ORDER BY is very fast but I expect the database to get bigger in the future.|||well..I am guessing that if the PK s are already in order either by alphabet or price,etc i would not have to use the SORT BY in the SELECT command which I can only conclude slows the query down.
SORT BY?
You mean ORDER BY,I don't think that using ORDER BY will give you a performance hit if you have indices on those fields,well in that case an clustered index can help you,if you want to keep the order in which the data is inserted on that PK.
Creating proper Indices helps a lot in the query performance...|||So, ORDER BY carries little overhead even if I have over 5000 rows? Right now, ORDER BY is very fast but I expect the database to get bigger in the future.
5000 rows is NOTHING.
if you put an index on the column you want to order by, you'll be fine.
also, nobody said that your PK needs to be clustered. If you are that concerned about perf of order by, make the PK nonclustered, and the index on your sorting column clustered, as rudra suggests.
EDIT: generally, I have found that very often it's wrong to make assumptions about where perf problems are. The only to know for sure is to measure. For sql this means use profiler and showplan. For compiled code it means profile it using some other tool. The point is you need to measure before making statements about where the perf issues lie.
Use cascading Update to change a primary key and its related foreign key.|||is cascading update slow?
It just dawned on me that if I changed a primary key I would have to change every PK in all rows.
Will there be a performance hit if this is done?|||in all rows of which table? you wouldn't need to do it if you're talking about ON UPDATE CASCADE
UPDATE takes as long as UPDATE takes, but if there are cascading updates, those will obviously take time too
could you give a more specific example, please|||OK for example:
PK Product Price
1 SQL Book $99.99
2 ASP Book $89.99
3 C# Intro $99.99
4 Computer $500.00
5 Hard Drive $150.00
For example, asp book has matched one of my queries and I want to switch ASP book and SQL book PKs. So the table now looks like this:
PK Product Price
1 ASP Book $89.99
2 SQL Book $99.99
3 C# Intro $99.99
4 Computer $500.00
5 Hard Drive $150.00
is this possible?|||i suppose it's possible, but by far the more important question is: why would you want to do this!!!!
no offence, but do you understand what the purpose of the PK is?|||well..I am guessing that if the PK s are already in order either by alphabet or price,etc i would not have to use the SORT BY in the SELECT command which I can only conclude slows the query down.|||use ORDER BY if you want a specific sequence
manipulating a numeric surrogate primary key to maintain a sequence is like trying to empty a swimming pool with a teaspoon
add an index to the table on the price column, and ORDER BY will be efficient|||So, ORDER BY carries little overhead even if I have over 5000 rows? Right now, ORDER BY is very fast but I expect the database to get bigger in the future.|||well..I am guessing that if the PK s are already in order either by alphabet or price,etc i would not have to use the SORT BY in the SELECT command which I can only conclude slows the query down.
SORT BY?
You mean ORDER BY,I don't think that using ORDER BY will give you a performance hit if you have indices on those fields,well in that case an clustered index can help you,if you want to keep the order in which the data is inserted on that PK.
Creating proper Indices helps a lot in the query performance...|||So, ORDER BY carries little overhead even if I have over 5000 rows? Right now, ORDER BY is very fast but I expect the database to get bigger in the future.
5000 rows is NOTHING.
if you put an index on the column you want to order by, you'll be fine.
also, nobody said that your PK needs to be clustered. If you are that concerned about perf of order by, make the PK nonclustered, and the index on your sorting column clustered, as rudra suggests.
EDIT: generally, I have found that very often it's wrong to make assumptions about where perf problems are. The only to know for sure is to measure. For sql this means use profiler and showplan. For compiled code it means profile it using some other tool. The point is you need to measure before making statements about where the perf issues lie.
Friday, February 10, 2012
Change non-clustered index to clustered
Hi,
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?I think I found the answer, you do a create index with drop existing.|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegroups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?I think I found the answer, you do a create index with drop existing.|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegroups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
Change non-clustered index to clustered
Hi,
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?
I think I found the answer, you do a create index with drop existing.
|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegro ups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>
|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?
I think I found the answer, you do a create index with drop existing.
|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegro ups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>
|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
Change non-clustered index to clustered
Hi,
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?I think I found the answer, you do a create index with drop existing.|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegroups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?I think I found the answer, you do a create index with drop existing.|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegroups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
Subscribe to:
Posts (Atom)