Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
My answer to a similar question: dba.stackexchange.com/a/7513/2660 –  Nick Chammas Feb 9 '12 at 21:07
add comment

2 Answers

up vote 14 down vote accepted

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.

share|improve this answer
4  
I have inherited many systems created by "geniuses". –  BBlake Feb 9 '12 at 19:36
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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