A few days ago I read post of Jen McCown (blog) about her idea of blogging about random articles from Books Online. I think this is a great idea, even if Jen says that it’s not exciting or sexy. I noticed that many of the questions that appear on forums and other media arise from pure fact that people asking questions didn’t bother to read and understand the manual – Books Online. Jen came up with a brilliant, concise acronym that describes very well the category of posts about Books Online – RTFM365. I take liberty of tagging this post with the same acronym. I often come across questions of type – ‘Hey, i am trying to create a table, but I am getting an error’. The error often says that the syntax is invalid. 1 CREATE TABLE dbo.Employees
2 (guid uniqueidentifier CONSTRAINT DEFAULT Guid_Default NEWSEQUENTIALID() ROWGUIDCOL,
3 Employee_Name varchar(60)
4 CONSTRAINT Guid_PK PRIMARY KEY (guid) );
5
The answer is usually(1), ‘Ok, let me check it out.. Ah yes – you have to put name of the DEFAULT constraint before the type of constraint:
1 CREATE TABLE dbo.Employees
2 (guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
3 Employee_Name varchar(60)
4 CONSTRAINT Guid_PK PRIMARY KEY (guid) );
Why many people stumble on syntax errors? Is the syntax poorly documented? No, the issue is, that correct syntax of the CREATE TABLE statement is documented very well in Books Online and is.. intimidating. Many people can be taken aback by the rather complex block of
code that describes all intricacies of the statement.
However, I don’t know better way of defining syntax of the statement or command.
The notation that is used to describe syntax in Books Online is a form of Backus-Naur notatiion, called BNF for short sometimes. This is a notation that was invented around 50 years ago, and some say that even earlier, around 400 BC – would you believe? Originally it was used to define syntax of, rather ancient now, ALGOL programming language (in 1950’s, not in ancient India).
If you look closer at the definition of the BNF, it turns out that the principles of this syntax are pretty simple. Here are a few bullet points:
italic_text is a placeholder for your identifier
<italic_text_in_angle_brackets> is a definition which is described further.
[everything in square brackets] is optional
{everything in curly brackets} is obligatory
everything | separated | by | operator is an alternative
::= “assigns” definition to an identifier
Yes, it looks like these six simple points give you the key to understand even the most complicated syntax definitions in Books Online. Books Online contain an article about syntax conventions – have you ever read it?
Let’s have a look at fragment of the CREATE TABLE statement:
1 CREATE TABLE
2 [ database_name . [ schema_name ] . | schema_name . ] table_name
3 ( { <column_definition> | <computed_column_definition>
4 | <column_set_definition> }
5 [ <table_constraint> ] [ ,...n ] )
6 [ ON { partition_scheme_name ( partition_column_name ) | filegroup
7 | "default" } ]
8 [ { TEXTIMAGE_ON { filegroup | "default" } ]
9 [ FILESTREAM_ON { partition_scheme_name | filegroup
10 | "default" } ]
11 [ WITH ( <table_option> [ ,...n ] ) ]
12 [ ; ]
Let’s look at line 2 of the above snippet: This line uses rules 3 and 5 from the list. So you know that you can create table which has specified one of the following.
just name – table will be created in default user schema
schema name and table name – table will be created in specified schema
database name, schema name and table name – table will be created in specified database, in specified schema
database name, .., table name – table will be created in specified database, in default schema of the user.
Note that this single line of the notation describes each of the naming schemes in deterministic way. The ‘optionality’ of the schema_name element is nested within database_name.. section. You can use either database_name and optional schema name, or just schema name – this is specified by the pipe character ‘|’.
The error that user gets with execution of the first script fragment in this post is as follows:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.
Ok, let’s have a look how to find out the correct syntax. Line number 3 of the BNF fragment above contains reference to <column_definition>. Since column_definition is in angle brackets, we know that this is a reference to notion described further in the
code. And indeed, the very next fragment of BNF contains syntax of the column definition.
1 <column_definition> ::=
2 column_name <data_type>
3 [ FILESTREAM ]
4 [ COLLATE collation_name ]
5 [ NULL | NOT NULL ]
6 [
7 [ CONSTRAINT constraint_name ] DEFAULT constant_expression ]
8 | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ]
9 ]
10 [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ]
11 [ SPARSE ]
Look at line 7 in the above fragment. It says, that the column can have a DEFAULT constraint which, if you want to name it, has to be prepended with [CONSTRAINT constraint_name] sequence. The name of the constraint is optional, but I strongly recommend you to make the effort of coming up with some meaningful name yourself. So the correct syntax of the CREATE TABLE statement from the beginning of the article is like this:
1 CREATE TABLE dbo.Employees
2 (guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
3 Employee_Name varchar(60)
4 CONSTRAINT Guid_PK PRIMARY KEY (guid) );
That is practically everything you should know about BNF. I encourage you to study the syntax definitions for various statements and commands in Books Online, you can find really interesting things hidden there.
Technorati Tags: SQL Server,t-sql,BNF,syntax
(1) No, my answer usually is a question – ‘What error message? What does it say?’. You’d be surprised to know how many people think I can go through time and space and look at their screen at the moment they received the error.
