Question Regarding Tables

Question

Question Regarding Tables

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
November 6, 2008 at 7:49 pm

#127565

I am sorry to ask this stupid question..but i had this doubt when i was at work...i had one sql file which had three inserts on a table..
begin
insert(...inserting rows on a table)
/*
commit
*/
i was first suppose to execute the code...then highlight the commit and then execute...i remember that i did execute the code..but i dnt rem whether i highlighted the "commit" and then executed..i checked the table...data is been inserted...so why do we have to use begin and commit...cant we just insert the records...i just want to make sure that im safe...again the records and inserted..but im not sure whether i committed the transaction or not..thx

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

If you did a begin transaction you need to do a commit. You can run DBCC OPENTRAN() to determine if you left the transaction uncommitted.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Sudeepta SSCommitted Points: 1666 More actions · Answer 2

You need to use begin and commit, when you are trying to run a batch. Either the entire upload is successful or nothing is updated.

I think SQL server also has an auto commit feature....

Please correct me if i am wrong....

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

Atif Hall of Fame Points: 3578 More actions · Answer 3

If you declare a transaction then you need to commit it. In case of ordinary batch without a declared transaction you do not need to commit explicitly.

DBDigger Microsoft Data Platform Consultancy.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

i checked the table...data is been inserted...

How did you check?

In the same connection it is possible to see the records without COMMITing.

In the other connection also it is possible to see with NOLOCK hint. It's called dirty read. Reading uncommitted rows.

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 5

set the isolation levels for database. So don't worry about whether record is commited or not.

for more info see SQL BOL.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 6

Kishore.P (11/7/2008)
set the isolation levels for database. So don't worry about whether record is commited or not.
for more info see SQL BOL.

COMMIT matters, even if ISOLATION LEVEL is SET. It cannot be ignored.