StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Our paging required that we be able to pass in the current page number and page size (along with some additional filter parameters) as variables. Since a select top @page_size doesn't work in MS SQL, we came up with creating an temporary or variable table to assign each rows primary key an identity that can later be filtered on for the desired page number and size. </p> <p>** Note that if you have a GUID primary key or a compound key, you just have to change the object id on the temporary table to a uniqueidentifier or add the additional key columns to the table.</p> <p>The down side to this is that it still has to insert all of the results into the temporary table, but at least it is only the keys. This works in MS SQL, but should be able to work for any DB with minimal tweaks.</p> <blockquote> <p>declare @page_number int, @page_size int -- add any additional search parameters here<br/></p> <p>--create the temporary table with the identity column and the id <br/> --of the record that you'll be selecting. This is an in memory<br/> --table, so if the number of rows you'll be inserting is greater<br/> --than 10,000, then you should use a temporary table in tempdb<br/> --instead. To do this, use <br/> --CREATE TABLE #temp_table (row_num int IDENTITY(1,1), objectid int)<br/> --and change all the references to @temp_table to #temp_table<br/> DECLARE @temp_table TABLE (row_num int IDENTITY(1,1), objectid int)</p> <p>--insert into the temporary table with the ids of the records<br/> --we want to return. It's critical to make sure the order by<br/> --reflects the order of the records to return so that the row_num <br/> --values are set in the correct order and we are selecting the<br/> --correct records based on the page<br/> INSERT INTO @temp_table (objectid)</p> <p>/* Example: Select that inserts records into the temporary table<br/> SELECT personid <br/> FROM person WITH (NOLOCK) <br/> inner join degree WITH (NOLOCK) on degree.personid = person.personid<br/> WHERE person.lastname = @last_name<br/> ORDER BY person.lastname asc, person.firsname asc<br/> */</p> <p>--get the total number of rows that we matched<br/> DECLARE @total_rows int<br/> SET @total_rows = @@ROWCOUNT<br/> --calculate the total number of pages based on the number of<br/> --rows that matched and the page size passed in as a parameter<br/> DECLARE @total_pages int <br/> --add the @page_size - 1 to the total number of rows to <br/> --calculate the total number of pages. This is because sql <br/> --alwasy rounds down for division of integers<br/> SET @total_pages = (@total_rows + @page_size - 1) / @page_size </p> <p>--return the result set we are interested in by joining<br/> --back to the @temp_table and filtering by row_num<br/> /* Example: Selecting the data to return. If the insert was done<br/> properly, then you should always be joining the table that contains<br/> the rows to return to the objectid column on the @temp_table</p> <p>SELECT person.*<br/> FROM person WITH (NOLOCK) INNER JOIN @temp_table tt<br/> ON person.personid = tt.objectid<br/> */<br/> --return only the rows in the page that we are interested in<br/> --and order by the row_num column of the @temp_table to make sure<br/> --we are selecting the correct records<br/> WHERE tt.row_num < (@page_size * @page_number) + 1 <br/> AND tt.row_num > (@page_size * @page_number) - @page_size<br/> ORDER BY tt.row_num</p> </blockquote>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload