Compare Difference In T-SQL Queries at Runtime
- Run the following SQL in your target database
EXEC sp_configure 'clr enabled', 1; RECONFIGURE; EXEC sp_configure 'show advanced options', 1 RECONFIGURE; EXEC sp_configure 'clr strict security', 0; RECONFIGURE; GO- Download tSQLt and follow the install guide.
- Go to release and download the latest Release version.
- execute install.sql in your target database.
Execute this stored procedure.
EXEC Diff.Uninstall;Please refer to the doc/examples while reading this section.
There is a Diff schema in your target database after you successfully installed this library.
Diff.CompareString @EXP_QueryString NVARCHAR(max), -- expected query string @ACT_QueryString NVARCHAR(max), -- actual query string @ExplicitColumnOrder TINYINT = 1, -- optional, set default value 1 to compare column order @ExplicitColumnName TINYINT = 1, -- optional, set default value 1 to compare column name(s) @CompareData TINYINT = 1 -- optional, set default value 1 to reorder data rows and compare the difference- An
INT, a sum of error values - Detailed difference in log
DECLARE @status_code INT; EXEC @status_code = Diff.CompareString 'Select 1', 'Select 2';Diff.Compare @ExpectedFilePath NVARCHAR(max), -- file path of expected query @ActualFilePath NVARCHAR(max), -- file path of actual query @ExplicitColumnOrder TINYINT = 1, -- optional, set default value 1 to compare column order @ExplicitColumnName TINYINT = 1, -- optional, set default value 1 to compare column name(s) @CompareData TINYINT = 1 -- optional, set default valueet 1 to reorder data rows and compare the difference- An
INT, a sum of error values - Detailed difference in log
DECLARE @status_code INT; EXEC @status_code = Diff.Compare 'path/to/query1.sql', 'path/to/query2.sql';Diff.CompareFolder @FolderPath NVARCHAR(1024) = N'/root/data/t', -- location of a folder where T-SQL files are stored -- accepts filepaths on Windows and Linux @ModifierName NVARCHAR(32) = N'Model', -- a filename suffix in identifying the current file as an anchor/master/standard/expected query -- e.g. In the filename 'q01Model.sql', 'Model' indicates the file is marked as correct. @FileSuffix NVARCHAR(8) = N'.sql' -- a filename extension of T-SQL files- Detailed difference in log
declare @status_code INT; exec Diff.ComapreFolder @FolderPath='/test', @ModifierName='Model'| value | error |
|---|---|
| 1 | runtime error or no valid select statement |
| 2 | expected column datatype(s) not matched |
| 4 | column order not matched |
| 8 | column names not matched |
| 16 | data row not matched |
Q: Why Diff.CompareFolder finds nothing in a folder on Windows?
A: Please grant read access of the folder to the user group Authenticated Users.
-
T-SQL Diff only receives the first
SELECTstatement as input to compare -
WITHstatements (e.g. cte) inSELECTare not supported -
Custom datatypes defined by CLR are not supported