Sql-Server

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

Have you heard someone recently say Drive Safe ? If you’re my age, I’m sure that grates on you somewhat. In the end, safe is an adjective, but here it’s being used as an adverb. If I’d said that at school, I would have been corrected and told it should have been Drive Safely.

Similarly, today I was watching the UK Lingo TV show . I really like it and generally, I hear quite correct English on it, particularly from the presenter. But I then heard him say He did brilliant. And immediately I had the same guttural reaction to it.

2026-08-22

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

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