Data Types and RDA
The following tables show the supported data types and data type mappings, and the unsupported data types that you can use with remote data access (RDA) to access data in a Microsoft SQL Server database.
Supported Data Types
| SQL Server data type | SQL Server Compact Edition data type |
|---|---|
bigint (int 8) |
bigint |
binary (n) |
binary (n) |
bit |
bit |
Character (n)(synonym:char (n)) |
nationalcharacter (n) or ntext If the length of the data is 4000 characters or less, RDA maps the SQL Server character data to SQL Server 2005 Compact Edition (SQL Server Compact Edition) nationalcharacter. Otherwise, it maps it to SQL Server Compact Edition ntext. If the length of the ntext data exceeds the length of the character column, push fails when the data is sent from SQL Server Compact Edition to SQL Server. |
character varying (n|max) (synonyms:char varyingvarchar (n|max)) |
national character varying (n) or ntext If the length of the data is 4000 characters or less, RDA maps the SQL Server character varying data to SQL Server Compact Edition national character varying; otherwise, it maps it to SQL Server Compact Edition ntext. If the length of the character varying (max) data exceeds the length of the ntext column, push fails when the data is sent from SQL Server to SQL Server Compact Edition. |
datetime |
datetime |
decimal |
Not Supported. Use numeric. |
double precision |
double precision |
float |
float |
image |
image |
integer(synonym:int) |
integer |
money |
money |
national character (n)(synonyms: nchar (n)) |
national character (n) |
national character varying (n) |
national character varying (n) |
national character varying (max) (synonyms: nvarchar(max)) |
ntext If the length of the national character varying (max) data exceeds the length of the ntext column, push fails when the data is sent from SQL Server to SQL Server Compact Edition. |
ntext |
ntext |
numeric(synonyms:decimal, dec) |
numeric |
real |
real |
smalldatetime |
datetime If the precision of the datetime data exceeds the precision of the smalldatetime column, synchronization fails when the data is sent from SQL Server Compact Edition to SQL Server. |
smallint (int 2) |
smallint (int 2) |
smallmoney |
money If the precision of the money data exceeds the precision of the smallmoney column, synchronization fails when the data is sent from SQL Server Compact Edition to SQL Server. |
sql_variant |
Not supported for RDA |
text |
ntext If the length of the text data exceeds 1,073,741,823 characters, synchronization fails when data is sent from SQL Server to SQL Server Compact Edition. |
tinyint |
tinyint |
uniqueidentifier |
uniqueidentifier |
varbinary (n) |
varbinary (n) |
varbinary (max) |
image If the length of the varbinary (max) data exceeds the length of the image column, synchronization fails when the data is sent from SQL Server to SQL Server Compact Edition. |
XML |
ntext |
Unsupported Data Types
| Data Type | Explanation |
|---|---|
sql_variant, DATE, TIME, UTCDATETIME, FILESTREAM, UDT |
Not supported Do not include columns of this data type in the RDA Pull method. |
Using Data Types
When possible, choose data types that are supported by both SQL Server and SQL Server Compact Edition. As a result, RDA does not have to perform data mapping. When this is not possible, make sure your application validates the values stored in SQL Server Compact Edition to ensure that RDA can map these values between SQL Server and SQL Server Compact Edition.
You cannot pull data from a table having a primary key of type char or varchar with length greater than 4000 characters. These column types with character lengths greater than 4000 are mapped to ntext, and a primary key cannot be created on an ntext column.
Once a data type is mapped to ntext in SQL Server Compact Edition subscription database, it will remain ntext even if the type is changed in SQL Server, until the subscription is reinitialized. Also, if a data type is mapped to ntext in SQL Server Compact Edition, any indexes on the SQL Server data type, such as nvarchar (MAX), are ignored and not created in SQL Server Compact Edition.
See Also
Concepts
Other Resources
Data Type Mappings (SQL Server Compact Edition)