Paging in SQL Server problems

2018-07-03 16:33:08

I have searched for paging in SQL Server. I found most of the solution look like that

What is the best way to paginate results in SQL Server

But it don't meet my expectation.

Here is my situation:

I work on JasperReport, for that: to export the report I just need pass the any Select query into the template, it will auto generated out the report

EX : I have a select query like this:

Select * from table A

I don't know any column names in table A. So I can't use

Select ROW_NUMBER() Over (Order By columsName)

And I also don't want it order by any columns.

Anyone can help me do it?

PS: In Oracle , it have rownum very helpful in this case.

Select * from tableA where rownum > 100 and rownum <200

Paging with Oracle

You should use ROW_NUMBER with an ORDER BY - because without an ORDER BY there is no determinism in how rows are returned. You can run the same query three times and get the results back in three different orders. Especially if merry-go-round scans come into play.

So unless you want your report to have the possibility of showing the same rows to users on multiple pages, or some rows never on any page, you need to find a way to order the result set to make it deterministic.

From my opinion, you can use sql query to find out how many columns in a table, and then find out a proper one for ' order by ' to depend on. The script of how to get out columns of an table refer to : How can I get column names from a table in SQL Server?

Check out this link

http://msdn.microsoft.com/en-us/library/ms186734.aspx

SQL Server has similar function ROW_NUMBER. Though it behaves a bit differently.

SQL Server provides no guarantee of row order unless you have have specified a column in order by clause. I would recommend that you give an order by clause that has unique values.

链接地址: http://www.djcxy.com/p/93854.html

上一篇: SQl选择特定的列，但使用*

下一篇: 在SQL Server中分页问题