Results 1 to 5 of 5

Thread: does an SQL query have the following functionality?

  1. #1
    Join Date
    Nov 2006
    Posts
    2

    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!!
    Last edited by amartinas; 11-04-2006 at 06:05 PM.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.

  3. #3
    Join Date
    Nov 2006
    Posts
    2
    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!

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    It depends on transaction isolation level user A used. Sql2k5 comes with snapshot isolation which keeps multiple versions of data row.

  5. #5
    Join Date
    Jun 2004
    Location
    Germany
    Posts
    26
    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
    Last edited by guidomarcel; 11-24-2006 at 05:14 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •