Begin...Commit Tran in select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Begin...Commit Tran in select statement

Author

Topic

debug
Starting Member

29 Posts

Posted - 2003-06-30 : 00:17:30

Hi All,

Please let me know if the following script can pose an overhead or extra cost in an OLTP enviornment :

CREATE PROC Select_Grid_Refresh
@vCloasID varchar(8) AS
BEGIN TRAN
SELECT DISTINCT
TControl.TxnID,
TControl.UserID,
TControl.TxnDate,
TControl.CoID,
TControl.TxnKey,
TControl.TxnAmount,
TControl.TxnCalcPrem,
TStatus.TableID,
TStatus.StatusID
FROM TControl, TStatus
WHERE TControl.CloasID = @vCloasID AND TControl.TxnID = TStatus.TxnID
ORDER BY TControl.TxnDate, TControl.TxnID, TStatus.TableID
COMMIT TRAN

Also, please let me know what i can do to optimise it.

DU

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-30 : 00:57:50

a begin/commit tran doesn't do anything for a single statement as every statement is atomic.
It doesn't do anything for a select only updates (unless you include locking hints).

Depends on the system whether or not this could cause problems. How much data is it selecting? Are there indexes to support the query?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources