Business-Intelligence

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

I’ve pointed out in previous blog posts that I’m not a fan of ORMs. What I’m even less of a fan of is code-first design, particularly in combination with an ORM like the Entity Framework.

It might seem cool and shiny and if you are just whipping up a small proof of concept or test app, please feel free to do it, but the minute you think of doing it for enterprise systems, please just don’t. And if you have colleagues wanting to do this, just say no.

2026-07-15

I work with data. I understand that for most organizations, that the data they own is the most valuable asset the company owns.

One thing I’ve learned from working with data is that unlike application software, data generally outlives generations of applications, is often used by many different applications, and typically just morphs from shape to shape over time. It almost never gets totally refreshed.

This is a good thing.

2026-07-09

I spend most of my life in a variety of customer sites. Most of these nowadays tend to be large financial organizations. When I talk to developers in those organizations, and ask them about how they interact with their DBAs, I can’t tell you how often I hear that they try to avoid ever making schema changes, etc. as it takes too long and the process is painful.

When I talk to the DBAs at the same organizations, and I ask them why they resist making schema changes, I hear this:

2026-07-05

Day by day, we’re getting further away from needing to use 32-bit DLLs. One place where I keep coming across them, is when doing integration work. The problem is that there are many 32-bit drivers for older systems, and no-one is going to ever bother to write 64-bit drivers for them. So, if you have to use those drivers, you need to run the 32-bit versions. And SSIS has options that allow you to run 32-bit drivers.

2026-06-29

The on-premises versions of SQL Server have the ability to connect one server to another via a mechanism called Linked Servers.

Azure-based SQL Server databases can communicate with each other by a mechanism called External Tables. I’ll write more about External Tables again soon.

Common performance issue with linked servers

With Linked Servers, I often hear people describing performance problems. There’s a configuration setting that commonly causes this, yet it seems to be almost unknown.

2026-03-11