Odd TSQL Behavior

May 20, 2009 at 2:18 pm (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, )

Before I describe this, let me thank Lynn Pettis over at SQL Server Central for coming up with the answer.

This morning a developer walked up and asked me what would happen if he ran a query that looked like this:

SELECT 1.SomeString

I said that he’d get an error. No, he says, try it. So I try it and I get this:

Somestring
1

Try it yourself. It works just fine. I’d never seen that before and didn’t have a clue what it was. Thinking that Microsoft had supplied some new short hand to define aliases I ran this:

SELECT 'dude'.dudette

Which resulted in the error:

 

Msg 258, Level 15, State 1, Line 1
Cannot call methods on varchar.

 

Which is what I would have expected. I tried a couple of functions and some other bits & pieces of data, but only integers seemed to work… Or was it? What would be the purpose of a function that defines aliases for an integer?

I finally posted a question up on SQL Server Central. One of the local brain trust over there, Lynn Pettis, chewed on it for a bit and identified the issue. SQL Server is “helping” you by assuming that you meant to put a space between the period (dot or . ) and the string, Somestring, as an alias. I then tried this:

SELECT 'dude'dudette

And was rewarded with the same behavior.

Personally, in this case, I don’t see this as TSQL “helping” me nearly so much as it confused the heck out of me. Thanks again for figuring it out Lynn.

Permalink 2 Comments