SQL Server : Get position (ordinal) of a record in a table

I have a table that stores a queue of tasks. Every task looks like:

Id | Operation | Argument | Status

Operation is a string

Argument is a single number

Status is one of : Queued, Busy, Complete, Failed

I need to figure out a position of an item in a queue, but need to skip开发者_运维技巧 tasks that are not queued.

Example:

1 A 5 Queued   -- 1
2 A 6 Queued   -- 2
3 B 3 Busy     -- x
4 B 4 Complete -- x
5 A 8 Queued   -- 3

Expected position follows -- position x means request is meaningless.

Question: what would be a good way to calculate such position?

At the moment I do:

SELECT TOP 1 p.Position FROM
(
    SELECT Id, Status, 
    ROW_NUMBER() over (order by Id) as Position 
    from QueuedJobs where Status = 0 AND Id <= @taskId
) as p
order by Position desc

In English: Calculate positions of each task up to my task, and give me the last position (which is my task)

My concern is performance, let's say I have 1000 records and have many requests (100's every second).

Database is SQL Server 2008

A query without an order by clause can return the rows in any order. What criterion is used to order the queued requests that are not busy or completed? Is it a FIFO queue? The oldest request is at the top? Typically a datetime value is used for that purpose. In any case, I'd number the rows in the order returned by the query in the front-end, where it would be done much more efficiently.

If Status 0 means "Queued", then:

Select Count(*)
From QueuedJobs
Where Status = 0
  and Id <= @taskId

Should tell you where you are :)