Azure-Sql-Db

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

If you’ve been working with databases for any length of time, you will have come across implementations of Entity-Attribute-Value (EAV) tables (or non-tables as some of my friends would call them).

Instead of storing details of an entity as a standard relational table, rows are stored for each attribute.

For example, let’s create a table of people:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.StaffMembers;
GO

CREATE TABLE dbo.StaffMembers
(
    StaffMemberID int NOT NULL
        CONSTRAINT PK_dbo_StaffMembers PRIMARY KEY,
    FullName nvarchar(100) NOT NULL,
    HairColor nvarchar(20) NULL,
    StartDate date NULL,
    LoyaltyPoints int NULL
);
GO

INSERT dbo.StaffMembers
(
    StaffMemberID, FullName, HairColor, StartDate, LoyaltyPoints
)
VALUES
(1, N'Fred Nurk', N'Blonde', '20250705', 3),
(2, N'Siew Yu Hock', N'Black', '20250709', 2),
(3, N'Abhishek Newma', N'Brown', '20250802', 2);
GO

When we query it, all is as expected:

2026-09-19

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

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

For general index maintenance, our friend Old Hallengren has an awesome solution for most people: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

We started to have customers using clustered columnstore indexes from SQL Server 2014, and they needed to be treated differently. At the time, I checked Ola’s latest scripts to see what happens with columnstore indexes.

It appeared that the code ignored nonclustered columnstore indexes (ie: index type of 6), which made sense as we would potentially need to rebuild them whenever the data changes, and at the time, the table was read-only. So that made lots of sense. Fortunately, that limitation is now gone.

2026-06-21