{"id":107814,"date":"2025-11-12T12:52:41","date_gmt":"2025-11-12T12:52:41","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107814"},"modified":"2025-12-17T11:32:56","modified_gmt":"2025-12-17T11:32:56","slug":"5-strategies-to-refactor-sql-code","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/5-strategies-to-refactor-sql-code\/","title":{"rendered":"5 Strategies to Refactor SQL Code"},"content":{"rendered":"\n

Code refactoring is a common process when developing in procedural languages<\/a> – and essential to developing high-quality code – yet somehow often gets overlooked in SQL<\/a>. In this article, we’ll explain what refactoring is, how it helps, and give concrete examples on how it can make your code more readable, reliable, and maintainable.<\/p>\n\n\n\n

What is Refactoring?<\/strong><\/h2>\n\n\n\n

Over time the term “refactoring” has expanded and is sometimes used to mean code quality improvement in general, but here we are using it with its original meaning: condensing and eliminating redundant segments of code<\/a>. Like factoring a number<\/a> in math, we break the code into smaller blocks, identify any repeated elements, then replace them with a single reference. The graphic below illustrates the basic concept of what refactoring tries to accomplish:<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

Refactoring can be considered a case of the DRY (Don’t Repeat Yourself)<\/a> principle of software development. Redundant code is harder to read, more prone to bugs, and significantly more expensive to maintain.<\/p>\n\n\n\n

Usually, refactoring doesn’t help performance; it just improves the code quality.  But on shorter queries where parsing<\/a> is a substantial portion of the total execution time, refactoring can improve performance too.<\/p>\n\n\n\n

We’ll start with some tips on refactoring single queries, then move on to strategies for refactoring across your entire code base. We’ll demonstrate using short examples, but real-world queries can benefit even more. One of the most extreme cases I encountered, refactoring reduced a 1,200 line query down to just 90 lines \u2013 without, of course, altering the results at all.<\/p>\n\n\n\n

1. Use a Derived Table<\/h2>\n\n\n\n

Even novice SQL developers are usually adept at using subqueries<\/a> in a WHERE<\/code> clause or as a column in a SELECT<\/code><\/a>. But a subquery that returns a table-valued object<\/a><\/em> can also be the target of the FROM<\/code> clause, meaning we can use the result just as we would a base table. Such derived tables<\/a> are ideal for creating intermediate values for further processing. The basic syntax looks like this:<\/p>\n\n\n\n

SELECT (use derived columns here) FROM\n  (\n  SELECT (create derived columns here) FROM (base table)\n  ) AS (table alias)\n<\/pre><\/div>\n\n\n\n
Let’s turn these words into an actual example. Imagine a company that awards a pay modifier of 1% for every month an employee has worked beyond their first twelve months. Our task is a query that calculates for each employee the value of this modifier, along with modified values for their base and bonus pay. Note:<\/em> we’re using MS SQL Server<\/a> date functions. The syntax for PostgreSQL<\/a> or MySQL<\/a> is slightly different.<\/em><\/p>\n\n\n\n
Our employee table contains the following columns:<\/p>\n\n\n\n
CREATE TABLE employees (\n  emp_id \tINT NOT NULL,\n  hire_date \tDATE NOT NULL,\n  base_pay \tNUMERIC(9,2) NOT NULL,\n  bonus_pay \tNUMERIC(9,2) NOT NULL,\n  \u2026 );\n<\/pre><\/div>\n\n\n\n
The query to calculate the pay values is straightforward, though very redundant – we have to repeat the date arithmetic three times:<\/p>\n\n\n\n
SELECT\n  emp_id,\n  (GREATEST(DATEDIFF(month, hire_date, GETDATE())-12,0) * .01 + 1),\n  (GREATEST(DATEDIFF(month, hire_date, GETDATE())-12,0) * .01 + 1) * base_pay,\n  (GREATEST(DATEDIFF(month, hire_date, GETDATE())-12,0) * .01 + 1) * bonus_pay\nFROM employees;\n<\/pre><\/div>\n\n\n\n
But if we move that date arithmetic into a subquery, the redundancy vanishes:<\/p>\n\n\n\n
SELECT emp_id,\n  pay_mod,\n  pay_mod * base_pay,\n  pay_mod * bonus_pay\nFROM \n (SELECT GREATEST(DATEDIFF(month, hire_date, GETDATE())-12,0) * .01 + 1 AS pay_mod, *\nFROM employees) AS sub\n<\/pre><\/div>\n\n\n\n
Note: a version of this with PostgreSQL-format date arithmetic is in Appendix I.<\/em><\/p>\n\n\n\n
Remember that a derived table is a table \u2013 <\/em>we can join other tables to it directly, just as if it was a base table. So we can use this technique to simplify much larger, more complex queries than this example.<\/p>\n\n\n\n
2. Lateral Join<\/h2>\n\n\n\n
A lateral join<\/a> is similar to a correlated subquery<\/a>: both are evaluated once for every row in the main query. But where a subquery can only return a single value, a lateral join can return an entire row of values, or even multiple rows. This makes it an obvious choice for refactoring queries that contain multiple identical or similar subqueries. Again, let’s illustrate this with a concrete example.<\/p>\n\n\n\n
Using our employees table from above, let’s write a query to pair each employee with the name and date of the first company “team building” event occurring after they were hired. We have a new table of team events:<\/p>\n\n\n\n
CREATE TABLE team_events (\n  event_name VARCHAR(128) NOT NULL,\n  event_date DATE NOT NULL );\n<\/pre><\/div>\n\n\n\n
Matching the right team event to each employee is easy enough: find event dates greater than the employee hire date, order by event_date<\/code>, then use TOP 1<\/code> (or LIMIT 1<\/code>, for MySQL\/PostgreSQL) to match a single row. The problem is we can’t use a normal join because of that pesky one-row limit. So you might be tempted to use subqueries:<\/p>\n\n\n\n
SELECT\n  e.emp_id, \n  ( SELECT event_date FROM team_events te\n\tWHERE te.event_date > e.hire_date ORDER BY event_date LIMIT 1)\n  ( SELECT event_name FROM team_events te\n\tWHERE te.event_date > e.hire_date ORDER BY event_date LIMIT 1)\nFROM employees e\n<\/pre><\/div>\n\n\n\n
If you had to select three or more columns in such a manner, you can see this quickly becomes a mess. But a lateral join handily removes the redundancy:<\/p>\n\n\n\n
SELECT e.emp_id, te.event_date, te.event_name\nFROM employees e\nLEFT JOIN LATERAL \n(\n  SELECT * FROM team_events te\n  WHERE te.event_date > e.hire_date ORDER BY event_date LIMIT 1\n) te ON true\n<\/pre><\/div>\n\n\n\n
(Note: SQL Server uses the \u2018APPLY\u2019 keyword for lateral joins; see Appendix I)<\/em><\/p>\n\n\n\n
A Left Join is used to ensure all employees are listed, even if no matching event exists. Notice that our join condition is just the “true” flag. This is because we encapsulate the join condition within the lateral subquery.<\/p>\n\n\n\n
3. Common Table Expression (CTE)<\/h2>\n\n\n\n
The word “common” in ‘Common Table Expression’<\/a> is a hint on how useful these can be for removing redundancy in queries. Our first two tips are limited on where the refactored code can be used. But CTEs are much more flexible: define it at the start of your query and use it anywhere you like, as often as you like. That makes CTEs the ‘Swiss army knife’ of SQL code refactoring.<\/p>\n\n\n\n
To illustrate, let’s expand our last example of team building events. These events hold a competition that matches each employee to everyone else present, and we want a query to reflect this. To generate this “round robin” result, we use the query from above to match employees to events, then, using the same query<\/em>, self-join the result table to itself. The resulting query is confusing and hard to read \u2013 the redundant blocks are highlighted to identify them:<\/p>\n\n\n\n
SELECT e1.emp_id, e2.emp_id, te1.event_name, te1.event_date\nFROM employees e1\nLEFT JOIN LATERAL\n(\nSELECT * FROM team_events te\nWHERE te.event_date > e1.hire_date ORDER BY event_date LIMIT 1\n) te1 ON true\nINNER JOIN employees e2 ON e1.emp_id < e2.emp_id\nLEFT JOIN LATERAL\n(\nSELECT * FROM team_events te\nWHERE te.event_date > e2.hire_date ORDER BY event_date LIMIT 1\n) te2 ON true\nWHERE te1.event_name = te2.event_name;<\/pre><\/div>\n\n\n\n
Using a CTE, we can condense the two redundant blocks into one, making the code shorter and much easier to understand:<\/p>\n\n\n\n
WITH emp_event AS (\nSELECT *\nFROM employees e\nLEFT JOIN LATERAL\n(\nSELECT * FROM team_events te\nWHERE te.event_date > e.hire_date ORDER BY event_date LIMIT 1\n) te ON true)\n\nSELECT ee1.emp_id, ee2.emp_id, ee1.event_name, ee1.event_date\nFROM emp_event ee1\nINNER JOIN emp_event ee2 ON ee1.event_name = ee2.event_name AND ee1.emp_id < ee2.emp_id;<\/pre><\/div>\n\n\n\n
Here we name our CTE \u201cemp_event\u201d to indicate its joining employees to events. While we use the CTE twice here, a CTE can be used three, four, or more times in a single query. Even if you use it just once, a CTE can still be useful. It doesn’t shorten the query, but separating out a block of code and naming it to indicate purpose can help organize a large query.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
10 tools for every stage of SQL Server development<\/h3>\n                
\n                                            SQL Toolbelt Essentials includes 10 ingeniously simple tools that cover your entire database development lifecycle.                                    <\/div>\n            <\/div>\n                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
4. Defining a View<\/h2>\n\n\n\n
All our examples so far have involved a single query. Let’s look more globally: refactoring across an entire database. Often a code pattern \u2013 especially those involving table joins \u2013 appears throughout a system over and over again. One of the easiest ways to eliminate this redundancy is by defining a view<\/a>.<\/p>\n\n\n\n
For our example, imagine a database tracking security alerts<\/a>. The alert description is based on its type, and comes from one lookup table, while the alert priority is based on its source, and comes from another lookup table. So, whenever we access an alert, we must join the same three tables in the same manner:<\/p>\n\n\n\n
SELECT a.*, at.description, ap.priority\n  FROM Alerts a\n  LEFT OUTER JOIN AlertTypes at ON a.type = at.type\n  LEFT OUTER JOIN AlertPriorities ap ON a.source = ap.source<\/pre><\/div>\n\n\n\n
The details of this query aren’t terribly important. The point is that it’s a block of code that’s likely to be repeated throughout an application, whenever an alert is accessed. On production systems, it’s common to see such join patterns \u2013 by themselves or as blocks in larger queries \u2013 repeated dozens or even hundreds of times.<\/p>\n\n\n\n
Defining a view allows us to remove the redundancy, centralizing the business logic within the view, and allowing us to treat these three tables as if they were a single unit. This is as simple as prefacing the code in question with a CREATE VIEW<\/code> statement:<\/p>\n\n\n\n
CREATE VIEW VwAlert AS\n  SELECT a.*, at.description, ap.priority\n  FROM Alerts a\n  LEFT OUTER JOIN AlertTypes at ON a.alert_type = at.alert_type\n  LEFT OUTER JOIN AlertPriority ap ON a.alert_source = ap.alert_source<\/pre><\/div>\n\n\n\n
We can of course query this view directly. But we can also treat it as a table, including it as part of a larger query, for example:<\/p>\n\n\n\n
SELECT * FROM devices d\nINNER JOIN VwAlerts va d.device_id = va.device_id\n<\/pre><\/div>\n\n\n\n
or:<\/p>\n\n\n\n
SELECT * FROM USERS u\nINNER JOIN VwAlerts va ON u.user_id = va.user_id\n<\/pre><\/div>\n\n\n\n
If our business logic involving alert definition changes, we update one view, rather than performing edits scattered across multiple queries.<\/p>\n\n\n\n
5. Generated Columns<\/h2>\n\n\n\n
Generated columns<\/a> (known as computed columns<\/a> in SQL Server) allow us to define a repetitive calculation inside a table definition, to be done automatically whenever any query or application accesses the table. This not only eliminates redundancy, but it centralizes business logic at the DDL (data definition layer) – preventing any query or application from overriding or altering it.<\/p>\n\n\n\n
In our employee table, let’s add a column with a code to indicate employee type: ‘P’ for permanent, ‘S’ for temporary seasonal help, and ‘I’ for interns. To determine how many days per year of paid time off they’re eligible for, the following rule is used:<\/p>\n\n\n
\n