So what exactly is the story with square brackets in SQL Statements?
What’s the difference between
SELECT [FirstName] FROM [Contacts]
and
SELECT FirstName FROM Contacts
?
Today, I’m going to explore this for a moment.
What are Square Brackets Use For In SQL?
First off, when we mention ‘Square Brackets’ we are talking about [ and ].
Around Object and Field Names
In simple terms, they are used to escape keywords, reserved words or special characters.
Most commonly, we see the need to use square brackets when people use space when naming objects and fields. Examining the original example above, since there are no special characters and none of the names are reserved words, it isn’t truly necessary to include square brackets.
That said, if instead of naming the field FirstName, they had named it First Name, then the brackets become obligatory around the field name.
Tip: A name that contains spaces is easier to read and can save you time when you design forms and reports, but may end up making you type more when you write SQL statements. You should consider this fact when you name objects in your Access database.Access SQL: SELECT clause
SELECT [First Name] FROM Contacts
Do Brackets Hurt?
Short answer, no. There is no real downside to including brackets, even when not strictly necessary.
You’ll see many experienced developers use them to ensure never having any problems. General functions that accept object names, field names, … often include brackets as we can never ensure that at some point it won’t be passed some special case that requires escaping.
Example 1
For example, if we look at code to find the first match in a form, instead of something like:
Sub FrmRequery(frm As Form, sSearchField As String, sSearchTerm As String)
Dim rs As DAO.Recordset
Set rs = frm.RecordsetClone
rs.FindFirst sSearchField & "='" & sSearchTerm & "'"
frm.Bookmark = rs.Bookmark
Set rs = Nothing
End Sub
it would be better to use something like:
Sub FrmRequery(frm As Form, sSearchField As String, sSearchTerm As String)
Dim rs As DAO.Recordset
Set rs = frm.RecordsetClone
rs.FindFirst "[" & sSearchField & "]='" & sSearchTerm & "'"
frm.Bookmark = rs.Bookmark
Set rs = Nothing
End Sub
because the second instance will accept any field name, whereas the first one will error if passed fields with spaces or other special characters in its name.
When coding always assume the worst; a few extra brackets guarantees your code doesn’t break and can handle anything thrown at it.
Example 2
Let’s look at another example. Say we were trying to script the creation of a table (a badly designed table, but none the less a table), we might try
CREATE TABLE Date
(
Id AUTOINCREMENT PRIMARY KEY,
Date DATETIME
);
This will fail and produce
Why? Because we have Reserved Words in the query in the name of the table, Date, as well as the field. So, if we truly wanted to create such a table, this would be when square brackets would be used and thus we would do:
CREATE TABLE [Date]
(
Id AUTOINCREMENT PRIMARY KEY,
[Date] DATETIME
);
Example 3
Similarly to the previous example, let us try to create a table named ‘Corporate Contacts’ with the following fields: Id, First Name, Last Name, Title, Is Active, Date Created. You might try:
CREATE TABLE Corporate Contacts
(
Id AUTOINCREMENT PRIMARY KEY,
First Name CHAR,
Last Name CHAR,
Title CHAR,
Is Active BIT,
Date DATETIME
);
but that will once again fail and bracketing is required because of the special characters (spaces, …) and the use of Reserved words. Thus, the proper syntax would become:
CREATE TABLE [Corporate Contacts]
(
Id AUTOINCREMENT PRIMARY KEY,
[First Name] CHAR,
[Last Name] CHAR,
Title CHAR,
[Is Active] BIT,
[Date] DATETIME
);
or
CREATE TABLE [Corporate Contacts]
(
[Id] AUTOINCREMENT PRIMARY KEY,
[First Name] CHAR,
[Last Name] CHAR,
[Title] CHAR,
[Is Active] BIT,
[Date] DATETIME
);
but the better solution is to never use either special characters, nor reserved words and create a table more like:
CREATE TABLE CorporateContacts
(
ContactId AUTOINCREMENT PRIMARY KEY,
FirstName CHAR,
LastName CHAR,
Title CHAR,
IsActive BIT,
CreationDate DATETIME
);
What’s the Takeaway
By now, there should be 2 major takeaways from this discussion:
- Do not use special characters (remember, spaces are evil when naming things!) or reserved words when naming anything within an application! This is a Best Practice for any developer and have included a link to more on the subject below.
- When writing SQL statements, VBA code, best err on the side of caution and use bracketing when you are not in control of what will be passed as names of objects, fields, …
A Few Resources on the Subject
