MSSQL Data Types (Magic xpa 3.x)
The following table shows the results of a Magic xpa Get Definition operation from an MSSQL table. Magic xpa equivalents for Microsoft SQL server data types are shown.
|
SQL Server Data Type
|
Attribute
|
Magic xpa Storage Type
|
Storage Size
|
Picture
|
|
CHAR(n), VARCHAR(n)
|
Alpha
|
Zstring
|
n+1, 2- 8001
|
n, 1-8000
|
|
UNIQUEIDENTIFER
|
Alpha
|
Zstring
|
39
|
38
|
|
Note: Assuming there is a column within a table entry that is defined as a UNIQUEIDENTIFER, the following rules apply:
If a default value such as NewID(), is defined in the column’s Database Default property, this function’s value is retrieved from the database and sent as a part of the INSERT statement.
If there is no Database default value defined, Magic xpa refers to the column as a regular column.
|
|
VARCHAR(max) -
for MSSQL 2005
|
Blob
|
Ansi
|
|
|
|
TEXT
|
Blob
|
Ansi
|
|
|
|
nTEXT
|
Blob
|
Unicode
|
|
|
|
INTEGER
|
Numeric
|
Signed Integer
|
4
|
10
|
|
SMALLINT
|
Numeric
|
Signed Integer
|
2
|
5
|
|
TINYINT
|
Numeric
|
Unsigned Integer
|
1
|
3
|
|
NUMERIC(p, s)
|
Numeric
|
Float
|
8
|
p-s, s
|
|
DECIMAL(p, s)
|
Numeric
|
Float
|
8
|
p-s, s
|
|
DOUBLE PRECISION
|
Numeric
|
Float
|
8
|
According to the Float property in the DBMS Properties (default: 10.3)
|
|
FLOAT
REAL
|
Numeric
|
Float
|
4
|
5.2
|
|
MONEY
|
Numeric
|
Float
|
8
|
According to the Float property in the DBMS Properties (default: 10.3)
|
|
SMALLMONEY
|
Numeric
|
Float
|
4
|
5.2
|
|
DATETIME
|
1. Date
2. Time
|
Creates two fields, one with String Date and one with String Time, that are connected by the Part of Datetime property.
|
8
|
##/##/####
|
|
SMALLDATETIME
|
1. Date
2. Time
|
Creates two fields, one with String Date and one with String Time, that are connected by the Part of Datetime property.
|
8
|
##/##/####
|
|
DATE (from SQL Server 2008)
|
Date
|
String Date
|
8
|
(Since version: 1.9)
|
|
TIME (from SQL Server 2008)
|
Time
|
String Time
Note: Magic xpa does not support Time(N) fields, except TIME(7) which is equivalent to TIME.
|
6
|
(Since version: 1.9)
|
|
BINARY( n),
|
Blob
|
String
|
n, 1- 8000
|
n, 1-8000
|
|
VARBINARY( n)
|
Blob
|
Binary
|
|
|
|
VARBINARY(max) -
for MSSQL 2005
|
Blob
|
Binary
|
|
|
|
XML
|
Blob
|
Ansii
|
|
|
|
IMAGE
|
Blob
|
Binary Large Object
|
Default (12)
|
|
|
BIT
|
Logical
|
Integer Logical
|
1
|
5
|
|
TIMESTAMP
|
Alpha
|
String
|
8
|
8
|
|
nCHAR
|
Unicode
|
ZUnicode
|
n*2+2
|
1-4000
|
|
nVARCHAR
|
Unicode
|
ZUnicode
|
n*2+2
|
1-4000
|
|
nVARCHAR(max) -
for MSSQL 2005
|
Blob
|
Unicode
|
|
|
|
Note:
|
-
When you use the Magic xpa Get Definition utility, Magic xpa will ask you if you want to separate the MSSQL DATETIME and SMALLTIME data types into two Magic xpa data types, one Date and one Time, which will be connected by the Part of Datetime property.
-
To see all parts of a DATE column in the format ‘YYYY/MM/DD HH:MM:SS.mmm’ and map to an Alpha attribute, you should map the MSSQL DATETIME and SMALLDATETIME data types to Magic xpa’s Alpha attribute. This can be done by specifying ‘SQL_DATETOALPHA=Y’ in the Database Information field in the Database Properties dialog box.
-
The storage size is calculated according to the database of the table - according to the gateway's storage table. The storage size is recalculated on import.
|
Using a MSSQL Table with Bigint Fields
