Ansi SQL Manual

ANSI SQL Manual

Here's the ‘Second Informal Review Draft’ of SQL:1992, which seems to have been accurate enough for everything I've looked up. 1992 covers most of the stuff routinely used across DBMSs.

Changing SQL Server query to pure ANSI SQL query

You might try to transfer the CTE and all applies to inlined sub-selects:

declare @rsBuildDetails table(dt datetime, build varchar(255), val varchar(255));

insert into @rsBuildDetails (dt, build, val) values 
 ('20100101', '1', 'pass')
,('20100102', '2', 'fail')
,('20100103', '3', 'pass')
,('20100104', '4', 'fail')
,('20100105', '5', 'fail')
,('20100106', '6', 'fail')
,('20100107', '7', 'pass')
,('20100108', '8', 'pass')
,('20100109', '9', 'pass')
,('20100110', '10', 'fail');

select *
from
(
    select distinct
           (
            select top 1 pre.Dt
            from @rsBuildDetails as pre
            where pre.dt<passed.dt 
              and pre.val='fail'
            order by pre.dt desc
           ) as FailedDt
         ,(
            select top 1 post.Dt
            from @rsBuildDetails as post
            where post.dt>passed.dt 
              and post.val='fail'
            order by post.dt asc
          ) AS SecondFailedDt
    from 
    (
        select * 
        from @rsBuildDetails
        where val='pass'
    ) AS passed
) AS tbl
where tbl.FailedDt IS NOT NULL 
  AND tbl.SecondFailedDt IS NOT NULL

Why does no database fully support ANSI or ISO SQL standards?

In the software industry you have some standards that are really standards, i.e., products that don't comply with them just don't work. File specifications fall into that category. But then you also have "standards" that are more like guidelines: they may defined as standards with point-by-point definitions, but routinely implemented only partially or with significant differences. Web development is full of such "standards", like HTML, CSS and "ECMAScript" where different vendors (i.e. web browsers) implement the standards differently.

The variation causes headaches, but the standardization still provides benefits. Imagine if there were no HTML standard at all and each browser used its own markup language. Likewise, imagine if there were no SQL standard and each database vendor used its own completely proprietary querying language. There would be much more vendor lock-in, and developers would have a much harder time working with more than one product.

So, no, ANSI SQL doesn't serve the same purpose as ANSI standards do in other industries. But it does serve a useful purpose nonetheless.

Is 'INDEX' valid SQL ANSI ISO standard keyword / reserved word?

There is no ANSI standard for SQL language used to create, alter, or manage indexes. So no, INDEX is not a keyword (reserved word) per ANSI standards.

Kevin Kline specifically backs me up here in his book SQL in a Nutshell. He points this out as one of the reasons the syntax for creating indexes varies greatly among vendors.

As further circumstantial evidence you'll also note a variety of vendors mention in their SQL documentation that statements regarding INDEXs are extensions to the ANSI standard. For example see IBM doc here for ALTER INDEX.

This is also a handy list of the ANSI SQL reserved words - but only up to 2003.

---

As a side note: the only time I've ever seen ANSI mentioned at all with regards to indexes is when talking about how a unique index (often simply a unique constraint) treats null values. According to ANSI a null does not equal a null; therefore a unique index should allow multiple null values since per ANSI they do not equal each other. Some engines follow this rule - others do not. The ANSI standard in this case only refers to whether two nulls are equal or unique... the standard has nothing to do with the index. There may be other ANSI standards that have a similar effect on INDEX but nothing regarding the DDL surrounding them.

Why isn't SQL ANSI-92 standard better adopted over ANSI-89?

According to "SQL Performance Tuning" by Peter Gulutzan and Trudy Pelzer, of the six or eight RDBMS brands they tested, there was no difference in optimization or performance of SQL-89 versus SQL-92 style joins. One can assume that most RDBMS engines transform the syntax into an internal representation before optimizing or executing the query, so the human-readable syntax makes no difference.

I also try to evangelize the SQL-92 syntax. Sixteen years after it was approved, it's about time people start using it! And all brands of SQL database now support it, so there's no reason to continue to use the nonstandard (+) Oracle syntax or *= Microsoft/Sybase syntax.

As for why it's so hard to break the developer community of the SQL-89 habit, I can only assume that there's a large "base of the pyramid" of programmers who code by copy & paste, using ancient examples from books, magazine articles, or another code base, and these people don't learn new syntax abstractly. Some people pattern-match, and some people learn by rote.

I am gradually seeing people using SQL-92 syntax more frequently than I used to, though. I've been answering SQL questions online since 1994.

Is there a reason ANSI SQL uses as a comparison operator?

Some early computer languages used <> as not equals, such as the original BASIC.

You may be better off thinking of <> as less than OR greater than rather than less than AND greater than. This is similar to the other relational operators, such as >= meaning greater than OR equal to. Then it makes perfect sense.

Or just do what everyone else does and think of it as "not equal to" - I've seen many variations such as <>, !=, /= and even ¬=.

I wouldn't get too deeply philosophical about what characters make up tokens in various languages. That way lies madness.

For example, in C, the expression delta != 7 could be read as DELTA = 7 (think "yelling out delta as an exclamation").

Or a == b meaning that you're really certain that a is equal to b :-)

---

Related Topics