does an SQL query have the following functionality?

lets say:

- table X in my database is populated with 10 records
- user A executes a query; gets 10 records
- table X is updated, and now contains 20 records
- user B executes a query; gets 20 records
-- of the 20 records, 15 are new records, and the other 5 are ones that were previously in the table when it had 10 records
- user A wants to re-sort the 10 records he queried

does user A still have access to only the 10 records he queried, somehow saved in "memory" by SQL, wherin i can somehow programatically ensure that he only gets results (from the re-sort he is requesting) based on the query he initially ran?

OR

will user A now only have access to the table of 20 records, and will now lose 5 of the records he was hoping to have access to in his re-sort?

thanks for any help!!

Are you using SQL server?. What front end are you using?. You may cache the records in front end without knowing what happened in the database server. User can sort the result set in the front end.

But if you are running it from query analyzer, then you need to re-issue the query and get 20 rows back.

no, i havent used anything yet. im trying to determine how i can sort old data without retreiving new records since they would invalidate the sort on the old data!

It depends on transaction isolation level user A used. Sql2k5 comes with snapshot isolation which keeps multiple versions of data row.

Hi,
if the DB actions of user B have been committed then they will be available for all users (including user A). If they have not been committed then user A will still see the 10 records although user B has added 15 new records and deleted 5 records.

But what is not possible is to commit the transactions from user B and user A will still see "his" 10 records if he is sending his query to the DB again. There is only one DB. If you need such a logic, you will have to code it yourself.

Hope this helps
GuidoMarcel
Author of www.sqlinform.com the free online SQL Formatter