The old procedure still runs, so nobody wants to touch it. Legacy T-SQL earns a careful rewrite when outdated joins, large-object types, and error handling make the next change riskier than it needs to be.
Inventory Legacy T-SQL Before Rewriting
Find the modules that carry old syntax and rank them by use and risk. A frequently executed procedure with an obsolete join deserves attention before an archived report nobody calls. Save its current definition, dependent objects, permissions, and representative output. A large formatting pass is a poor place to hide a logic change.
I start with one module and one behavior at a time. Which query can you compare before and after on a stable data set? Set that test up first. Legacy T-SQL can contain an accidental business rule that nobody wrote down. A rewrite that looks cleaner but drops that rule is still a regression.
Replace Old Join Forms Explicitly
Comma joins with predicates in WHERE are easy to misread, especially when a predicate is missing. Older outer-join operators such as *= are not valid modern T-SQL. Write ANSI JOIN clauses with the relationship in ON and filters in WHERE. Pay special attention to outer joins because moving a filter between those clauses can change which unmatched rows survive.
The following form makes the relationship clear. Compare it with the old query using rows that have no matching customer and rows with multiple matches. I check both directions of a result comparison when the change is supposed to preserve output.
SELECT o.OrderId, c.CustomerName
FROM dbo.Orders AS o
LEFT JOIN dbo.Customer AS c
ON c.CustomerId = o.CustomerId
WHERE o.OrderDate >= '2025-01-01'
AND o.OrderDate < '2025-02-01';Move Away From TEXT and NTEXT
TEXT and NTEXT are deprecated large-object types. Modern varchar(max) and nvarchar(max) work with a broader set of string functions and simpler application interfaces. Changing the type still requires a data and dependency review. Check maximum values, indexes, computed expressions, and code that uses READTEXT or WRITETEXT. A column type change can hold locks and log substantial work.
I inventory old types before proposing a migration. The catalog query identifies candidate columns without modifying data. Plan the conversion in a test copy and compare value lengths and row counts afterward. Do not assume that choosing nvarchar(max) is always right for an existing varchar-like column. Encoding and application behavior are part of the contract.
SELECT s.name AS schema_name, t.name AS table_name,
c.name AS column_name, ty.name AS data_type
FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
WHERE ty.name IN (N'text', N'ntext', N'image')
ORDER BY s.name, t.name, c.column_id;
Update Error Handling With Intent
Old RAISERROR usage can mix formatting, severity, and flow control in ways that surprise a caller. THROW provides a direct way to raise a new error or rethrow one inside CATCH. A bare THROW in CATCH preserves the original error context. For a new business error, use an appropriate user error number and state. Keep the message useful but free of private values.
I do not replace every RAISERROR mechanically. Some code uses formatting or NOWAIT behavior that THROW does not reproduce. Record what the caller and logging system expect, then choose the replacement. Test transaction state and error number. A nicer sentence that changes rollback behavior is not a safe modernization.
Watch for Other Aging Patterns in Legacy T-SQL
Look for SELECT * in stored application interfaces, ambiguous date strings, implicit conversions, and scalar functions wrapped around indexed columns in predicates. Each can become a maintenance or performance problem. Replace them only with a defined benefit and a comparison test. An old-looking expression is not automatically wrong, while a modern-looking one can still scan the whole table.
I also check compatibility level before using a newer function. The engine version alone does not tell you whether every syntax choice is available to a database. Keep deployment targets in the review. A procedure that compiles on a developer laptop but fails on the older supported instance has not been modernized successfully.
Compare Results and Plans
Run the old and new queries against the same stable data. Compare returned keys and values in both directions, including NULLs and duplicates. Then compare execution plans and resource use on representative parameters. A rewrite should preserve meaning unless a business rule change was approved. If it changes meaning, document the new contract explicitly.
Which edge rows would reveal a changed outer join? Add those to a test fixture. I review counts by important category, not only total row counts. Two incorrect results can still have the same number of rows. The optimizer can also choose a different plan after a seemingly harmless expression change. Measure that on the target workload.
Deploy Legacy T-SQL Changes in Small Reviewable Steps
Keep the original module definition and a tested rollback path. Separate datatype migrations from formatting and from error-handling changes when possible. That makes a failure easier to diagnose. After deployment, check the module definition, run the same behavior tests, and watch the application’s error reports.
I leave a short note explaining why the old pattern was replaced. Future maintainers need that reason more than a lecture about age. The best modernization removes ambiguity while preserving behavior that users rely on. If a pattern cannot be safely changed now, document its risk and the evidence required for a later change.
Legacy T-SQL cleanup should begin with behavior. Capture representative results, relevant execution plans and edge cases before rewriting a query. An equivalent-looking join can change duplicate rows when old predicates were incomplete. A date conversion can change behavior under a different language setting. I treat the old statement as a specification to investigate, not a template to copy mechanically.
After the rewrite, compare result sets with more than a row count. Check keys, duplicates, null behavior and ordering only when an ORDER BY defines it. Then test performance with the same parameters and data volume. I have seen a syntactically modern query return the right first page while changing a rare outer-join case. Small deployments with a rollback path make the discrepancy easier to isolate. The goal is clear supported SQL with preserved meaning, not a style badge.
Related reading on this blog: How to Find SQL Server Deprecated Features Used by the Application? Interview Question of the Week #165 and Finding Deprecated Features Before They Bite.
Legacy syntax is not a reason to rewrite blindly, it is a reason to test the contract before improving it.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
