Installing without CLR for Azure Synapse Serverless

[dvasdekis]

Serverless Synapse currently doesn't support CLR. Running the below:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

I get:
The specified option 'clr enabled' is not supported by this edition of SQL Server and cannot be changed using sp_configure.

Is there a way to benefit from this framework without CLR? I only need AssertEqualsTable for my use case.

[dvasdekis]

After some trial and error, I was able to create a 'unit test-like' construct on Azure Synapse Serverless with the below function.

It will only check what an EXCEPT statement will check - not sure of the limitations of such a statement, but it does complain if rows are different and/or datatypes are different. Additionally, you have to define your two result sets for comparison as 2 different views.

-- Stored proc that tests to see if results are the same
CREATE OR ALTER PROCEDURE [mart].[sp_results_eq]
  @schema1 NVARCHAR(500),
  @viewname1 NVARCHAR(500),
  @schema2 NVARCHAR(500),
  @viewname2 NVARCHAR(500)
  AS
  BEGIN
  SET NOCOUNT ON;
  	-- This proc takes the results from view 1, and compares it to view 2. If they don't match, it raises an error.
	-- We can use this in DevOps Pipelines to enforce checks on query results.
    
	DECLARE @sql NVARCHAR(1000);  -- SQL string to run
	DECLARE @rows_out bigint;    -- Rows returned from SQL result
	DECLARE @errortext NVARCHAR(500);  -- Error text to display when our results don't match

	-- Create a sql statement to compare results with 
    SET @sql = N'SELECT @rownums = count(*) FROM ((select * from ['+ @schema1 +'].['+ @viewname1 +'] EXCEPT select * from [' + @schema2 + '].[' + @viewname2 + ']) UNION ALL (select * from [' + @schema2 + '].[' + @viewname2 + '] EXCEPT select * from [' + @schema1 +'].[' + @viewname1 + '])) sq';

	-- Draft our error text
	SET @errortext = N'Error! ' + @schema1+'.'+@viewname1 + ' does not match ' +@schema2+'.'+@viewname2
	-- here, execute the dynamic SQL and produce rows_out
	EXEC sp_executesql @SQL, N'@rownums bigint OUTPUT', @rownums=@rows_out OUTPUT;

	IF (@rows_out > 0)
	BEGIN
	RAISERROR (@errortext, -- Message text.  
               18, -- Severity - sufficient to error out 
               1 -- State. 
               );  
	END
END
GO

To use this function:

EXEC [mart].[sp_results_eq] @schema1 = 'temp', @viewname1 = 'dv_set1', @schema2 = 'temp', @viewname2 = 'dv_set2';  

[mbt1]

At the current time, tSQLt will not work without CLR functionality. Microsoft has approached us to work on resolving this, but it requires changes in SQL Server, so this likely will be several years in the future.