Sql-Server

Over the years, I’ve seen so many discussions regarding the certification process and exams. I’ve seen posts from many people that are very experienced with products saying they can’t see any point in the certification exams and also argue that competencies in the Microsoft Partner program shouldn’t be based on exams. They feel these people should somehow just be recognised for their other contributions.

Grandfathering

Regarding the certification process, I don’t agree that anyone should be just grandfathered in. Any of the people that have a great deal of knowledge and experience really should be able to just take the exams and be done with it.

2026-03-17

Something I really struggle with in this industry is when newcomers to a system want to change standards within existing systems because they think something else is better. It’s a sign of immaturity yet it often applies to people who should be senior. Many system architects fall into this category.

What’s in a PK name?

For example, a vendor system that I’ve been working with has single column primary keys in all tables, and all the primary key columns are named PKey_ID (I’ve changed it a bit to protect the guilty). Now I can’t say I like that naming at all, but that’s not the point. There are a large number of tables that already have that naming scheme.

2026-03-15

When writing and working with T-SQL in SQL Server, you’ll often come across the number 1033. In many cases, that will have the word English associated with it. But what are these numbers, and which ones does SQL Server know about?

The number 1033 comes from the reference to a Windows Locale ID. You’ll find the list of Windows Locale IDs here:

Microsoft Locale ID List

It’s a big list with entries that look like this:

2026-03-13

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

I’m not a great fan of linked servers in SQL Server but they are often necessary. If I’m working with the latest version of SQL Server, I really prefer to use External Data Sources and External Tables. But not everyone is on the latest version. In the meantime, what I see all the time, is people hardcoding server names like this:

SDUPROD2022.WWIDB.Payroll.Employees

That makes your code really hard to manage. One option to get around that is to use synonyms.

2026-03-09

Most SQL Server developers are aware that they can create computed columns. We do that by defining a column AS some expression like this:

CREATE TABLE Sales.OrderLines
(
    ...
    UnitPrice decimal(18, 2) NOT NULL,
    PickedQuantity decimal(18, 3) NOT NULL,
    LineTotal AS ROUND(UnitPrice * PickedQuantity, 2)
    ...
)

Each time the value from that LineTotal column is queried, the calculation is performed so the result can be returned. This makes sense when the value is changing regularly and the value is queried infrequently.

2026-03-07

While SQL Server’s plan cache generally is self-maintaining, poor application coding practices can cause the plan cache to become full of query plans that have only ever been used a single time and that are unlikely to ever be reused. We call this plan cache pollution.

Causes

The most common cause of these issues are programming libraries that send multiple variations of a single query. For example, imagine I have a query like:

2026-03-05

I’ve had a number of clients over the years who were wondering how the SQL dependencies in ASP.NET evolved, and what they were used for. I’ve updated a previous blog post about this below.

Traditional ASP Applications

In traditional ASP applications, every time the user would request a page, the application would retrieve the data from the database to render the page. Clearly this doesn’t scale very well. It works for a small number of users but not a large number. Often though, this data access can be completely avoided for certain types of data.

2026-03-03

I always say that one of the things that I love about consulting or mentoring work is that I see things (mostly code) that I would have never have thought of.

Sometimes, it’s good information where I learn a new technique that I hadn’t considered. But most times, it’s just something weird that a client has done.

GUID Table Names

A good example of this was a site where every table had a GUID name. Yes, I’m talking about tables with names like:

2026-03-01

Recently, I saw a subtle coding issue related to the UNION operator. With SQL Server, the UNION operator combines two rowsets into a single rowset. If UNION ALL is used then all rows are returned. With just UNION without the ALL, only distinct rows are returned. All good so far.

One of the most common performance issues that I come across is where people have just used UNION where they should have used UNION ALL. That extra distinct operation is often not needed, yet often very expensive.

2026-02-27