Data Type Mapping and Considerations
For client and server synchronization, Sync Framework supports server data types that can be mapped to valid data types in SQL Server Compact by using ADO.NET. The following tables show how types are mapped by default. The first two tables show mappings between ADO.NET and SQL Server Compact. The third table shows mappings between SQL Server 2008 and SQL Server Compact. These mappings are possible because these two versions of SQL Server share many of the same data types. If an application requires different mappings, use the SyncSchemaColumn object to map the types. For an example of how to use this object, see How to: Initialize the Client Database and Work with Table Schema.
Mappings Between ADO.NET and SQL Server Compact
ADO.NET data type |
SQL Server Compact data type |
|---|---|
Boolean |
bit |
Byte |
tinyint |
Byte[] |
varbinary |
Char |
nchar |
DateTime |
datetime |
Decimal |
numeric |
Double |
float |
Int16 |
smallint |
Int32 |
int |
Int64 |
bigint |
SByte |
tinyint |
Single |
real |
String |
ntext |
UInt16 |
smallint |
UInt32 |
int |
UInt64 |
bigint |
SQL Server Compact data type |
ADO.NET data type |
|---|---|
bigint |
Int64 |
binary |
Byte[] |
bit |
Boolean |
datetime |
DateTime |
float |
Double |
image |
Byte[] |
int |
Int32 |
integer |
Int32 |
money |
Decimal |
nchar |
String |
ntext |
String |
numeric |
Decimal |
nvarchar |
String |
real |
Single |
smallint |
Int16 |
timestamp |
Byte[] |
tinyint |
Byte |
uniqueidentifier |
Guid |
varbinary |
Byte[] |
Mappings Between SQL Server 2008 and SQL Server Compact 3.5
SQL Server 2008 data type |
SQL Server Compact 3.5 SP 1 data type |
|---|---|
bigint |
bigint |
binary(n) |
varbinary |
bit |
bit |
char(n) |
nchar(n) or ntext If the length of the data is 4,000 characters or less, nchar is used; otherwise, ntext is used. |
CLR user-defined type |
Not supported. |
date |
nchar(27) value of the form 'YYYY-MM-DD' 1 |
datetime |
datetime |
datetime2 |
nchar(27) value of the form 'YYYY-MM-DD hh:mm:ss.nnnnnnn' 1 |
datetimeoffset |
nvarchar(34) value of the form 'YYYY-MM-DD hh:mm:ss.nnnnnnn [+/-] hh:mm' 1, 2 |
decimal |
Not supported; use numeric. |
double |
double |
float |
float |
geography |
Not converted by Sync Services 3 |
geometry |
Not converted by Sync Services 3 |
hierarchyid |
Not converted by Sync Services 3 |
image |
image |
int |
int |
money |
money |
nchar(n) |
nchar(n) |
ntext |
ntext |
nvarchar(n) |
nvarchar(n) |
nvarchar(max) |
ntext If the length of the data exceeds the length of the ntext column, synchronization fails. |
numeric |
numeric |
real |
real |
smalldatetime |
datetime If the precision of the datetime data exceeds the precision of the smalldatetime column, synchronization fails. |
smallint |
smallint |
smallmoney |
money |
sql_variant |
ntext If binary data exists in the sql_variant column, the binary data must be an even number of bytes or a conversion error occurs. |
text |
ntext If the length of the text data exceeds 1,073,741,823 characters, synchronization fails. |
time |
nvarchar(16) value of the form 'hh:mm:ss.nnnnnnn' 1 |
tinyint |
tinyint |
uniqueidentifier |
uniqueidentifier |
varbinary(n) |
varbinary(n) |
varbinary(max) |
image If the length of the data exceeds the length of the image column, synchronization fails. |
varchar(n) |
nvarchar(n) or ntext If the length of the data is 4,000 characters or less, nvarchar is used; otherwise, ntext is used. |
varchar(max) |
ntext If the length of the data exceeds the length of the ntext column, synchronization fails. |
xml |
ntext |
1 Keep the following issues in mind for these date and time types:
If the server provider is hosted on a computer that is running ADO.NET 2.0, these types are converted on the server. If the server provider is hosted on a computer that is running ADO.NET 2.0 SP1, the types are sent to the client and then converted there.
Values can be treated differently at the client and server. For example, with a column of type datetime2 at the server, the values '0001-01-01 00:00:00.0000000' and '0001-01-01 12:00 AM' are the same. On the client, the values are treated as different strings. This behavior has the following consequences:
Columns of these types should not be used in primary keys.
Columns of these types should be treated as read-only on the client, unless an application ensures that the formatting of values is controlled.
2 If the server provider is hosted on a computer that is running ADO.NET 2.0 SP1, ADO.NET 2.0 SP1 must also be available on the client for conversion to succeed. Automatic conversion of datetimeoffset on the client is not supported by .NET Compact Framework 2.0 SP1 or .NET Compact Framework 3.5.
3 To synchronize these types, you can convert them to varbinary(max) or image on the server by using the SyncSchemaColumn object. For an example of how to use this object, see How to: Initialize the Client Database and Work with Table Schema.
Mapping Considerations
Sync Services has the following behavior with data types:
Data from columns that have the timestamp data type is not copied from the server. timestamp columns are mapped to the binary(8) data type of during synchronization. This is because timestamp data is typically meaningful only in the database in which it was created.
ROWGUID columns are copied from the server to the client database, but the SQL Server ROWGUIDCOL property is not. For an example of how to set this property, see How to: Initialize the Client Database and Work with Table Schema.
Identity columns are copied from the server to the client database, but the identity seed and increment are always set to 0 and 1, respectively. This is regardless of how the properties were set in the server database. SQL Server Compact identity columns must have a data type of int or bigint. SQL Server Compact identity columns cannot have a data type of smallint, tinyint, decimal, or numeric. For more information about identity columns, see Selecting an Appropriate Primary Key for a Distributed Environment.
Computed columns are copied to the client database during download, but the computed column property is not. We recommend that you not use computed columns in bidirectional and upload scenarios, because insert operations can fail on upload. To avoid this problem, filter the columns out of the data set by not including them in the WHERE clause of the SELECT statements that are used to retrieve data. For more information about filtering, see How to: Filter Rows and Columns.