Firebird DocsetFirebird Database DocsMigration from MS-SQL to Firebird → Data Types
Firebird home Firebird home Prev: Database AdministrationHome: Firebird DocsetUp: Migration from MS-SQL to FirebirdNext: SQL Syntax

Data Types

Converting the bit data type
Converting the identity data type
Converting the uniqueidentifier data type

This section describes the different data types available in Firebird and MS SQL, and how to translate types from one system to another.

MS SQL has different data types, depending on the version. The following table lists the data types along with the version in which they were introduced.

Table 1. Data Types Conversion Table

MSSQL Ver

Data Type

Firebird

MSSQL definition and comments

6.5

bigint

INT64

8-byte integer type.

6.5

binary

CHAR

Fixed-length binary data with a maximum length of 8,000 bytes. In 6.5, maximum was 255.

6.5

bit

CHAR(1)

Integer data with either a 1 or 0 value. Typically, replaced by constants 'T' and 'F'.

6.5

char

CHAR

Fixed-length non-Unicode character data with a maximum length of 8,000 characters. In 6.5, maximum was 255. Firebird can hold up to 32,767 characters.

6.5

cursor

A reference to a cursor. This can only be used inside stored procedure or triggers; it cannot be used on table declarations.

6.5

datetime

TIMESTAMP

Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.

6.5

decimal

DECIMAL

Fixed precision and scale numeric data from -1038 -1 through 1038 -1.

6.5

float

FLOAT

Floating precision number data from -1.79E + 308 through 1.79E + 308.

6.5

image

BLOB

Variable-length binary data with a maximum length of 231 - 1 (2,147,483,647) bytes.

6.5

int

INTEGER

Integer (whole number) data from -231 (-2,147,483,648) through 231 - 1 (2,147,483,647).

6.5

money

DECIMAL(18, 4)

Monetary data values from -263 (-922,337,203,685,477.5808) through 263 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

7

nchar

CHAR(x) CHARACTER SET UNICODE_FSS

Fixed-length Unicode data with a maximum length of 4,000 characters.

7

ntext

BLOB SUB_TYPE TEXT

Variable-length Unicode data with a maximum length of 230 - 1 (1,073,741,823) characters.

6.5

numeric

NUMERIC

In MS SQL, decimal and numeric are synonims.

7

nvarchar

VARCHAR(x) CHARACTER SET UNICODE_FSS

Fixed-length Unicode data with a maximum length of 4,000 characters.

6.5

real

DOUBLE

Floating precision number data from -3.40E + 38 through 3.40E + 38.

6.5

smalldatetime

TIMESTAMP

Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute. Firebird's has greater range and accuracy.

6.5

smallint

SMALLINT

Integer data from -215 (-32,768) through 215 - 1 (32,767).

6.5

smallmoney

DECIMAL(10, 4)

Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. Note that Firebird's range is greater with this declaration.

2000

sql_variant

BLOB

Allows the storage of data values of different data types.

2000

table

none

Stores results temporarily for later user.

6.5

text

BLOB SUB_TYPE TEXT

Variable-length non-Unicode data with a maximum length of 231 - 1 (2,147,483,647) characters.

6.5

timestamp

INTEGER

A database-wide unique number. In Firebird, you will have to manage uniqueness through generators.

6.5

tinyint

SMALLINT

Integer data from 0 through 255. Firebird does not have such a small data type.

6.5

varbinary

CHAR

Variable-length binary data with a maximum length of 8,000 bytes.

6.5

varchar

VARCHAR

Variable-length non-Unicode data with a maximum of 8,000 characters. Firebird can hold up to 32,765 characters. In 6.5, maximum was 255.

7

uniqueidentifier

CHAR(38)

A globally unique identifier (GUID). In Firebird, you will have to generate the values with User-Defined Functions (UDFs).

A subtle difference in the way NUMERIC and DECIMAL behave in Firebird to bear in mind is that the NUMERIC definition means exactly the precision requested (total number of digits), while DECIMAL mean at least the request precision (the digits to the right of the decimal symbol, however, are maintained exactly). In MS SQL, on the other hand, numeric and decimal are synonims.

There is also a very common quasi-data type, identity, which can only be used when defining tables. This is an int which is automatically assigned a value on insertion and cannot be changed.

Converting the bit data type

The bit data type is used to hold a single boolean value, 0 or 1. MS SQL does not support assigning NULL to this fields. InterBase can emulate this with an INTEGER or a CHAR(1) data type.

The acceptable values can be restricted using domains. For more information on Firebird domains, see the Data Definition documentation.

Converting the identity data type

There are many ways to perform the conversion. In general, Firebird is more flexible and powerful in this respect.

The most direct conversion is to create a BEFORE trigger on the table, assigning to the previous column the value from a generator. This ensures that the number is unique.

For added flexibility, a single generator can be used for many tables. In this case, the type would work in a similar way as a timestamp would - by creating a database-wide unique identifier.

Another common technique is to create a stored procedure to allow access to the generator, and allow clients to pre-fetch the number. This is particularly useful for tools such as Delphi which import the NOT NULL constraint on primary keys and refuse to post records with NULL values.

CREATE TABLE my_table ( 
  my_number integer not null primary key
) 
CREATE GENERATOR my_generator
CREATE TRIGGER my_before_trigger FOR my_table 
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.my_number IS NULL) 
    THEN NEW.my_number = GEN_ID(my_generator, 1); 
END 
CREATE PROCEDURE get_my_generator
RETURNS (new_value INTEGER) 
AS 
BEGIN 
  new_value = GEN_ID(my_generator, 1);
END 

Converting the uniqueidentifier data type

MS SQL depends on uniqueidentifier data types for replication. It is also a handy way of creating a world-wide unique identifier for a record.

To use the field like this, create a BEFORE trigger on the table with the field, and retrieve the value from a UDF.

TODO: write the UDF and write the importing procedure

Prev: Database AdministrationHome: Firebird DocsetUp: Migration from MS-SQL to FirebirdNext: SQL Syntax
Firebird DocsetFirebird Database DocsMigration from MS-SQL to Firebird → Data Types