Home | FAQ | Tutorial@ | Translator | Contact

Grammar Translator Tutorial

Using the Grammar Translator

  1. Enter Command Syntax

    Go to the "CREATE TABLE (Transact-SQL)" page in SQL Server Books Online (BOL) and copy all of the text from "CREATE TABLE" to <partition_number_expression> TO <partition_number_expression> into the textbox on the left.

  2. Click "Translate". You should see the following syntax errors:

    8:48: syntax error, unexpected ], expecting }
    [ { TEXTIMAGE_ON { filegroup | "default" } ] 
                                               ^
27:7: syntax error, unexpected identifier, expecting >
<data type> ::= 
      ^

    If you do not, then the errors in BOL have been corrected. This is good generally but bad for the tutorial, so in that case, please use the text from create_table_bol.txt and try again.

    Each error message consist of three (3) lines. The first line shows the line and column position of the error followed by a description of the error. The next line shows the line from the input in which the error occurred. The third line uses a caret (^) to show the column in which the incorrect symbol appears. The "CREATE TABLE" syntax from BOL has two syntax errors, which is bad, but it gets worse as we will see later in the tutorial. Now we will fix the errors.

  3. Fix Syntax Errors

    The error message shows where the error was detected. In many cases, the real cause of the error occurs earlier in the input.

    The first error (line 8) can indeed be fixed by inserting a closing curly bracket } before the closing square bracket ], but we can see that the opening curly bracket before "TEXTIMAGE_ON" is unnecessary, so we can just remove it. The second error (line 27) is caused by the space in <data type>. The Transact-SQL Syntax Conventions page seems to suggest that spaces are legal in this context. However, in practice, the production names practically never have spaces, so I decided to treat it as an error.

    To fix the errors we will remove the unnecessary opening curly bracket on line 8, and change <data type> to <data_type> on line 27, and then click "Translate" again.

  4. Generate Diagram

    When all syntax errors have been fixed, click "Generate Railroad Diagram". This will take you to Gunther Rademacher's Railroad Diagram Generator. Click your browser's "Back" button to return to the Grammar Translator. If you are using Firefox you may have to click "Back" twice.

  5. Fix Semantic Errors

    Unfortunately, BOL also contains what I call semantic errors. That is, the grammar does not accurately describe what is legal and what is not. To be sure that the command syntax is accurate, you have to study the entire BOL article in detail. This defeats the purpose of the Grammar Translator somewhat. Anyway, we will now identify and fix the errors.

    1. This one is minor. "AS FileTable" should have been written as "AS FILETABLE". The translator parses upper-case text as keywords and lower-case text such as table_name as variables.

      Solution: change "FileTable" to "FILETABLE".

    2. This one is more serious. Note that there is a path from FILETABLE to the pair of round brackets () containing the list of columns. This suggests that it is possible to create a FILETABLE with user-defined columns, but a FILETABLE has a fixed schema so you cannot specify columns when creating one.

      The problem is that "AS FILETABLE" is specified as optional text. "AS FILETABLE" is not really optional; there is a choice. You can create a FILETABLE or a regular table.

      Solution:

      • Remove the square brackets around "AS FILETABLE".

      • Put an opening curly bracket before "AS FILETABLE" and a vertical bar | after "AS FILETABLE". The closing curly bracket belongs immediately after the closing round bracket at the end of line 5. The corrected text looks like this: 

        CREATE TABLE 
    [ database_name . [ schema_name ] . | schema_name . ] table_name 
    { AS FILETABLE  |
    ( { <column_definition> | <computed_column_definition>
        |<column_set_definition> | [ <table_constraint> ] [ ,...n ] } ) }
    [ ON { partition_scheme_name ( partition_column_name ) | filegroup 
        | "default" } ] 
    [ TEXTIMAGE_ON { filegroup | "default" } ] 
    [ FILESTREAM_ON { partition_scheme_name | filegroup 
        | "default" } ]
    [ WITH ( <table_option> [ ,...n ] ) ]
[ ; ]

    3. Here the diagram is saying that a table can have one column or one computed column or one column set or any number of comma-separated table constraints. In fact, a table can have multiple columns, and how would you create constraints if the table has no columns, which is possible according to this syntax? The problem is that the repetition operator [ ,...n ] binds to the preceding symbol, which is the <table_constraint> in this case.

      Solution: On line 5, move the closing curly bracket from immediately after the repetition operator to immediately before it, so that the text starting at the opening round bracket that contains the list of columns looks like this: 

      ( { <column_definition> | <computed_column_definition> 
        | <column_set_definition> | [ <table_constraint> ] } [ ,...n ]  )

      Now that all the choices are enclosed in curly brackets, the repetition operator applies to the entire bracketed expression.

      If you click "Translate" after making the changes above, the diagram should look like this:

      The diagram now accurately depicts the choice between a FILETABLE and a regular table, and the round brackets now contain any number of columns and constraints.

    4. In the diagram above, the round brackets are mismatched. It is possible to have an opening round bracket without a closing round bracket and vice versa. We even see that "VARCHAR max" would be legal, when it is in fact "VARCHAR(max)". By the way, max should have been written as MAX.

      This happens because a sequence x y (x followed by y) has a higher precedence than the choice x | y (x or y) so that '(' x | y | z ')' is parsed as {'(' x} | y | {z ')'}, where curly brackets are used like round brackets in arithmetic. The result is that the opening round bracket precedes only x, the closing round bracket follows only z, and y is not enclosed in round brackets at all.

      Solution: Enclose everything inside the pair of round brackets in a pair of curly brackets. The correct text looks like this: 

      <data_type> ::= 
[ type_schema_name . ] type_name 
    [ ( { precision [ , scale ] | max | 
        [ { CONTENT | DOCUMENT } ] xml_schema_collection } ) ]

      and the diagram looks like this:

    5. Finally, there is another error caused by a misunderstanding of the grammar's precedence rules. The diagram below shows that a primary key or unique constraint consists of one column followed by any number of ASC or DESC separated by commas.

      Solution: Put curly brackets around column [ ASC | DESC ]. 

      < table_constraint > ::=
[ CONSTRAINT constraint_name ] 
{ 
    { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        ( { column [ ASC | DESC ] } [ ,...n ] )

      The corrected diagram looks like this:

    At this point, I was tired of fixing errors so there could be errors remaining, but I believe that after making the corrections described above the grammar and the resulting diagram will be correct. You can find the full text of the corrected grammar here. You may also view a patch that can be applied to BOL. You may also view the complete diagram. If something is still wrong or you have a comment, please contact me.

  6. Add Microsoft syntax (optional)

    By default, the Railroad Diagram Generator's diagrams include the corresponding EBNF productions:

    The W3C EBNF is not useful for my purposes so the "Suppres EBNF display" checkbox is checked by default in the Grammar Translator.

    If you want to include the Microsoft syntax, then save the diagram and click "Add MS Syntax". You will be taken to a new page where you will select the diagram that you just saved and upload it. You will be prompted to save the updated version. Please note that if you are using Internet Explorer (IE), the "Save" prompt is a yellow bar at the bottom of the IE window, and is easy to miss if you are not expecting it.

    You can add Microsoft syntax to a diagram whether it included EBNF or not, and you can also update diagrams that were saved in previous sessions. If you saved PNG diagrams, you will have to unzip the archive and upload index.html and save the updated file in the same folder from which it was uploaded.

    A diagram with Microsoft syntax looks like this:

Home