Why is a space a valid column name in SqlServer?

Question

See for yourself:

create table #temp ([ ] varchar(1))<br />
insert into #temp values ('1')<br />
select [ ] from #temp<br />

What in the world is the rationale for allowing this?

Answer 1

I use it all the time as a placeholder/seperator when debugging complex queries. I might have something like this:

SELECT a.*, ' ' as [ ], b.*
FROM a
LEFT JOIN b on ...

This way I get a blank section in between the two tables so I can easily see in the results where one stops and the other starts.

Later on when I get the results and performance I need I'll change the select clause to only use the columns I care about.

That said, I suppose there's no reason I couldn't use something else for the column name.

Answer 2

I think the rationale is more along the lines of:

Is it worth preventing this functionality?

I don't know how SQL is coded internally but I would suspect it would take more effort to prevent this then to allow it.

Answer 3

I guess a space is just like any other character. You can even create a table named [ ]. (I tried it, and it worked.) I think there are simply no restrictions on this.

Answer 4

Any valid characters can be used in between [] to define the column & table names. Given that something like [A Column] is valid (with space), there is no reason to prevent a single space as a column name.

However, trailing spaces are removed so.

[ ] and [  ] (1 & 2 spaces) 

will be both treated as

[ ] (1 space).

Answer 5

Space is an acceptable character, as some people like to have spaces in their column names. And if it can be a part of a column name then why not a column name by itself?

Why do they ever do it will remain a mystery to me, as it makes programming so much more difficult (constantly enclosing everything in "" or []). I have actually seen a column name with a question mark, which is definitely something I would avoid using in any identifier, but it's possible, still.

Answer 6

I kinda appreciate the assumption that I'm responsible enough not to hurt myself with potentially dangerous features. I haven't found a good reason for spaces in object names myself, but it seems to be popular in some cases. This is an extreme example of running with scissors.