SQLTeam.com | Weblogs | Forums

Sorting Calculated Fields based on dates best practice



I have the need to calculate a status field based on 3 other fields in my table, then allow sorting on that field.
The calculated status field I call xStatus is based on a table field status, which can be -1 (deleted), 1 (active) or 0 (not-active) and if 1 (active) then I need to look at start and end dates to determine the final status of 'Pending', 'Current' or "Expired'., so all possible xStatus's will be: Deleted, Not_Active, Pending, Current, or Expired.

I'm sure I can use conditionals to get what I want, my question is about efficiency.

If I did not need to sort on the xStatus field I would probably calculate it client side (in javascript). The query will use paging, so I'm using SKIP and FETCH NEXT ROWS ONLY, but the potential dataset is large (10,000?) with possibly 100's of users.

I have written some conditional queries and they seem to work fine, this one is the most complex (I have not worked it out yet).

Should I be worried about putting too much load on sql server with complex calculated queries?


Not typically. If the SQL Server is decently sized and properly tuned (which it isn't, but let's hope it's close enough), then it should not be an issue at all. The db engine is designed to deal with large amounts of data.