Does SQL server autocomits?

Question

I have an app which keeps inserting rows into table.using stored procedures.

Based on my search so far Oracle needs commit but sql server does it automatically.

I could not find any solid reference to confirm the above.

So question: does Sql server needs commit after every insert and delete(inside stored procedures) or it is automatic?

Something similar http://stackoverflow.com/questions/1090240/how-do-you-set-autocommit-in-an-sql-server-session/1090885#1090885 — Garry, May 21 '15 at 20:07

score 1 · Accepted Answer · answered May 21 '15 at 20:05

SQL will commit by default. If you don't want it to commit, you can begin a TRANSACTION, and then you can choose to COMMIT TRANSACTION or ROLLBACK TRANSACTION

More info: https://msdn.microsoft.com/en-us/library/ms188929.aspx?f=255&MSPPError=-2147217396

score 1 · Answer 2 · answered May 21 '15 at 20:14

The answer can be complicated depending on configuration and your exact code, however in general Sql Server writes with each operation. For all practical purposes if you do something like:

CREATE TABLE dbo.DataTable( Value nvarchar(max) ) 

GO

CREATE PROC dbo.WriteData
  @data NVARCHAR(MAX)
AS BEGIN

  INSERT INTO DataTable( Value ) VALUES ( @data )

END

GO

EXEC dbo.WriteData 'Hello World'

SELECT    * 
FROM      DataTable

DROP TABLE dbo.DataTable
DROP PROC dbo.WriteData

Once the proc has completed the data is commited. Again, depending on lots of factors the timing of this can change or be delayed.

However for what it sounds like you are asking if your "INSERT" the data is inserted no need to finalize a transaction unless you started one.

Does SQL server autocomits?

2 Answers2