Tag: T-SQL

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

A customer was trying to compare two tables and capture a state as a performance metric. In this case, they were wanting to use Redgate Monitor and custom metrics, but since the tables were in a Memory-Optimized table, they couldn’t as Redgate Monitor runs inside a transaction.

Note, there are workarounds, but they’re clunky.

Fortunately, I had a quick solution, which involved SQL Server User Settable Objects. This post looks at how this works.

The Scenario

Let’s take the transaction out of the equation by using my own metric. SQL Server includes a few procedures that fall into a pattern. They are named with numerics as shown:

• sp_user_counter1
• sp_user_counter2
• sp_user_counter3
• …
• sp_user_counter10

Each of these corresponds to a value that is captured in a perfmon counter. The counters are in the objects called “SQLServer:User Settable”. The counter name is “query” and the instance is “User Counter n” where n is the number corresponding to the stored procedure.

By default, these are 0, and you can see them here:

You can also see them in Perfmon

I’ll stick with SQL Server.

If I want to alter a value, I call the appropriate procedure. I can do something like call the proc for 5 and set a value. I’ll then query the counters from T-SQL. You can see this below as the value for counter 5 is set to 3.

This value remains set. I’ll set counters 2 and 8 to 2, and then query again. Note that 5 is still set.

If I want the value set to 0, I need to set it. I’ll do that for counter 8.

These are metrics, so I need to pick an integer. I can’t set a decimal (or other type) and have it work. If the implicit conversion works, it works, but the value is a decimal.

If I make this a string, it fails with an error as ‘2.5’ doesn’t convert to an int. Same for a date. Using an int, like ‘5’, works.

Solving the Issue

In this case, for the customer, we solved the issue with a proc that performed their query The result of this query (and int) is sent to a counter value like this:

CREATE or alter proc My_Checker
AS   
BEGIN
    declare @i int
    select @i = count(*)
     from dbo.Customer a
     INNER JOIN dbo.Candidates b
     on a.CustomerName  = b.PersonName
     select @i = @i + 1
    EXEC dbo.sp_user_counter1 @1;
END  

This can run from an Agent job on their schedule, updating the counter as appropriate.

For their alerting, they can query this metric and set the boundaries that matter to them. In this case, whenever this is greater than 0, they want an alert.

The user settable values aren’t that useful, especially as there are 10, but I’ve used them in a few places when I wanted to get instrumentation for an application. This allows me to easily capture values and watch them from any monitoring system.

Worth knowing about and using if you need random things captured.

SQL New Blogger

This post took me around 15 minutes to write, though I spent about 10 minutes mocking this for our customer based on their system and then stripping out a few items that are specific to them.

This is a post that shows how I can use features of SQL Server to solve a problem, which is something every employer wants. An AI would make this code easier, but I have to know to guide an AI in this direction and evalaute if this works. I’d certainly need to check if other apps were using these counters, which isn’t something an AI might think to do, especially as there could be lots of repos to scan.

You can write something like this, showing how you’re use this features. Bonus points if you use an AI to help you (and disclose how).

While wandering around the documentation looking for some Question of the Day topics, I learned something new about the money data type. This post discusses what I learned.

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

The Money Type

Did you know that you can add a currency symbol to the money data type for assignment? I didn’t. This isn’t in the documentation, but it’s something I need to submit as a PR.

In any case, I can assign money like this:

DECLARE @YenAmount MONEY;
SET @YenAmount = ¥1500; 

SELECT @YenAmount AS RawValue; 

Note that this isn’t really assigning Yen values. It’s just a number, but since the money type supports certain literals, this works. If I select the amount, I get just a number.

If I change the symbol, it still works because SQL Server doesn’t really interpret the amount and symbol or the variable name. That being said, this is bad code.

The money and smallmoney data type page lists the symbols you can use, but none of them are stored. Where this page fails is that it doesn’t help you get the values back out as the currency.

Format helps here. I can use this with some culture to determine what I want to get out. For example, I get Yen with this:

 FORMAT(@YenAmount, 'c', 'ja-JP')

You can see the results here:

I can also get Pounds.

SQL New Blogger

This post took me about 5 minutes to assemble as I’d already had the code, but it’s an example of a quick thing based on other work I was doing.

You can showcase this and help others see that you are learning and growing.