Export (0) Print
Expand All
9 out of 16 rated this helpful - Rate this topic

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, DateTimeOffsetFileStream, Geography, Geometry, HierarchyID, Time.

Supported

Supported

Supported

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.