Actually I think that the SQL dialect on SQLServerCE is fine.
The difference is that SQLServer is a database server intended to serve
1-10000 users (client apps, web requests or whatever) while SQLServerCE
is an embedded database that is superb for serving 1 users but can serve
a small number of users fine.
I would use SQLServer for web app and for a server app, but SQLServerCE
for a desktop app (storing data locally).