Should I be using SQL transactions, while reading records?

If you are querying all the records in a single query, and pulling them back in one go, there is no need. Everything is wrapped up in an implicit transaction. That is to say, even if you get back one million records, and even if other processes are changing the records, you'll see what all one million records looked like at the same point in time.

The only times you would really need a transaction (and, often, a specific locking hint) in a read only process are:
- You read the records "piece-meal" and need nothing else to alter the values while you itterate though. [Such as a connected recordset in ADO that you then cursor through.]
- You read some data, do some calculations, then read some related data, but on the assumption nothing changed in the mean time.

In short, you need transactions when you want other processes to be stopped from interfering with your data between SQL statements.

Transaction wrapping is not needed for pure reads.

Within your SQL statement, Lock Hints should take care returning proper data to you (http://msdn.microsoft.com/en-us/library/aa213026%28SQL.80%29.aspx).

On a server level, you can set Transaction Isolation levels (http://msdn.microsoft.com/en-us/library/ms173763.aspx).

Edit

Explaining pure reads

If all your SQL statement has these kinds of reads then you do not need to wrap in a transaction

SELECT Col1, Col2From Table1    INNER JOIN Table2        ON Table1.Id = Table2.Table1Id

If you are reading results that can be affected by other transactions in parallel then you must wrap in a transaction. For eg:

BEGIN TRANSACTIONINSERT INTO AccountTransactions (Type, Amount) Values ('Credit', 43.21)UPDATE AccountSummary SET Balance = Balance + 43.21SELECT @Balance = Balance FROM AccountSummaryCOMMIT TRANSACTION

Really, you are just returning the balance, but the entire monetary transaction has to work in two places.

If you need the most up to date to the millisecond information you can use a transaction that is constructed with a TransactionOptions having an IsolationLevel of Serializable.

This would effect performance as it will lock the table (or parts of the table), so you need to figure out if you really need this.

For most uses, if you are doing a read, you do not need to wrap a transaction around it (assuming you are only doing reads in the one operation).

It really depends on your application, what data it requires and how it uses it.

For example, if you do a read and depending on the results you do a write or update, but it is critical that the data you just read is current, you should probably wrap the whole logic into a single transaction.