Ansi-92 in sql server 2005

Is there any flag to enable ansi-92 joins syntax for sql server 2005?
-NunoNuno wrote:
> Is there any flag to enable ansi-92 joins syntax for sql server 2005?
> -Nuno
SQL Server 2005 supports the ANSI-92 syntax [ INNER | { { LEFT | RIGHT
| FULL } [ OUTER] } ] by default. You don't need to enable it.
Maybe you mean the old-style *= syntax (which is not an ANSI standard).
To use that in 2005 you have to set the compatibility level to 80 or
earlier:
EXEC sp_dbcmptlevel 'database_name',80 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||As I understand it (probably wrong). The SQL 92 standard was approved
by both ANSI and ISO, so it's a fair point to refer to it as an ANSI
standard.|||Sorry - i see now...
You're arguing that the *= isn't in the ansi standard, rather than that
the standard 92 wasn't an ANSI standard.
My bad.|||What standard was the old style? Or was that microsoft specific?
I know in Oracle I used to use column1(+) = column2, which I have seen
referred to as SQL 1989 standard.
Just curious...
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1147382385.927779.244890@.v46g2000cwv.googlegroups.com...
> Nuno wrote:
> SQL Server 2005 supports the ANSI-92 syntax [ INNER | { { LEFT | RIGHT
> | FULL } [ OUTER] } ] by default. You don't need to enable it.
> Maybe you mean the old-style *= syntax (which is not an ANSI standard).
> To use that in 2005 you have to set the compatibility level to 80 or
> earlier:
> EXEC sp_dbcmptlevel 'database_name',80 ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thanks David. Thats exactly what I meant.
"Jim Underwood" wrote:

> What standard was the old style? Or was that microsoft specific?
> I know in Oracle I used to use column1(+) = column2, which I have seen
> referred to as SQL 1989 standard.
> Just curious...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1147382385.927779.244890@.v46g2000cwv.googlegroups.com...
>
>|||Would setting the compatibility mode to SQL 2000(80) have any affect on the
new 2005 features such as the new ability to use .net assemblies in
conjunction with queries?
Also(one last question), what are the benifits(if any) of having the
compatibility mode on (90) vs (80)?
Thanks a million for the help,
Nuno
"David Portas" wrote:

> Nuno wrote:
> SQL Server 2005 supports the ANSI-92 syntax [ INNER | { { LEFT | RIGHT
> | FULL } [ OUTER] } ] by default. You don't need to enable it.
> Maybe you mean the old-style *= syntax (which is not an ANSI standard).
> To use that in 2005 you have to set the compatibility level to 80 or
> earlier:
> EXEC sp_dbcmptlevel 'database_name',80 ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Nuno wrote:
> Would setting the compatibility mode to SQL 2000(80) have any affect on th
e
> new 2005 features such as the new ability to use .net assemblies in
> conjunction with queries?
> Also(one last question), what are the benifits(if any) of having the
> compatibility mode on (90) vs (80)?
> Thanks a million for the help,
> Nuno
See sp_dbcmptlevel for an explanation of the backwards-compatibility
features. Although certain new features will work with compatibility
set to 80 this isn't entirely documented so you may be on your own. In
general if you want to use new features it would be best to select 90
compatibility. It's certainly a good idea to re-write your old joins if
you are able to.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Jim Underwood wrote:
> What standard was the old style? Or was that microsoft specific?
> I know in Oracle I used to use column1(+) = column2, which I have seen
> referred to as SQL 1989 standard.
> Just curious...
>
*= came from Sybase. The + syntax was used by Oracle (and DB2?). I'm
pretty sure that neither were in the ANSI 89 standard. I noticed that
at least some Oracle users prior to version 9 were under the illusion
that the product used Standard SQL.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--