Oleg Netchaev (4/21/2010)

---

Hugo Kornelis (4/21/2010)

---

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

Thank you Hugo,

I have been using the semicolons in every statement for at least 2 years now and I try not to fail on this one. I can see that the guideline about placing one before the cte makes sense though. This recommendation is caused precisely by the fact that majority of people still don't use semicolons, so placing one before cte definition is simply to assure that no errors are introduced by someone forgetting to terminate their statement with semicolon. Typical example would be when different developers submit new scripts, one of such scripts begins with cte, and if the previous one was written by someone who did not terminate his last statement then the error will be introduced into the final script should all the small ones be combined.

Oleg