SQL: The T-SQL SIGN function and what's in a return type?

When you've worked with a product like SQL Server for a long time, and more importantly, are one of the odd people who've read a great amount of the documentation simply for interest, it feels really strange to come across a basic function that you'd never noticed before. That's how I felt when someone mentioned the T-SQL SIGN function.

I thought, "the what function??".

Now it works pretty much as you'd expect. It returns:

  • +1 for positive numbers
  • 0 for zero values
  • -1 for negative numbers
  • NULL for NULL values

No surprises there and you can see that in the main picture above.

What I wasn't expecting (and have to say if I was creating it that I would not have done), was the output data types. Here are the values returned:

I don't get why the return type has been designed to match the input type. It seems to me that a value indicating positive, zero, or negative should really have a fixed data type ie: int.

Regardless, I was also intrigued by the "Other types" going to float. It's not all types, as the value appears to need to be directly castable to float:

I tried other numeric data types to see what happens. I pushed a set of them into a temporary table:

And then checked the column data types that were returned:

It really does try to mostly match the input data type. I was mostly interested to see if decimal values would match the precision and scale, and they do.

There must have been some logic for the varying output data types, and that means there must have been some envisaged functionality beyond just indicating the sign. I'd love to hear from any of you if you have any ideas on how the varying output data types for this could be used in a practical way.

The online documentation also says "from SQL Server 2008" but that's just the oldest supported version anyway. Anyone know what version this was first introduced in?

 

 

2 thoughts on “SQL: The T-SQL SIGN function and what's in a return type?”

  1. I know that when developing older reporting applications using WebFOCUS it was extremely helpful to understand the exact output data types especially if for some reason I needed change them at the Application level but not the Database.

    1. Data types are clearly important, but for a function that just indicates the sign of a number, I'm not seeing a valid reason for multiple output types. For example, ISNUMERIC always returns int.

Leave a Reply

Your email address will not be published.