Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Begin Transaction - End Transaction in a select statement Expand / Collapse
Author
Message
Posted Tuesday, October 22, 2013 8:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 26, 2015 7:58 AM
Points: 17, Visits: 153
General question - Has anyone seen or had experience with stored procedures that include BEGIN TRANSACTION and END TRANSACTION commands in a select statement? If so, what advantages/disadvantages are there to including them and conversely, what advantages/disadvantages are there to excluding them?
I am of the belief that they serve no purpose in a select statement and are totally unnecessary. Agree? Disagree?
Thanks,
Post #1507167
Posted Tuesday, October 22, 2013 8:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 44,898, Visits: 42,914
In the default isolation level, no effect. In other isolation levels it can change behaviour and results.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1507174
Posted Tuesday, October 22, 2013 8:26 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, June 15, 2016 1:56 PM
Points: 607, Visits: 966
As was stated above it doesn't have an effect on READ COMMITTED or READ UNCOMMITED, but it has a big effect if your isolation level is set to REPEATABLE READ or SERIALIZABLE. So, the answer is it depends on isolation level.



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1507178
Posted Tuesday, October 22, 2013 8:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 44,898, Visits: 42,914
Keith Tate (10/22/2013)
but it has a big effect if your isolation level is set to REPEATABLE READ or SERIALIZABLE.


or SNAPSHOT



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1507181
Posted Thursday, October 24, 2013 7:38 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 15, 2016 10:16 PM
Points: 755, Visits: 582
I am of the belief that they serve no purpose in a select statement and are totally unnecessary. Agree? Disagree?
Thanks,


Agree.

Post #1508047
Posted Thursday, October 24, 2013 8:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 44,898, Visits: 42,914
T.Ashish (10/24/2013)
I am of the belief that they serve no purpose in a select statement and are totally unnecessary. Agree? Disagree?
Thanks,


Agree.



Incorrect. See my and Keith's posts above.



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1508078
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse