Referencing tables

Question

In SQL Server, why is this:

[dbo].[table_name]

preferable to this:

dbo.table_name

And along those lines, why even list the dbo at all if there's only one schema?

Answer 1

It's just in case you have a keyword as a tablename like [user]

Answer 2

If the table name contains key words, you will have to enclose it inside [ ]. Most of the tools (like ORM) use this technique to avoid any errors or for consistency.

Answer 3

It allows keywords or punctuation in a table name.

It's often used by code generators for all names, so they don't have to figure out if it actually needed.

Answer 4

These usually come up in generated code - because it's easier to make the code generation produce fully-qualified and escaped references than to deduce what escaping/qualification is required when.

Answer 5

If there is only one schema then prefixing the table name is not necessary or useful I think. Using the brackets [] is useful when you have an identifier that is a reserved word in sql server. If, for instance, you have a table named Select you can refernce it as SELECT * FROM [Select] but not as SELECT * FROM Select.

Answer 6

Doesn't this allow you to have whatever 'bad' items you desire in there?
Keywords, spaces, etc...

Answer 7

I would prefer to avoid using punctuation and reserved words in table and column names, and not use the square brackets. This makes the SQL far easier to read.

Answer 8

See this question (SQL Syntax Curiosity) for discussion of brackets.

Answer 9

I don't use the brackets, which are only necessary if you use keywords as schemas or table names, which you shouldn't.

But I would recommend against dropping the dbo at the front. The reason is that eventually you might want to start organizing your code into schemas, and then you will need the prefix. If you get in the habit of using the schema.table format, it will be a lot easier to search your code for places where the tables are used.

Let's say you have a table called dbo.user, and you decide to move it to another schema. If you have to search through a bunch of stored procedures or dynamic sql for "user", you will likely get a ton of false positives. You can't be totally sure that you made all the changes you needed to. Searching for "dbo.user" is a lot more concise.

Answer 10

The [] are only required if the object name contains characters like spaces or if it is a keyword. It is generally regarded as best practice not to use any of these as object names so you should never need the []. Having said that they also do no harm, if it is generated code and includes the brackets then you may as well leave them.
Using dbo is a good idea becuase

1. Performance is better (see here for some figures)
2. dbo is required in some cases, like calling user defined functions. I think it is tidyer to always include it.
3. Not qualifying the object name could lead to bugs in the future if you do create multiple schemas.