T-SQL Dollar Sign In Expressions

Question

In this answer, there is a trick which allows to use the ROW_NUMBER() windowed function with a 'constant' in the ORDER BY clause:

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

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

I've tried to execute a simple query:

SELECT $;

And it returns 0.

Could somebody explain this?

Answer 1

It's just a money constant (what T-SQL calls literals).

You presumably would have been less surprised if you saw the expression $2.50, which would just be another constant.

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

---

It can be tricky to determine what type of data you're looking at in T-SQL. One trick, if you suspect you know what type it is is to pick an incompatible type and attempt the conversion:

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 for pointing to the right direction.

I'd just like to add to his answer results of a query that gives an exact description what are constants with the $ sign:

  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