Paging data using SQL

Paging is one of those things that you wish there was more support for, but you want real control over. I’ve found a number of ways to do it, each has advantages and drawbacks. These are the two best solutions I’ve found. I have to list two, as one works only when sorting by unique db fields, and the other is slower but can be sorted by any field.

For data sorted by a unique field (eg. primary key or unique Name field): A More Efficient Method for Paging Through Large Result Sets

For example:

CREATE PROCEDURE [dbo].[stored_procedure_name] ( @startRowIndex int, @maximumRows int ) AS DECLARE @first_id int, @startRow int -- A check can be added to make sure @startRowIndex isn't > count(1) -- from employees before doing any actual work unless it is guaranteed -- the caller won't do that -- Get the first employeeID for our page of records SET ROWCOUNT @startRowIndex SELECT @first_id = employeeID FROM employees ORDER BY employeeid -- Now, set the row count to MaximumRows and get -- all records >= @first_id SET ROWCOUNT @maximumRows SELECT e.*, d.name as DepartmentName FROM employees e INNER JOIN Departments D ON e.DepartmentID = d.DepartmentID WHERE employeeid >= @first_id ORDER BY e.EmployeeID SET ROWCOUNT 0 GO



For data that needs to be sorted by a non-unique field, a different approach is needed. There might be a way to adapt the previous method… but I haven’t been able to yet.

Easy, yet fast, SQL paging solution

For example:

SELECT TOP 5 * FROM Books WHERE BookID NOT IN ( SELECT TOP 45 BookID FROM Books ORDER BY BookID ) ORDER BY BookID



The problem seems to centre on there being no way to distinguish between rows when sorting by a non-unique field. It is possible to store the primary key, however the sorting is complex.
For example, the first item of a page is test2, below. but since we sort by date, test1 is selected instead.

Name date test3 2008-04-09 test2 2008-04-09 test4 2008-04-09 test1 2008-04-10 test5 2008-04-10



This can be prevented by using a primary key. However, the sorting cannot be maintained, as any subsequent rows with Name > selected row will not sort lower. The goal is to find a way that retrieves items in the exact same order, and allows the selection to start from any row.

Database Conundrum

This error is bugging me:

Operand type clash: nvarchar is incompatible with image

It happens when I try to submit a complex form. Problem is, I don’t know which field is causing the error. Not much on internet, probably just sending the wrong datatype. Debugging time!

Update: No easy fix for this one. There are some similar cases here and here, but nothing that’s quite the same. Still don’t know which field is the problem. Seems like it has something to do with nulls, as those links say.

Update 2: Fixed it! Problem was with datatypes agreeing between page code, stored procedure and table design. (stored procedure had image, other two were varchar).

ASP.Net Validation

Some neat tricks that I didn't know about:

Bike in the snow

Bike - Originally uploaded by kramdk.

Well, I found this bike on the way back from a Christmas party. It looked quite cool covered in snow, so I thought a picture was in order. Dunno how anyone could ride on it - Would take ages to get the snow off, and then it'd be soaked. Well, they'd probably just pick it up and take the train, like me...

On memory, computers, and photos

Well, I've been busy... that's my excuse for not posting sooner. But, anyway...

I've had some of the worst luck with computer hard drives over the last year, than I've ever experienced before. I'm sure there are worse stories out there, but this is just not fair. I've had to use about 4-5 different hard drives through this year, on different computers. And it seems that every single one has decided to die or at least start problems while I've been using it. I'm beginning to not trust hard drives at all now. Which is why this article from eWeek was quite interesting to me. Flash hard drives? Well, no mechanical parts, for one. That fact doesn't seem to be mentioned on the site. That is one of the major reasons I'd go for a flash drive. As to flash taking over the world.... well, it's the world of technology, anything could happen.

But, for now, as we have to use mechanical hard drives based on magnetic discs, here are some useful programs to help warn of an impending hard drive failure:

  • HDD Tune is a very nice free application to check up on the health and performance of a hard disk
  • DiskCheckup is not as flashy as other software of this type, but it's free and it'll get the job done. It's also only 45kb, so takes no time to download.
  • There are many morecommerical applications, but these two are some of the better free programs I found.

On to the next, completely unrelated, part: photos. I love them! But, I'm a bit stuck about how to deal with digital photos. Don't get me wrong, I much prefer digital over regular film photos. They're so easy to share, change, and store. But the problem comes with printing them. I can't manage to get as good printed photos from digital than the film photos. And, since they're digital, I can already see them, so it doesn't really occur to me to print them out. Sometimes, I just want photos I can hold in my hand, and take with me on a visit and sit and look through with friends and family.

The next problem comes with the sheer number of photos that build up over time. Especially when any sort of event happens. It's great to have them, but how do you sort them out? And this brings me full circle to the question of storage. I don't trust hard drives. So, on cd's? on dvd's? What if I can't read cd's and dvd's in 10 years? What about my photos? So, printing them out seems like the best option to store them. The best ones, anyway. It's funny how old ways, that should be outdone by new methods, sometimes raise themselves as the best way... Progress isn't always good, I suppose. Maybe we should think in terms of more options, rather than replacements. And there's my 10 cents.