Sql-Server

Most people seem to be unaware that a CREATE SCHEMA statement can also include other DDL or DCL within it.

I’ve never been very keen on the option to add additional clauses in the CREATE SCHEMA statement as I’ve seen lots of issues when scripts are executed manually. You get a different outcome, depending upon how you execute it. For example, if you run statements in this script:

CREATE SCHEMA SomeSchema AUTHORIZATION Someone

CREATE TABLE Blah (Some table definition);

Where does the table Blah get created? Well, it depends.

2026-02-15

I had a discussion with a friend the other day who wouldn’t believe me that T-SQL allowed an UPDATE operation to be executed against a function in some situations. I must admit that when I first heard about it, it did my head in a bit as well, at least based on all I thought I knew about programming languages in general.

You can only perform an UPDATE against a table-valued function in T-SQL when the function was declared as an inline TVF. It still feels to me like it shouldn’t ever be permitted, but if you want to try it, here’s an example:

2026-02-13

It was great to catch up with Ben Weissman today and to have him on a SQL Down Under podcast.

Ben is a fellow Data Platform MVP.

Ben focusses on datawarehousing and analytics, still with a lot of on premises work but of course also Fabric / Azure work.

He has long experience with SQL Server, having started with SQL Server 6.5.

Ben says he loves to travel and loves food. He’s an author of books and video courses, and the organizer of data related events like Data Grillen, New Stars of Data, SQL Konferenz, and Data Saturday in both Vienna and Germany and other locations.

2026-02-11

One of the discussion lists that I participate in, had a brief discussion the other day about whether or not it’s possible to perform log shipping between differernt versions of SQL Server.

Specifically, can you do log shipping between SQL Server 2017 and SQL Server 2025?

Partial success

SQL Server does support restoring earlier version databases on later versions of the product. The databases get upgraded along the way when you perform restores of databases. SQL Server also allows you to restore transactions logs from earlier versions of the product but the upgrade doesn’t happen until recovery of the database occurs. And that’s why you can’t use STANDBY mode in this situation.

2026-02-09

One of my favorite DMFs that most people seem to be completely unaware of, is sys.dm_fts_parser(). It allows you to see the result of the word-breaking occurring within full text search. If you execute the query:

SELECT * 
FROM sys.dm_fts_parser('"Hello Greg. How are you? I haven''t seen you for a while."', 1033, 0, 0);

It returns the following data (along with some other columns):

That’s really impressive as it lets you parse text into words. The 1033 was the locale I chose (for US English) and the other two parameters were a stop word list (formerly called a noise word list) and whether or not it should be accent sensitive.

2026-02-03

Encryption in SQL Server is often discussed as a checklist item:

• Is the database encrypted?
• Do we need Always Encrypted?
• What does compliance require?

What gets discussed far less often is how these features behave in real systems, what problems they actually solve, and what trade-offs they introduce.

This post looks at Transparent Data Encryption (TDE) and Always Encrypted from a practical standpoint: what each one protects, what it doesn’t, and how to decide which one belongs in your design.

2026-02-01

The Service Master Key (SMK) is one of the most misunderstood security components in SQL Server.

It is frequently described as:

• The root of all encryption
• The key that protects everything
• Something you must back up constantly

Those statements are not entirely wrong - but they are incomplete, and in some cases actively misleading.

It’s important to understand what the Service Master Key actually does, when it matters, when it doesn’t, and why many operational decisions around it are based on myth rather than mechanics.

2026-01-30

One of SQL Server’s greatest strengths is its ability to execute queries in parallel. When parallelism is available, the engine can divide work across multiple CPU cores and dramatically reduce query duration for large or complex workloads.

Yet in real systems, it’s surprisingly common to find queries that could run in parallel but don’t. The reason is often not hardware, configuration, or cost threshold settings — but query anti-patterns that quietly force the optimizer into a serial plan.

2026-01-26

One of the more frustrating classes of SQL Server application failures is also one of the most avoidable: collation conflicts involving tempdb.

Background

I see these issues all the time. Developers create applications that require specific collations at the server level, because they don’t handle temporary tables (and by extension) tempdb well.

All this can be avoided, and you can easily build applications that will work with temporary tables, without worrying about the server (and tempdb) collation.

2026-01-26

This is the thirteenth tale in a series of stories about data. I hope you enjoy the series.

Today’s tale relates to a SQL Server system that had wonderful hardware, yet was unbelievably slow. Let’s discuss what happened.

Some background information

I had a call from the Microsoft office in Brisbane, Queensland, asking if I had time to look at a problem at a customer site. Both they, and the customer, had thrown considerable resources at trying to resolve a performance problem, but frustratingly, it persisted.

2026-01-24