UKRAINE. Kiev. 2019. Kiev Children's Railway. Museum

Fun and profit with UTF-8

When SQL Server 2019 was announced, it brought with it UTF-8 support. Also available in Azure SQL Database, UTF-8 is exciting if you have a legacy database and application that needs to support Unicode strings for globalization, but you can’t afford to double the storage requirement by converting all your CHAR and VARCHAR columns to
-> Continue reading Fun and profit with UTF-8

turned off vintage black television

Deprecated and discontinued features in SQL Server

My co-authors and I recently wrapped up the book SQL Server 2019 Administration Inside Out, which should be hitting the shelves in the next week or two. At the end of Chapter 1 I have a small note about deprecated and discontinued features, and this post expands on that section. The following statements are true:
-> Continue reading Deprecated and discontinued features in SQL Server

flat lay photography of purple and red leaves

The XML data type is not immutable

Immutability In many programming languages, strings of text are immutable, meaning they don’t change. When you modify a string, a new string is created in memory by copying the original. The old string stays in memory unless some process removes it. This might be a manual process of de-allocating that memory, or garbage collection if
-> Continue reading The XML data type is not immutable

Person using laptop

Did you know we have another SQL Server book coming out?

SQL Server 2017 Administration Inside Out was the first technical book I contributed to, and all its authors were very happy with how it turned out. All the content was written from scratch, which made it quite challenging to manage (it originally started as a book about SQL Server 2016, and then Microsoft surprised everyone
-> Continue reading Did you know we have another SQL Server book coming out?

Detail of a building in Ottawa, Canada

SQL Server 2019 is here

With the release of SQL Server 2019, I wanted to highlight in a single place some things that I’m excited about. Drawing on sessions I presented this year at SQLBits and SQL Saturday Edmonton respectively, these are features in SQL Server 2019 for the busy DBA: UTF-8 in-engine support Intelligent query processing features Replacing sqlcmd
-> Continue reading SQL Server 2019 is here

SQL Server 2019 on Docker no longer runs as root by default

In my home lab I have an Ubuntu virtual machine that runs both SQL Server 2017 and SQL Server 2019 in Docker containers.

After SQL Server 2019 Release Candidate 1 was released, when I performed my usual migration to get the latest version, I noticed that the command line for the SQL Server instance was different.

I used sudo docker ps -a --no-trunc to see the full command, which is emulated below (note: this output is heavily abbreviated).

[…]

Better SQL Server memory defaults in 2019

In 2016 I created the Max Server Memory Matrix as a guide for configuring the maximum amount of memory that should be assigned to SQL Server, using an algorithm developed by Jonathan Kehayias. SQL Server 2019 is still in preview as I write this, but I wanted to point out a new feature that Microsoft has
-> Continue reading Better SQL Server memory defaults in 2019

Better SQL Server CPU defaults in 2019

SQL Server 2019 is still in preview as I write this, but I wanted to point out a new feature that Microsoft has added to SQL Server Setup, on the Windows version. On the Database Engine Configuration screen are two new tabs, called MaxDOP and Memory. These are both new configuration options for SQL Server
-> Continue reading Better SQL Server CPU defaults in 2019

The easy way to handle UTF-8 in a .NET application when dealing with SQL Server 2019

A short post this week. On a mailing list recently, someone noticed that a .NET application writing to SQL Server did not have the expected behaviour with UTF-8 collation and data types. To refresh our memories, UTF-8 is newly supported in SQL Server 2019, and provides potential savings of up to 50% when storing strings,
-> Continue reading The easy way to handle UTF-8 in a .NET application when dealing with SQL Server 2019

The case of scalar UDF inlining, where context is everything

Here’s an interesting story for you this week. As part of the new Intelligent Query Processing improvements introduced in the upcoming SQL Server 2019, we find a new feature called scalar UDF inlining. This post is not about scalar UDF inlining exactly, but IQP-adjacent if you like. It works by taking a typical scalar user-defined
-> Continue reading The case of scalar UDF inlining, where context is everything