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.
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 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.
Data Type Restrictions
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 (int8), 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 TABLEtable_nameALTER COLUMNcolumn_nameIDENTITY (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.
See Also
Concepts
Other Resources
Data Type Mappings (SQL Server Compact Edition)