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.
https://www.brentozar.com/archive/2021/04/never-ever-ever-start-t-sql-comments-with-two-dashes/
