CODEWITHSUNDEEP
sql-server
Michael Todd
Michael Todd

Reputation: 17051

Why is a space a valid column name in SqlServer?

See for yourself:

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

What in the world is the rationale for allowing this?

Upvotes: 10

Views: 2158

Answers (6)

Steven
Steven

Reputation: 3928

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).

Upvotes: 4

dkretz
dkretz

Reputation: 37655

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.

Upvotes: 2

Peter Perháč
Peter Perháč

Reputation: 20782

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.

Upvotes: 4

Jeroen Landheer
Jeroen Landheer

Reputation: 9903

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.

Upvotes: 2

JoshBerke
JoshBerke

Reputation: 67068

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.

Upvotes: 10

Joel Coehoorn
Joel Coehoorn

Reputation: 415790

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.

Upvotes: 9

Related Questions