#SQLSERVERLearning : Column Data Type Conversion in Oracle and SQL Server.

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access here.

Ideally converting data type after having underlying data on table is tedious.It required special consideration(Index/Change Code/Conversion) to avoid any failure or run-time exceptions.
Once Schema is design , data getting stored in Column need to adhere its type.

Only Number can be stored in NUMBER datatype and so on.
We can say it is a datatype constraint getting enforce for a column, it define type of data we would be storing as strong handshake.

Let start with an example in oracle,
We will create a table with a column as Varchar2 datatype and try to modify it to Number.

DROP TABLE TEST123;

CREATE TABLE TEST123
(COL1 VARCHAR(10) , COL2 INTEGER);

INSERT INTO TEST123 VALUES ('11', 112);
INSERT INTO TEST123 VALUES ('12', 112);
INSERT INTO TEST123 VALUES ('1343', 112);
INSERT INTO TEST123 VALUES ('-1133', 112);

we are storing number in varchar data type.
Also note all data currently in Col1 is also a Legal Number, hence conversion should not be a problem.
We should not get any data type mismatch for same.

ALTER TABLE TAB_TEST123 MODIFY COL1 NUMBER(10,0); -- modify column to be NUMBER

SQL Error: ORA-01439: column to be modified must be empty to change datatype
01439. 00000 - "column to be modified must be empty to change datatype"


Oracle does not allow to typecast column data type to a Number, though all data was valid Number.
For data type modification column should be empty, it should not have any data.

We have many approach for data type modification, but personally prefer adding Virtual column with desired data type.

ALTER TABLE TEST123 ADD (COL1_NUM AS (TO_NUMBER(COL1))); --adding virtual column
Table TEST123 altered.

It will add one more column with data type as NUMBER and whose values is getting source from Col1.

DESC TEST123;

Name Null Type
-------- ---- ------------
COL1           VARCHAR2(10)
COL2          NUMBER(38)
COL1_NUM NUMBER

Using Virtual Column we would not need to change most of logic or index structure and many more constraint.

Let see how we can achieve same in SQL Server.we will be using similar table as created for Oracle.
Let try to convert same to Integer datatype

DROP TABLE TEST123;

CREATE TABLE TEST123
(COL1 VARCHAR(10) , COL2 INTEGER);

INSERT INTO TEST123 VALUES ('11', 112);
INSERT INTO TEST123 VALUES ('12', 112);
INSERT INTO TEST123 VALUES ('1343', 112);
INSERT INTO TEST123 VALUES ('-1133.12', 112); -- Added a Decimal Values

ALTER TABLE TEST123 ALTER COLUMN COL1 INT;

SQL Server will try to modify data type for column and will perform implicit check on column data for exact data type match.
In current data set for value -1133.12, it can’t be cast to int datatype.
It will give below exception.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '-1133.12' to data type int.
The statement has been terminated.

As compare to oracle, SQL server will cast data type of column though it has some existing data.

Conversion to Float will be successful.
ALTER TABLE TEST123 ALTER COLUMN COL1 FLOAT;

TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
------------- ------------------------ ----------
TEST123 COL1 6 float
TEST123 COL2 4 int

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal, SQL Server Learning and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s