Basic BNF
The Backus–Naur Form (BNF) metasyntax permeates SQL Server‘s Books Online (BOL). Understanding it can turn techno-babble into a succinct understanding of how to execute a function. This article briefly introduces BNF ala T-SQL.
Why BNF Matters
There are at least two good reasons for learning about BNF. First, it’s how the International Standards for Organizations (ISO) defines the SQL language via ISO/IEC 9075. Second, and as a result of the first point, BOL leverages BNF to introduce most functions. The following excerpt from the SQL Server 2008 Books Online exemplifies the Backus–Naur Form for a common function.
BNF 101
This ubiquitous metasyntax rests on a simple foundation. It starts with the following line, where <symbol> equates to the item being defined, such as, a function.
<symbol> ::= _expression_
The term to the right of ::= encompasses the rules defining <symbol>. These rules may be fundamental building blocks, such as a string constant, or other symbols. These “other symbols” may contain other symbols allowing for tremendous expressive power via recursion.
BNF formally entitles the <symbol> token a “nonterminal symbol” and the building blocks “terminal symbols”. The technical difference between the two is angle brackets. All nonterminal symbols are wrapped within the < and > characters. Nonterminal symbols usually imply the existence of subsequent BNF definitions, hence the “nonterminal” description.
Imagine we need to define a function entitled, foo. Executing it requires arguments of another foo or the imaginary function, bar. Bar expects either the constant value of “1” or “2”. BNF for these ideas might appear as follows.
<foo>::= <foo> |<bar>
<bar>::= 1 | 2
BNF ala T-SQL
Moving from the theoretical to BNF for T-SQL does not demand too much imagination. BOL describes the necessary tweaks in the “Transact-SQL Syntax Conventions (Transact-SQL)” section. It summarizes minor modifications to the canonical Backus–Naur Form along with a few typographic hints, such as, bold text indicating a SQL Server object.
By returning to our earlier foo-bar example we can easily demonstrate some of these conventions. First, assume foo does not require any arguments. Adding square brackets indicates this fact as shown next.
<foo>::= [<foo> |<bar>]
<bar>::= 1 | 2
Let us also make it a requirement that when calling bar it demands further information, which can be accomplished via squiggly brackets. And if these arguments are not provided, then bar will assume a default value of constant “1” as indicated by an underline.
<foo>::= [<foo> |<bar>]
<bar>::= {1 | 2}
If the above two lines of metasyntax fail to impress, consider the volume of information they convey. It is for this reason that familiarity with BNF ala T-SQL pays off. Do you prefer reading a little metasyntax or a page of text?
Alter Index Metasyntax
Let’s conclude our discussion by briefly dissecting our initial BNF sample, the Alter Index function. As we cut through it, keep in mind that the accompanying explanations only exist because we’re learning BNF ala T-SQL.
Line: ALTER INDEX { index_name | ALL }
Explanation:
· ALTER INDEX – capitalization tells us that these are T-SQL keywords
· { } – squiggly brackets mean that ALTER INDEX requires an argument
· index_name – italicized text connotes a user supplied value
· ALL – another T-SQL keyword
· index_name | ALL – pipe informs us of two mutually exclusive arguments
Line: ON <object>
Explanation:
· ON – T-SQL keyword (Remember the capitalization rule?)
· <object> - the infamous BNF nonterminal symbol which BOL calls “name for a block of syntax”
· Note that <object> is described as expected later on in the metasyntax
Line: { REBUILD
Explanation:
· { – Opening bracket signaling required arguments
· REBUILD – T-SQL keyword
Line: [ [PARTITION = ALL]
Explanation:
· [ – Opening bracket for a collection of optional arguments
· [PARTITION = ALL ] – optional argument
· PARTITION – T-SQL keyword
· ALL - T-SQL keyword
This seems like a good place to stop since we are starting to repeat ourselves. Did I mention that BNF is simple and recursive, as well as, succinctly descriptive?
Further Information
Aside from BOL, most of the information for this article was drawn from the following references.
BNF Overview
http://en.wikipedia.org/wiki/Backus%E2%80%93Naur_Form
Purchasable documents describing databases by ISO http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_tc_browse.htm?commid=45342
Free BNF extracts for SQL-92, SQL-99 and SQL-2003
