Transaction and Column Encryption Setting Warning

Why pay attention to the Column Encryption Setting = ON warning?

When Always Encrypted is enabled (Column Encryption Setting = ON), the SSMS may use parameterization to support writes to encrypted columns (if you have it enabled). To do that, it may change your SQL text before sending it to SQL Server.

For one-off, adhoc queries this usually does not matter. But for stored procedures, views, functions, triggers, etc., it can be a problem because you may end up deploying a rewritten version of your code, not the exact text you authored. That can include changes like comments removed and formatting rewritten using default rules.

In practice: avoid deploying database objects while Always Encrypted is enabled. Turn it off for deployments, then re-enable it for runtime operations that need it.

Example: This is the procedure definition I ran while Column Encryption Setting = ON:

CREATE PROCEDURE test
AS
/* test code */
SELECT DISTINCT vs.volume_mount_point,
                vs.file_system_type,
                vs.logical_volume_name,
                CONVERT (DECIMAL (18, 2), vs.total_bytes / 1024.0 / 1024 / 1024) AS [Total Size (GB)],
                CONVERT (DECIMAL (18, 2), vs.available_bytes / 1024.0 / 1024 / 1024) AS [Available Size (GB)],
                CONVERT (DECIMAL (18, 2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %]
FROM sys.master_files AS f WITH (NOLOCK)
     CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
ORDER BY vs.volume_mount_point
OPTION (RECOMPILE);

The sproc definition I see when I script it out back from the database - comments are gone and query has been reformatted with default settings.

CREATE PROCEDURE [dbo].[test]
AS
SELECT   DISTINCT vs.volume_mount_point,
                  vs.file_system_type,
                  vs.logical_volume_name,
                  CONVERT (DECIMAL (18, 2), vs.total_bytes / 1024.0 / 1024 / 1024) AS [Total Size (GB)],
                  CONVERT (DECIMAL (18, 2), vs.available_bytes / 1024.0 / 1024 / 1024) AS [Available Size (GB)],
                  CONVERT (DECIMAL (18, 2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %]
FROM     sys.master_files AS f WITH (NOLOCK) CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
ORDER BY vs.volume_mount_point
OPTION (RECOMPILE);

I shared practical experience working with Always Encrypted at PASS 2022.