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

paint splatter on glass panel

Is it true that editing a single row in Management Studio empties and reloads the entire table?

TL;DR: No. A customer recently brought up an interesting thesis, that if you edit a table’s values using SQL Server Management Studio (SSMS) using the edit feature, that the table is dropped and recreated in the background when you commit the changes. This is false, but there had to be a good reason why they
-> Continue reading Is it true that editing a single row in Management Studio empties and reloads the entire table?

close-up photo of audio mixer with red dim light

Installing SQL Server on Windows? Don’t forget these two useful switches

A few days ago on Twitter I wrote: Couldn’t connect to new SQL Server install because I forgot to enable TCP/IP. I’m the lead author for a Microsoft Press book about SQL Server administration. Aside from demonstrating that checklists are helpful, it did raise an interesting point about why this isn’t a configurable option in
-> Continue reading Installing SQL Server on Windows? Don’t forget these two useful switches

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

A steel padlock

When security and news collide

Behold! There’s a scary monster called skip‑2.0, announced by ESET: This backdoor targets MSSQL Server 11 and 12, allowing the attacker to connect stealthily to any MSSQL account by using a magic password – while automatically hiding these connections from the logs. Such a backdoor could allow an attacker to stealthily copy, modify or delete
-> Continue reading When security and news collide

Speaking at SQL Saturday Oregon

SQL Saturday Oregon — in the city of Portland — is taking place next weekend on Saturday November 2nd, 2019, and I’ll be presenting a revised edition of my session “Back to the Future with Temporal Tables.” It’s the Saturday before PASS Summit 2019 which is once again in Seattle. I’m giving two sessions there
-> Continue reading Speaking at SQL Saturday Oregon

Green rope meshwork

Using indexed views? What is an imprecise or non-deterministic convert?

After last week’s post about using WITH (NOEXPAND) to query indexed views even on SQL Server Enterprise Edition, this week is a short but interesting side-road into deterministic values, and why it is important to get your data types correct. Longtime readers will know I care deeply about data types. Here is a T-SQL query
-> Continue reading Using indexed views? What is an imprecise or non-deterministic convert?

A filing cabinet drawer containing index cards

Using indexed views? Don’t forget this important tip.

Today after fighting with designing a view that let me create a clustered index on it (indexed views — also known as materialized views — are awesome in the right context!), I kept running into the same problem whenever I clicked Display Estimated Execution Plan in SQL Server Management Studio. The problem was that the plan was doing
-> Continue reading Using indexed views? Don’t forget this important tip.