Tag: SQLNewBlogger

I was trolling the docs and noticed the SIGN() function. I have never written this in production code, but it is an interesting function. This post looks at where I might use this and when the need arises.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

How It Works

The SIGN() function works by taking an argument and evaluating if the value is positive, negative, or zero. The example in the MSLearn Docs shows the values working in a few ways. I’m reproducing that here to look at how it works.

If I change this to work with float and strings, we get similar values.

Essentially, this implements this code:

IF @value > 0 SELECT @value, 1
    IF @value = 0 SELECT @value, 0
    IF @value < 0 SELECT @value, –1

Or this code:

    CASE WHEN @value > 0 THEN 1
    WHEN @value = 0 THEN 0
    WHEN @value < 0 THEN -1
    END AS valsign

This is a simple function, and it’s easily duplicated in code, so why use it?

Use Cases

Most of the mathematical algorithms I’ve implemented don’t deal with negative numbers in a material way. Aggregates, such as averages and sums will take the value into account and the sign isn’t important.

In some cases, it might. Perhaps I want to do some math around distances from zero, but I don’t want the values to cancel each other out. For example, maybe I have a small data set. I have some shipments and weights.

Now, it makes sense that we’re shipping to and from our warehouse and tracking the direction with a negative quantity for returns. However, to calculate total shipping weight, a sum doesn’t work:

I really want to normalize the values. I could use SIGN() here, as shown:

Of course, ABS() works as well, so that’s not necessarily a great example. I’d argue both are slightly obscure without a comment in the code.

Another example, perhaps I’m looking to determine a trend of movement. I saw this on the Internet from someone else.

If I run this code, I’m getting the change of values, but also the direction of travel. That TrendDirection lets me know which ways things changed.

I might want to look for (or alert on) a trend. So, if I look at lines 14-17, I have a trend of increasingly negative values. Perhaps if I have 3 in a row (a complex LAG), I raise an alert.

Here’s a LAG with SIGN repeated to show that.

There are other cases I might care about, but these come to mind.

SQLNewBlogger

This is an example of a post that shows I know how a function works, but mostly where I might use it. I added my own thoughts, and a couple of use cases.

This post took about 40 minutes to write, with the code setup and some internet searching involved. I did use Prompt AI to generate some tables and code, which made things easy, but I had to think a bit on the scenarios and how I felt about them.

All good things to showcase in the age of AI. If an AI generated code, could you determine the use? Knowing SIGN() can help. Write your own post and showcase your knowledge. Disclose if AI helps.

Someone asked why I would use TRY_PARSE after I posted a question at SQL Server Central: Getting the Average. Isn’t is slower?

A fair question. This quick post looks at why.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

A Quick Setup

The question above has the setup code, but what if I add another row? For example, I’ll run this .

insert dbo.commission
(
    salesperson
  , commission
)
values
(‘Steve’, ‘A’)

Now, let’s look at the data and run the query from the question.

This works. However, let’s remove the (slow) TRY_PARSE() from the aggregate.

Error. Why? I can’t convert “A” in the AVG to a number. It fails.

You might think, I’ll never get bad data like this. But you might? A user might enter something you don’t expect. An AI might model this as a string, which is bad, but it happens. If it’s an EAV type table, or there are other data  items and you’re trying to extract the numbers from here, TRY_PARSE is helpful.

SQLNewBlogger

I wrote a post last week and this is a followup that really just took less than 5 minutes to setup and run. Plus I responded for the user in the post.

This showcases me thinking about a question and situation and really gives an interviewer something to ask me. This lets them dive into my thought process and gives them confidence I don’t just write code without thinking.

Add to your blog with short posts like this (or drop on LinkedIn).

I got a notification from a question I’d posted at SQL Server Central: Getting the Average. A user had posted their repro didn’t work, with no real comment. As a SQLNewBlogger FYI, that type of post shows poor communication and a lack of communication. I see that a lot and it’s a challenge in the modern world.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

A Quick Setup

This was what the user posted:

declare @t table
(
    id int identity
  , i int
);

insert @t
select null;

insert @t
select 11;

select avg(try_parse(i as int))
from @t
group by id;

This does return an error, as you can see below.

Why?

Well, the TRY_PARSE() docs give part of an explanation. I highlighted this in Yellow, but the relevant text says “only for converting strings”.

Shouldn’t an int convert to a string? No, the precedence rules have int higher than char types. We convert lower to higher, not higher to lower.

SQLNewBlogger

I noticed something, thought for a second why this wouldn’t work, and then checked the docs. I decided to write this up and it was a 5-10 minute post for me. Easy to do and showcasing knowledge.

It helps me remember, might teach someone something, and gives an interviewer something to ask me. Add to your blog with short posts like this (or drop on LinkedIn).