T-SQL Login Expressions

Question

This answer has a trick that allows you to use a ROW_NUMBER() window function with a constant in the ORDER BY :

 SELECT ROW_NUMBER() OVER (ORDER BY $/0) FROM master..spt_values 

After some googling, I can't find what the dollar sign means in this context?

I tried to execute a simple query:

 SELECT $; 

And it returns 0 .

Can someone explain this?

Answer 1

This is just money constant (that T-SQL calls literals).

You would probably be less surprised if you saw the expression $2.50 , which would just be a different constant.

Some other examples: select £,¢,¤,¥,€ all also return 0s.

---

It is difficult to determine the type of data that you are viewing in T-SQL. One trick if you suspect that you know which type it should select an incompatible type and try to convert:

 select CONVERT(date,$) 

Result:

 Msg 529, Level 16, State 2, Line 1 Explicit conversion from data type money to date is not allowed. 

Answer 2

Thanks to @Damien_The_Unbeliever for pointing in the right direction.

I would like to add to his answers the results of a query that gives an exact description of what constants with a $ sign are:

  SELECT $ AS Value, SQL_VARIANT_PROPERTY ( $ , 'BaseType' ) AS BaseType, SQL_VARIANT_PROPERTY ( $ , 'Precision' ) AS Precision, SQL_VARIANT_PROPERTY ( $ , 'Scale' ) AS Scale, SQL_VARIANT_PROPERTY ( $ , 'TotalBytes' ) AS TotalBytes, SQL_VARIANT_PROPERTY ( $ , 'MaxLength' ) AS MaxLength UNION ALL SELECT $2.50, SQL_VARIANT_PROPERTY ( $2.50 , 'BaseType' ), SQL_VARIANT_PROPERTY ( $2.50 , 'Precision' ), SQL_VARIANT_PROPERTY ( $2.50 , 'Scale' ), SQL_VARIANT_PROPERTY ( $2.50 , 'TotalBytes' ), SQL_VARIANT_PROPERTY ( $2.50 , 'MaxLength' ) 

Results:

 Value BaseType Precision Scale TotalBytes MaxLength 0.00 money 19 4 10 8 2.50 money 19 4 10 8