From: JimLad on
Hi guys,

A very simple question. What the deal with semicolons? I'm still
working on 2000 at the moment, but we are upgrading to 2008 R2
shortly. Should we be using semicolons religiously in all TSQL code
now? I have to say I don't use any at all at the moment.

Cheers,

James
From: --CELKO-- on
>> Should we be using semicolons religiously in all TSQL code now? I have to say I don't use any at all at the moment. <<

Semicolons are part of the standard and are now required in a few
places in T-SQL in 2008. They will be more so as Microsoft gets up to
Standards. It makes your code readable and portable in the meantime.
From: Tom Cooper on
Beginning with SQL 2005, there were new constructs added to T-SQL that
require the use of semicolons as the end of statements. Prior to that
semicolons were always allowed at the end of T-SQL statements, but were
never required. Because they were always optional, most programmers never
used them and many didn't even know they were part of the standard.

Beginning with SQL 2005, there are constructs for which you must use
semicolons. As new features are added with following releases, more and
more new features require the use of semicolons. This will continue for
future release of SQL Server (the reason is that it is much easier to write
a parser if you have a statement terminator that you know must be there).
So, for those cases, you will have to start using them and since you have to
use them for some constructs, and can use them for all SQL statements, it's
a good idea to begin using them everywhere when writing/updating code.

However, the good news is that no piece of code that was working in SQL 2000
will fail when you move to SQL 2008 R2 (at least AFAIK). The code may fail
for some other reason, but not because you don't have semicolons. So I
would recommend that for existing code that you are not otherwise changing,
follow "if it aint broke, don't fix it". But as you write new code or make
modifications to old code, try to get in the habit of using the semicolons.

Once you get on to the new release, if you use a construct that requires
semicolons, SQL is very good at giving you error messages that are helpful
and tell you what's wrong. That is, you usually get something like
"Incorrect syntax near the keyword 'with'. If this statement is a common
table expression, an xmlnamespaces clause or a change tracking context
clause, the previous statement must be terminated with a semicolon." and
mostly don't get "Incorrect syntax near ...",

Tom

"JimLad" <jamesdbirch(a)yahoo.co.uk> wrote in message
news:fc30face-ebdc-4f5e-a231-f4c8bf6d3b1d(a)g19g2000yqc.googlegroups.com...
> Hi guys,
>
> A very simple question. What the deal with semicolons? I'm still
> working on 2000 at the moment, but we are upgrading to 2008 R2
> shortly. Should we be using semicolons religiously in all TSQL code
> now? I have to say I don't use any at all at the moment.
>
> Cheers,
>
> James

From: Erland Sommarskog on
JimLad (jamesdbirch(a)yahoo.co.uk) writes:
> A very simple question. What the deal with semicolons? I'm still
> working on 2000 at the moment, but we are upgrading to 2008 R2
> shortly. Should we be using semicolons religiously in all TSQL code
> now? I have to say I don't use any at all at the moment.

Nor do I. Except when, as Tom said, they are requireed. In which case
I put them before the keyword that calls for it. Or in case of MERGE,
where it is required at the end of the statement, on a line of its own.

Microsoft has deprecated semicolon-less T-SQL, but I don't see that
they will ever go away. The compatibility cost would just be too
severe.

And it doesn't help that T-SQL requires semicolons in places where
you least expect it.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: JimLad on
On 15 July, 23:03, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> JimLad (jamesdbi...(a)yahoo.co.uk) writes:
> > A very simple question. What the deal with semicolons? I'm still
> > working on 2000 at the moment, but we are upgrading to 2008 R2
> > shortly. Should we be using semicolons religiously in all TSQL code
> > now? I have to say I don't use any at all at the moment.
>
> Nor do I. Except when, as Tom said, they are requireed. In which case
> I put them before the keyword that calls for it. Or in case of MERGE,
> where it is required at the end of the statement, on a line of its own.
>
> Microsoft has deprecated semicolon-less T-SQL, but I don't see that
> they will ever go away. The compatibility cost would just be too
> severe.
>
> And it doesn't help that T-SQL requires semicolons in places where
> you least expect it.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks guys.

Many thanks.

James