Data Types and Replication

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

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

SQL Server Data Type SQL Server Compact Edition 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 Edition replication maps the SQL Server character data to SQL Server Compact Edition national character. Otherwise, it maps the character data to SQL Server Compact Edition 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 Edition 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 Edition replication maps the SQL Server character varying data to SQL Server Compact Edition national character varying; otherwise, it maps the character varying data to SQL Server Compact Edition 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 Edition 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 Edition.

Computed Columns

Not supported.

If you use the Publication wizard and indicate that SQL Server Compact Edition 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.

datetime

datetime

decimal

Not supported. Use numeric.

double precision

double precision

float

float

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 Edition.

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 Edition 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 Edition 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 Edition.

timestamp

Not supported.

If you use the Publication wizard and indicate that SQL Server Compact Edition 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.

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 Edition.

varchar

See character varying

XML

ntext

Whenever possible, choose data types that are supported by both SQL Server and SQL Server Compact Edition, 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 Edition database to ensure that replication can map these values between SQL Server and SQL Server Compact Edition.

For more information about SQL Server data types, see "Data Types" in SQL Server Books Online.

The following restrictions apply to SQL Server Compact Edition 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 Edition 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 Edition identity columns must have a data type of integer (int 4) or bigint (int 8). SQL Server Compact Edition 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 Edition.
    SQL Server Compact Edition 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 Edition 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 2000 publication, the computed column and timestamp data types are not supported. When subscribing to a SQL Server 2005 publication, the computed column, timestamp, date, time, and utcdatetime data types are not supported.
  • CHAR and NTEXT data type in SQL Server and SQL Server Compact Edition
    SQL Server permits the CHAR datatype to be larger than the CHAR datatype for SQL Server Compact Edition. To replicate the contents, the large CHAR datatype is converted to NTEXT data types on SQL Server Compact Edition. Although SQL Server and SQL Server Compact Edition 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 Edition 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 Edition database remains ntext until the subscriber is reinitialized.

Community Additions

ADD
Show: