Unusual T-SQL - What is happening here?

Can someone please explain to me what is going on with this query?

select 99.foo

It is b开发者_开发知识库ehaving the same as

select 99 as foo

or

select foo = 99 

Also, it only appears to work with integers.

The select 99.foo is translated as select 99. as foo (as you can leave out the space before the alias), selecting '99.' as a numeric. As there are no numbers after the decimal point, it just displays '99'.

You can confirm this by running:

select sql_variant_property(99., 'BaseType')

which returns numeric.

This is not the same as select 99 as foo, which does select '99', but as an int. This can be confirmed by runnning:

select sql_variant_property(99, 'BaseType')

which returns int.

Although all three queries appear to be the same, the first is different from the next two in the type of the value that is returned.

The 'AS' is optional, so select 99 foo works.

The trailing period is interpreted as "point zero", so select 99. foo also works.

And there doesn't have to be a space between the value and the alias, so select .99foo works too.

These also work:

 select 99foo

 select 'ninetynine'foo

You can write SELECT col AS name to have the col column returned with the name name. It is allowed to leave out the AS, meaning that SELECT col name is the same thing, but it's also allowed to leave out the space between them. In your example, 99. is the number 99 and foo is the column name. In the $.foo example, $. is the literal for a MONEY value of 0, which is why it outputs 0.00.

The apparently confusing part is presumably the . after the 99, which makes it look like you're querying a field from belonging to an object called 99.

Just to really be weird, you can do

SELECT 99foo

and

SELECT 99AS foo

which both work (and give an int in a column called foo). Examples using . seem to return various numerics. I'd say the parser's trying to be clever. If you meant the data to be one of the non-numeric field types then you'd be enclosing the data in apostrophes, therefore you meant something numeric. Therefore, anything that isn't a number is being used to deduce an alias.