November 24, 2008 at 8:07 am
i HAVE TABLE WITH NAME TABLE1
IT HAS THE FALLOWING COLUMNS.
COLUMN1,........
COLUMN1 IS THE INT DATA TYPE
IT CONTAINS THE MAX NUMBER 500000 AS TOTAL NUMBER OF ROWS IS 450000
NOW I WANT MAKE COLUMN1 AS SERRGATE/IDENTITY KEY
ANY SUGGETIONS
November 27, 2008 at 9:31 am
Hi,
First, you can't change the column to identity.
You must add a new column or to create a new table
with an identity column.
I choose to use the second choice.
CREATE TABLE TABLE1
(
COLUMN1 int NOT NULL,
COLUMN2 varchar(10) NULL
)
INSERT INTO TABLE1 VALUES(450000,'TEST1')
INSERT INTO TABLE1 VALUES(450001,'TEST2')
-- Creating the new table with identity column.
CREATE TABLE NEW_TABLE1
(
COLUMN1 int identity(500000,1) NOT NULL,
COLUMN2 varchar(10) NULL
)
-- I switch between the old table to the new table.
ALTER TABLE TABLE1 SWITCH TO NEW_TABLE1
-- Drop the old table.
DROP TABLE TABLE1
-- Rename the new table to old table name.
EXEC sp_rename 'NEW_TABLE1' ,'TABLE1'
November 27, 2008 at 10:36 am
shmuel_v (11/27/2008)
Hi,First, you can't change the column to identity.
Why not? do you have any proof of this..?
November 27, 2008 at 11:15 am
If you know another solution then i will be happy to learn.
November 27, 2008 at 11:47 am
shmuel_v (11/27/2008)
-- I switch between the old table to the new table.ALTER TABLE TABLE1 SWITCH TO NEW_TABLE1
I believe that SWITCH requires either the source table or the target table to be partitioned. I don't think that it works on two non-partitioned tables.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 27, 2008 at 11:47 am
Just set the column as identity... set auto increment by 1...
and starting from 50000001(which no. u want to start from ).
It should not effect anything.. unless you have some constraint on that column.
Then nos or values which u have now wont be affected by setting it as identity. When u insert a new row it will start with 5000001.
I hope this will help you... if u have any other requirement reply back.
November 27, 2008 at 11:51 am
steveb (11/27/2008)
shmuel_v (11/27/2008)
Hi,First, you can't change the column to identity.
Why not? do you have any proof of this..?
The ALTER TABLE ... ALTER COLUMN statement has no option to add or remove the IDENTITY(s,i) property. There is no other way to alter a column's definition in place.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 27, 2008 at 11:57 am
The only "easy" way to do this that I know of is to use Management Studio to do it in the Table Designer. Although this seems like it is altering it in place, what it is actually doing is somethin very similar to what shmuel_v posted (except that is does not use SWITCH to move the data):
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_YourTable
(
ID2B int NOT NULL IDENTITY (1, 1),
Column2 bit NULL,
Column3 varchar(50) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_YourTable ON
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.Tmp_YourTable (ID2B, Column2, Column3)
SELECT ID2B, Column2, Column3 FROM dbo.YourTable WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_YourTable OFF
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.Tmp_YourTable', N'YourTable', 'OBJECT'
GO
COMMIT
And of course, if this is a big table, it could take quite a while.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 27, 2008 at 12:02 pm
1. rbarryyoung ,
There is no need to have a partition table to execute a switch between 2 tables.
2. Gouthama Kolivad,
wrong, when you use the SSMS to choose identity he generate a script
that create a temp table, a new table and do an insert select statement.
(If the table is big then it will take a lot of time and resources to move the data)
switch, just rename the table and doesn't copy any data.
November 27, 2008 at 12:07 pm
Gouthama Kolivad (11/27/2008)
Just set the column as identity... set auto increment by 1...and starting from 50000001(which no. u want to start from ).
It should not effect anything.. unless you have some constraint on that column.
I assume that you are talking about using Management Studio to do this. If so, then you should be aware that it certainly can affect things as it has to make a completely new table, copy the data into it, drop the old table and then rename the new table to the old table. You can see this in my previous post where a pasted an example of the SQL that SSMS actually uses.
If you look at that SQL, you will notice that this is wrapped in a transaction and that the INSERT includes "WITH (HOLDLOCK TABLOCKX)". The effect of this will be to prevent any further data changes to the tbale until the whole transaction is done, which could take some time. So in that sense, it could seriously affect some things.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 27, 2008 at 12:11 pm
shmuel_v (11/27/2008)
1. rbarryyoung ,There is no need to have a partition table to execute a switch between 2 tables.
Hmm, that's odd then, because according to BOL one or both of the tables must be partitioned.
Guess I will try it and see...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 27, 2008 at 12:20 pm
Well I tried it and it does indeed work. Neat trick!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 27, 2008 at 12:21 pm
By the way,
I allready executed my example and you can trust me it work !!!
November 27, 2008 at 12:22 pm
Just a question: It looks like SWITCH will only work here for IDENTITY changes. Are there any other column changes that it will work for?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 27, 2008 at 12:32 pm
Not only on identity column.
Switch just change the table name without considuration of the structure.
By the way, even in sql server 2008 there is not an alter table command for identity.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply