Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

BNF Basics

By Tom Fischer,

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.

Books Online sample

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


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 }


·         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>


·         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


·         {  –  Opening bracket signaling required arguments

·         REBUILD – T-SQL keyword

Line:  [ [PARTITION = ALL]


·         [ – 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

Purchasable documents describing databases by ISO

Free BNF extracts for SQL-92, SQL-99 and SQL-2003

Total article views: 8971 | Views in the last 30 days: 1
Related Articles

Function to search string enclosed within brackets

Looking for help with function that can search words enclosed within brackets and return it as table...


Building Debug Symbols – Troubleshooting symbol building

Recently, I have working with the Debugger Symbols for SQL Server to generate call stacks and learn ...


Decimal symbol

Is possible to set decimal symbol


Full text indexing of symbols?

want to index on "@"


Full text indexing of symbols?

want to index on "@"


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones