Does SQL know to "cache" values, or seond query will cross the entire pipeline?

Question

UPDATE [User] SET SellerRating = SellerRating + {0} WHERE Id='{1}'; SELECT SellerRating FROM [User] WHERE Id='{1}'", rating, toUserId);

When using an SQLCommand object to Execute scalar on the above query,
does SQL Server knows he have the value for the second query in it's hand ?
or will it start searching the index again?

and how can it know?

Thank you for your time

Answer 1

does SQL Server knows he have the value for the second query in it's hand ? or will it start searching the index again?

Pretty much any respectable database in the world would 1) search again and 2) find the rows in the cache. So the the answer is Yes to both questions. I think you're missing an important piece of knowledge and you must read about buffer pool and how it works.

Answer 2

In fact, just to be clear, the return for the SELECT may not be the same as the rows updated in the UPDATE statement.

Between the two statements, another transaction may update, insert, or delete rows in the table. As a general approach, SQL has to evaluate the second SELECT to take such changes in the data into account.

Of course, there are caveats, which are database- and configuration- specific. These statements could be inside a transaction that has write locks on the table, for instance. But, in general, the second statement has to be evaluated.

What does change, though, is that the data touched by the UPDATE statement may still be in the page cache. If so, then the SELECT should run very quickly, since there would be no cache misses.

Answer 3

Database operations are independent of one another, so no, SQL Server doesn't consider the fact that you have two queries involving the same row(s).

Also, you need to parameterize your queries.