Differences Between SQL Server Compact and SQL Server
Use the following table to determine which Transact-SQL commands, features, and data types are supported by SQL Server Compact, compared with SQL Server.
Transact-SQL command, feature, or data type |
SQL Server Compact 4.0 |
SQL Server 2005 |
SQL Server 2008/SQL Server 2008 R2 |
|---|---|---|---|
SET DEFAULT option in ALTER TABLE (SQL Server Compact). |
Supported |
Not supported |
Supported |
DISTINCT in aggregates (for example, count(distinct). |
Not supported |
Supported |
Supported |
HAVING Clause (SQL Server Compact) clause can contain complex expressions when used in aggregate and grouping queries. |
Only simple column references |
Supported |
Supported |
WITH TIES and PERCENT in TOP clause. |
Not supported |
Supported |
Supported |
Column as arguments to TOP clause. Also, the value of TOP expression must be in the range of integer. |
Not supported |
Supported |
Supported |
TOP (SQL Server Compact)(N) limitation. |
Accepts [0, MAX_INT] for TOP |
Accepts [0, MAX_BIGINT] for TOP |
Supported |
ORDER BY <column number> |
Not supported |
Supported |
Supported |
Syntax of UPDATE STATISTICS (SQL Server Compact) ON <TABLE NAME>. |
Supported |
Supported |
Supported |
Merge Upsert. |
Not supported |
Not supported |
Supported |
Aliasing of database object names with '='. |
Not supported |
Supported |
Supported |
Aliasing of database object names with 'AS'. |
Supported |
Supported |
Supported |
Altering of IDENTITY Property (SQL Server Compact) seed and step ALTER TABLE (SQL Server Compact) statement. |
Supported |
Not supported |
Supported |
Modulo on real, float, money, and numeric data types. |
Not supported |
Supported |
Supported |
Aggregate value expressions contain ntext or image data types. |
Not supported |
Supported |
Supported |
Large objects, for example, ntext and image data types, in ORDER BY clauses. |
Not supported |
Supported |
Supported |
ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses, except when these data types are used with the LIKE or IS NULL predicates. |
Not supported |
Supported |
Supported |
Alter column of type ntext or image. |
Not supported |
Supported |
Supported |
If you want to run multiple queries simultaneously, you must include a new line character for each statement and a semicolon at the end of each statement. |
Supported |
Not supported |
Not supported |
Stored procedures and triggers. |
Not supported |
Supported |
Supported |
Views. |
Not supported |
Supported |
Supported |
User-defined functions and full text. |
Not supported |
Supported |
Supported |
Service Broker. |
Not supported |
Supported |
Supported |
SQL common language runtime (CLR) integration. |
Not supported |
Supported |
Supported |
Table valued parameters. |
Not supported |
Supported |
Supported |
Filtered indexes. |
Not supported |
Not supported |
Supported |
Spatial indexes. |
Not supported |
Supported |
Supported |
Default to smallest available index value. |
Supported |
Not supported |
Not supported |
User-defined functions can be data sources for the APPLY clause. |
Not supported |
Supported |
Supported |
Data compression. |
Not supported |
Supported |
Supported |
In SQL Server, when you make the database case sensitive, object names (for example table names, view names, or stored procedure names) also become case sensitive. In SQL Server Compact, object names remain case insensitive. |
Not supported |
Supported |
Supported |
Data types introduced in SQL Server 2008: Ordpath, sparse columns. |
Not supported |
Supported |
Supported |
Data types introduced in SQL Server 2008: Date, DateTime2, DateTimeOffset, FileStream, Geography, Geometry, HierarchyID, Time. |
Supported |
Supported |
Supported |
See Also
Concepts
What's New in SQL Server Compact 4.0
Transactions (SQL Server Compact)
Other Resources
Features Supported by the Editions of SQL Server 2008 R2