Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Tuesday, July 29, 2014 3:22 PM
Points: 17, Visits: 152
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 @ 8:19 AM
Points: 40,208, Visits: 36,617
In the default isolation level, no effect. In other isolation levels it can change behaviour and results.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 593, Visits: 929
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 @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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: 2 days ago @ 4:22 AM
Points: 720, Visits: 552
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 @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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