Share via


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)