SQL Server compact use NOTE 2 SQL Server compact Paging

SQL Server compact does not support top and row_number. the frequently used SQL pages cannot be used in SQL Server compact. How does SQL Server compact implement paging?

The answer is order by, offset, and fetch. However, SQL Server compact4.0 and later are required.

 

With the offset-Fetch clause, you can extract only the results of a certain time range or page from the result set. Offset-Fetch can only be used with the order by clause.

Order by {order_by_expression [ASC | DESC]} [,... n] [<offset_fetch>]

 

<Offset_fetch >::={ offset {integer_constant | offset_row_count_expression} {row | rows} [fetch {first | next} {integer_constant | fetch_row_count_expression} {row | rows} Only]}

 

Offset {integer_constant | offset_row_count_expression} {row | rows}

Specifies the number of rows that will be skipped before the row is returned from the query expression. The offset clause parameter can be an integer or expression greater than or equal to zero. Row and rows can be used interchangeably.

 

Fetch {first | next} <row count expression> {row | rows}

Only specifies the number of rows returned after the offset clause is processed. The parameters in the fetch clause can be integers or expressions greater than or equal to 1. Row and rows can be used interchangeably. Similarly, first and next can be used interchangeably.

 

Use the limit in offset-Fetch

 

Order by is required to use the offset and fetch clauses.

The offset clause must be used with fetch. Order... Fetch.

The offset/fetch row count expression can be any arithmetic, constant, or parameter expression that returns an integer. This row's counting expression does not support scalar queries.

 

The following example shows how to use the offset-Fetch clause with order.

 

Example 1 describes how to use the offset-Fetch clause with order by clause. The first 10 rows are skipped from the sorting result set and the remaining rows are returned.

SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS;

 

Example 2: skip the first 10 rows from the sorting result set and return the next 5 rows.

SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;