SQL Management Studio strips stored procedure comments when connecting with Column Encryption Setting Enabled
When connecting to SQL Azure using the "Column Encryption Setting = Enabled" connection setting (in order to use Always Encrypted), any ALTER PROC statement strips comments and reformats the submitted SQL when saving the SP. When connecting to the same database WITHOUT that connection setting, stored procs can be saved as expected, including comments and user formatting.
This is a duplicate of https://feedback.azure.com/admin/v3/suggestions/32895916/
Please, upvote that UserVoice item.
Dana Stephenson commented
This ALSO happens when using the wizard to encrypt a column, if the wizard helpfully decides to touch a stored proc. I noticed this when I had a stored proc with a type mismatch (using a VARCHAR parameter to update an NVARCHAR column) and the wizard complained about it when encrypting the column... the warning was helpful but reformatting the stored proc is NOT.
This does not only affect stored procedures, but also views and so on. I agree that this is a complete desaster as it makes it impossible to comment your code completely
This problem is a disaster! It's completely unacceptable for a pre-processor to permanently strip out comments, newlines, and change the physical layout of code in stored procedures! It has been a major issue for us and has already led to unrecoverable loss of logic that was temporarily commented during diagnostics. It amazes me that this has somehow gone uncorrected for nearly a year now. FIX IT.