Sql-Server

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

If you are aware of Betteridge’s law of headlines , you already know the answer, but let me explain.

There are a lot of odd myths that surround SQL Server. One of the more persistent ones is related to indexes on columns that hold bit values.

A SQL Server column that’s a bit can be in three states. It can have the value zero; it can have the value one; and it can be NULL (ie: have no value at all).

2026-09-11

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

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