Microsoft SQL Server has a more robust selection of data types than Oracle. There are many possible conversions between the Oracle and SQL Server data types. It is recommended that you use the DTS Wizard to automate the creation of the new CREATE TABLE statements. You can then modify these statements as necessary.
| Oracle | Microsoft SQL Server |
|---|---|
| CHAR | char is recommended. char type columns are accessed somewhat faster than varchar columns because they use a fixed storage length. |
| VARCHAR2 and LONG |
varchar or text. (If the length of the data values in your Oracle column is 8000 bytes of less, use varchar; otherwise, you must use text.) |
| RAW and LONG RAW |
varbinary or image. (If the length of the data values in your Oracle column is 8000 bytes of less, use varbinary; otherwise, you must use image.) |
| NUMBER | If integer between 1 and 255, use tinyint. If integer between -32768 and 32767, use smallint. If integer between -2,147,483,648 and 2,147,483,647 use int. If you require a float type number, use numeric (has precision and scale). Note: Do not use float or real, because rounding may occur (Oracle NUMBER and SQL Server numeric do not round). If you are not sure, use numeric; it most closely resembles Oracle NUMBER data type. |
| DATE | datetime. |
| ROWID | Use the identity column type. |
| CURRVAL, NEXTVAL | Use the identity column type, and @@IDENTITY, IDENT_SEED(), and IDENT_INCR() functions. |
| SYSDATE | GETDATE(). |
| USER | USER. |
The Unicode specification defines a single encoding scheme for practically all characters widely used in businesses around the world. All computers consistently translate the bit patterns in Unicode data into characters using the single Unicode specification. This ensures that the same bit pattern is always converted to the same character on all computers. Data can be freely transferred from one database or computer to another without concern that the receiving system will correctly translate the bit patterns into characters.
One problem with data types that use 1 byte to encode each character is that the data type can represent only 256 different characters. This forces multiple encoding specifications (or code pages) for different alphabets. It is also impossible to handle systems such as the Japanese Kanji or Korean Hangul alphabets that have thousands of characters.
Microsoft SQL Server translates the bit patterns in char, varchar, and text columns to characters using the definitions in the code page installed with SQL Server. Client computers use the code page installed with the operating system to interpret character bit patterns. There are many different code pages. Some characters appear on some code pages, but not on others. Some characters are defined with one bit pattern on some code pages, and with a different bit pattern on other code pages. When you build international systems that must handle different languages, it becomes difficult to pick code pages for all the computers that meet the language requirements of multiple countries. It is also difficult to ensure that every computer performs the correct translations when interfacing with a system that uses a different code page.
The Unicode specification addresses this problem by using 2 bytes to encode each character. There are enough different patterns (65,536) in 2 bytes for a single specification covering the most common business languages. Because all Unicode systems consistently use the same bit patterns to represent all characters, there is no problem with characters being converted incorrectly when moving from one system to another.
In SQL Server, nchar, nvarchar, and ntext data types support Unicode data. For more information about SQL Server data types, see SQL Server Books Online.
User-defined data types can be created for the model database or for a single user database. If the user-defined data type is defined for model, that data type is available to all new user databases created from that point forward. The user-defined data type is defined with the sp_addtype system stored procedure. For more information, see SQL Server Books Online.
You can use a user-defined data type in the CREATE TABLE and ALTER TABLE statements, and bind defaults and rules to it. If nullability is explicitly defined when the user-defined data type is used during table creation, it takes precedence over the nullability defined when the data type was created.
This example shows how to create a user-defined data type. The arguments are the user-type name, data type, and nullability:
sp_addtype gender_type, 'varchar(1)', 'not null'
go
This capability might initially appear to solve the problem of migrating Oracle table creation scripts to SQL Server. For example, it is quite easy to add the Oracle DATE data type:
sp_addtype date, datetime
This does not work with data types that require variable sizes, such as the Oracle data type NUMBER. An error message is returned indicating that a length must also be specified:
sp_addtype varchar2, varchar
Go
Msg 15091, Level 16, State 1
You must specify a length with this physical type.
The timestamp columns enable BROWSE-mode updates and make cursor update operations more efficient. The timestamp is a data type that is automatically updated every time a row containing a timestamp column is inserted or updated.
Values in timestamp columns are not stored as an actual date or time, but are stored as binary(8) or varbinary(8), which indicates the sequence of events on rows in the table. A table can have only one timestamp column.
For more information, see SQL Server Books Online.