Beginning a CTE

  • Comments posted to this topic are about the item Beginning a CTE

  • This was removed by the editor as SPAM

  • Good question. The questions don't have to be long and complex to learn something from them.

  • I knew what was intended but the correct answer surely should be "No, Never".

    The semi-colon is not required to begin the CTE; it is required to terminate the previous batch. A pedantic distinction perhaps, but one that will become increasingly important as the terminator becomes required for more and more statements.

    Indeed, not ending a statement with a semi-colon is on the deprecated features list. I do not believe they will ever enforce this (it would break too much code), but it is a clear statement of intent.

    Better not to start a CTE with a semi-colon, but instead get used to terminating all statements with one.

  • Anyone who doesn't terminate SQL statements with a semicolon should note the following (from https://msdn.microsoft.com/en-us/library/ms177563%28v=sql.110%29.aspx ):

    Transact-SQL statement terminator.Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

    It's much easier to start doing this now than have to apply it to all your code at once when it becomes compulsory!

  • Thought the same thing - also Do I always need a semicolon to begin a CTE? Yes, if - therefore no you don't 😛

  • The Question said "Do I always need a semicolon to begin a CTE? "

    I picked NO - because the statement begins "Do I Always ...". As a newcomer to SQL, and a pedant, it is a misleading question, because if the previous statement in a batch has not been terminated, you will need to insert the semi-colon, but I would add to the end of the previous statement. The answer says that it is convention , not a hard and fast rule.

    Regads,

    Steve

  • From my old days of Oracle, I seem to remember it was compulsory in SQL*Plus;

  • paul s-306273 (6/3/2016)


    From my old days of Oracle, I seem to remember it was compulsory in SQL*Plus;

    We've been told for some time (since 2005 I think) that semicolons were going to be enforced within SQL Server. As a rule, I always use them and encourage my fellow teammates to use them as well. I don't understand why MS is wavering on the commitment to enforce them as they've been part of the ISO-1999 standard for a very long time.

  • I don't understand why MS is wavering on the commitment to enforce them as they've been part of the ISO-1999 standard for a very long time.

    Too much old code will break.

  • The intention was clear enough, but it does terminate a statement. Perhaps it's my background, but I always use them and encourage others to do the same.

  • Terje Hermanseter (6/3/2016)


    I don't understand why MS is wavering on the commitment to enforce them as they've been part of the ISO-1999 standard for a very long time.

    Too much old code will break.

    That would be true for a number of things due to version upgrades over the years. It's impossible to preserve 100% backward compatibility and yet move forward. If it's a real problem, then MS could offer a tool to insert semicolons where appropriate by using the same parser that is currently used to determine the end of a statement.

    Just a thought...

  • I always use them and encourage others to do the same.

    I've been too sloppy. I need to be more consistent and start to use them even when I only writes single statements. That way I think I will enforce the habit of always using them.

  • martin.whitton (6/3/2016)


    Anyone who doesn't terminate SQL statements with a semicolon should note the following (from https://msdn.microsoft.com/en-us/library/ms177563%28v=sql.110%29.aspx ):

    Transact-SQL statement terminator.Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

    It's much easier to start doing this now than have to apply it to all your code at once when it becomes compulsory!

    +100

    The requirement for using a semicolon at the end of each statement should have been there from the beginning.

    To me it would be like writing a COBOL statement and forgetting the period.

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • Toreador (6/3/2016)


    I knew what was intended but the correct answer surely should be "No, Never".

    The semi-colon is not required to begin the CTE; it is required to terminate the previous batch. A pedantic distinction perhaps, but one that will become increasingly important as the terminator becomes required for more and more statements.

    Well said. +1

Viewing 15 posts - 1 through 15 (of 62 total)

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