Business-Intelligence

A while back, I wrote about the ANSI_NULLS option for tables and after that I had several more questions related to NULL values.

Note that I mentioned NULL values, not values of NULL, and not values equal to NULL. That’s because being NULL is a state that a value is in, not itself a value. That’s why our queries say IS NULL and not = NULL.

Magic values

Now because of that, many developers see values that are NULL as a pain in the neck as they have to be dealt with separately, unlike other values. This can lead to many poor design decisions but I want to start today with the decision to use magic values.

2026-10-25

Today, I was editing some SQL code using the SQL editor in a Microsoft Fabric Warehouse.

I needed to reorder a list of columns.

I find that a pretty frustrating editor but usually I can avoid it by connecting with SSMS. But for some reason, SSMS decided it didn’t want to connect to the warehouse today either.

No problems, I thought I’ll just edit the code with a combination of SSMS, and Excel (to reorder the column names), and then paste the code back into the Fabric SQL editor. But every time I did that, I ended up with a bunch of strange orange boxes all throughout the code.

2026-10-23

One question that I’m asked all the time when consulting is whether reducing the size of database tables will make queries run faster or not.

The underlying question is typically about whether the client should implement some sort of archiving strategy, to reduce the amount of data in a table by moving older data off into another table.

My answer is that it might help, but if it does, you probably have another issue that would be a better one to solve instead.

2026-09-29

In an earlier post , I discussed the design of EAV (Entity Attribute Value) tables, and looked at why they get used. I’d like to spend a few moments now looking at the pros and cons of these designs.

Let’s use the same table as the last time as an example:

Pros

The main positive that’s typically described is that the schema is flexible. By this, the developers usually mean I don’t have to change the database schema (or worse, have someone else change it) when my needs change.

2026-09-21

Many database developers (and other developers) seem to regard the endless use of abbreviations as some badge of honor. Don’t be one of these people.

Avoid abbreviations almost all the time.

I’ve written before about my dislike for the EOMONTH T-SQL statement. Given the same version introduced names like DATETIMEOFFSETFROMPARTS, surely we didn’t have to save 3 characters and could have had ENDOFMONTH. (I heard it was in fact named this way to match the Excel function. I hope that’s not true. Matching something from another language that was created a long time ago isn’t the right answer here).

2026-09-17

When designing databases, one question that comes up all the time is how large columns should be.

Numbers

For numbers, the answer is always big enough but not too big. This week I’ve been working at a site where the client numbers were stored in int columns. Given the clients are Australians and the Australian Bureau of Statistics Population Clock says there are just under 25 million of us, an integer seems a pretty safe bet, given it can store positive numbers up over two billion. It’s hard to imagine that number being exceeded, but I’ve seen people deciding that it needs to be a bigint. I doubt that. Even if we count all our furry friends, we aren’t going to get to that requirement.

2026-09-15

Was 2018 a leap year?

What about the year 2000?

Will 2100 be a leap year?

When I was a young student at school, we learned that leap years were every four years. Then as I got older, I learned that there was more to it than that.

It’s important to understand that any calendar is just an approximation, and there is a lot more to calendars than most people realize.

2026-09-09

I’ve never liked how the LEN function works in T-SQL for SQL Server.

To test out what others thought, I recently posted a short quiz on my social networks. Here’s the question:

What would you expect the output from this query to be?

DECLARE @Value1 varchar(10) = 'Hello   ';
DECLARE @Value2 varchar(10) = 'There ';

SELECT LEN(@Value1), LEN(@Value2), LEN(@Value1 + @Value2);

Nothing tricky. The first value has 3 trailing spaces. The second value has one.

Then I gave the following answer options:

2026-09-03

I see so much written about how to scale SQL Server systems, and this generally starts with needing to improve SQL Server database performance. When I read articles from the SQL Server field support teams with titles like Top 10 Performance Problems for SQL Server, I often just smile.

The problem is one of perspective. If you are looking at the performance problems that are brought to the support teams to solve, you get a very, very skewed view of what’s typical.

2026-08-26

Way back in SQL Server 2012, a whole raft of new T-SQL functions were introduced. As I’ve mentioned before, I get excited when there are new T-SQL functions.

Some of the ones added back then, I wasn’t so excited about. EOMONTH was in that category, not because of the functionality, but because of the name (wish it was ENDOFMONTH or better, END_OF_MONTH), and lack of symmetry (lack of a STARTOFMONTH or BEGINNINGOFMONTH).

2026-08-24