|
Home > Archive > microsoft.public.sqlserver.server > October 2002 > Questions about ANSI SQL standards
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Questions about ANSI SQL standards
|
|
|
| I have a few questions concerning ANSI SQL standards I was hoping someone
could answer.
1) What is the latest ANSI SQL standard? I've heard of SQL-92 but I would
think that newer standards have been released since 1992.
2) Is there a website that outlines these standards?
3) Is there a website that shows what MS SQL Server syntax is not
ANSI-compliant?
Thanks in advance,
Tom
| |
| Tibor Karaszi 2002-10-05, 9:14 pm |
| > 1) What is the latest ANSI SQL standard? I've heard of SQL-92 but I would
> think that newer standards have been released since 1992.
SQL:1999
> 2) Is there a website that outlines these standards?
Here's a good place to start:
http://ourworld.compuserve.com/home...OTSQL/books.htm
> 3) Is there a website that shows what MS SQL Server syntax is not
> ANSI-compliant?
Not that I know of. Create such would be a huge undertaking. I'm working on some things, but
that is a more pragmatic approach.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"TJT" <Thomas.Todd@smed.com> wrote in message news:#Mm2z7#YCHA.2712@tkmsftngp10...
> I have a few questions concerning ANSI SQL standards I was hoping someone
> could answer.
>
> 1) What is the latest ANSI SQL standard? I've heard of SQL-92 but I would
> think that newer standards have been released since 1992.
> 2) Is there a website that outlines these standards?
> 3) Is there a website that shows what MS SQL Server syntax is not
> ANSI-compliant?
>
> Thanks in advance,
> Tom
>
>
| |
| Peter Gulutzan 2002-10-05, 9:14 pm |
| "TJT" <Thomas.Todd@smed.com> wrote in message news:<#Mm2z7#YCHA.2712@tkmsftngp10>...
> I have a few questions concerning ANSI SQL standards I was hoping someone
> could answer.
>
> 1) What is the latest ANSI SQL standard? I've heard of SQL-92 but I would
> think that newer standards have been released since 1992.
The current standard is SQL-99, or (as it's now more frequently known)
SQL:1999.
This supersedes SQL-92; however, Microsoft's documentation still says
that SQL Server complies with SQL-92 Entry Level. By contrast, IBM and
Oracle claim some compliance with SQL-99 core.
> 2) Is there a website that outlines these standards?
You shouldn't be able to get the standard document itself because it's
copyrighted. But the committee that makes the standards does have a
website: sql-99.org. Via that link, you'll find that there is a new
version which is well advanced, it will doubtless have a name like
SQL:2003, and draft copies are downloadable. There are of course
various websites that give small amounts of introductory material
which you can find via a search engine, and there are books on
standard SQL -- we keep a list with links to sample chapters and
reviews at http://ourworld.compuserve.com/home...tsql/books.htm.
> 3) Is there a website that shows what MS SQL Server syntax is not
> ANSI-compliant?
This is a timely question, because I have just written an article for
dbazine.com which addresses that very subject. It should appear online
in a week or two at http://dbazine.com.
Peter Gulutzan
Co-Author of SQL Performance Tuning
(http://ourworld.compuserve.com/home...tsql/tuning.htm)
| |
| Hal Berenson 2002-10-05, 9:14 pm |
| With SQL-92 there was an explicit test suite that you had to pass in order
to claim compliance. So saying "SQL-92 Entry Level" means you passed the
test suite. This was a requirement for databaser sales to the U.S.
Government, who developed the test suite for exactly that purpose. SQL
Server 2000 does pass the test suite, and has many features that go beyond
those required by Entry Level. But neither it, nor any vendor, ever did all
the features needed to pass the higher level test suites.
SQL-99 was approved too late for Microsoft to consider addressing it in SQL
Server 2000. Unlike previous standards it was very controversial and saw
considerable change much later in the process than is desirable. More
importantly, SQL-99 has no associated test suite and its nearly impossible
to prove compliance with SQL-99. That's why you are seeing statements like
"some compliance with SQL-99 Core". Well, SQL Server 2000 has "some"
compliance with SQL-99 Core as well. It just isn't marketed that way. And
without a test suite, and a government purchasing spec requiring conformance
to SQL-99, there is probably never going to be true acceptance of the
standard. Each vendor will just implement the parts of the standard that
interest them and then claim compliance. Applications written to Oracle's
"some" will not necessarily work against IBM's "some". No doubt Microsoft
will eventually implement enough of SQL-99 that it too will claim compliance
with SQL-99 Core.
Basically where this leaves everyone is that SQL-92 Entry Level is still the
true measure of commonality between database systems. All major vendors
conform to this level, and any vendor who doesn't has a product that is
suspect (ie, it's hard to take seriously a SQL relational database system
that doesn't conform to SQL-92). SQL-92 Entry Level is also the SQL variant
that you should write your application in if you want it to be portable to a
wide variety of database systems.
Hal
"Peter Gulutzan" <pgulutzan@yahoo.ca> wrote in message
news:bc8f8132.0209250947.57cf1cb7@posting.google.com...
> "TJT" <Thomas.Todd@smed.com> wrote in message
news:<#Mm2z7#YCHA.2712@tkmsftngp10>...
> > I have a few questions concerning ANSI SQL standards I was hoping
someone
> > could answer.
> >
> > 1) What is the latest ANSI SQL standard? I've heard of SQL-92 but I
would
> > think that newer standards have been released since 1992.
>
> The current standard is SQL-99, or (as it's now more frequently known)
> SQL:1999.
> This supersedes SQL-92; however, Microsoft's documentation still says
> that SQL Server complies with SQL-92 Entry Level. By contrast, IBM and
> Oracle claim some compliance with SQL-99 core.
>
> > 2) Is there a website that outlines these standards?
>
> You shouldn't be able to get the standard document itself because it's
> copyrighted. But the committee that makes the standards does have a
> website: sql-99.org. Via that link, you'll find that there is a new
> version which is well advanced, it will doubtless have a name like
> SQL:2003, and draft copies are downloadable. There are of course
> various websites that give small amounts of introductory material
> which you can find via a search engine, and there are books on
> standard SQL -- we keep a list with links to sample chapters and
> reviews at http://ourworld.compuserve.com/home...tsql/books.htm.
>
> > 3) Is there a website that shows what MS SQL Server syntax is not
> > ANSI-compliant?
>
> This is a timely question, because I have just written an article for
> dbazine.com which addresses that very subject. It should appear online
> in a week or two at http://dbazine.com.
>
> Peter Gulutzan
> Co-Author of SQL Performance Tuning
> (http://ourworld.compuserve.com/home...tsql/tuning.htm)
| |
| Gert-Jan Strik 2002-10-05, 9:14 pm |
| In addition to the other posters: you can use the fips flagger to check
whether a particular statement is ANSI-SQL 92 compliant. Lookup SET
FIPS_FLAGGER in BOL.
Gert-Jan
TJT wrote:
>
> I have a few questions concerning ANSI SQL standards I was hoping someone
> could answer.
>
> 1) What is the latest ANSI SQL standard? I've heard of SQL-92 but I would
> think that newer standards have been released since 1992.
> 2) Is there a website that outlines these standards?
> 3) Is there a website that shows what MS SQL Server syntax is not
> ANSI-compliant?
>
> Thanks in advance,
> Tom
| |
| Barry McAuslin 2002-10-05, 9:16 pm |
| There is also a document in the SQL Server 2000 Resource Kit called TSQL
Quick Reference.
The following is from this document.
This is an update to the SQL Server 7 Quick Reference document showing the
changes syntax made for SQL Server 2000. All changes to syntax between SQL
Server 7 and SQL Server 2000 are shown with tan shading.
The other major purpose of this document is to show the differences between
the Core level of the most recent SQL standard (SQL:1999) and SQL Server
2000. Syntax required by SQL:1999 but not supported by SQL Server 2000 is
shown in red with pink shading. In cases where SQL Server supports SQL:1999
features that are outside Core but does so with non-standard syntax, the
standard syntax is also show (in black). SQL:1999 features that are outside
Core and which are not supported by SQL Server do not appear in this
document.
Barry
"Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message
news:3D92045D.12BF033C@toomuchspamalready.nl...
> In addition to the other posters: you can use the fips flagger to check
> whether a particular statement is ANSI-SQL 92 compliant. Lookup SET
> FIPS_FLAGGER in BOL.
>
> Gert-Jan
>
>
> TJT wrote:
> >
> > I have a few questions concerning ANSI SQL standards I was hoping
someone
> > could answer.
> >
> > 1) What is the latest ANSI SQL standard? I've heard of SQL-92 but I
would
> > think that newer standards have been released since 1992.
> > 2) Is there a website that outlines these standards?
> > 3) Is there a website that shows what MS SQL Server syntax is not
> > ANSI-compliant?
> >
> > Thanks in advance,
> > Tom
| |
| Tibor Karaszi 2002-10-05, 9:16 pm |
| That's a good document. Thanks for the pointer, Barry!
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"Barry McAuslin" <barry_mcauslin.yahoo@nospam.com> wrote in message
news:#n4B5AiZCHA.3808@tkmsftngp08...
> There is also a document in the SQL Server 2000 Resource Kit called TSQL
> Quick Reference.
>
> The following is from this document.
>
> This is an update to the SQL Server 7 Quick Reference document showing the
> changes syntax made for SQL Server 2000. All changes to syntax between SQL
> Server 7 and SQL Server 2000 are shown with tan shading.
>
> The other major purpose of this document is to show the differences between
> the Core level of the most recent SQL standard (SQL:1999) and SQL Server
> 2000. Syntax required by SQL:1999 but not supported by SQL Server 2000 is
> shown in red with pink shading. In cases where SQL Server supports SQL:1999
> features that are outside Core but does so with non-standard syntax, the
> standard syntax is also show (in black). SQL:1999 features that are outside
> Core and which are not supported by SQL Server do not appear in this
> document.
>
> Barry
>
|
|
|
|
|