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.
