Sql-Server

Many years back, I was invited to chair a course accreditation panel for a local TAFE (Technical and Further Education) course. They had started to offer a computing-related 3-year diploma, and the hope was that it wasn’t too far below the 3-year degrees offered at local universities. One part of that accreditation process involved me discussing the course with the staff members who were teaching it.

After talking to almost all the staff, what struck me was how similar they all were. In the requirements for the course, there was a standard that each staff member needed to meet, but there was also a requirement for the group of staff to be diverse enough to have broad knowledge of the industry. There was no individual staff member that you could identify as not being at the appropriate standard, but almost all of them had exactly the same background, career progression, etc.

2026-08-16

Here’s a quick T-SQL test for you.

Without looking below to see the answer first, try to guess what each of these statements will produce as output:

SELECT TRY_CAST('' AS int);
SELECT TRY_CAST('    ' AS int);
SELECT TRY_CAST('' AS date);
SELECT TRY_CAST('' AS decimal(18, 2));
SELECT TRY_CONVERT(date, '', 103);

And to slightly distract you from checking out the answers yet, here is another wise-looking owl who is thinking about the answers, and warning you not to look further down the page yet:

2026-08-12

Had a client today asking about this error message. They were working away on a machine and suddenly they got the message Failed to update the database because the database is read-only.

• The user hadn’t changed anything that they were aware of.
• Based on the user’s permissions (ie: what they could see), everything in SSMS looked normal.
• When they checked the sys.databases view, the database showed MULTI_USER.
• There was enough disk space.
• Folder permissions had not changed.
• The user was puzzled.

The issue was caused by the database being part of an availability group, and the AG had failed over. So suddenly, the database the user was connected to, was now a read-only replica, not the primary replica. That’s why the database said it was read-only.

2026-08-10

I recently posted about rebuilding clustered columnstore indexes when they needed maintenance . One of the questions that a reader asked was:

Which is best in performance and CCI packing for fast query:

• Truncate table, drop CCI, insert data, create CCI.
• Truncate table, insert data and therefore keep CCI.

And of course, that’s a great question. Here are the issues:

If I had to just pick one of those, for best query performance and best CCI packing, the first option is usually better:

2026-08-06

There is an endless discussion in the development community about whether databases should include declared foreign key constraints or not.

As a consultant, I regularly visit a wide variety of client sites. Many of these have applications designed without constraints. When I ask why there are no constraints, the response is invariably one of the following:

• We don’t need them because the app ensures that it’s right
• They don’t work well with our application development
• They are too slow
• What’s a foreign key constraint?

Most of the sites that I work with have sizeable databases. This discussion is important in those situations.

2026-08-04

Database mirroring was added way in SQL Server 2005. One of the features added to it later in SQL Server 2008 was auto page repair. When SQL Server 2012 was released, Availability Groups also offered auto page repair.

Just how useful is this feature though ?

I’ll start by saying that it can’t hurt.

When SQL Server is reading a page on the primary replica and receives an unrecoverable I/O error (typically but not always, an error 823 for a checksum error), it will try to repair the page when:

2026-07-29

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

If I want accounting advice, it’s unlikely that I’d ask my dentist for that advice.

Accountants and IT

Many years ago, I created applications for food wholesalers. When the owners of these businesses decided to get a new or better computing system, invariably they’d speak to their accountants. I understand the reasons why that might seem logical to them at first, but what I saw when these clients did this, is that they invariably ended up with the wrong systems.

2026-07-19

Just after I was born, my mother and father called me Gregory. Ever since then, everyone has called me Greg. And that included my parents. To the day she died, my mother called me Greg and so did my dad while he was alive (miss you both).

I have friends who tell me that the only time their mothers used their full names was when they were in trouble for some reason.

2026-07-17