Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-15 : 17:33:28
|
Hi.I know how to do paging in SQL Server 2005/2008. Well, it's been a while if I am honest!I will be passing into a SPROC some params including PageNumber and ItemsPerPage. I also will need to return a "total records" result via an OUTPUT parameter.Now, I have a table which has no ID column. but thousands of records.How can I create an efficient query which does the above? (Paging and also finding the total records)Many thanks. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-15 : 18:44:57
|
All of that can be done, but you will need some way to order the rows, so the specific rows in a given page are deterministic. Do you have something like that even if you don't have a PK? Also, if the data changes (insertions/updates/deletes) while you are paging, without an ever-increasing PK, it would not be dependable. |
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-15 : 19:02:49
|
Just to add another spanner to the works... the query in question has a UNION. This brings back the complete resultset of results required for the search criteriaTo go to your question James K, there is a unique record ID and it is set not within the DB but from the external apps.usually I do something like this... without the union:quote: DECLARE @firstRow int DECLARE @lastRow int SELECT @firstRow = (@pageNumber - 1) * @itemsPerPage + 1, @lastRow = (@pageNumber - 1) * @itemsPerPage + @itemsPerPage, @totalRecords = (SELECT COUNT(DISTINCT(t.[TrackID])) FROM Tracks t INNER JOIN Artist a ON a.ArtistID = t.ArtistID INNER JOIN TrackOccasion tOcc ON tOcc.TrackID = t.TrackID WHERE a.ArtistName LIKE '%' + @keyword + '%' AND tocc.OccasionID = @occasionID ); WITH TracksQuery AS ( SELECT a.ArtistName, t.TrackID, t.TrackName, t.FullDuration, t.NonRegisteredPlaybackStartTime, t.NonRegisteredPlaybackEndTime, t.RegisteredPlaybackStartTime, t.RegisteredPlaybackEndTime, t.TrackDescription, t.TrackURL, t.GuidanceNotes, t.Religious, t.CatNo, t.Era, t.Tempo, t.Vocals, t.[Year], t.AdditionalInformation, ROW_NUMBER() OVER (ORDER BY t.[TrackName], a.[ArtistName] ASC) AS RowNumber FROM Tracks t INNER JOIN Artist a ON a.ArtistID = t.ArtistID INNER JOIN TrackOccasion tOcc ON tOcc.TrackID = t.TrackID WHERE a.ArtistName LIKE '%' + @keyword + '%' AND tocc.OccasionID = @occasionID ) SELECT RowNumber, ArtistName, TrackID, TrackName, FullDuration, NonRegisteredPlaybackStartTime, NonRegisteredPlaybackEndTime, RegisteredPlaybackStartTime, RegisteredPlaybackEndTime, TrackDescription, TrackURL, GuidanceNotes, Religious, CatNo, Era, Tempo, Vocals, [Year], AdditionalInformation FROM TracksQuery WHERE RowNumber BETWEEN @firstRow AND @lastRow
but in this instance, it will be a union involvement. |
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-15 : 19:54:35
|
ok so I am almost there but with a problem. Not only is the performance hidious, but I guess it is because of the way the DB is and nothing I can really do about it but this does not quite work.After the UNION, it brings back some more records. So I am expecting just 10 items to show as per the paging request but it brings back 14 (the UNION adds the 4 records)quote: DECLARE @itemsPerPage intSET @itemsPerPage = 10DECLARE @pageNumber intSET @pageNumber = 1DECLARE @totalRecords intDECLARE @firstRow intDECLARE @lastRow intSELECT @firstRow = (@pageNumber - 1) * @itemsPerPage + 1,@lastRow = (@pageNumber - 1) * @itemsPerPage + @itemsPerPage,@totalRecords = (SELECT COUNT(tmpCount.ControlNumber) FROM ( SELECT DISTINCT <fieldListHere> FROM Control <inner join statements> <Left outer join statement> WHERE (<first condition>) AND Manufacturer.MfrName LIKE @manufacturer UNION SELECT DISTINCT <fieldListHere> FROM Control <inner join statements here... 1 extra table than above> <Left outer join statement> WHERE (<first condition>) AND Manufacturer.MfrName LIKE @manufacturer ) AS tmpCount);WITH AllMyRecords AS (SELECT DISTINCT <fieldListHere> ,ROW_NUMBER() OVER (ORDER BY <fields> ASC) AS RowNumber FROM Control inner join statements> <Left outer join statement> WHERE (<first condition>) AND Manufacturer.MfrName LIKE @manufacturer UNION SELECT DISTINCT <fieldListHere>, ROW_NUMBER() OVER (ORDER BY <fields> ASC) AS RowNumber FROM Control <inner join statements here... 1 extra table than above> <Left outer join statement> WHERE (<first condition>) AND Manufacturer.MfrName LIKE @manufacturer )SELECT * FROM AllMyRecords WHERE RowNumber BETWEEN @firstRow AND @lastRow ORDER BY AllMyRecords.MfrName, AllMyRecords.ModelNumber ASC
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-16 : 02:31:42
|
can you show some sample data and explain your problem. what additional data you're getting? and how your output should beWithout that its difficult for us to get your issue as we cant see your systemSee here for guidelines on how to post data in consumable formathttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|