SQL Server Compact does not fully support all SQL Server data types. As a subscriber to SQL Server publications, SQL Server Compact must translate unsupported types to those that are supported.

Supported Data Types and Data Type Mappings

The following table shows the data type mappings that are performed when replicating between SQL Server and Microsoft SQL Server Compact 3.5. The table lists mappings for each SQL Server data type and describes restrictions or special behaviors.

SQL Server Data Type

SQL Server Compact Data Type

bigint

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, SQL Server Compact replication maps the SQL Server character data to SQL Server Compact national character. Otherwise, it maps the character data to SQL Server Compact ntext. If the length of the ntext data exceeds the length of the character column, synchronization fails when the data is sent from SQL Server Compact to SQL Server.

character varying(n)(synonym: varchar(n))

national character varying or ntext

If the length of the data is 4000 characters or less, SQL Server Compact replication maps the SQL Server character varying data to SQL Server Compact national character varying; otherwise, it maps the character varying data to SQL Server Compact ntext. If the length of the ntext data exceeds the length of the character varying column, synchronization fails when the data is sent from SQL Server Compact to SQL Server.

character varying(MAX)(synonym: varchar(MAX))

ntext

If the length of the character varying(MAX) data exceeds the length of the ntext column, synchronization fails when the data is sent from SQL Server to SQL Server Compact.

Computed Columns

Not supported.

If you use the Publication wizard and indicate that SQL Server Compact subscriptions will be used, any column of the data type Computed Column is vertically partitioned out of the publication. If you do not use the wizard, you must exclude columns of this data type in the publication.

date

nchar(10) value of the form 'YYYY-MM-DD'

SQL Server Compact stores wide characters. Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.

datetime

Datetime

datetime2

nvarchar(27) value of the form 'YYYY-MM-DD hh:mm:ss.nnnnnnn'

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'

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

geography

image

Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.

geometry

image

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)(synonym: nchar(n))

national character(n)

national character varying(n)(synonym: nvarchar(n))

national character varying(n)

national character varying(MAX)(synonym: nvarchar(MAX))

ntext

If the length of the national character varying(MAX) data exceeds the length of the ntext column, synchronization 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.

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 when the data is sent from SQL Server to SQL Server Compact.

time

nvarchar(16) value of the form 'hh:mm:ss.nnnnnnn'

SQL Server Compact stores wide characters. Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.

timestamp

Not supported.

If you use the Publication wizard and indicate that SQL Server Compact subscriptions will be used, any column of the data type timestamp is vertically partitioned out of the publication. If you do not use the wizard, you must exclude columns of this data type in the publication. If timestamp/rowversion column is not partitioned vertically, the data in this column is not replicated.

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.

varchar

See character varying

XML

ntext

Whenever possible, choose data types that are supported by both SQL Server and SQL Server Compact, so that it is not necessary for replication to perform data mapping. When this is not possible, your application should validate the values stored in the SQL Server Compact database to ensure that replication 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. For more information about SQL Server data types, see Data Types in SQL Server Books Online.

Data Type Restrictions

The following restrictions apply to SQL Server Compact subscribers:

  • Indexes

    You cannot publish a table having an index on varchar (MAX), nvarchar(MAX), varbinary(MAX), and XML columns. Creating the SQL Server Compact subscription fails because these column types are mapped to ntext or image, and a primary key cannot be created on an ntext or image column.

  • Identity Columns

    SQL Server Compact identity columns must have a data type of integer (int 4) or bigint (int 8). SQL Server Compact identity columns cannot have a data type of smallint, tinyint, decimal, or numeric. If you subscribe to a publication having an identity column other than integer (int 4) or bigint (int 8), creating that subscription fails on SQL Server Compact.

    SQL Server Compact lets you modify the seed and increment values at the Subscriber by using the ALTER TABLE table_name ALTER COLUMN column_name IDENTITY (seed,increment) statement. This lets you manage identity ranges manually. However, if your publication includes an identity column and the identity range is being managed by the Publisher, you should not modify the seed or increment values at the Subscriber. If you specify a seed that is greater than your allocated range identity, SQL Server Compact returns an error when you try to insert a new record in the table. When you next synchronize, the Publisher corrects the problem by assigning your Subscriber a new identity range.

  • Unsupported Data Types

    When subscribing to a SQL Server 2005 publication, the computed column, timestamp, date, time, hierarchyid, filestream, and utcdatetime data types are not supported.

  • CHAR and NTEXT data type in SQL Server and SQL Server Compact

    SQL Server permits the CHAR datatype to be larger than the CHAR datatype for SQL Server Compact. To replicate the contents, the large CHAR datatype is converted to NTEXT data types on SQL Server Compact. Although SQL Server and SQL Server Compact permit changing the datatype of a CHAR column, neither permits changing the datatype of an NTEXT column. Therefore, although it would be possible to change a large CHAR datatype on the SQL Server side, the change would fail on the SQL Server Compact side because it is no longer a CHAR datatype.

  • NTEXT or IMAGE data types

    If a column is mapped to ntext, and the SQL Server changes the data type for the same column to char, nchar, etc, the column on SQL Server Compact database remains ntext until the subscriber is reinitialized.

Concepts

Data Types and RDA

Managed Data Type Mappings (SQL Server Compact)