InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 08:19:28 GMT20http://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxNice n easy questionMon, 29 Oct 2012 01:44:10 GMTkalyani.k478http://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxThank you for the question!Thu, 09 Aug 2012 09:08:26 GMTNeha05http://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]dineshbabus (6/25/2012)[/b][hr]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.[/quote]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.Tue, 26 Jun 2012 14:56:19 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxThanks 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.Mon, 25 Jun 2012 21:21:02 GMTDineshbabuhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]dineshbabus (6/25/2012)[/b][hr]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')goOnly one row is inserted.Thanks ,Dineshbabu[/quote]Have you looked at the previous posts about XACT_ABORT? This will return one row.SET XACT_ABORT ONcreate 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')goSELECT * FROM #T1Mon, 25 Jun 2012 06:27:23 GMTwdolbyhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxHi 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')goOnly one row is inserted.Thanks ,DineshbabuMon, 25 Jun 2012 04:28:37 GMTDineshbabuhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]wdolby (5/18/2012)[/b][hr]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 firstCREATE TABLE #temp(id int not null,data char(20) not null,descr char(20)CONSTRAINT PK_Temp PRIMARY KEY (id));--Then execute:ALTER TABLE #tempADD descr1 char(20);SELECT id, data, descr, descr1 FROM #temp;Msg 207, Level 16, State 1, Line 4Invalid column name 'descr1'.[/quote]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.Fri, 18 May 2012 06:44:02 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]Hugo Kornelis (5/17/2012)[/b][hr][quote][b]wdolby (5/17/2012)[/b][hr]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:[/quote]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?[/quote]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 firstCREATE TABLE #temp(id int not null,data char(20) not null,descr char(20)CONSTRAINT PK_Temp PRIMARY KEY (id));--Then execute:ALTER TABLE #tempADD descr1 char(20);SELECT id, data, descr, descr1 FROM #temp;Msg 207, Level 16, State 1, Line 4Invalid column name 'descr1'.Seperating the ALTER and SELECT works fine:--Create table firstCREATE TABLE #temp(id int not null,data char(20) not null,descr char(20)CONSTRAINT PK_Temp PRIMARY KEY (id));--Then execute:ALTER TABLE #tempADD descr1 char(20);GOSELECT id, data, descr, descr1 FROM #temp;Fri, 18 May 2012 06:32:16 GMTwdolbyhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxThank 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:[code="sql"]INSERT INTO QOTD1(COL1, COL2, COL3)VALUES (1, 'X', 'Some'), (1, 'Y', 'thing'), (2, 'Z', 'or other');-- orINSERT INTO QOTD1(COL1, COL2, COL3)SELECT 1, 'X', 'Some' UNION ALLSELECT 1, 'Y', 'thing' UNION ALLSELECT 2, 'Z', 'or other';[/code]Thank you for the question.Thu, 17 May 2012 21:58:55 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxGood question. Thanks for submitting.Thu, 17 May 2012 20:13:03 GMTBritt Cluffhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]wdolby (5/17/2012)[/b][hr]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:[/quote]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?Thu, 17 May 2012 15:40:54 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxGreat question...thanks.Thu, 17 May 2012 13:01:14 GMTzymoshttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxI 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:CREATE TABLE #temp (id int not null, data char(20) not null, descr char(20) CONSTRAINT PK_Temp PRIMARY KEY (id));INSERT INTO #tempVALUES (1, 'test1', 'testing1');--Syntax errorINSERT INTO temp2VALEUS (2, 'test2', 'testing2');INSERT INTO #tempVALUES (2, 'test3', 'testing3');GOSELECT * FROM #temp;Msg 102, Level 15, State 1, Line 12Incorrect syntax near 'VALEUS'.Msg 208, Level 16, State 0, Line 2Invalid object name '#temp'.Thu, 17 May 2012 10:35:11 GMTwdolbyhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxGreat question and as always great discussion... :-)Wed, 16 May 2012 21:44:48 GMTHardy21http://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxNice question, thanks!Wed, 16 May 2012 16:42:02 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxgreat question - cheersWed, 16 May 2012 10:59:28 GMTOzYbOi d(-_-)bhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]baabhu (5/15/2012)[/b][hr]Nice Question. I was distracted by the factor of go will make it as one transaction.[/quote]Personally the gotcha wasn't the GO, it was figuring out if Management Studio would process the 3rd INSERT after erroring on the 2nd. For some reason I was thinking it would stop after the error instead of keep chugging alongWed, 16 May 2012 09:53:19 GMTChris Harshmanhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxA very interesting one -- thanks, Ron!Wed, 16 May 2012 09:33:27 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]Hugo Kornelis (5/16/2012)[/b][hr][quote][b]Thomas Abraham (5/16/2012)[/b][hr][quote][b]Hugo Kornelis (5/16/2012)[/b]The only way I can reproduce what you are seeing, both on SQL Server 2008 R2 (I don't have 2008 vanilla) and on SQL Server 2012, is by setting the SET XACT_ABORT option to ON. (It is OFF by default).Are you running this from a connection where you previously executed a SET XACT_ABORT ON command? Or from a client that implicitly emits this command upon connecting?[/quote]I was using a fresh connect that does not emit this command. Is there a command on the order of sp_configure that I can run to verify?[/quote]According to [url=http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/]this article[/url], you can check the current setting of SET XACT_ABORT by running SELECT 16384 & @@OPTIONS; - if the result is 16384, it is on; if it's 0, the option is off.[/quote]Since the major objective of the QOTD is to teach, let me say. [b]Thanks[/b] for taking the time to add more learning value to the QOD (for those that read the comments).Wed, 16 May 2012 07:32:58 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]Hugo Kornelis (5/16/2012)[/b]According to [url=http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/]this article[/url], you can check the current setting of SET XACT_ABORT by running SELECT 16384 & @@OPTIONS; - if the result is 16384, it is on; if it's 0, the option is off.[/quote]Mystery solved. Above query returned 16384. When I added SET XACT_ABORT OFF to the script, I got 2 rows back.Thank you Hugo for helping me figure this out!Wed, 16 May 2012 06:59:34 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]Hugo Kornelis (5/16/2012)[/b][hr]Nice question. I knew about implicit transaction, but I had to do some digging because I couldn't remember if primary key violation errors are batch-aborting or not.[/quote]Yes, I thought it was batch aborting. Doh! Good question, thanks.Wed, 16 May 2012 06:55:25 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]Thomas Abraham (5/16/2012)[/b][hr][quote][b]Hugo Kornelis (5/16/2012)[/b]The only way I can reproduce what you are seeing, both on SQL Server 2008 R2 (I don't have 2008 vanilla) and on SQL Server 2012, is by setting the SET XACT_ABORT option to ON. (It is OFF by default).Are you running this from a connection where you previously executed a SET XACT_ABORT ON command? Or from a client that implicitly emits this command upon connecting?[/quote]I was using a fresh connect that does not emit this command. Is there a command on the order of sp_configure that I can run to verify?[/quote]According to [url=http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/]this article[/url], you can check the current setting of SET XACT_ABORT by running SELECT 16384 & @@OPTIONS; - if the result is 16384, it is on; if it's 0, the option is off.Wed, 16 May 2012 06:36:21 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]Hugo Kornelis (5/16/2012)[/b]The only way I can reproduce what you are seeing, both on SQL Server 2008 R2 (I don't have 2008 vanilla) and on SQL Server 2012, is by setting the SET XACT_ABORT option to ON. (It is OFF by default).Are you running this from a connection where you previously executed a SET XACT_ABORT ON command? Or from a client that implicitly emits this command upon connecting?[/quote]I was using a fresh connect that does not emit this command. Is there a command on the order of sp_configure that I can run to verify?Wed, 16 May 2012 06:22:00 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]Thomas Abraham (5/16/2012)[/b][hr][quote][b]Hugo Kornelis (5/16/2012)[/b]No idea what went wrong. I copied and pasted the code above, hit execute, and got two rows back. SQL Server 2012. What version are you running?[/quote]SQL Server 2008[/quote]The only way I can reproduce what you are seeing, both on SQL Server 2008 R2 (I don't have 2008 vanilla) and on SQL Server 2012, is by setting the SET XACT_ABORT option to ON. (It is OFF by default).Are you running this from a connection where you previously executed a SET XACT_ABORT ON command? Or from a client that implicitly emits this command upon connecting?Wed, 16 May 2012 06:12:21 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]Hugo Kornelis (5/16/2012)[/b]No idea what went wrong. I copied and pasted the code above, hit execute, and got two rows back. SQL Server 2012. What version are you running?[/quote]SQL Server 2008Wed, 16 May 2012 06:01:05 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]Thomas Abraham (5/16/2012)[/b][hr]Thanks for the question.Got it wrong, and I even ran a test. Code below:[code]CREATE TABLE #Temp(Col1 INT NOT NULL CONSTRAINT PK_QOTD_1 PRIMARY KEY (Col1)) INSERT #Temp(Col1) VALUES (1)INSERT #Temp(Col1) VALUES (1)INSERT #Temp(Col1) VALUES (2)GOSELECT * FROM #Temp [/code](not exactly the same code as the question, but functionally equivalent) Only got one row back from SELECT, as I had anticipated. So, what have I got wrong here?[/quote]No idea what went wrong. I copied and pasted the code above, hit execute, and got two rows back. SQL Server 2012. What version are you running?Wed, 16 May 2012 05:58:00 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxNice question.Wed, 16 May 2012 05:50:48 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxThanks for the question.Got it wrong, and I even ran a test. Code below:[code]CREATE TABLE #Temp(Col1 INT NOT NULL CONSTRAINT PK_QOTD_1 PRIMARY KEY (Col1)) INSERT #Temp(Col1) VALUES (1)INSERT #Temp(Col1) VALUES (1)INSERT #Temp(Col1) VALUES (2)GOSELECT * FROM #Temp [/code](not exactly the same code as the question, but functionally equivalent) Only got one row back from SELECT, as I had anticipated. So, what have I got wrong here?Wed, 16 May 2012 05:50:21 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]Hugo Kornelis (5/16/2012)[/b][hr]Nice question. I knew about implicit transaction, but I had to do some digging because I couldn't remember if primary key violation errors are batch-aborting or not.(One possible -slight!- improvement to the question would have been to explicitly mention that the three insert statements are executed as a single batch; the current wording can be interpreted as them being executed one by one).[/quote]Prior to submitting the question, I tested as a batch, then executing one insert statement at a time. Made no difference to what happens.[quote] have not tested if the code as posted would actually work or complain about the column reference in the column constraint. If it does work, I would classify it as a parser bug, since the description in BOL implies that the seperating column between columns and table constraints is mandatory. [/quote]Retested the CREATE TABLE statement not more than 2 minutes ago[code="sql"]CREATE TABLE QODT1(Col1 int NOT NULL,col2 CHAR(1) NOT NULL,col3 VARCHAR(20) CONSTRAINT PK_QODT1 PRIMARY KEY (col1))[/code]It worked when I initially created the question, and it works today.L' Eomot InverséIn answer as to why I cited as a reference, the statement for SQL 2000, sort of a tongue in cheek action on my part, to show people how long the IMPLICIT TRANSACTIONS feature/rule has been in effect.Wed, 16 May 2012 05:13:59 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxnice question!!!!Wed, 16 May 2012 05:09:49 GMTrfr.ferrarihttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxGood question.I managed to get it wrong (thinking the error terminated the batch - as would an "insert value in identity column with identity insert ON" error, for example - so that the answer would be 1); serves me right for answering before breaking my fast, I guess.There appears to be a bug in the parser which is shown up by this question (not insisting on a comma which thepublished BNF shows as essential). I guess it's a pretty harmless bug, though.And why oh why does the explanation refer to sql 2000 documentation instead of to documentation for one of the currently supported releases?Wed, 16 May 2012 03:06:10 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxHi Hugo,I did test it, and the table constraint (not column constraint) gets created, even though there is no comma in between.[code="plain"]Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)[/code]Wed, 16 May 2012 01:56:08 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspx[quote][b]Christian Buettner-167247 (5/16/2012)[/b][hr]Hm, I am a little confused because of the missing comma before the CONSTRAINT declaration. That does not seem to be required based on a first test. But I checked my BOL to see verify the syntax, and this is what I found (excerpt):[code="plain"] ( { <column_definition> | <computed_column_definition> | <column_set_definition> } [ <table_constraint> ] [ ,...n ] ) [/code]I cannot derive any meaning from that. Can anyone interpret this notation?Anyways, it looks like the web contains an updated version of the syntax which makes more sense:[code="plain"]( { <column_definition> | <computed_column_definition> | <column_set_definition> | [ <table_constraint> ] [ ,...n ] } )[/code][url]http://msdn.microsoft.com/en-us/library/ms174979(v=sql.105).aspx[/url]However this doesnt explain why it is possible to add the table constraint without a comma between the last column specification and the constraint itself.Any ideas?[/quote]Chris, as far as I know, a constraint that follows a column definition without seperating column is considered a column constraint. But a column constraint should not reference any column.I have not tested if the code as posted would actually work or complain about the column reference in the column constraint. If it does work, I would classify it as a parser bug, since the description in BOL implies that the seperating column between columns and table constraints is mandatory.Wed, 16 May 2012 01:50:59 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxWow - 2 points for that?I was expecting some sort of the catch - I thought this was the easiest QOTD ever.Wed, 16 May 2012 01:47:48 GMTpaul s-306273http://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxNice question. I knew about implicit transaction, but I had to do some digging because I couldn't remember if primary key violation errors are batch-aborting or not.(One possible -slight!- improvement to the question would have been to explicitly mention that the three insert statements are executed as a single batch; the current wording can be interpreted as them being executed one by one).Wed, 16 May 2012 01:44:23 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxHm, I am a little confused because of the missing comma before the CONSTRAINT declaration. That does not seem to be required based on a first test. But I checked my BOL to see verify the syntax, and this is what I found (excerpt):[code="plain"] ( { <column_definition> | <computed_column_definition> | <column_set_definition> } [ <table_constraint> ] [ ,...n ] ) [/code]I cannot derive any meaning from that. Can anyone interpret this notation?Anyways, it looks like the web contains an updated version of the syntax which makes more sense:[code="plain"]( { <column_definition> | <computed_column_definition> | <column_set_definition> | [ <table_constraint> ] [ ,...n ] } )[/code][url]http://msdn.microsoft.com/en-us/library/ms174979(v=sql.105).aspx[/url]However this doesnt explain why it is possible to add the table constraint without a comma between the last column specification and the constraint itself.Any ideas?Wed, 16 May 2012 01:42:40 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxVery nice question, thanks!Wed, 16 May 2012 01:32:26 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxGood question. Now, off to bed I go as today comes early.Wed, 16 May 2012 01:16:27 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxGood question, testing the basics. :)Tue, 15 May 2012 22:50:30 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic1300727-1222-1.aspxNice Question. I was distracted by the factor of go will make it as one transaction.Tue, 15 May 2012 20:05:40 GMTbaabhu