Changing Column Data type

Question

Post reply

Changing Column Data type

k.thanigaivel

Ten Centuries

Points: 1247
More actions
November 21, 2012 at 9:57 pm

#265146

Hi
We need to change int column to bigint data type on a primary key column.
So we generate script from managment studio. it changed data type by using temp table.
My question why they did not use alter column query to change data type? which is the best alter column method or temp table method ? please provide your suggestion.
Thanks
Vel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

BriPan SSCrazy Points: 2061 More actions · Answer 1

going with alter column method is best option according to me as its a primary column for your table.

sanket kokane Hall of Fame Points: 3465 More actions · Answer 2

First ,thanks for the nice question ,I always used to thought it takes alter table command at the back end .

I have attached my results with client statistics .

Waiting for experts to comment on it .

-----------------------------------------------------------------------------
संकेत कोकणे

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

sanket kokane (11/21/2012)
First ,thanks for the nice question ,I always used to thought it takes alter table command at the back end .
I have attached my results with client statistics .
Waiting for experts to comment on it .

Now check the before'n'after fragmentation of the clustered index and see what happens. I haven't tried it but I'm thinking it will have gotten worse.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sanket kokane Hall of Fame Points: 3465 More actions · Answer 4

The test result i post,was done on table without primary key .

in OP's case,he want it to alter column which having primary Key .

if you Use

alter table tablename

alter column columnname bigint

it gives error

Msg 5074, Level 16, State 1, Line 2

The index 'PK__tablename __05AD357414270015' is dependent on column 'columnname'.

Msg 4922, Level 16, State 9, Line 2

ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.

So temp_table is the only option .

-----------------------------------------------------------------------------
संकेत कोकणे

k.thanigaivel Ten Centuries Points: 1247 More actions · Answer 5

No, please try this

alter table Atest drop constriant PK_ATest

alter table Atest alter column id bigint

alter table Atest add constriant PK_Atest primary key clustered (id)

sanket kokane Hall of Fame Points: 3465 More actions · Answer 6

k.thanigaivel (11/22/2012)
No, please try this
alter table Atest drop constriant PK_ATest
alter table Atest alter column id bigint
alter table Atest add constriant PK_Atest primary key clustered (id)

that isn't like you are logically creating new table ?

-----------------------------------------------------------------------------
संकेत कोकणे

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

sanket kokane (11/22/2012)
k.thanigaivel (11/22/2012)
No, please try this
alter table Atest drop constriant PK_ATest
alter table Atest alter column id bigint
alter table Atest add constriant PK_Atest primary key clustered (id)
that isn't like you are logically creating new table ?

Into object dependencies here, as the PK index is tied to the column you simply just cant change the column, you need to remove the index, change the column, re-add the index. So you need to make the table a heap, then change it back to a B-Tree when changing a clustered index.

Also one thing that no one has mentioned yet is any foreign keys which reference the column as well will need to be dropped all referenced columns changes and all FK's recreated.