Change field length or type in a database

Posted on 2003-03-18
Medium Priority
Last Modified: 2013-12-03
If I has a table with a field
   1) length = 10
   2) type = int
the table contains some data.
Now, I decide to change the field
   1) length = 20
   2) type = varchar

Can I change the field length/type without lossing my data inside the table ?
Assume I use Oracle or MsSQL databse.
Question by:private99
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 34

Expert Comment

ID: 8163944
Not sure how oracle handles it, but SQL Server will automatically conver the data-types if you issue an ALTER TABLE statement or change the structure through EM manager.

You should be ok since you are increasing the size of a field and converting a number to a string.  You would probably have more problems if you were decreasing the size of a field or converting a string to a number.


Accepted Solution

Danielzt earned 400 total points
ID: 8164060

From data type                    To data type    Result
int, smallint, or tinyint          char           *
                                   varchar        *
                                   nchar          E
                                   nvarchar       E
money, smallmoney, numeric,
decimal, float, or real            char           E
                                   varchar        E
                                   nchar          E
                                   nvarchar       E

* --- Result length too short to display.
E --- Error returned because result length is too short to

For Oracle
You can change any column’s datatype if all rows for the column contain nulls. However, if you change the datatype of a column in a materialized view container table, the corresponding materialized view is invalidated.
You can omit the datatype only if the statement also designates the column as part of the foreign key of a referential integrity constraint. Oracle automatically assigns the column the same datatype as the corresponding column of the referenced key of the referential integrity constraint.
You can always increase the size of a character or raw column or the precision of a numeric column, whether or not all the columns contain nulls. You can reduce the
size of a column’s datatype as long as the change does not require data to be modified. Oracle scans existing data and returns an error if data exists that exceeds the new length limit.
You can modify a DATE column to TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE. You can modify any TIMESTAMP WITH LOCAL TIME ZONE to a DATE column.


Expert Comment

ID: 8165102
for oracle

alter table <tablename> modify <coloumn name> varchar2(20);

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question