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 3.5 data type |
|---|---|
bigint (int 8) |
bigint |
binary (n) |
binary (n) |
bit |
bit |
Character (n)(synonym:char (n)) |
national character (n) or ntext If the length of the data is 4000 characters or less, RDA maps the SQL Server character data to SQL Server Compact national character. Otherwise, it maps it to SQL Server Compact 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 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 national character varying; otherwise, it maps it to SQL Server Compact 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. |
date |
nchar(10) value of the form 'YYYY-MM-DD' .gif "ms173018.note(en-us,SQL.100).gif") Note:
SQL Server Compact stores wide characters. Conversion support for this type was provided by the SQL Server Compact SP1 release.
|
datetime |
datetime |
datetime2 |
nvarchar(27) value of the form 'YYYY-MM-DD hh:mm:ss.nnnnnnn' Note:
SQL Server Compact stores wide characters. Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.
|
datetimeoffset |
nvarchar(34) value of the form 'YYYY-MM-DD hh:mm:ss.nnnnnnn [+/-] hh:mm' Note:
SQL Server Compact stores wide characters. Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.
|
decimal |
Not Supported. Use numeric. |
double precision |
double precision |
float |
float |
filestream |
image Note:
Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.
|
geography |
image Note:
Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.
|
geometry |
image Note:
Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.
|
hierarchyid |
image Note:
Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.
|
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. |
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 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 to SQL Server. |
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. |
time |
nvarchar(16) value of the form 'hh:mm:ss.nnnnnnn' Note:
SQL Server Compact stores wide characters. Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.
|
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. |
XML |
ntext |
Unsupported Data Types
| Data Type | Explanation |
|---|---|
sql_variant, UTCDATETIME, 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. 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 to ensure that RDA can map these values between SQL Server and SQL Server Compact.
Existing applications that do not natively support date, datetime2, datetimeoffset, and time need to deal with data that is mapped to compatible data types like nchar and nvarchar.
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 the SQL Server Compact 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, any indexes on the SQL Server data type, such as nvarchar (MAX), are ignored and not created in SQL Server Compact.
See Also
Concepts
Other Resources
Managed Data Type Mappings (SQL Server Compact)