Transactions 1

  • Comments posted to this topic are about the item Transactions 1

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Nice Question. I was distracted by the factor of go will make it as one transaction.

  • Good question, testing the basics. 🙂

    M&M

  • Good question. Now, off to bed I go as today comes early.

  • 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

  • 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

  • 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).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Wow - 2 points for that?

    I was expecting some sort of the catch - I thought this was the easiest QOTD ever.

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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

  • 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

  • nice question!!!!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • 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.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • 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

  • 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 41 total)

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