Tag: T-SQL

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.

While writing another post I realized my UNION query didn’t work as one might initiall expect, so I decided a short post was worth writing. This is based on a previous post on QUOTENME().

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

Missing a Row

When I ran this code, I got only a single row. There’s a UNION here, so why? One would expect two rows from these queries.

Let’s change to UNION ALL. Now we see this:

You can likely spot the reason, but it’s because both rows in the result are the same. In this cse, UNION is designed to remove duplicates. In the docs, it explicitly says

• UNION ALL – Includes duplicates
• UNION Excludes duplicates

We can see this in this examples I’ve got this code that gives me two virtual tables of numbers, some of which are duplicate:

WITH myTally(n)
AS
(SELECT n 
 FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
)
, myTally2(n)
AS
(SELECT n 
 FROM (VALUES (1), (20), (3), (40), (5), (60), (7), (08), (9), (100)) b(n)
)
SELECT n
FROM myTally
UNION 
SELECT n
 FROM myTally2

When I run the query, with UNION, I see these results, 14 rows:

If I change to UNION ALL, 20 results.

Use UNION when you want unique things. UNION ALL if you need to see ALL The Rows.

SQL New Blogger

This post was about 8 minutes spent after I finished the other post. It is a quick expansion on something I saw in another post, it has a separate focus, and it shows I’ve realized something and built on previous work.

You can showcase these skills.