The Bit Bucket

As well as the variables that you declare, SQL Server has a number of built-in system variables and some built-in system functions.

The system variables are the ones with @@ at the start of their name. Here is an example that’s often used:

SELECT @@VERSION;

It is documented here.

On my SQL Server 2022 system, the value returned is this:

Now while that is a reasonable example of a system variable that has been used for a long time, note that it’s a bit messy as there is a lot of information in a single returned value. There are now better ways to get at the system version in a more useful way:

2025-01-25

I recently watched a YouTube video with Ali Abdaal that talked about productivity. Not sure what it was exactly, but I really liked listening to Ali. So I was pleased to find that his book Feel-Good Productivity was available on Audible.

I really liked this book. I found Ali great to listen to and compelling in his story. It’s another one where I really also appreciate that he narrated the book himself.

2025-01-24

Variables in T-SQL work very much the way they do in other languages. A variable really is nothing more than a name given to a memory location. In T-SQL, variable names must start with an @ sign. An example would be:

@CustomerName

As well as defining a name, variables have other properties.

The first is that we have a data type. It defines the types of things, like strings or numbers and so on, that can we store in that location.

2025-01-24

If ever there was a blog post that I didn’t ever expect to be writing it’s this one.

For such a long time, Notepad has been such a simple and stable application. That no longer seems to be the case.

Symptoms

Recently I had a situation where Notepad just didn’t work properly. I saw these things:

If I double-clicked a .txt file, it no longer opened. What would happen instead, is that Notepad started, then reported that it could not find the file that I’d just double-clicked.
In Windows Explorer, wherever I expected to see a text file icon like the one in the image above, what I saw was a simple rectangle (white with a black border).
If I opened Notepad first, then used File>Open to go to the same files, they opened ok.

It was all rather frustrating.

2025-01-17

I’m a long-term fan of Annabel Crab and her work. So I was pleased to get a chance to listen to a book she wrote a while back, called The Wife Drought: Why Women Need Wives and Men Need Lives. It didn’t disappoint.

There was an old adage that behind every successful man, there was an awesome woman. That’s a view that needs updated wording, but it’s true that the people who really get ahead in the world, have a supportive partner who fits the role that wives traditionally did. And still today, that’s mostly still wives in that role not husbands, even though that proportion is slowly changing.

2025-01-08

A while back, I received another book from my friends at PackT. It was A Developer’s Guide to Cloud Apps Using Microsoft Azure by Hamida Rebai Trabelsi. I have been meaning to get a chance to read it, and I finally did.

I don’t know Hamida. I think I need to add her to my LinkedIn contacts though, as she clearly has similar interests in many areas. Hamida is a fellow MVP from Tunisia and currently working in Canada. Her MVP award was for developer technologies.

2024-12-16

Another Fabric Down Under podcast is out the door.

This time, the guest was Devang Shah.

Devang is a Principal Program Manager at Microsoft with over 16 years of experience in cloud technology, customer success, and business development. He’s led multi-million-dollar Azure deals, engaged with strategic customers, and orchestrated teams of cloud solution architects, solution sellers, and technology strategists across multiple regions and cultures.

Devang’s core competencies include creating clarity, simplifying complex patterns, creating mutually beneficial environments, and delivering value to customers, partners, and teams. He’s passionate about helping enterprises benefit from digital transformation through adoption of cloud, IoT, and AI.

2024-11-23

I work with a lot of clients that are creating analytic systems. They all collect large numbers of data points and analyze them. Most do a fairly good job with it.

One area that many also handle well is detecting anomalies i.e. data that is out of the ordinary.

But another area that I see very few handling well, is the data that is missing, rather than just the data that is present. There’s a huge difference between data that arrived, and is odd, and data that just didn’t arrive at all.

2024-11-18

One of the most popular courses that we used to run in person was a Query Performance Tuning and Advanced T-SQL course. I recently finished converting the Advanced T-SQL course to an online format, but a key (no pun intended) part of the Query Performance Tuning course was the content on reading SQL Server execution plans.

For so long, I’ve wanted to get the execution plan content available online, as reading them is such an important skill. Well, we finished it today ! The new course is:

2024-10-30

One of our popular courses is Advanced T-SQL for Developers and DBAs.

If you’re still writing T-SQL like it’s SQL Server 2000, or even SQL Server 2016, it’s time to look at what’s changed over the years.

We’ve just pushed out substantial updates to that course. The new content areas pushed out in this update are:

New Module 1 Content: (Using Common Data Types Effectively)

UNISTR - embedding Unicode within strings
ANSI string concatenation with ||
Extensions to TRIM, LTRIM, and RTRIM, particularly for removing characters apart from spaces
New options for CURRENT_DATE
Truncating dates with DATETRUNC
Grouping periods of dates with DATE_BUCKET

New Module 2 Content: (Using Special Data Types)

Creating tables of values with GENERATE_SERIES
Optimizing optional parameters by using IS [NOT] DISTINCT FROM
Working with bits (shifting left, right, counting, and setting and getting bits)

New Module 7 Content: (Ranking, Pivoting, and Grouping Data)

Using APPROXIMATE_PERCENTAGE (discrete and continuous) to enhance performance and reduce memory usage
Using GREATEST and LEAST to reduce query complexity

New Module 8 Content: (Using TOP, APPLY, and Window Functions)

Using named windows to simplify queries
New NULL treatment options for window clauses

New Module 10 Content: (Working with JSON Data)

New options for storing data by using the json data type
Enhanced options for OPENJSON
Using ISJSON to check JSON formats, including checking for VALUE, ARRAY, OBJECT, and SCALAR types
JSON_PATH_EXISTS helps when working with nullable data
Using JSON constructors to create objects and arrays
Using the new JSON aggregates

We’re really pleased with this update.

2024-10-09