May 17, 2012 at 3:40 pm
wdolby (5/17/2012)
I got this wrong thinking the entire batch would roll back. After playing around with it, now I know the transactions prior to the rollback will still be committed since a batch can have multiple transactions. However, since a batch is submitted as a unit, any syntax or resolution errors will cause the entire batch to rollback:
Almost correct.
In fact, syntax errors will cause the batch to abort before it even starts to execute. That is because the entire batch is first parsed and compiled before the first statement is executed.
Resolution errors are different. SQL Server supports "defered name resolution" - if a table does not exist at parse and compile time, compiling the offending statement is postponed until execution time, in the hope that the table will be created by one of the preceeding statements in the batch. If that is not the case, only that statement is skipped. You can see this if you change "VALEUS" to "VALUES" in your sample code - now the syntax is correct, but the table does not exist; the batch still executes, but since the table still doesn't exist when that statement is executed, you get a run time error. This error aborts the batch (the following INSERT statement is not executed), but doesn't roll back the previous insert - how could it, since the implicit transaction this was executed in was already committed?
May 17, 2012 at 8:13 pm
Good question. Thanks for submitting.
http://brittcluff.blogspot.com/
May 17, 2012 at 9:58 pm
Thank you for the question. I found it easy because we have three INSERT statements there. Each one is an implicit transaction. Now nothing would be returned if it was only one INSERT:
INSERT INTO QOTD1(COL1, COL2, COL3)
VALUES (1, 'X', 'Some'), (1, 'Y', 'thing'), (2, 'Z', 'or other');
-- or
INSERT INTO QOTD1(COL1, COL2, COL3)
SELECT 1, 'X', 'Some'
UNION ALL
SELECT 1, 'Y', 'thing'
UNION ALL
SELECT 2, 'Z', 'or other';
Thank you for the question.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
May 18, 2012 at 6:32 am
Hugo Kornelis (5/17/2012)
wdolby (5/17/2012)
I got this wrong thinking the entire batch would roll back. After playing around with it, now I know the transactions prior to the rollback will still be committed since a batch can have multiple transactions. However, since a batch is submitted as a unit, any syntax or resolution errors will cause the entire batch to rollback:Almost correct.
In fact, syntax errors will cause the batch to abort before it even starts to execute. That is because the entire batch is first parsed and compiled before the first statement is executed.
Resolution errors are different. SQL Server supports "defered name resolution" - if a table does not exist at parse and compile time, compiling the offending statement is postponed until execution time, in the hope that the table will be created by one of the preceeding statements in the batch. If that is not the case, only that statement is skipped. You can see this if you change "VALEUS" to "VALUES" in your sample code - now the syntax is correct, but the table does not exist; the batch still executes, but since the table still doesn't exist when that statement is executed, you get a run time error. This error aborts the batch (the following INSERT statement is not executed), but doesn't roll back the previous insert - how could it, since the implicit transaction this was executed in was already committed?
Thanks for the information. Why does the deferred name resolution fail in this case? If I create the table, and then attempt to alter and select in the next batch, the valid alter statement is rolled back.
--Create table first
CREATE TABLE #temp
(id int not null,
data char(20) not null,
descr char(20)
CONSTRAINT PK_Temp PRIMARY KEY (id));
--Then execute:
ALTER TABLE #temp
ADD descr1 char(20);
SELECT id, data, descr, descr1 FROM #temp;
Msg 207, Level 16, State 1, Line 4
Invalid column name 'descr1'.
Seperating the ALTER and SELECT works fine:
--Create table first
CREATE TABLE #temp
(id int not null,
data char(20) not null,
descr char(20)
CONSTRAINT PK_Temp PRIMARY KEY (id));
--Then execute:
ALTER TABLE #temp
ADD descr1 char(20);
GO
SELECT id, data, descr, descr1 FROM #temp;
May 18, 2012 at 6:44 am
wdolby (5/18/2012)
Why does the deferred name resolution fail in this case? If I create the table, and then attempt to alter and select in the next batch, the valid alter statement is rolled back.--Create table first
CREATE TABLE #temp
(id int not null,
data char(20) not null,
descr char(20)
CONSTRAINT PK_Temp PRIMARY KEY (id));
--Then execute:
ALTER TABLE #temp
ADD descr1 char(20);
SELECT id, data, descr, descr1 FROM #temp;
Msg 207, Level 16, State 1, Line 4
Invalid column name 'descr1'.
Deferred name resolution is like an on/off switch. If a query references a non-existing table, compilation of the query is postponed. But if all tables in the query exist when the batch is parsed and compiled, the query is compiled immediately - and if it then refers to a column that doesn't exist in the table, you get a parse/compile time error and the batch is aborted before it even starts executing.
Please don't ask me why SQL Server does have seperate logic for tables that don't yet exist, but does not have similar logic for columns that don't yet exist. I don't think there's a logical answer to that.
June 25, 2012 at 4:28 am
Hi Guys,
Can anyone explain how two rows will be inserted. I tried the same and for me only first record is getting inserted.
create table T1 (a int not null,b char(1) not null,col3 varchar(20) constraint PK_T1 primary key (a))
insert into T1 (a,b,col3) values (1,'a','c')
insert into T1 (a,b,col3) values (1,'a','c')
insert into T1 (a,b,col3) values (2,'b','c')
go
Only one row is inserted.
Thanks ,
Dineshbabu
--
Dineshbabu
Desire to learn new things..
June 25, 2012 at 6:27 am
dineshbabus (6/25/2012)
Hi Guys,Can anyone explain how two rows will be inserted. I tried the same and for me only first record is getting inserted.
create table T1 (a int not null,b char(1) not null,col3 varchar(20) constraint PK_T1 primary key (a))
insert into T1 (a,b,col3) values (1,'a','c')
insert into T1 (a,b,col3) values (1,'a','c')
insert into T1 (a,b,col3) values (2,'b','c')
go
Only one row is inserted.
Thanks ,
Dineshbabu
Have you looked at the previous posts about XACT_ABORT? This will return one row.
SET XACT_ABORT ON
create table #T1 (a int not null,b char(1) not null,col3 varchar(20) constraint PK_T1 primary key (a))
insert into #T1 (a,b,col3) values (1,'a','c')
insert into #T1 (a,b,col3) values (1,'a','c')
insert into #T1 (a,b,col3) values (2,'b','c')
go
SELECT * FROM #T1
June 25, 2012 at 9:21 pm
Thanks Man.
Can u tell me what will be default mode of "XACT_ABORT" in sql server. If i'm setting it as ON then how long it will be effective.
--
Dineshbabu
Desire to learn new things..
June 26, 2012 at 2:56 pm
dineshbabus (6/25/2012)
Thanks Man.Can u tell me what will be default mode of "XACT_ABORT" in sql server. If i'm setting it as ON then how long it will be effective.
The default is OFF. If you set it ON, it will remain ON until you break the connection. The exception to this is if you set it ON within a specific scope - e.g. if you change the setting in a stored proc, it will affect that proc and all procs called from it, but not the caller, so it will revert to whatever it was when you leave the proc. Same for when you change the setting in dynamic sql.
August 9, 2012 at 9:08 am
Thank you for the question!
October 29, 2012 at 1:44 am
Nice n easy question
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply