Synapse SQL Dedidcated Pools (aka SQLDW) does not support comments in views or procs in the same was as standalone SQL. This is annoying as comments can be very useful with tracking changes and lineage of objects, especially when used in conjunction with Schema Compare tools in Visual Studio.
Everyone has seen that incident where somehow PRD has a different View than DEV, and comments help us see why that is the case and if its a forward port neded to fix or back port due to some naughty developer or some panic Hot Fix direct to PRD.
Comments for Views
In SQLDW, you can add comments into a statement, so what we have started doing at Prodata is adding comments after the first WORD of the statement on View
For example here is a handy view which is work in progress on my attempt at request aggregation for sp_whoIsActive style analysis of queries in SQLDW.
CREATE VIEW [dbo].[Requests] AS WITH /* Description: Return Data for SQLDW Requets, Steps, Operations in summary from used By: sp_WhoIsActive, sp_WhoWasActive History: 11/04/2021 Bob, Created for Perf Tuning */ requests as ( select row_number() over (order by r.request_id desc ) as [#] , r.request_id, r.session_id, r.command , r.submit_time, r.start_time, r.total_elapsed_time , r.status , r.resource_class , getdate() as collection_time , s.login_name , s.app_name , s.client_id , s.sql_spid from sys.dm_pdw_exec_requests r INNER JOIN sys.dm_pdw_exec_sessions s on s.session_id=r.session_id WHERE r.session_id <> SESSION_ID ( ) AND app_name <> 'Microsoft SQL Server Management Studio' ), steps as ( select request_id, operation_type , total_elapsed_time, SUBSTRING(command,1,(CHARINDEX(' ',command + ' ')-1)) as command From sys.dm_pdw_request_steps WHERE location_type='Compute' ) select TOP 5000 r.[#], r.request_id, r.start_time, r.status, r.total_elapsed_time as request_time , m.total_elapsed_time as step_time , r.command , m.steps, op.op_commands, m.operations , r.session_id , r.submit_time , r.resource_class , r.collection_time , r.login_name , r.app_name , r.client_id , r.sql_spid from requests r LEFT JOIN ( SELECT request_id, string_agg(command,',') WITHIN GROUP (ORDER BY total_elapsed_time DESC) as op_commands FROM ( SELECT request_id, command + '(' + convert(varchar,count(*)) + ':' + convert(varchar,sum(total_elapsed_time)) +'ms)' as command , sum(total_elapsed_time) as total_elapsed_time FROM STEPS WHERE operation_type='OnOperation' group by request_id,command ) op1 GROUP BY request_id ) op on op.request_id= r.request_id LEFT JOIN ( SELECT request_id, string_agg(operation,',') WITHIN GROUP (ORDER BY total_elapsed_time DESC) as operations, count(*) as steps, sum(total_elapsed_time) as total_elapsed_time FROM ( SELECT request_id, replace(operation_type,'Operation','') + '(' + convert(varchar,count(*)) + ':' + convert(varchar,sum(total_elapsed_time)) +'ms)' as operation, sum(total_elapsed_time) as total_elapsed_time, count(*) as steps FROM STEPS group by request_id,operation_type ) m1 GROUP BY request_id ) m on m.request_id= r.request_id order by r.submit_time desc; GO
Comments for SProcs
Stored Procedures are different in that you can add the comment after the AS, but not before the ALTER/CREATE command like in standalone SQL
ALTER PROC [dbo].[sp_WhoWasActive] AS /* Desc: Return Running Statements History: 29/03/2021 Bob, Created */ BEGIN SET NOCOUNT ON; SELECT TOP 500 * FROM Requests ORDER BY submit_time desc END
Of course, like Brent Ozar recently blogged, never ever ever use the double line flavour of comments. This is true on all flavours of SQL.