Unusual T-SQL - What is happening here?

Question

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

select 99.foo

It is behaving the same as

select 99 as foo

or

select foo = 99 

Also, it only appears to work with integers.

Answer 1

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.

Answer 2

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