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.
