• Oleg Netchaev (4/21/2010)


    Since that time I got into habit of always starting the cte expression with a semicolon

    I never understand that habit. The semicolon is a statement terminator, not a statement starter, so the logical place for it is at the end of a statement, not before the next one.

    The semicolon has always been part of the Transact SQL syntax, but unlike almost every other language, it was optional. And hence, nobody ever used it. This first changed when the SQL Server 2005 parser required a statement to be terminated in order to recognise WITH as the CTE starter, not as a query hint. But in SQL Server 2008, the SQL team went a step further and added "Not using a statement terminator for Transact-SQL statements" to the list of deprecated features. This means that in some future version, you will be required to terminate all statements.

    So do as I do - start to accustom yourself to terminating all statements with a semicolon today. You'll be thankful later. (And you'll never have to worry about the semicolon before a CTE anymore).


    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/