November 20, 2002
Oracle v SQL Server, Part 1
I recently needed to develop an application in a hurry at work, so I built the database in Oracle. However, the work is now going to be ongoing and the client uses SQL Server.
So I am converting my tables and associated scripts. As an Oracle bod for many a year this new fangled technology is all a bit different, but strangely familiar. I am going to use this blog to record those little differences as and when I find them.
First, DDL. When creating tables in SQL Server data types are subtly different.
- VARCHAR2 should be replaced by VARCHAR
- NUMBER should be replaced by DECIMAL (or NUMERIC)
- NUMBER columns with no digits after the decimal point can be converted to INT (or BIGINT)
- DATE should be replaced by DATETIME
- SQL Server has a MONEY (and associated SMALLMONEY) data type, Oracle doesn't. Use at your own risk.
- CLOB should be replaced by TEXT
- BLOB should be replaced by IMAGE
A minor point, but if you are migrating some SQL scripts, REM isn't a valid comment marker in Transact-SQL, replace it with double dash ("--") or put C style comment markers (/* */) around whole blocks of comments.
Posted by Andy Todd at November 20, 2002 03:17 PM
Hi
In Oracle, how would you handle data type Bit found in MS SQL?
Posted by: Charl on November 28, 2002 01:53 PMNot sure, I'll have to experiment. I don't have any columns in my databases which are BIT. You could probably place it in a CHAR(1) or NUMBER(1) but I would need to experiment.
Posted by: Andy Todd on November 28, 2002 05:08 PMHow can the exceptions are explicitly caught in T-SQL as in PL/SQL?
Posted by: swathi on January 4, 2003 04:12 AMThere is a command in pl/sql for viewing information on how a database/table is created (i.e. data types, column/field names) I can't find this command anywhere, I think it's something like:
DESCRIBE TableName
Do you know what it is? And what would be it's equivalent in Transact-SQL?
Posted by: Gina on October 16, 2003 11:50 PMLook at
EXEC sp_help tablename
(replacing "tablename" with the name of your table, of course...)
to give you a picture of the table, its keys, etc.