The Bit Bucket

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

I’ve had a busy time reading books lately. Another one from my friends at PackT that looked interesting to me was Database Design and Modeling with PostgreSQL and MySQL: Build efficient and scalable databases for modern applications using open source databases by Alkin Tezuysal and Ibrar Ahmed.

PostgreSQL and MySQL

Database design and modeling is a passion of mine and, while most of my time in recent years has been spent on the SQL Server side of the fence, I’m always involved in PostgreSQL projects, and occasionally with MySQL. Each database has “a way of doing things” that’s considered best practices. MySQL has never been a database engine that I’m keen on, but with tools like WordPress, it’s always been part of some projects.

2024-10-02

Another book that my friends at PackT sent me for review recently was Microsoft Power BI Performance Best Practices by Thomas LeBlanc and Bhavik Merchant.

Over the years, I’ve come across Thomas many times but haven’t had the chance to spend time with him, unlike Bhavik who worked here in Australia quite a bit. They are a good combination to write this book.

Content - Downloads

I like the fact that samples of everything discussed in the book are available. I also like the idea that if you bought the printed book, that you can download a PDF as well.

2024-10-01