\nI was telling students this week about the story of the semicolon and SQL Server. And, when going over some Denali demos, it came up again.\n<\/p>\n
\nSQL Server does not mandate semicolons as end-of-statement indicators as some databases do (as well as the ANSI standard). You can use a semicolon (or as many semicolons as you like, really) as end-of-statement, but its most often used as a statement separator for folks that submit multiple SQL statements in a database API (e.g. ADO.NET) CommandText. But you can also use CR-LF or even a single space (if the statements are simple enough) to separate statements in a batch.\n<\/p>\n
\nIn SQL Server 2005, they introduced some ambiguous (to the parser) keywords for the first time. These were CTEs ("WITH"), SEND, RECEIVE, BEGIN DIALOG CONVERSATION, and BEGIN CONVERSATION TIMER. With these, if they are not the first statement in a batch, the statement before them must end with a semicolon.\n<\/p>\n
\nAt that point folks started wondering if they should go back through all their code and put a semi after every statement. My current thought is "not for existing code (unless you're doing maintenance and need it), but yes for new code". Why? They could have more such rules in future and you'd need to memorize them all.\n<\/p>\n
\nIn SQL Server 2008, there is yet another rule, "MERGE" statement must be terminated with a semicolon.\n<\/p>\n
\nAnd in Denali, it occurs again. The new "THROW" statement has the same requirement as "WITH"; if its not the first statement in the CATCH block, the statement before it must end with a semicolon. Same thing if THROW is used to throw an exception in a batch, a la RAISERROR, then the statement before it must end with a semi. And, just in case you decide to put a semicolon after everything that looks like a T-SQL statement, an interesting thing about TRY-CATCH blocks. I can put a semicolon after BEGIN TRY, BEGIN CATCH, or END CATCH without incident. But putting a semicolon after<\/strong> END TRY causes<\/strong> an error.\n<\/p>\n \nSo watch those semicolons and know the rules. Or you'll run into them the hard way.\n<\/p>\n \n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":" I was telling students this week about the story of the semicolon and SQL Server. And, when going over some Denali demos, it came up again. SQL Server does not mandate semicolons as end-of-statement indicators as some databases do (as well as the ANSI standard). You can use a semicolon (or as many semicolons as […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31],"tags":[],"class_list":["post-566","post","type-post","status-publish","format-standard","hentry","category-sql-server-2012"],"yoast_head":"\n