SQL Compact select top 1

Question

While porting an application from SQL 2005 to SQL Server Compact Edition, I found that I need to port this command:

SELECT TOP 1 Id FROM tblJob WHERE Holder_Id IS NULL

But SQL Server Compact Edition doesn't support the TOP keyword. How can I port this command?

Answer 1

SELECT TOP(1) Id 
FROM tblJob 
WHERE Holder_Id IS NULL

Need the brackets as far as I know.

reference: http://technet.microsoft.com/en-us/library/bb686896.aspx

addition: likewise, only for version 3.5 onwards

Answer 2

This is slightly orthogonal to your question.

SQL Server Compact Edition actually doesn't perform very well with SQL queries. You get much better performance by opening tables directly. In .NET, you do this by setting the command object's CommandText property to the table name, and the CommandType property to CommandType.TableDirect.

If you want to filter the results, you will need an index on the table on the column(s) you want to filter by. Specify the index to use by setting the IndexName property and use SetRange to set the filter.

You can then read as many or as few records as you like.