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.
I took a course in University on compilers and I sympathize with the developers. It’s a tricky problem. Whitespace is a convenient way to split up tokens, but it’s not always necessary.
I think a lot of this would be a lot less confusing if decimal constants required at least one digit after the decimal. I mean, if you’re going to use a decimal, then use it! But it’s too late now. I’m sure someone somewhere is depending on this functionality.
Also interesting. Now that you have a firm grasp on this mess, try to predict the results of the following before plugging it into SQL Server:
select 1.e1e2
Without running it… e2 since the rest is math. That came up on the discussions over on SSC…
Yep. Worked. Strange stuff. You could really mess with people’s heads a bit with this.
Why is this ?
select 1.e10 returns “10000000000”
select LEN(1.e10) returns “6”
Why “6”? Can someone throw some light !!!
Yeah, you’re creating a floating point number when you do that, and LEN is the size, in bytes, not length, of the data type.
This is fantastically strange. I love coming across syntax like this! Just so I know I can relate to the developers code language across the realms of .net and SQL!