TOP clause now supported

So now in SQL Compact 3.5 you can make queries against Northwind.sdf with the following syntax:

SELECT Top(10) *

FROM [Order Details]
ORDER BY [Unit Price] * Quantity DESC

And you will get the 10 most valuable orders listed.


It's nice to see this omission finally added to the SQL Compact syntax.

Hidden gem: Rename table

This new feature is not mentioned anywhere, but listed in the docs:

sp_rename: Changes the name of a user table in the current database. Currently, sp_rename support in SQL Server Compact 3.5 is limited to tables.

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' [ , [ @objtype = ] 'object_type' ]
In the following example I will demonstrate it's use (using the VS 2008 beta 2 Server Explorer):

The current tablename is Test.

Run the sp_rename command, with the 2 parameters: Old name and new name

The query parser complains.

But runs the query none the less.

Do a refresh...

And voila... all from plain old T-SQL!

SQL Compact 3.5 - Manage relationships

Now foreign key relationships can be managed via the UI in Server Explorer, as two new pages have been added to the Table Properties dialog:









This is the "classic" dialog from SQL CE 3.1
(click to enlarge)









Notice the two new pages, a "Add Relations" page.







And a "Manage Relations" page.

This is a nice addition to the SQL Compact Management UI - and is a missing piece requested by many users.

SQL Compact 3.5 testing

I have begun testing SQL Compact 3.5 (as part of VS 2008 beta 2) and will start to post my findings in this blog.