結果集比較的通用儲存過程

我團隊中的一些任務與儲存過程優化有關。我主要是一名 .NET 開發人員，但我想在更改過程時編寫一個通用的數據比較過程。主要目標是：

• 確保更改不會破壞現有功能（相同的輸入應提供相同的輸出）
• 可以不時執行以輕鬆發現優化引入的錯誤
• 還應該提供基本的分析資訊，以便“優化”版本實際上更快
• 應該從 SQL Server 執行

程式碼：

-- 
-- Description: compares the results returned by two stored procedures. Comparison is performed using a 'loopback' linked server and using openquery, so the final query
--              must obey openquery limitations. It returns all rows that are within the first result set and not within the second and viceversa. If all result sets are 
--              empty, results are equivalent (order does not matter)
--
-- PARAMS:
--      @Procedure1FullName: procedure 1 full name (i.e. database.schema.proc_name)
--      @Params1Str: procedure 1 params as string (e.g. @param1 = value1, @param2 = 'value2)'
--      @Procedure2FullName: procedure 2 full name
--      @Params2Str: procedure 2 params as string
--      @ResultSetStr: result set column specification (it is required for usage of procedure in SQL 2012+)
--      @LoopBackServerName: loopback (same server) linked server name - required to use openquery on the same server (and database)
--      @Debug: outputs debug info
--
-- =============================================
ALTER PROCEDURE [dbo].[uspCompareProcedureResults]
(
   @Procedure1FullName VARCHAR(255),
   @Params1Str VARCHAR(MAX),
   @Procedure2FullName VARCHAR(255),
   @Params2Str VARCHAR(MAX),
   @ResultSetStr VARCHAR(MAX),
   @LoopBackServerName VARCHAR(255) = 'loopback',
   @ForceShowDetails BIT = 0,
   @Debug BIT = 0
)
AS
BEGIN
   DECLARE @SQL NVARCHAR(MAX) = ''
   DECLARE @InputStr NVARCHAR(MAX)

   -- escaping string parameters
   SET @Params1Str = REPLACE(@Params1Str, '''', '''''')
   SET @Params2Str = REPLACE(@Params2Str, '''', '''''')

   SET @InputStr = @Procedure1FullName + '(' + @Params1Str + ')'

   SET @SQL = '
       DECLARE @StartTime datetime;
       DECLARE @Diff1 BIGINT;
       DECLARE @Diff2 BIGINT;

       -- executing and measuring time for the first procedure
       SET @StartTime = GETDATE();
       SELECT * INTO #R1
       FROM OPENQUERY(' + @LoopBackServerName + ', ''set fmtonly off exec ' + @Procedure1FullName + ' ' + @Params1Str + ' WITH RESULT SETS (( ' +  @ResultSetStr + '))'');
       SET @Diff1 = DATEDIFF(ms, @StartTime, GETDATE());

       -- executing and measuring time for the second procedure
       SET @StartTime = GETDATE();
       SELECT * INTO #R2
       FROM OPENQUERY(' + @LoopBackServerName + ', ''set fmtonly off exec ' + @Procedure2FullName + ' ' + @Params2Str + ' WITH RESULT SETS (( ' +  @ResultSetStr + '))'');
       SET @Diff2 = DATEDIFF(ms, @StartTime, GETDATE());

       -- changing all float columns to decimal to ensure correct comparison
       DECLARE @InnerSQL NVARCHAR(MAX) = N''''

       select @InnerSQL += ''alter table #R1 alter column '' + QUOTENAME(COLUMN_NAME) + '' DECIMAL(28, 6);''
       FROM tempdb.INFORMATION_SCHEMA.COLUMNS
       where table_name like ''#R1[___]%'' and DATA_TYPE = ''float'';

       EXEC (@InnerSQL);

       SET @InnerSQL = N'''';
       select @InnerSQL += ''alter table #R2 alter column '' + QUOTENAME(COLUMN_NAME) + '' DECIMAL(28, 6);''
       FROM tempdb.INFORMATION_SCHEMA.COLUMNS
       where table_name like ''#R2[___]%'' and DATA_TYPE = ''float'';

       EXEC (@InnerSQL);

       -- creating temporary tables to hold result sets differences
       SELECT ''R1 \ R2'' AS [R1 \ R2], * INTO #R12 
       FROM #R1 
       WHERE 1 = 0

       SELECT ''R2 \ R1'' AS [R2 \ R1], * INTO #R21
       FROM #R1 
       WHERE 1 = 0

       -- inserting data
       INSERT INTO #R12 
       SELECT ''R1 \ R2'' AS [R1 \ R2], * FROM #R1 
       EXCEPT
       SELECT ''R1 \ R2'' AS [R1 \ R2], * FROM #R2;

       INSERT INTO #R21
       SELECT ''R2 \ R1'' AS [R2 \ R1], * FROM #R2 
       EXCEPT
       SELECT ''R2 \ R1'' AS [R2 \ R1], * FROM #R1;

       -- difference flag
       DECLARE @IsDiff BIT = 0
       IF EXISTS (SELECT 1 FROM #R12) OR EXISTS (SELECT 1 FROM #R21)
           SET @IsDiff = 1

       SELECT ''' + @InputStr + ''' AS ''' + LEFT(@InputStr, 128) + ''', @IsDiff AS ''Diff results'', ''R1'' AS [R1], @Diff1 AS ''Duration1 [ms]'', @Diff2 AS ''Duration2 [ms]'';

       -- showing details if a difference exists or details must be output
       if (@IsDiff  = 1 OR ' + CAST(@ForceShowDetails AS VARCHAR) + ' = 1)
       BEGIN
           SELECT ''Results for first procedure'' AS ''Results for first procedure'', * FROM #R1;
           SELECT ''Results for second procedure'' AS ''Results from the second procedure'', * FROM #R2;
           SELECT * FROM #R12
           SELECT * FROM #R21
       END
   '

   if (@Debug = 1)
   BEGIN
       PRINT '@SQL = ' + @SQL
       PRINT 'SQL len = ' + CAST(LEN(@SQL) AS VARCHAR(MAX))
   END

   EXEC (@SQL)
END

呼叫範例：

declare @paramsStr VARCHAR(max) = '@year=2014,@month=6'
declare @resultSetStr VARCHAR(MAX) = 'kpi_id INT, kpi_value NUMERIC(18, 2)'
exec uspCompareProcedureResults 
   @Procedure1FullName = '[loopback].[DB].[usr].[get_data]', @Params1Str = @paramsStr, 
   @Procedure2FullName = '[loopback].[DB].[usr].[get_data_next_gen]', @Params2Str = @paramsStr, 
   @ResultSetStr = @resultSetStr, @ForceShowDetails = 0, @Debug = 1
GO 

限制/注意事項/已知問題：

• 需要連結伺服器指向同一個實例（由 使用openquery）
• 該過程必須只返回一個結果集
• 所有浮點數都轉換為小數（定點數）以避免微小的浮點數差異
• 該過程也可以適應在 SQL Server 2008 中執行（只需刪除 WITH RESULTS SETS）

該程序完成了它的工作，但我想知道：是否有更容易/更好的選擇來實現比較。

你應該看看 Red Gate 的工具集。他們有一個名為SQL Test的 SQL 單元測試工具。它可能會幫助您採用更全面的方法。

引用自：https://dba.stackexchange.com/questions/127742