Business-Intelligence

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

SQL Server databases have a reputation for being hard to test, or at least hard to test appropriately.

For good testing, and particularly for unit tests, you really want the following:

• Database in a known state before each test
• Database containing large amounts of (preferably masked) data (production-sized)
• Quick restore after each test before the next test

For most databases, this is hard to achieve. The restore after each test means that a normal database restore can’t be used. What I often see instead, is people using transactions to try to achieve this i.e. the process becomes:

2026-08-18

I work with a lot of SQL Server databases that are poorly normalized. One of my pet dislikes is the column to rule them all.

Here are simple tests:

• If I ask you what’s stored in a column and you can’t tell me a single answer, then you’ve got a problem.
• If you need to refer to another column to work out what’s in the first column, then you’ve got a problem.

Here are some examples:

2026-07-27

Intrusions into computer systems are happening all the time now. We need to address this issue as an industry, but it’s important to understand that the way we design databases plays a big role in the impacts that occur during intrusions.

If you don’t accept that you could have an intrusion, you are living in La La Land. (See https://en.wikipedia.org/wiki/Fantasy_prone_personality )

A bug in any one of the frameworks that you use, the code that you write, the protocols that you use, the operating system or hosting services that you use can potentially expose you to an intrusion.

2026-07-25