Unusual T-SQL - What is happening here?

Question

Can someone explain to me what is happening with this request?

select 99.foo 

He behaves just like

 select 99 as foo 

or

 select foo = 99 

In addition, it only works with integers.

Answer 1

select 99.foo converts to select 99. as foo (since you can leave a space in front of the alias) by selecting "99". like a numeric . Since there are no numbers after the decimal point, it simply displays "99".

You can confirm this by doing:

 select sql_variant_property(99., 'BaseType') 

which returns numeric .

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

 select sql_variant_property(99, 'BaseType') 

which returns an int .

Although all three queries seem the same, the first is different from the next two in the return type.

Answer 2

"AS" is optional, so select 99 foo works.

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

And there should not be a space between the value and the alias, so select .99foo also works.

They also work:

  select 99foo select 'ninetynine'foo 

Answer 3

You can write SELECT col AS name to return a col column named name . It is allowed to leave AS , which means that SELECT col name is the same thing, but it is also allowed to leave space between them. In your example, 99. is the number 99, and foo is the column name. In the example $.foo $. is a literal for the value MONEY 0, so it outputs 0.00 .

Answer 4

Apparently, the confusing part seems to be there. after 99, which makes it look like you are requesting a field from object owned by 99.

Just to be weird, you can do

 SELECT 99foo 

and

 SELECT 99AS foo 

which both work (and give int in the foo column). Examples of using. various numbers seem to return. I would say that the parser is trying to be smart. If you meant that the data is one of the non-numeric field types, then you should insert the data in apostrophes, so you meant something numeric. Therefore, everything that is not a number is used to display an alias.