{"id":71926,"date":"2017-07-31T16:55:26","date_gmt":"2017-07-31T16:55:26","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=71926"},"modified":"2021-09-29T16:21:10","modified_gmt":"2021-09-29T16:21:10","slug":"basics-good-t-sql-coding-style-part-4-performance","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/basics-good-t-sql-coding-style-part-4-performance\/","title":{"rendered":"The Basics of Good T-SQL Coding Style \u2013 Part 4: Performance"},"content":{"rendered":"

The series so far:<\/h4>\n
    \n
  1. The Basics of Good T-SQL Coding Style<\/a><\/li>\n
  2. The Basics of Good T-SQL Coding Style \u2013 Part 2: Defining Database Objects<\/a><\/li>\n
  3. The Basics of Good T-SQL Coding Style \u2013 Part 3: Querying and Manipulating Data<\/a><\/li>\n
  4. The Basics of Good T-SQL Coding Style \u2013 Part 4: Performance<\/a><\/li>\n<\/ol>\n\n

    When working with SQL Server, performance should be at the forefront, whether you are designing databases, managing indexes, or writing T-SQL queries. Each of these three factors plays a role in delivering applications that will perform over the long haul. Although one factor is as important as the next, we focus only on the queries component in this article, as part of our continued discussion about the basics of good coding.<\/p>\n

    Writing queries can be tricky because you need to continuously evaluate your code for performance-related issues, taking into account a wide range of considerations. For example, you should consider how you\u2019re using temporary tables, outer joins, comparison expressions, correlated subqueries, and a variety of other elements.<\/p>\n

    Throughout the article, I provide a number of examples of T-SQL queries to help our discussion along. The examples are based on the AdventureWorks2014<\/strong> database, installed on a local instance of SQL Server 2016. I\u2019ve tried to hit as many of the more salient issues as possible, but keep in mind that a discussion focused on performance is much broader than what we can cover here, even though we\u2019re limiting it only to T-SQL queries.<\/p>\n

    Also be aware that performance-related issues are seldom black-and-white. More often than not, your final approach to a query will be based on your specific circumstances and the data you\u2019re working with. You might have to try different approaches before coming up with the best one for your particular circumstances, as they currently exist and will in the foreseeable future. Perhaps the way to think of this article is as a set of suggested guidelines, without any assumptions of absolute truth.<\/p>\n

    The ubiquitous table source<\/h2>\n

    At the heart of most queries are one or more table sources (tables, views, derived tables or joined tables) that make it possible to get at the data you need in order to produce the necessary results. The tables will come in many forms. A query might use temporary tables, common table expressions (CTEs), table variables, Table functions, Table parameters (TVPs) or joins to provide one or more workable data sets that can be massaged and transformed before returning the final results. In fact, any query that contains a FROM<\/strong> clause is starting out with a table source.<\/p>\n

    Given that T-SQL is a set-based language, thinking in terms of table sources makes sense. But working with these structures is not always a straightforward process when it comes to performance. Let\u2019s start with an example of a temporary table. The following T-SQL code creates the #sales<\/strong> temporary table and joins it with the Person<\/strong> table in the AdventureWorks2014<\/strong> database:<\/p>\n

    CREATE TABLE #sales\r\n  (SalesPersonID INT, TotalSales MONEY);\r\n  INSERT INTO #sales\r\n  SELECT SalesPersonID, SUM(SubTotal) AS TotalSales\u00a0\r\n  FROM Sales.SalesOrderHeader\r\n  WHERE SalesPersonID IS NOT NULL\r\n  GROUP BY SalesPersonID;\r\n  SELECT p.FirstName, p.LastName, s.TotalSales\u00a0\r\n  FROM #sales s INNER JOIN Person.Person p\r\n  \u00a0 ON s.SalesPersonID = p.BusinessEntityID\r\n  ORDER BY p.LastName, p.FirstName;<\/pre>\n
    The example is fairly straightforward. We\u2019re simply aggregating the sales totals and mapping that information to the applicable sales rep. The SELECT<\/strong> statement returns the results shown in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    FirstName<\/strong><\/td>\nLastName<\/strong><\/td>\nTotalSales<\/strong><\/td>\n<\/tr>\n<\/thead>\n
    Syed<\/td>\nAbbas<\/td>\n172524.4512<\/td>\n<\/tr>\n
    Amy<\/td>\nAlberts<\/td>\n732759.1841<\/td>\n<\/tr>\n
    Pamela<\/td>\nAnsman-Wolfe<\/td>\n3325102.5941<\/td>\n<\/tr>\n
    Michael<\/td>\nBlythe<\/td>\n9293903.0046<\/td>\n<\/tr>\n
    David<\/td>\nCampbell<\/td>\n3729945.349<\/td>\n<\/tr>\n
    Jillian<\/td>\nCarson<\/td>\n10065803.5404<\/td>\n<\/tr>\n
    Shu<\/td>\nIto<\/td>\n6427005.554<\/td>\n<\/tr>\n
    Stephen<\/td>\nJiang<\/td>\n1092123.8561<\/td>\n<\/tr>\n
    Tete<\/td>\nMensa-Annan<\/td>\n2312545.69<\/td>\n<\/tr>\n
    Linda<\/td>\nMitchell<\/td>\n10367007.4265<\/td>\n<\/tr>\n
    Jae<\/td>\nPak<\/td>\n8503338.6457<\/td>\n<\/tr>\n
    Tsvi<\/td>\nReiter<\/td>\n7171012.7501<\/td>\n<\/tr>\n
    Jos\u00e9<\/td>\nSaraiva<\/td>\n5926418.3555<\/td>\n<\/tr>\n
    Lynn<\/td>\nTsoflias<\/td>\n1421810.9242<\/td>\n<\/tr>\n
    Rachel<\/td>\nValdez<\/td>\n1827066.7118<\/td>\n<\/tr>\n
    Garrett<\/td>\nVargas<\/td>\n3609447.2148<\/td>\n<\/tr>\n
    Ranjit<\/td>\nVarkey Chudukatil<\/td>\n4509888.9311<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The reason I\u2019ve included this example is not to demonstrate how to create or use temporary tables, but to point out the larger issue of choosing carefully which table structure to use in a given situation, taking into account the ramifications on performance, along with other considerations.<\/p>\n
    There are, in fact, a number of important differences between temporary tables and table variables<\/a>. The point of all this is that you need to fully understand how each one works before implementing them in your code. Only then can you choose the one best suited for your particular circumstances.<\/p>\n
    You should also keep in mind these table sources for less common use cases. For example, if you need to create a join on a large table and you plan to filter the data in that table, you might consider creating a temporary table to hold the subset of data and then joining to the temporary table as a way to boost performance.<\/p>\n
    Thinking in terms of data sets can often help you recast a T-SQL statement to perform better. The database engine works best with sets of data, unlike the engines used for procedural languages. Take for example, the following SELECT<\/strong> statement, which includes a correlated subquery in the WHERE<\/strong> clause:<\/p>\n
    SELECT SalesPersonID, SUM(SubTotal) AS TotalSales\u00a0\r\n  FROM Sales.SalesOrderHeader h\r\n  WHERE SalesPersonID = (\r\n  \u00a0 SELECT BusinessEntityID\r\n  \u00a0 FROM HumanResources.EmployeePayHistory p\r\n  \u00a0 WHERE Rate > 25 AND p.BusinessEntityID = h.SalesPersonID)\r\n  GROUP BY SalesPersonID;<\/pre>\n
    The SELECT<\/strong> statement returns the total sales for each sales rep with a pay rate greater than 25, giving us the results shown in the following table:<\/p>\n\n\n\n\n\n\n\n
    SalesPersonID<\/strong><\/td>\nTotalSales<\/strong><\/td>\n<\/tr>\n<\/thead>\n
    285<\/td>\n172524.4512<\/td>\n<\/tr>\n
    287<\/td>\n732759.1841<\/td>\n<\/tr>\n
    274<\/td>\n1092123.8561<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The statement uses a correlated subquery to retrieve the BusinessEntityID<\/strong> value for each rep with a pay rate that exceeds 25. As with any correlated subquery, this one depends on the outer query for at least one of its values, in this case, SalesPersonID<\/strong>. Because of this construction, the query in the subquery must run for each row returned by the outer query. For a small table, this is not a big deal, but when we start talking of millions of rows, performance can go down the tubes.<\/p>\n
    One way to get around this is to join the two tables, rather than using a correlated subquery. This lets us work with the data in sets, rather than having to perform many individual operations. If the source table is large, and you\u2019ll be filtering the data, you can take the approach described above, which is to create a temporary table that contains a subset of data, and then join on that.<\/p>\n
    Note, however, that we should be evaluating our code not only in terms of how to use certain types of data sources, but also when not to use them. For example, the following SELECT<\/strong> statement uses a left outer join to retrieve products that are not listed as product assembly components:<\/p>\n
    SELECT p.ProductID\r\n  FROM Production.Product p\r\n  \u00a0 LEFT JOIN Production.BillOfMaterials b\r\n  \u00a0 ON p.ProductID = b.ProductAssemblyID\r\n  WHERE b.ProductAssemblyID IS NULL\r\n  ORDER BY p.ProductID;<\/pre>\n
    In this case, the statement returns 266 rows out of 504 total products, which are relatively small amounts. But if we were talking millions of rows, we would quickly see the impact on performance. In such cases, we might consider an approach other than joining the Product<\/strong> table to the BillOfMaterials<\/strong> table. When we join the tables, the database engine loads all 2,679 rows from the BillOfMaterials<\/strong> table before filtering out the rows whose ProductAssemblyID<\/strong> column is not null, which are the majority of rows.<\/p>\n
    Instead of defining a join to the BillOfMaterials<\/strong> table, we can add a condition to the WHERE<\/strong> clause that uses the NOT<\/strong> EXISTS<\/strong> operator and a subquery to check values in the BillOfMaterials<\/strong> table. In this way, we\u2019re simplifying our data set in favor of a more robust WHERE<\/strong> expression.<\/p>\n
    In some cases, we might want to move in the opposite direction, turning expression logic into a data set. For example, the following T-SQL code creates a function and uses that function in a query\u2019s select list:<\/p>\n
    CREATE FUNCTION dbo.fnGetTotalItems (@OrderID INT)\r\n  RETURNS INT\r\n  WITH SCHEMABINDING AS\r\n  BEGIN\r\n  \u00a0 DECLARE @TotalItems INT\r\n  \u00a0 SELECT @TotalItems = SUM(OrderQty)\r\n  \u00a0 FROM Sales.SalesOrderDetail\r\n  \u00a0 WHERE SalesOrderID = @OrderID\r\n  \u00a0 GROUP BY SalesOrderID\r\n  \u00a0 RETURN @TotalItems\r\n  END;\r\n  GO\r\n  SELECT SalesOrderID, OrderDate, SalesPersonID,\u00a0\r\n  \u00a0 dbo.fnGetTotalItems(SalesOrderID) AS TotalItems\r\n  FROM Sales.SalesOrderHeader\r\n  ORDER BY SalesOrderID;<\/pre>\n
    Whenever we include a scalar function in an expression, we have to keep in mind its impact on performance. As with the correlated subquery, the database engine will execute the query within the function for every row in the outer query.<\/p>\n
    One solution is to turn the scalar function into an inline table-valued function. We would then use the function within the FROM<\/strong> clause, using the CROSS<\/strong> APPLY<\/strong> operator to join the SalesOrderHeader<\/strong> table to the function. This way, we can eliminate a great deal of unnecessary processing and consequently deliver better performance.<\/p>\n
    When writing T-SQL queries, we should always evaluate the statement\u2019s elements to ensure that we\u2019re using the data sets in a way to maximize performance, whether creating temporary table structures, joining data sets, or mixing things up in some other fashion.<\/p>\n
    Building comparison expressions<\/h2>\n
    Like other elements of T-SQL coding, the ways in which we set up the conditional expressions in our WHERE<\/strong> clauses can impact performance. For example, suppose we use the NOT<\/strong> IN<\/strong> operator in a conditional expression:<\/p>\n
    SELECT ProductID\r\n  FROM Production.Product\r\n  WHERE ProductID NOT IN (\r\n  \u00a0 SELECT ProductAssemblyID FROM Production.BillOfMaterials\u00a0\r\n  \u00a0 WHERE ProductAssemblyID IS NOT NULL)\r\n  ORDER BY ProductID;<\/pre>\n
    As with most performance-related issues, using the NOT<\/strong> IN<\/strong> operator for a small data set is usually not a problem, but can be so for a larger one. This is because the database engine uses a table scan to find the data, rather than an index seek, even if an index is defined on the filtering column. Instead, consider using NOT<\/strong> EXISTS<\/strong> or perhaps joining the tables.<\/p>\n
    You can run into similar issues if you wrap the filtering column in a function, as in the following SELECT<\/strong> statement:<\/p>\n
    CREATE FUNCTION dbo.fnGetProductName (@ProductID INT)\r\n  RETURNS NVARCHAR(50)\r\n  WITH SCHEMABINDING AS\r\n  BEGIN\r\n  \u00a0 DECLARE @ProductName NVARCHAR(50)\r\n  \u00a0 SELECT @ProductName = name\r\n  \u00a0 FROM Production.Product\r\n  \u00a0 WHERE ProductID = @ProductID\r\n  \u00a0 RETURN @ProductName\r\n  END;\r\n  GO\r\n  SELECT SalesOrderID, ProductID\r\n  FROM Sales.SalesOrderDetail\r\n  WHERE dbo.fnGetProductName(ProductID) = 'LL Road Seat\/Saddle'\r\n  ORDER BY SalesOrderID;<\/pre>\n
    In this case, the fnGetProductName<\/strong> function retrieves the product name, based on the ProductID<\/strong> value, and them compares that name to the string expression. Because of this construction, the database engine is again likely to do a table scan, rather than an index seek, even if an index exists.<\/p>\n
    Another performance hole is to create a conditional expression that uses the LIKE<\/strong> operator in conjunction with specifying a wildcard at the beginning of the specified value:<\/p>\n
    SELECT ProductID, name AS ProductName\r\n  FROM Production.Product\r\n  WHERE name LIKE '%bike%'\r\n  ORDER BY ProductID;<\/pre>\n
    The issue is not so much with the use of a wildcard, but rather with its placement at the beginning of the search value. In this position, the database engine must again do a complete table scan, reading every row of the Product<\/strong> table because it has no other way of ensuring that all potential values are returned.<\/p>\n
    When using the WHERE<\/strong> clause to filter data, keep in mind its impact on performance and always evaluate whether there is a better way of doing something. Even using a not equal comparison operator (<><\/strong>) in a conditional expression can result in performance issues if the database engine cannot use the indexes effectively when generating a query plan. Try using a different construction to see whether that improves performance.<\/p>\n
    Returning result sets<\/h2>\n
    When defining a query, you should ensure that you\u2019re returning results as efficiently as possible. Even if a statement is technically correct and does not cause the database engine to balk, you might still inadvertently introduce performance issues. For example, the following CREATE<\/strong> PROCEDURE<\/strong> statement defines a very simple stored procedure that includes all the basic components, but fails to include a SET<\/strong> NOCOUNT<\/strong> ON<\/strong> clause:<\/p>\n
    CREATE PROCEDURE dbo.GetPeopleInfo\r\n  AS\r\n  BEGIN\r\n  \u00a0 DECLARE @people TABLE (PersonID INT);\r\n  \u00a0 INSERT INTO @people\r\n  \u00a0 SELECT BusinessEntityID FROM Person.Person;\r\n  \u00a0 SELECT PersonID FROM @people;\r\n  END\r\n  GO<\/pre>\n
    By including a SET<\/strong> NOCOUNT<\/strong> ON<\/strong> clause at the beginning of your procedure, you\u2019re telling the database engine to forego sending information messages back to the client for each executed statement, helping to improve application performance. Although this can seem a small step to take, it will in fact help and should be included in most of your stored procedures, unless you have a specific reason for returning those messages.<\/p>\n
    Another consideration when it comes to stored procedures is to maximize the benefit you get with each procedure by building them to return multiple result sets, when appropriate. This can reduce the number of connections and calls between the client and server. Just be sure that if you\u2019re working with a data framework, it can handle multiple result sets.<\/p>\n
    When retrieving data, whether or not it is within a stored procedure, you should also be aware of how you use operators and other options within your statements. A good example of this is the UNION<\/strong> operator, which lets you join similarly structured data sets, as in the following example:<\/p>\n
    SELECT BusinessEntityID, FirstName, LastName, PersonType\u00a0\r\n  FROM Person.Person WHERE PersonType = 'EM'\r\n  UNION\r\n  SELECT BusinessEntityID, FirstName, LastName, PersonType\u00a0\r\n  FROM Person.Person WHERE PersonType = 'SC';<\/pre>\n
    When you specify the UNION<\/strong> operator as I have here, the database engine combines the data sets, sorts the data in ascending order, and removes the duplicates. Not surprisingly, sorting and deduplication operations can add significant overhead, especially for large data sets. In some cases, you might not need to sort the data and remove duplicates. For example, the SELECT<\/strong> statements above return only unique rows because the BusinessEntityID<\/strong> column is included, which is the primary key. In such cases, we can add ALL<\/strong> to the UNION<\/strong> operator (as in UNION<\/strong> ALL<\/strong>) to join the data sets without incurring the sorting and deduplication overhead.<\/p>\n
    An issue related to this is the use of the DISTINCT<\/strong> keyword at the beginning of the select list, as in the following example:<\/p>\n
    SELECT DISTINCT BusinessEntityID, FirstName, LastName\r\n  FROM Person.Person WHERE PersonType = 'EM';<\/pre>\n
    The DISTINCT<\/strong> keyword, as with the UNION<\/strong> operator, sorts the data and removes duplicates, which is fine if you\u2019re working with only a small amount of data, but it can significantly impact performance on large data sets, depending how the data has been indexed. In general, try to avoid DISTINCT<\/strong> for large data sets if you don\u2019t really need it, as is the case in our example (once again, because we\u2019ve included the BusinessEntityID<\/strong> column in the select list). If we still need to sort the data, we can add an ORDER<\/strong> BY<\/strong> clause.<\/p>\n
    One other issue worth pointing out is the use of the asterisk wildcard in place of a query\u2019s select list, as in the following example:<\/p>\n
    SELECT * FROM Person.Person WHERE PersonType = 'EM';<\/pre>\n
    As a general rule, you should avoid using the wildcard and instead specify the columns so you\u2019re returning only the data you need. Why incur extra overhead to return unnecessary data? Besides, specifying the columns can also help prevent your applications from breaking, should the table structure change.<\/p>\n
    Proceed with caution<\/h2>\n
    None of the issues we covered in the previous section are absolutes. At times, you might want to omit the SET<\/strong> NOCOUNT<\/strong> ON<\/strong> clause or use the UNION<\/strong> operator without ALL<\/strong> or add DISTINCT<\/strong> to your select list. You might even want to use a wildcard in you select list, at least when running ad hoc statements. The point is, you should proceed with caution whenever using any of these.<\/p>\n
    There are also other T-SQL elements that warrant such careful consideration, such as the use of dynamic SQL, as in the following example:<\/p>\n
    DECLARE @sql NVARCHAR(1000);\r\n  DECLARE @id NVARCHAR(10);\r\n  SET @id = '869';\r\n  SET @sql = 'SELECT name FROM Production.Product\r\n  \u00a0 WHERE ProductID = ' + @id + ';';\u00a0\r\n  EXECUTE sp_executesql @sql;<\/pre>\n
    You\u2019ll often see warnings about using dynamic SQL because of security risks (i.e., SQL injection attacks), but dynamic SQL can have performance implications as well, depending on how statements are constructed and executed. This is a huge topic, however, and well beyond the scope of this article, so be sure you do your homework before implementing dynamic SQL, for both security and performance reasons.<\/p>\n
    Also be cautious about defining triggers that return results to the application. An application that issues a request to modify data normally does not expect results, such as those in the following example:<\/p>\n
    CREATE TRIGGER InsertTrig ON Production.Culture\r\n  AFTER INSERT\r\n  AS\r\n  BEGIN\r\n  \u00a0 DECLARE @CultureId NCHAR(6)\r\n  \u00a0 SELECT @CultureId = INSERTED.CultureId\r\n  \u00a0 FROM INSERTED\r\n  \u00a0\r\n  \u00a0 INSERT INTO dbo.EventLog(EventDescript)\r\n  \u00a0 VALUES('Table: Production.Culture; CultureID: ' + @CultureId)\r\n  \u00a0 SELECT TOP(1) CultureID, Name\u00a0\r\n  \u00a0 FROM Production.Culture ORDER BY ModifiedDate DESC\r\n  END;\r\n  GO<\/pre>\n
    The trigger\u2019s main purpose is to log information to the EventLog<\/strong> table after data is inserted into the Production.Culture<\/strong> table. However, the trigger also includes a SELECT<\/strong> statement that returns the CultureID<\/strong> and Names<\/strong> values to the calling application. We can demonstrate this by running the following INSERT<\/strong> statement:<\/p>\n
    INSERT INTO Production.Culture (CultureID, Name)\r\n  VALUES('tst', 'test culture');<\/pre>\n
    After the data has been inserted, the database engine will log the data to the EventLog<\/strong> table and then return the following information:<\/p>\n\n\n\n\n\n
    CultureID<\/strong><\/td>\nName<\/strong><\/td>\n<\/tr>\n<\/thead>\n
    tst \u00a0<\/td>\ntest culture<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Unless the application includes a mechanism for handling these values, the extra resources needed to return this information are wasted. This does not mean you must always avoid returning values, just be sure you have a reason for doing so and that the application can handle them.<\/p>\n
    You should also be cautious when including hints in your queries, such as those in the following example:<\/p>\n
    SELECT h.SalesOrderID, d.SalesOrderDetailID, h.OrderDate\r\n  FROM Sales.SalesOrderHeader h\r\n  \u00a0 INNER HASH JOIN Sales.SalesOrderDetail d\r\n  \u00a0 WITH (TABLOCK)\r\n  \u00a0 ON h.SalesOrderID = d.SalesOrderID\r\n  OPTION (KEEP PLAN);<\/pre>\n
    In this case, I\u2019ve included the HASH<\/strong> join hint, TABLOCK<\/strong> table hint, and KEEP<\/strong> PLAN<\/strong> query hint (a lot of hints, I realize, but I wanted to demonstrate them all at once). Regardless of the type, you should use hints only when you\u2019re certain that you can improve on the query optimizer.<\/p>\n
    The optimizer generally does a good job of maximizing performance, in which case, using hints could make things worse. But in those rare cases when the optimizer is falling short\u2014and you know in your heart with absolute certainty that you can do a better job\u2014then consider the judicious use of hints. Just make sure you fully test whatever you end up implementing, using a large enough data set to provide you with meaningful results.<\/p>\n
    One final issue worth mentioning is the use of SET<\/strong> options, such as FORCEPLAN<\/strong>, which is shown in the following example:<\/p>\n
    SET FORCEPLAN ON;\r\n  GO\r\n  SELECT h.SalesOrderID, d.SalesOrderDetailID, h.OrderDate\r\n  FROM Sales.SalesOrderHeader h\r\n  \u00a0 INNER JOIN Sales.SalesOrderDetail d\r\n  \u00a0 ON h.SalesOrderID = d.SalesOrderID;\r\n  SET FORCEPLAN OFF;\r\n  GO<\/pre>\n
    When you set FORCEPLAN<\/strong> to ON<\/strong>, the query optimizer processes the tables in a join in the same order they\u2019re specified in the FROM<\/strong> clause, overriding the query optimizer. As with hints, you want to be careful taking steps that change the optimizer\u2019s behavior, unless you\u2019re certain you know what you\u2019re doing.<\/p>\n
    Plenty more to think about<\/h2>\n
    In this article, we\u2019ve covered a number of issues related to query performance, as it applies to T-SQL queries. We could have covered any one of these issues in more detail, many of which deserved articles of their own. There are also plenty of other issues we could have covered, such as avoiding the use of cursors, specifying schema names when references database objects, or not interweaving data manipulation language (DML) and data definition language (DDL) within a batch. That said, what we have covered should give you a good sense of the types of issues you should be taking into account in order to maximize performance.<\/p>\n
    Whenever you\u2019re writing or updating T-SQL queries, you need to be thinking in terms of performance, along with security and data accuracy, of course. With performance, however, it\u2019s not always apparent which statement construction will deliver the best results, and you might need to try different approaches before you get it right, keeping in mind that indexing and database design also play pivotal roles. The key is to evaluate each statement element to ensure that you\u2019re taking the best approach to maximizing performance, even if it means trying several different approaches.<\/p>\n","protected":false},"excerpt":{"rendered":"
    There are several obvious problems with poor SQL Coding habits. It can make code difficult to maintain, or can confuse your team colleagues.  It can make refactoring a chore or make testing difficult.  The most serious problem is poor performance. You can write SQL that looks beautiful but performs sluggishly, or interferes with other threads.  A busy database developer adopts good habits so as to avoid staring at execution plans.  Rob Sheldon gives some examples.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[5134,45110],"coauthors":[6779],"class_list":["post-71926","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql-prompt","tag-t-sql-formatting"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71926","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=71926"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71926\/revisions"}],"predecessor-version":[{"id":72021,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71926\/revisions\/72021"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=71926"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=71926"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=71926"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=71926"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}