That procedure works, and nobody wants to be the person who breaks it. Refactoring old T-SQL becomes manageable when you preserve its contract, change one behavior, and compare evidence before deployment.
Start Refactoring Old T-SQL With One Change
Start with a procedure whose purpose and callers you can identify. Write down the input meanings and result contract. Include output parameters, return codes, and side effects when the procedure uses them.
I start with a change I can explain in one sentence. A smaller change narrows the reason for any difference. It also gives the reviewer a clearer decision than a rewritten page of SQL.
This example replaces a YEAR expression on an indexed timestamp with explicit range boundaries. It leaves the selected columns and ordering intact. Both versions use SET NOCOUNT ON, so message behavior stays consistent.
Removing a cursor or replacing a scalar function requires its own behavioral checks. Adding NOCOUNT affects row-count messages that some callers inspect. Don't combine those changes with the range filter because the procedure is open.
The following objects belong in an empty test database. Their data is synthetic and their names are distinctive. No example changes an existing production procedure.
CREATE TABLE dbo.RefactorSales
(
SaleId int NOT NULL PRIMARY KEY,
SoldAt datetime2(7) NOT NULL,
NetAmount decimal(12,2) NULL
);
CREATE INDEX IX_RefactorSales_SoldAt
ON dbo.RefactorSales (SoldAt) INCLUDE (NetAmount);
INSERT dbo.RefactorSales VALUES
(1, '2024-02-29T12:00:00', 10.00),
(2, '2024-12-31T23:59:59.9999999', NULL),
(3, '2025-01-01T00:00:00', 20.00),
(4, '2025-06-15T09:00:00', 30.00);
GO
CREATE PROCEDURE dbo.SalesBaseline @ReportYear int
AS
BEGIN
SET NOCOUNT ON;
IF @ReportYear IS NULL OR @ReportYear NOT BETWEEN 1 AND 9998
THROW 51060, 'Report year must be between 1 and 9998.', 1;
SELECT SaleId, SoldAt, NetAmount
FROM dbo.RefactorSales
WHERE YEAR(SoldAt) = @ReportYear
ORDER BY SoldAt, SaleId;
END;
GO
EXEC dbo.SalesBaseline @ReportYear = 2024;Retain the original definition before adapting an existing procedure. Keep its execution context and permission requirements visible. The test copy should preserve the behavior you intend to compare.
Capture a Set of Inputs and Results
Select inputs that exercise the actual procedure's branches. Include an empty result and boundary values. A single convenient input doesn't establish that the contract survived.
The sample captures several requested years with a small test loop. It tags each output row with its test input. That tag prevents two input cases from becoming indistinguishable during comparison.
CREATE TABLE #Inputs (TestId int NOT NULL PRIMARY KEY, ReportYear int NOT NULL);
INSERT #Inputs VALUES (1, 2024), (2, 2025), (3, 2026);
CREATE TABLE #OneResult
(
SaleId int, SoldAt datetime2(7), NetAmount decimal(12,2) NULL
);
CREATE TABLE #BeforeRows
(
TestId int, SaleId int, SoldAt datetime2(7), NetAmount decimal(12,2) NULL
);
DECLARE @TestId int = 1, @ReportYear int;
WHILE @TestId <= 3
BEGIN
SELECT @ReportYear = ReportYear FROM #Inputs WHERE TestId = @TestId;
TRUNCATE TABLE #OneResult;
INSERT #OneResult EXEC dbo.SalesBaseline @ReportYear = @ReportYear;
INSERT #BeforeRows SELECT @TestId, SaleId, SoldAt, NetAmount FROM #OneResult;
SET @TestId += 1;
END;
SELECT TestId, SaleId, SoldAt, NetAmount
FROM #BeforeRows ORDER BY TestId, SoldAt, SaleId;Run the remaining blocks in the same connection so temporary tables remain available. INSERT EXEC is convenient for this single-result-set procedure. Procedures with several result sets or nested INSERT EXEC need a different harness.
Control source data while comparing the two versions. Concurrent writes create differences unrelated to the refactor. A restored test database or fixed fixture gives the comparison a stable source.
I keep unusual inputs in the test set instead of saving them for later. NULL amounts and midnight boundaries expose assumptions early. The quiet branch deserves a test too.
Replace the Filter Without Changing the Interface
Compute one year's start and the next year's start from the validated parameter. Compare SoldAt directly with those values. The included start and excluded end preserve the intended calendar-year selection.
GO
CREATE PROCEDURE dbo.SalesCandidate @ReportYear int
AS
BEGIN
SET NOCOUNT ON;
IF @ReportYear IS NULL OR @ReportYear NOT BETWEEN 1 AND 9998
THROW 51060, 'Report year must be between 1 and 9998.', 1;
DECLARE @Start date = DATEFROMPARTS(@ReportYear, 1, 1);
DECLARE @End date = DATEADD(year, 1, @Start);
SELECT SaleId, SoldAt, NetAmount
FROM dbo.RefactorSales
WHERE SoldAt >= @Start AND SoldAt < @End
ORDER BY SoldAt, SaleId;
END;
GO
CREATE TABLE #AfterRows
(
TestId int, SaleId int, SoldAt datetime2(7), NetAmount decimal(12,2) NULL
);
DECLARE @TestId int = 1, @ReportYear int;
WHILE @TestId <= 3
BEGIN
SELECT @ReportYear = ReportYear FROM #Inputs WHERE TestId = @TestId;
TRUNCATE TABLE #OneResult;
INSERT #OneResult EXEC dbo.SalesCandidate @ReportYear = @ReportYear;
INSERT #AfterRows SELECT @TestId, SaleId, SoldAt, NetAmount FROM #OneResult;
SET @TestId += 1;
END;The candidate enables a straightforward range predicate on the timestamp. It doesn't promise a particular physical plan. SQL Server still chooses access based on the table, statistics, and selected input.
For refactoring old T-SQL, retain error behavior that callers rely on. Both sample procedures reject the same unsupported year range. Test invalid inputs separately, because result-table capture doesn't describe exceptions.
Check the meaning of the stored timestamp before approving the calendar range. A UTC column and local reporting year need converted boundaries. Fixing access syntax doesn't settle the reporting clock.
Verify Refactoring Old T-SQL With Two-Way EXCEPT
EXCEPT returns distinct differences between compatible result sets. A comparison in one direction misses extra candidate rows. A plain set comparison also hides duplicate-count changes.
Group each captured result by all compared fields and include its multiplicity. Then apply EXCEPT in both directions. This preserves a check for repeated rows without relying on total counts alone.
SELECT TestId, SaleId, SoldAt, NetAmount, COUNT_BIG(*) AS Copies
INTO #BeforeGrouped
FROM #BeforeRows GROUP BY TestId, SaleId, SoldAt, NetAmount;
SELECT TestId, SaleId, SoldAt, NetAmount, COUNT_BIG(*) AS Copies
INTO #AfterGrouped
FROM #AfterRows GROUP BY TestId, SaleId, SoldAt, NetAmount;
SELECT N'Baseline only' AS DifferenceKind, d.*
INTO #Differences
FROM
(
SELECT TestId, SaleId, SoldAt, NetAmount, Copies FROM #BeforeGrouped
EXCEPT
SELECT TestId, SaleId, SoldAt, NetAmount, Copies FROM #AfterGrouped
) AS d
UNION ALL
SELECT N'Candidate only', d.*
FROM
(
SELECT TestId, SaleId, SoldAt, NetAmount, Copies FROM #AfterGrouped
EXCEPT
SELECT TestId, SaleId, SoldAt, NetAmount, Copies FROM #BeforeGrouped
) AS d;
SELECT * FROM #Differences;
IF EXISTS (SELECT 1 FROM #Differences)
THROW 51061, 'Captured result values or multiplicities differ.', 1;In my run, the differences table stayed empty and the THROW never fired. No differences means these captured values and multiplicities agree for these inputs. It doesn't prove equivalence for every possible input. Ordering, metadata, and side effects need separate checks.
Compare declared result types and nullability alongside values. A cast introduced during capture can hide a metadata change. Keep the public result contract visible rather than checking only what fits the capture table.
Measure Reads Under Comparable Conditions
Use STATISTICS IO to inspect reads produced by each version. Keep the input and source data the same. Read the messages rather than inventing a percentage improvement from the query shape.
SET STATISTICS IO ON;
EXEC dbo.SalesBaseline @ReportYear = 2025;
EXEC dbo.SalesCandidate @ReportYear = 2025;
SET STATISTICS IO OFF;On this four-row fixture, both versions reported the same logical reads, which proves nothing about a large table. Logical reads describe page access from the data cache. Physical reads also depend on cache state. Repeat a controlled comparison and inspect the actual execution plans before interpreting differences.
The small fixture demonstrates behavior, not representative production performance. Measure against an approved realistic test copy for performance conclusions. Selective and broad parameter values can lead to different plans.
Don't clear shared caches to make a demonstration look decisive. Record the conditions under which you collected the messages. A promising plan still owes you measured evidence.
Log Refactoring Old T-SQL in a Short Record
Record the procedure, intended change, input set, comparison outcome, and measured evidence location. Preserve the previous definition for recovery. A short record is easier to maintain than an undocumented rewrite.
CREATE TABLE #ChangeLog
(
RecordedUtc datetime2(0) NOT NULL,
ProcedureName sysname NOT NULL,
ChangeText nvarchar(300) NOT NULL,
VerificationState nvarchar(100) NOT NULL
);
INSERT #ChangeLog VALUES
(SYSUTCDATETIME(), N'SalesCandidate',
N'Replace YEAR(SoldAt) with a validated half-open date range.',
N'Result comparison supplied; metadata, errors, and reads require review.');
SELECT * FROM #ChangeLog;Keep the real record in your approved change process rather than a temporary table. The sample shows its shape. Don't mark measurements complete merely because the measurement query exists.
Which caller would notice a changed column type or missing row-count message? Include that caller in the review. A procedure's contract extends beyond the rows shown in SSMS.
Test execution context and permissions with the same role the application uses. Administrative success doesn't prove caller success. A candidate that returns correct rows under an elevated account can still break its normal caller.
Avoid treating result order as a property of EXCEPT. Capture and review the original ORDER BY contract separately. Preserve tie-breaking columns when the caller expects stable ordering across repeated requests.
Record any known limitation of the comparison harness beside its outcome. That keeps an empty differences table in context. The old procedure has earned the right to be suspicious.
Accept the Small Change Before Starting Another
Deploy through the approved database change process after the comparisons are reviewed. Keep the recovery definition available and verify representative calls afterward. Observe errors and reads under the real workload.
Use refactoring old T-SQL as a repeatable small-change practice. Finish the evidence for this procedure before selecting another change. A procedure can be cautious without remaining untouched forever.
Related reading on this blog: SQL Server: Find Distinct Result Sets Using EXCEPT Operator and SET STATISTICS IO ON: SQL in Sixty Seconds #128.
A safe refactor is not a large rewrite, it is a small change backed by comparable evidence.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
