May 15, 2012 at 8:04 pm
Comments posted to this topic are about the item Transactions 1
May 15, 2012 at 8:05 pm
Nice Question. I was distracted by the factor of go will make it as one transaction.
May 15, 2012 at 10:50 pm
Good question, testing the basics. 🙂
M&M
May 16, 2012 at 1:16 am
Good question. Now, off to bed I go as today comes early.
May 16, 2012 at 1:32 am
Very nice question, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 16, 2012 at 1:42 am
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):
( { <column_definition> | <computed_column_definition>
| <column_set_definition> }
[ <table_constraint> ] [ ,...n ] )
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:
( { <column_definition> | <computed_column_definition>
| <column_set_definition> | [ <table_constraint> ] [ ,...n ] } )
http://msdn.microsoft.com/en-us/library/ms174979(v=sql.105).aspx
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?
Best Regards,
Chris BĂĽttner
May 16, 2012 at 1:44 am
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).
May 16, 2012 at 1:47 am
Wow - 2 points for that?
I was expecting some sort of the catch - I thought this was the easiest QOTD ever.
May 16, 2012 at 1:51 am
Christian Buettner-167247 (5/16/2012)
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):
( { <column_definition> | <computed_column_definition>| <column_set_definition> }
[ <table_constraint> ] [ ,...n ] )
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:
( { <column_definition> | <computed_column_definition>| <column_set_definition> | [ <table_constraint> ] [ ,...n ] } )
http://msdn.microsoft.com/en-us/library/ms174979(v=sql.105).aspx
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?
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.
May 16, 2012 at 1:56 am
Hi Hugo,
I did test it, and the table constraint (not column constraint) gets created, even though there is no comma in between.
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)
Best Regards,
Chris BĂĽttner
May 16, 2012 at 3:06 am
Good 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?
Tom
May 16, 2012 at 5:09 am
nice question!!!!
May 16, 2012 at 5:13 am
Hugo Kornelis (5/16/2012)
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).
Prior to submitting the question, I tested as a batch, then executing one insert statement at a time. Made no difference to what happens.
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.
Retested the CREATE TABLE statement not more than 2 minutes ago
CREATE TABLE QODT1(Col1 int NOT NULL,col2 CHAR(1) NOT NULL,col3 VARCHAR(20)
CONSTRAINT PK_QODT1 PRIMARY KEY
(col1))
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.
May 16, 2012 at 5:50 am
Thanks for the question.
Got it wrong, and I even ran a test. Code below:
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)
GO
SELECT * FROM #Temp
(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?
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 16, 2012 at 5:50 am
Nice question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply