views:

140

answers:

2

I have a Location table in my OLTP which, while coding SSIS against it, I found a LocationCode column name to have a trailing space in it.

Create Table Location
(
    [LocationId] INT IDENTITY (1, 1)
    [LocationCode ] INT
)

Note that LocationCode column name has a trailing space.

And yet, the following code works.

SELECT LocationCode
From Location

As a matter of fact, we located several places where the trailing space is used in stored procs, and it has continued to work.

How is this possible?

+4  A: 

If delimited identifiers are used when naming an object and the object name contains trailing spaces, SQL Server stores the name without the trailing spaces.

http://msdn.microsoft.com/en-us/library/ms176027%28SQL.90%29.aspx

p.s. Delimited identifiers everywhere is a code smell -- they should be used SPARINGLY, not for every identifier.

Matt Rogish
@Matt: In SSMS, I rightclicked the table name and did "Script Create To New Window" - it came up with [LocationCode ] with a trailing space.
Raj More
Feel free to remove the delimiters then if they're being auto-generated for identifiers that follow standard naming rules. Otherwise, it just adds noise :D
Matt Rogish
(It's probably a bug in SSMS)
Matt Rogish
@MattRogish: Try creating the table that I showed, and then script it out of SSMS.. the trailing space is retained. But you can query it without the trailing space.
Raj More
Indeed, inside syscolumns it includes the trailing space (do select 'start' + name + 'end' from sysobjects where name like 'LocationCode%') however the interpreter must strip out the trailing spaces. So, it stores the space but ignores it upon query (as it probably ought to). Sounds like we found a documentation bug :D
Matt Rogish
Yes sir, I submitted it as a bug here https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=483389
Raj More
A: 

In addition to what @Matt Rogish said, a trailing space in a column name is not treated like any other special character. It is actually ignored when parsing queries (whereas a hyphen or a tilda would not be ignored).

I have submitted a bug to Microsoft Connect for SQL Server.

Raj More