Dynamic Table Paging*
Paging data in your application, be it thin or thick client, is a frequent need: list of customers, address directory, invoice details, etc. – any number of situations may crop up where the paging grid in the UI becomes a necessity.
If your data is big enough to be paged in your display, then, for the most part, it is a large enough set that you are not advised to pull all of the set back at once to be cached by the UI, particularly if your storage medium is SQL Server. A lot of you are probably familiar with using row_number, the OVER statement and maybe even a CTE to resolve ordering the data and grabbing groups at a time for the application. But what do you do if you need to support paging, and you are not sure what the table is going to be?
I will point out that the title is asterisked, my caveat to this whole discussion is: should this be necessary. Beside that point though, it is a relatively interesting issue. How would you implement a paging query when you don’t know what table you might want, or when the schema might be unknown to you? (Again, this should not be used widespread, but scenarios can come up where this makes sense).
The first requirement in this scenario is getting table meta-data back to your application; in SQL 2005 and above, this is a simple matter of querying sys.tables. After you have the table list, the other two variables to declare in this scenario are your page size and what page in the paged data you desire. The full script is provided below – most of it is pretty straight forward, except that you will notice that I pull a little stunt in getting the column(s) to do the order by on for the row_number and over functions.
Given that we need something to order this data by, and we don’t know the schema, I query the information_schema tables to get the tables primary key column or columns – if none, it will just take the first column. Not the most advanced, but then again, this script shoots blind – which is the point of this example.
The other little bonus is that it also removes column types from the query that would likely choke an auto-column generating .Net control – varbinary, xml, etc. are excluded in the columns dynamically selected from the table parameter – to get a closer look at this, you can modify the script and print the @TableSelectColumns variable.
I would be interested to hear how others might apply this scenario, so if you are up for it, post how you used this or a similar script!
@TableObjectId int, @PageSize int,
@PageIndex int, @TableRows int,
@TableName nvarchar(75), @KeyOrderColumn nvarchar(50),
@StrSQL nvarchar(4000), @PageRowStart int,
@PageRowEnd int, @TableSelectColumns nvarchar(2000),
— Set the parameters
set @TableName = Your Table Name Here
set @PageSize = Your Page Size Here
set @PageIndex = Your Page Index here
set @StrSQL = ‘select @columnsOUT = coalesce(@columnsOUT + ”,”, ””)+ ”[”+c.name+”]”
from sys.columns c
inner join sys.types t on t.system_type_id = c.system_type_id
where c.object_id = OBJECT_ID(”’ + @TableName + ”’)
and t.system_type_id not in (34,35,98,99, 165,173,241,231)’
set @Params = ‘@columnsOUT nvarchar(2000) output’
exec sp_executesql @StrSQL,@Params, @columnsOUT = @TableSelectColumns output
if(@PageIndex < 2)
set @PageRowStart = 0
if(@PageIndex > 1)
set @PageRowStart = ((@PageIndex * @PageSize) – @PageSize)
set @PageRowEnd = (@PageIndex * @PageSize)
set @TableObjectId = (select OBJECT_ID(@TableName))
— select the key column(s)
select @KeyOrderColumn = coalesce(@KeyOrderColumn + ‘, ‘, ”)
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
where CONSTRAINT_TYPE = ‘PRIMARY KEY’
and A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
and object_id(A.TABLE_NAME) = @TableObjectId
— Grab the first column since we have no key column declared
if(len(@KeyOrderColumn) < 1 or @KeyOrderColumn is null)
set @KeyOrderColumn = ( select name from sys.syscolumns where id = @TableObjectId and colorder = 1)
set @TableRows = (select rows from sys.partitions where object_id = @TableObjectId and index_id < 2)
if(@PageSize > @TableRows)
set @StrSQL = ‘select ‘ + @TableSelectColumns + ‘ from ‘ + @TableName + ‘ order by ‘ + @KeyOrderColumn
if(@PageSize < @TableRows)
set @StrSQL = ‘select ‘ + @TableSelectColumns
+ ‘, RowNumber into #Results from (select *, row_number() over(order by ‘
+ @KeyOrderColumn + ‘ ) as RowNumber from ‘
+ @TableName + ‘) SortTable where RowNumber > ‘
+ cast(@PageRowStart as nvarchar) + ‘ and RowNumber < ‘
+ cast(@PageRowEnd as nvarchar)
+ ‘ alter table #Results drop column RowNumber ‘
+ ‘ select * from #Results’
exec sp_executesql @StrSQL