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.