Data Type Mapping and Considerations (Synchronization Services)

Microsoft Synchronization Services for ADO.NET supports server data types that can be mapped to valid data types in Microsoft SQL Server Compact 3.5 Service Pack 1 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 3.5. The third table shows mappings between SQL Server 2005 and SQL Server Compact 3.5. 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

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(10) value of the form 'YYYY-MM-DD' 1
datetime	datetime
datetime2	nvarchar(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 Synchronization Services 3
geometry	Not converted by Synchronization Services 3
hierarchyid	Not converted by Synchronization 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

Synchronization 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 3.5 identity columns must have a data type of int or bigint. SQL Server Compact 3.5 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 (Synchronization Services).
• 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.

See Also

Concepts

Considerations for Application Design and Deployment (Synchronization Services)