Voice of the DBA

Tag: SQLNewBlogger

Using the SIGN() Function: #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.
August 26, 2026
Why Use TRY_PARSE(): #SQLNewBlogger

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).

August 12, 2026
TRY_PARSE Limitations: #SQLNewBlogger
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).
August 5, 2026
Changing the Owner Removes Permissions: #SQLNewBlogger
This is actually inspired by an article SQL Server Central, which taught me something new. I decided to verify what was in the article and do some research. The summary

tl;dr if you change the schema owner, all permissions are dropped.

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

The Scenario

We start by creating three logins and their corresponding database users. Think of them as three colleagues with different roles:
- User1 — will own the schema
- User2 — will be granted access to a table
- User3 — will eventually take over schema ownership
Imagine there are a lot of User2 variants, as different logins are granted access to this table. To me, this is a problem, and I would use a role if I managed the system.
```
CREATE LOGIN User1 WITH PASSWORD = 'Demo12#1'
CREATE USER User1 FOR LOGIN User1
GO
CREATE LOGIN User2 WITH PASSWORD = 'Demo12#2'
CREATE USER User2 FOR LOGIN User2
GO
CREATE LOGIN User3 WITH PASSWORD = 'Demo12#3'
CREATE USER User3 FOR LOGIN User3
GO
```
Next, we create a schema explicitly authorised to User1, then add a table to it and populate it with some sample data.
```
CREATE SCHEMA MySchema AUTHORIZATION User1
GO
CREATE TABLE MySchema.MyTable (myid INT)
GO
INSERT MySchema.MyTable (myid)
VALUES (1), (2), (3)
GO
SELECT * FROM MySchema.MyTable
GO
```
At this point, User1 owns MySchema. Any objects inside it — like MyTable — fall under that ownership.

Now we grant User2 SELECT permission on the table. This is straightforward, explicit, and intentional. I’ll perform an explicit grant of permissions here.
```
GRANT SELECT ON MySchema.MyTable TO User2
GO
```
We can verify it works by impersonating User2 and running the query:
```
SETUSER 'User2'
GO
SELECT * FROM MySchema.MyTable
GO
SETUSER
GO
```
The query succeeds and returns all three rows. So far, everything is working as expected, as we see below..

Now, the tricky part I didn’t know.

Here’s where things get interesting. A database administrator decides to transfer ownership of MySchema from User1 to User3:
```
ALTER AUTHORIZATION ON SCHEMA::MySchema TO User3;
GO
```
This might seem like a routine administrative change — just updating who “owns” the schema. No permissions were explicitly revoked. No error is raised. But something has quietly changed.

If I now run the code above, I can’t access the table as User2.

I’ve lost access. If I check the ALTER AUTHORIZATION docs, I see this, with the last sentence being the important one. Permissions are dropped.

Something to know, and glad that Prompt AI knows this:

Summary

If you change the owner (authorization) on an object, and it’s not a database, permissions are dropped. This should be a warning at the very least, though to be fair, I’ve never changed schema ownership. It could happen, but in general, I try to keep dbo as the owner of all schemas.

In any case, something good to know.

SQL New Blogger

I took some code from an article I read (edited, really) and then used it to setup a scenario to test the concept. I likely will never forget this, and if I an AI suggests this, or can’t figure out what went wrong, I’ll have some idea myself to verify or validate a fix.

This post took about 20 minutes, including running the code a few times to test things, but it was a good exercise to show what I know, how I can use AI, and how I can spot issues.

You could use something like this as a learning exercise and to showcase your skills, even in the age of AI.
July 15, 2026