T-SQL differences between SQL Server Compact Edition and real SQL Server?

Question

Coming from an extensive SQL Server T-SQL background, I'm having trouble finding any specific documentation describing the differences in T-SQL support between SQL CE and full blown SQL Server. What are some of the differences you've run into? Is there a side-by-side T-SQL comparison somewhere? I've seen the MSDN documentation here, but I cannot seem to find any details about what T-SQL statements aren't portable between the two. One difference I found right away is the lack of an ISNUMERIC() function. Any others?

NOTE: I'm specifically more interested in differences in DML functionality than DDL and administrative functions which I'm sure are numerous and completely uninteresting.

Answer 1

• SQL Reference (SQL Server Compact 4.0)

SQL Server Compact supports only the Transact-SQL grammar documented in this section. SQL Reference for SQL Server Compact is a subset of Transact-SQL for SQL Server.

• Syntax conventions and query elements

Syntax Conventions

• Data Types

SQL Server Compact 4.0 supports the following data types.

• Functions

SQL Server Compact supports the following functions.

• Information Schema

The following table lists Information Schema views that are supported by SQL Server Compact.

• Operators

SQL Server Compact supports the following operators.

• Reserved Words

A reserved word in SQL Server Compact does not necessarily function the same way as the corresponding word in SQL Server. The following table lists the reserved words in SQL Server Compact.

• Features not supported in SQL Server Compact 4.0

Starting with SQL Server Compact 4.0, there are few scenarios and features that are not supported by SQL Server Compact. This topic gives a brief account of features that were supported by the preceding versions of SQL Server Compact, including SQL Server Compact 3.5 SP2.

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

previous version:

• SQL Server Compact 3.5 SP2 - SQL Reference

• SQL Server Compact 3.0 - SQL Reference