How to get SQL Railroad Diagrams from MSDN BNF syntax notation.
- by Phil Factor
pre
{margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Courier New";
margin-left: 0cm;
margin-right: 0cm;
margin-top: 0cm;
}
On SQL Server Books-On-Line, in the
Transact-SQL Reference (database Engine), every
SQL Statement has its syntax represented in ‘Backus–Naur Form’ notation (BNF)
syntax. For a programmer in a hurry, this should be ideal because It is the
only quick way to understand and appreciate all the permutations of the syntax. It is a great feature once you get your
eye in. It isn’t the only way to get the information;
You can, of course, reverse-engineer an understanding of the syntax from the
examples, but your understanding won’t be complete, and you’ll have wasted time doing it. BNF is a good start in
representing the syntax: Oracle and SQLite go one step further, and have proper
railroad diagrams for their syntax, which is a far more accessible way of doing it.
There are three problems with the BNF on MSDN. Firstly, it is isn’t a standard version of
BNF, but an ancient fork from EBNF, inherited from Sybase. Secondly, it is
excruciatingly difficult to understand, and thirdly it has a number of
syntactic and semantic errors. The page describing DML triggers, for
example, currently has the absurd BNF error that makes it state that all statements in the body of the trigger must be
separated by commas. There are a few other detail problems too. Here is the
offending syntax for a DML trigger, pasted from MSDN.
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL
NAME <method specifier [ ; ] > }
<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
<method_specifier> ::=
This should, of course, be
/* Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) */
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ NOT FOR REPLICATION ]
AS { {sql_statement [ ; ]} [ ...n ] | EXTERNAL NAME
<method_specifier> [ ; ] }
<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS CLAUSE ]
<method_specifier> ::=
assembly_name.class_name.method_name
I’d love to tell Microsoft when I spot errors like this so they can correct them but I can’t.
Obviously, there is a mechanism on MSDN to get errors corrected by using comments, but that doesn’t
work for me (*Error occurred while saving your data.”), and when I report that the comment system doesn’t work
to MSDN, I get no
reply.
I’ve been trying to create railroad diagrams for all the important SQL Server SQL statements, as
good as you’d find for Oracle, and have so far published the
CREATE TABLE
and ALTER TABLE
railroad diagrams based on the BNF. Although I’ve been aware of them, I’ve never realised until recently how many errors
there are. Then, Colin Daley
created a translator for the SQL Server dialect of
BNF which outputs standard EBNF notation used by the W3C. The example MSDN BNF for the trigger would be
rendered as …
/* Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) */
create_trigger ::= 'CREATE TRIGGER' ( schema_name '.' ) ? trigger_name 'ON' ( table | view ) (
'WITH' dml_trigger_option ( ',' dml_trigger_option ) * ) ? ( 'FOR' | 'AFTER' | 'INSTEAD OF' ) ( ( 'INSERT' ) ? ( ',' ) ?
( 'UPDATE' ) ? ( ',' ) ? ( 'DELETE' ) ? ) ( 'NOT FOR REPLICATION' ) ? 'AS' ( ( sql_statement ( ';' ) ? ) + | 'EXTERNAL
NAME' method_specifier ( ';' ) ? )
dml_trigger_option ::= ( 'ENCRYPTION' ) ? ( 'EXECUTE AS CLAUSE' ) ?
method_specifier ::= assembly_name '.' class_name '.' method_name
Colin’s intention was to allow anyone to paste SQL Server’s BNF notation into his website-based
parser, and from this generate classic railroad diagrams via Gunther
Rademacher's Railroad Diagram Generator. Colin's application does this for you:
you're not aware that you are moving to a different site. Because Colin's 'translator' it is a parser, it will pick
up syntax errors. Once you’ve fixed the syntax errors, you will get the syntax in the form of a human-readable railroad
diagram and, in this form, the semantic mistakes become flamingly obvious.
Gunter’s Railroad Diagram Generator is brilliant. To be able, after correcting the MSDN dialect of
BNF, to generate a standard EBNF, and from thence to create railroad diagrams for SQL Server’s syntax that are as good
as Oracle’s, is a great boon, and many thanks to Colin for the idea. Here is the result of the W3C EBNF from Colin’s
application then being run through the Railroad diagram generator.
create_trigger:
dml_trigger_option:
method_specifier:
Now that’s much better, you’ll agree. This is pretty easy to understand, and at this point any
error is immediately obvious.
This should be seriously useful, and it is to me. However
there is that snag. The BNF is generally incorrect, and you can’t expect the average visitor to mess about with
it.
The answer is, of course, to correct the BNF on MSDN and maybe even add railroad diagrams for the
syntax. Stop giggling! I agree it won’t happen. In the meantime, we need to collaboratively store and publish these
corrected syntaxes ourselves as we do them. How? GitHub? SQL
Server Central? Simple-Talk? What should those of us who use the system
do with our corrected EBNF so that anyone can use them without hassle?
