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 the programming language framework supports it.
How is this relevant to XML data types in SQL Server?
The XML data type in SQL Server is mutable, because SQL Server does not store XML data as a string. Through a series of algorithms, the data is transformed so that it makes best use of the data type (and generally uses less storage than the original string).
How does this affect me?
Auditing, mainly. Because the data will be transformed as soon as it is persisted to that column, it means we cannot recreate the exact representation of that XML document when we read it out again. If we want to keep an immutable record (of web service calls for example), we should store that XML as NVARCHAR(MAX), or VARCHAR(MAX) using one of the new UTF-8 collations introduced in SQL Server 2019 and Azure SQL Database compatibility level 150.
Share your thoughts in the comments below.
Photo by Jeremy Thomas on Unsplash.