5

Is there a reason we can specify a tablename with [schema].[tableName] instead of just schema.tablename?

is it JUST to allow spaces in a tableName?

1

2 Answers 2

16

They're called Delimited Identifiers, and their primary purpose is to allow for special characters and keywords to be used in/as table names

For example, some genius decided the name of the primary table in a database I work with would be master, which is a keyword.

They're really not needed at all except in those cases.

1
  • 4
    I have inherited many systems created by "geniuses".
    – BBlake
    Feb 9, 2012 at 19:36
11

They escape names that are not "friendly" - they can be useful if your database names contain special characters (such as spaces, dots or dashes) or represent SQL keywords (such as USE or DATABASE :-)). Without the square brackets, a query like this will fail:

SELECT column FROM database.dbo.table;

However if you write the query this way, the keywords are ignored:

SELECT [column] FROM [database].dbo.[table];

When building scripts or writing solutions that generate scripts from metadata (e.g. generating a script of all tables or all indexes), I always wrap entity names in square brackets so that they will work regardless of what kind of wonky names have been implemented (I am not always doing this for systems I control). You can do this easily using QUOTENAME function, e.g.:

SELECT QUOTENAME('table'), QUOTENAME('column'), QUOTENAME('database');

Results:

[table] [column] [database]

If you search my answers here and on Stack Overflow for QUOTENAME you will find that I use it in a lot of scripts where I'm helping folks automate script generation or building dynamic SQL. Without the square brackets, a lot of people would run into issues running the scripts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.