SQL Compact select top 1

Question

While poring an app from SQL 2005 to SQL Server Compact, I need to port command

SELECT TOP 1 Id FROM tblJob WHERE Holder_Id IS NULL

But SQL Server Compact does not know the TOP keyword. Any idea how to port such command? Than you for your answers in advance!

Answer 1

a quick googling showed this link: http://www.windows-tech.info/15/5fd9a279cc8f339c.php

Maybe this will help you.

Answer 2

Looks like it can't be done in compact. You have to read all the jobs, or make a SqlReader, and just read the first one.

Answer 3

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 4

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.

Answer 5

@Mike Dimmick Thank you. Would be interesting to see a test. Our app is using NHibernate. We use SQL Compact only for non-intall demo CD, so perfomance is not an issue. At least until is not order of magnitude different from Express edition.

The command in query is from initial script to fill up demo data into our DB.

Answer 6

I have installed Microsoft SQL Server 3.5 SP1 English and when running

SELECT * FROM tblJob
go
SELECT TOP(1) * FROM tblJob

Running it in Mangement Studio the first query runs ok, second failes with

(1 row(s) affected) 
Major Error 0x80040E14, Minor Error 25501 
SELECT TOP(1) * FROM tblJob There was an error parsing the query. [ Token line number = 1,Token line offset = 8,Token in error = TOP ]

Answer 7

Well found a reason. Management studio carries and uses it's own version od SQL Server Compact. See more in http://en.wikipedia.org/wiki/SQL_Server_Compact.

SQL Server Management Studio 2005 can read and modify CE 3.0 and 3.1 database files (with the latest service pack), but the SQL Server Management Studio 2008 from the "Katmai" 2008 CTP release (or later) is required to read version 3.5 files.

The RTM of SQL Server Management Studio 2008 and Microsoft Visual Studio Express 2008 SP1 can create, modify and query CE 3.5 SP1 database files.

Answer 8

I've used Fill method of SqlCEDataAdapter. You can do:

DbDataAdapter.Fill (DataSet, Int32, Int32, String) Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataSet and DataTable names. Supported by the .NET Compact Framework.

http://msdn.microsoft.com/en-ie/library/system.data.common.dbdataadapter.fill(v=VS.80).aspx