SQL Server CE 3.5 SELECT TOP

Another gotcha but this time in the dialect that SQL Compact Edition 3.5 uses.

Version 3.5 introduced a couple of new features to make it's SQL more compatible with that of SQL Server and one of those is TOP.

Simple you'd think but oh on - they've decided to use a different dialect for the same command.

In SQL server you write:

SELECT TOP 10 [name] FROM [people] ORDER BY [name]

In SQL CE 3.6 you write:

SELECT TOP (10) [name] FROM [people] ORDER BY [name]

How annoying as it means that the same piece of SQL can't be used without a little translation layer. Why anybody though that wrapping the number of lines in parenthesis was a good idea I'll never know!