How to Read a T-SQL Syntax Diagram

The CREATE INDEX page looks like punctuation learned to type. A T-SQL syntax diagram becomes simple once you know what brackets, braces, pipes, and dots mean.

Read the Legend Before the Statement

Microsoft’s T-SQL reference uses a compact notation. Uppercase words are keywords. Names in placeholder positions are values you supply. Square brackets mark optional syntax. Braces group required choices. A vertical pipe separates alternatives. An ellipsis means the preceding item can repeat.

I start by reading the legend, not the whole page. Otherwise a long CREATE INDEX syntax block looks like a grocery receipt from another planet. Once the symbols are clear, read only the branch that fits your task. You do not need to understand every storage and partition option to create a basic index.

Which parts of the syntax are actually required for your statement? Answer that before copying an example. The answer is usually much smaller than the full diagram.

Square Brackets in a T-SQL Syntax Diagram Mean Optional

In a syntax diagram, square brackets around an item mean you can omit that item. They are notation, not characters to type. This is different from square brackets around a SQL identifier, such as a table name with spaces. Context tells you which use you are seeing.

Consider an optional UNIQUE keyword in CREATE INDEX. If you omit it, the index is not unique. If you include it, SQL Server enforces unique key values. The diagram tells you the choice exists; the feature documentation tells you what it does.

I check the item immediately outside the brackets. A comma can belong inside a repeated optional list. Copying punctuation without the chosen item produces a syntax error. Read the whole bracketed unit before typing.

Braces and Pipes in a T-SQL Syntax Diagram Force a Choice

Braces mark a required selection from alternatives. The pipe separates those alternatives. If the syntax shows a required choice between CLUSTERED and NONCLUSTERED, choose the one you intend and type that keyword. Do not type the braces or the pipe.

A pipe inside square brackets gives an optional choice. You can select one alternative or omit the entire bracketed group. The container changes the rule. This is why reading one symbol without its neighbors can mislead you.

When a T-SQL syntax diagram has nested groups, trace from the outside inward. Decide which feature branch applies, then read the options inside it. Ignore branches for features you are not using. The shortest valid route is a useful first script.

Ellipses Mean Repetition

An ellipsis after a column item means the pattern can repeat. A notation with a comma before the dots means additional items are comma separated. You do not type the dots or the letter n. Add the columns you need, with the delimiter the diagram shows.

CREATE INDEX uses a list of key columns. The INCLUDE clause uses a separate list of nonkey columns. Read the two lists separately. A column in the wrong list changes index behavior, even when the statement compiles.

The diagram describes grammar, not index design. It cannot decide your key order or whether an included column helps a query. Use a plan and workload evidence for that choice. Syntax is the doorway, not the whole house.

Build a Small CREATE INDEX Example

Start with a temp table. Create an index with one key and one included column. The script below is a safe grammar exercise in your own session. It uses a named index, the required ON target, a key list, and an optional INCLUDE list.

Read the statement against the T-SQL syntax diagram. CREATE INDEX is literal. The index name and table name are values you supply. The parentheses hold a column list. INCLUDE is an optional branch. The semicolon ends the statement.

I test syntax on a disposable table before adapting it to production. A successful CREATE INDEX on a large table can take resources and block work. The grammar lesson belongs in tempdb; the production change belongs in a reviewed maintenance plan.

CREATE TABLE #IndexDemo
(
    CategoryId int NOT NULL,
    Amount decimal(10,2) NOT NULL
);
CREATE INDEX IX_IndexDemo_Category
ON #IndexDemo (CategoryId)
INCLUDE (Amount);

Check What SQL Server Built

The catalog can show the index name, type, and key columns. Query it in the same session before the temporary table is dropped. This is a useful habit after changing a long statement with several optional clauses. Confirm the result rather than assuming the parser understood your intent.

The first query below lists the index. The second lists the columns and whether they are keys or included columns. If you run them in a new session, the temporary table is gone. That is expected.

Why inspect a simple example? Because the same check scales to a real table when an index definition is disputed. The catalog is the built object, while the syntax diagram is the recipe.

SELECT i.name, i.type_desc
FROM tempdb.sys.indexes AS i
WHERE i.object_id = OBJECT_ID('tempdb..#IndexDemo');

SELECT c.name, ic.key_ordinal, ic.is_included_column
FROM tempdb.sys.index_columns AS ic
JOIN tempdb.sys.columns AS c
  ON c.object_id = ic.object_id
 AND c.column_id = ic.column_id
WHERE ic.object_id = OBJECT_ID('tempdb..#IndexDemo')
ORDER BY ic.index_id, ic.index_column_id;

Use the T-SQL Syntax Diagram as a Map

When a statement fails, find the first token SQL Server rejects. Walk back to the matching branch in the diagram. Check whether you omitted a required choice, typed notation characters, or missed a delimiter in a repeated list. This beats randomly moving commas.

Version and platform notes matter. A syntax page can cover SQL Server, Azure SQL, and other products with different options. Read the Applies to notes for the branch you chose. A valid option on one platform does not automatically exist on another.

After the exercise, drop the temp table or close the session. Keep the small working example as a starting point. The next time a syntax page looks crowded, read one path through it instead of every path at once.

DROP TABLE #IndexDemo;

A final check is to compare your statement with a documented example for the same SQL Server product and release. Syntax diagrams can include options for several platforms. The Applies to note belongs to the branch you selected, not only to the page title. If an option fails in your test, verify platform support before changing punctuation. A clean test script is easier to reason about than a production statement with ten unrelated options.

Related reading on this blog: Create Index Without Locking Table and How to Validate Syntax and Not Execute Statement: An Unexplored Debugging Tip.

A syntax diagram is not code to paste, it is a map of choices to make.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.