CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014read-uncommitted
Furkan Gözükara
Furkan Gözükara

Reputation: 23830

SET TRANSACTION isolation level READ uncommitted - is it necessary to explicitly use transaction begin and end?

Does this query below work or do I have use explicitly transaction being and end?

Yes I know the dangers of the read uncommitted

SET TRANSACTION isolation level READ uncommitted 

SELECT TOP 100 tblguilds.guild_id, 
               tblguilds.guildname, 
               tblguilds.leaderuserid, 
               tblusersprofile.username 
FROM   tblguilds 
       LEFT JOIN tblusersprofile 
              ON tblusersprofile.userid = tblguilds.leaderuserid 
WHERE  tblguilds.guild_id NOT IN (SELECT guildcode 
                                  FROM   tblguildapplied 
                                  WHERE  userid = 1) 
ORDER  BY Newid()

Upvotes: 0

Views: 7417

Answers (1)

objectNotFound
objectNotFound

Reputation: 1783

Yes that will work but be aware that it will be in effect for the entire session. Meaning any SQL that you execute after that select will be using that isolation level. If you want to restrict that to certain tables in the select stmt then consider using the NOLOCK hint

See more over here : WITH (NOLOCK) vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

The Begin transaction and commit ( there is no END transaction) applies only to insert/updates/deletes

On a side note I would modify that query to use a left outer join instead of a NOT IN to improve performance.

Upvotes: 2

Related Questions