SQL Server CE: Insert row to the top of the table

Question

Is this posible? The reason for this is that I'm using a table like a queue, poping the TOP(1) elements sequentially for processing. And I would like to insert some rows to be processed immediately. It's ce 3.5 by the way, thanks.

Answer 1

1) If you're using the table as a queue, you're using the wrong tool for the job. Tables are sets of inherently unordered data. This is because

2) Rows can be ordered based on the value of any of their fields. This means that you can "insert" something in the middle by adding the row with a value (typically the ID) that has a value which will precede the other rows in the table.

Why are you using a database table to do this? You're clearly looking for a priority queue. If you're doing it because you want the jobs that are being processed to persist if the software fails, consider adding all items, with their priority, to the table, but then taking them out and inserting them into an appropriate priority queue in the software, decoupling your data storage from your scheduling mechanism.

Answer 2

You need a column to maintain order, either identity or a timestamp. Then you can do "select top(1) order by x desc" to get the most recent.

Answer 3

Create an priority column on you database, then sort on it (along with your identity):

CREATE TABLE myqueue(priority INT NOT NULL, id INT IDENTITY NOT NULL PRIMARY KEY, payload VARCHAR(200) NOT NULL)

CREATE INDEX ix_myqueue(priority, id)

INSERT
INTO myqueue (priority, payload)
VALUES (0, 'Regular message')

INSERT
INTO myqueue (priority, payload)
VALUES (1, 'Urgent message')

SELECT id, payload
FROM myqueue
ORDER BY priority DESC, id DESC
TOP 1

id | payload
2  | Urgent message

DELETE
FROM mytable
WHERE id = 2

SELECT payload
FROM myqueue
ORDER BY priority DESC, id DESC
TOP 1

id | payload
1  | Regular message

DELETE
FROM mytable
WHERE id = 1