Change datatype

Question

Post reply

Change datatype

Mike Levan

SSChampion

Points: 14201
More actions
October 31, 2008 at 11:00 am

#127338

I am changing datatype int to varchar(10) whata re the pros and cons, will i lose any data by any chance, what is the gud approach and how.
thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Pros = You will be able to put string characters into your field now

Cons = Any application, procedure or function that relies on this data type as an INT will need to be modified.

Mike Levan SSChampion Points: 14201 More actions · Answer 2

How can i find all the objects using that column so that i can change it later. will i lose any data in conversion of datatype.

SanjayAttray SSChampion Points: 13157 More actions · Answer 3

try something like this.

SELECT sys.objects.name, syscomments.ctext

FROM sys.objects INNER JOIN

syscomments ON sys.objects.object_id = syscomments.id

WHERE (syscomments.ctext LIKE '%column_name%')

will give you all views, procs, function etc which are using column name in them.

SQL DBA.

Mike Levan SSChampion Points: 14201 More actions · Answer 4

I can find which talbes has "mycolumn" but how cud i find which database has that column, because i need to search 87 databases in a server.

Do we also need to change datatype of a view after changing in the table?

thanks

SQL_DBA_3 SSCarpal Tunnel Points: 4772 More actions · Answer 5

will i lose any data in conversion of datatype.

no ..you just need to change that in related sps,functions.

Do we also need to change datatype of a view after changing in the table?

you need to check if that view is using any where condition on this column.you need to put the values in 'string'(quotes).

Mike Levan SSChampion Points: 14201 More actions · Answer 6

I want to do this off peak time for allthe DB's. What is the best way to implement it by sheduling a job.

when i run all alter table commands of a DB at once someitmes the system hangs. Does it longer if i run them at once and does faster when i run each alter command by itself?

---------------------------------------------

Can i do something like this..

ALTER TABLE Emp ALTER COLUMN jobtype Varchar(5)

WHERE

(SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns

WHERE name = 'jobtype' ))

SQL_DBA_3 SSCarpal Tunnel Points: 4772 More actions · Answer 7

You cannot run T-sql statements in all DBs at once.You have to do it one-by-one.

Also you cannot have a where clause in Alter table statement.You may go through dynamic sql to accomplish this.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Mike Levan (10/31/2008)
I am changing datatype int to varchar(10) whata re the pros and cons, will i lose any data by any chance, what is the gud approach and how.
thanks

Ummm... when you say you're changing the datatype from INT to VARCHAR(10), do you mean just in a Select (ok to do and no chance of data loss) or do you mean you're changing the datatype of a column (ie. Bozo-no-no because it'll allow non-numeric data, etc, etc)? 😉

--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)

sgmunson SSC Guru Points: 110640 More actions · Answer 9

Along the same lines as Jeff Moden's post, perhaps the most important issue here is WHY the column needs to be changed to varchar(10). If the problem is the limitation of the "int" datatype's maximum value, may I suggest changing to "bigint" instead?

Here are the various integer data types and their size limits:

Data type Range Storage

bigint

-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

8 Bytes

int

-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

4 Bytes

smallint

-2^15 (-32,768) to 2^15-1 (32,767)

2 Bytes

tinyint

0 to 255

1 Byte

Changing from int to bigint doubles the number of digits you can use for a number, and would at least maintain a numeric data type, whereas changing to varchar would allow non-numeric data to creep in, which might require that you add check constraints in order to ensure non-numeric data doesn't get in, and also might require significant changes to other queries, views, stored procedures, applications, etc., just to deal with looking at a number as a varchar field.

While you would still need to check other code for data typing in temp tables or derived tables or dependent views or queries to ensure the bigint datatype is properly propagated, that's far easier than verifying that applications still work with a character field.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

steve block SSCertifiable Points: 7698 More actions · Answer 10

Also, if you do any ORDER BYs on the column, you will get a different result for the varchar than you will for the int.

i.e.: 1,10,101,2,22,3

instead of 1,2,3,10,22,101

Steve