Earlier in Chapter 3, I discussed various steps you can take to standardize your T-SQL. This included being consistent when you format your T-SQL code, name your database objects, and comment your T-SQL code. While developing standards that allow for consistency when writing, naming, and documenting T-SQL, there is more to creating a comprehensive T-SQL coding standard.
In this chapter, I will discuss various advantages to implementing coding standards. I will also go over what types of factors you should consider when defining coding standards. When developing coding standards, you will also want to determine how to implement those coding standards. I will cover the basic process needed to institute these coding standards.
Why Use Coding Standards
There are many different things you can do to improve your code quality. When improving your code quality, you want to think about your end goal. I want my T-SQL code to run efficiently, can be debugged quickly, and is easy to understand. This is often the result of creating consistency in how you design and write your T-SQL code. One option is to implement coding standards.
Oftentimes, there are specific T-SQL practices that you know you want people to follow. Some of these behaviors are easily agreed upon and most people use them by default. However, there can be other T-SQL best practices that you know are not common in general or in your organization. You want to create a set of guidelines that allows everyone to easily know what they should be doing.
There are also certain things in T-SQL that you know you do not want to see in T-SQL code no matter the reason. Once again, some of these topics are not common, but others may also be pervasive. Either way, you want to clearly define this all as part of your coding standards. Using coding standards can minimize the back and forth regarding what types of T-SQL code is allowed or preferable vs. other code.
You can use coding standards to set boundaries for what is and is not allowed. I have come across times where software engineers or database developers want to use specific T-SQL because it is easier to read and write. However, there are times where the T-SQL code that makes sense easily to a human does not perform as well with SQL Server. While I want those same individuals to be able to come to me ask question about the current process, there are times where I have too many higher priority requests, and I do not have time to address their concerns.
It would be nice to have these guidelines agreed on prior to the standards being needed. This also should provide some reassurance that each topic has been discussed and multiple parties have come to the same conclusion that certain T-SQL code is either performing well for many scenarios or could be at a high risk to cause performance issues.
I know when I come into work my day is already full. Not only from the things I know I need to do but the last-minute requests and the one-off questions. The real goal to implementing coding standards is to make your life easier. That includes being able to rest easy knowing that quality T-SQL code is in your database. Ideally, you should be able to spend that time working on future database design enhancements or getting rid of technical debt.
Another factor affecting our lives is decision fatigue. This is the concept where a person gets worn down having to make decision after decision throughout the day. With the advent of technology, we experience that kind of fatigue every day. When it comes to making decisions regarding T-SQL coding, this type of decision fatigue can be even more overwhelming. There are many ways to accomplish the same thing in SQL Server. One of the other advantages to implementing T-SQL Coding Standards is that it can minimize the decisions a database developer needs to make when writing new code.
When you first implement coding standards, make sure to get buy-in from all parties. In many cases, you will not be able to have a unanimous decision. If you can, get a majority vote for any change you would like included in the coding standards. Another method is to make sure every individual agrees on the final standard before those standards are approved and implemented.
One of the main challenges with T-SQL in the workplace is that many people that are responsible for writing T-SQL are not database experts. This can not only cause frustration, but it can also contribute to poorly performing queries. One of the nicest and hardest aspects about T-SQL is how easy it is to write your first query. SQL is an abbreviation for Structured Query Language, and that is how simple queries are written. You can read them almost like a sentence.
However, this can give the false impression that what is happening with SQL Server behind the scenes is also simple. Most of us work in a high stress, fast paced environment. This often means we do not have time to stop and explain every decision we make. This can include why or why not certain T-SQL code should not be used. That is where the strength of T-SQL Coding Standards really shines. It allows you to have the conversation once to set up the ground rules. After those rules or standards are in place, T-SQL developers and engineers can write their code in any way they want if it meets the predefined standards.
I have heard software developers say that having defined coding standards when writing applications can be very helpful. It allows them to easily jump into any code, whether they wrote it or not, and immediately feel comfortable. This is because while they may know they have never looked at this set of code, their brain already sees similarities with other code they have written. It gets rid of the instantaneous response that the code they are looking at is not theirs. The benefit is that they can immediately jump into reviewing the code or making the necessary changes.
Now that you have determined that you want to define coding standards, the next step is to implement these coding standards. This is where things can get tricky. It will be tempting to want every stored procedure that goes through script review to adhere to these standards. However, some of the code you will see is already out in Production. I have found there are times, especially when others are under strict deadlines, that it is especially frustrating when a stored procedure is rejected merely because it does not follow coding standards.
A possible compromise is to give a conditional approval and request that a new user story be created so that the T-SQL code can be updated to fit the new coding standards. This gives the developer reassurance that they can meet the deadlines of their current sprint, and it also lets you know that this T-SQL code should be corrected in a future release to meet coding standards.
An easy way to handle these new user stories is to add them to the following sprint. This allows them to get cleaned up while you still remember what needs to be changed. This also makes sure that the others performing script reviews know that the effort to get all T-SQL code compliant with the coding standards is important to all parties.
What to Include in Coding Standards
Ideally, coming up with coding standards will provide you with a framework that minimizes your overall challenges with reviewing and deploying T-SQL code. You want coding standards to cover how T-SQL code should look, how T-SQL code should function, how T-SQL code should perform, and how T-SQL code should be understood.
Some of the basics of coding standards have already been discussed in Chapter 3. This includes formatting T-SQL code by creating standards as to how the T-SQL is written. You will also want standards indicating how to name T-SQL database objects. It is also useful to have standards indicating how T-SQL code should be commented.
T-SQL Design
Using SQL coding standards, you can define database design through coding standards. This can include how database objects should be organized including schemas. Coding standards can also indicate how data should be stored in the database. You can indicate what types of columns should be included in primary keys. It is also possible to indicate how tables should be clustered or when it is best to use clustered or non-clustered indexes.
ANSI STANDARD
One consideration is whether to keep your T-SQL code in compliance with ANSI standards. While SQL Server has some functionality that can be used, not all of this functionality is compliant with ANSI standards. In many cases, the SQL Server specific commands and functions can be rewritten using ANSI standard code. Making sure you write your T-SQL code as ANSI standard allows you to easily move to another relational database management system that is also ANSI standard compliant.
Normal Form
While covering T-SQL Coding Standards, you may also want to define design elements. This includes what and how data should be grouped together in tables and what information should be in those tables. There are various levels of normal forms , and you can use your T-SQL Coding Standards to indicate what those are. There are various types of database normalization that are commonly discussed. These include first normal form (1NF), second normal form (2NF), third normal form (3NF), and fourth normal form (4NF).
Each type of normal form builds on the one before it. The lowest-level normal form is the first normal form. This includes requiring that each table has a Primary Key and that each record only holds a single value.
Table Size
When creating tables, it is tempting to want to put all columns related to a specific item into the same table. This can cause tables to have many, many columns. One of the challenges of having a table with a significant number of columns is how the data is stored. This can ultimately lead to performance issues. It is possible to design your tables to prevent this kind of issue. In addition, properly designed tables make the overall database design more adaptable as applications change over time.
Name Value Pair
Working with SQL Server, the goal is to think about set-based actions. Keeping this is mind, you will want to be careful designing tables. You want to make sure that you do not minimize the number of columns to keep the table width small. You want to consider what kinds of columns are valuable. If you are using one column to define the value type and another column to define the actual value, you have designed a name-value pair relationship.
This type of design may be easier to understand and create simpler tables, but this design does not embrace the benefits of using SQL Server. When using name-value pairs, it becomes increasingly difficult to use indexes. Therefore, SQL Server will need to look through more data to pull back the necessary values.
Primary Key
When creating and designing tables, you will want to think about what type of data will be stored. This not only has to do with how the data is ordered but also the table’s relationship to other tables. In many cases, it is ideal to require primary keys as part of your coding standards. While a primary key is not required in all scenarios, it should help clarify what fields are used to define relationships to other tables.
Foreign Key
SQL Server is a relational database management system, and one of the main benefits of SQL Server is the relationships between tables. However, you need to specify the relationships between tables so that SQL Server can ensure that valid data is saved across the tables. When this happens, SQL Server checks the relationship between the table storing the values and the referencing table. SQL Server does this using a foreign key. Because SQL Server knows that it can trust the relationship, SQL Server can often filter out data more efficiently and find data faster. Therefore, it may be a good idea to specify that foreign key relationships are part of your T-SQL Coding Standards.
Non-clustered Index
I have found that non-clustered indexes are a more highly debated topic than I would have ever expected. One of the challenges of using non-clustered indexes is that there is a cost associated with recording the data as part of the index or updating the index as the values change. However, SQL Server uses indexes to quickly find the data referenced by various queries. You will want to make sure that the benefit of using non-clustered indexes outweighs the cost.
One of the ways that you can do this is to make sure that you properly define your indexes. Due to the cost associated with maintaining indexes, you will want to make certain that any index you create is regularly being used by the available queries. You may also want to define how to determine which columns should be part of the index and which columns should be pulled back as part of the index.
Constraint Definition
Your tables can have one or more columns. Each of those columns holds some type of information. When it comes to that information, you may know that only certain values can be stored in that column. Using foreign keys can keep the data integrity in some situations, but there are other times that it may not make sense to use a foreign key. In those cases, you can specify a constraint on the record. The constraint can limit what type of information can be stored in those columns.
However, forcing SQL Server to manage those constraints puts an extra load on SQL Server. So, you may want to consider minimizing the use of constraints to limit what type of data can be stored in the database and rely on the application code to provide good data. Another use of constraints is to specify default values for certain fields. This can happen for columns where you know the most likely value that will exist when a record is created. For instance, you may want to specify a default value of True for an IsActive column as most records created will be active at the time they are created. Once again, this can be handled by the application, but you may also decide to enforce this as part of your T-SQL Coding Standards.
It is important to consider T-SQL Coding Standards that encourage good design practices. This includes determining how closely you will adhere to ANSI standards. There are other topics to include such as choosing the minimum normal form allowed as part of your database design. This decision may affect other aspects of your T-SQL design including primary keys, foreign keys, and non-clustered indexes. You may also consider how you want to handle constraints. Once you have determined the T-SQL Coding Standards you want to enforce for good T-SQL design practices, you may want to decide what coding standards are needed with regard to the overall performance of T-SQL.
T-SQL Performance
After determining the T-SQL Coding Standards related to database design and implementation, you will want to determine what standards you need to help minimize and address any performance issues that may come up. This includes creating T-SQL Coding Standards that help make sure SQL Server does not get bogged down with unnecessary information. You will also want to consider how T-SQL code needs to be or not be written to minimize performance overhead.
Select Necessary Data
SQL Server can store quite a bit of data, and SQL Server can also retrieve large quantities of data. There are many scenarios where applications are retrieving data. When first learning about a database and the associated tables, it is often tempting to select all the columns in a table. While this is the easiest method to return data, it is not usually ideal.
First is the syntax used to select all rows by using the asterisks. The issue with this method is if the columns in the underlying object change, the code may not work as expected. Even worse, the code may not provide an error and users would not know they were getting bad data.
The second issue that can happen is more common. When you pull back all columns, there is considerably more work put on SQL Server. Not only must SQL Server return everything for all rows of data returned, but it is likely that SQL Server will not be able to use any indexes to return this data. This is the equivalent to reading this entire book each time you want to study one specific section. You would learn what you needed, but you would also spend a considerable amount of time reading topics that you were mostly likely going to disregard since they were not relevant for your needs at that time.
Sargeable
Query Using Non-sargeable Criteria
Execution Plan from Non-sargeable Criteria
Properties for Index Scan from Non-sargeable Criteria
Query Using Sargeable Criteria
Execution Plan from Sargeable Criteria
Properties for Index Seek from Sargeable Criteria
The actual number of rows read for Listing 9-2 is 1 as shown in Figure 9-4. Comparing 1 row read for Listing 9-2 to 32,242 rows read for Listing 9-1, it becomes very clear which query is more efficient.
We can also look at the total CPU usage for both queries to see how each query performs. If I run both queries in Listings 9-1 and 9-2 at the same time, 98% of the total execution plan of both queries is spent on the query in Listing 9-1. I can also use SET STATISTICS TIME ON to find the CPU time and elapsed time. The CPU time is the total amount of time the query execution spent on the CPU or CPUs. The elapsed time is the total time for the query to execute. I ran both queries several times and found that the average CPU time for Listing 9-1 was 15 milliseconds and the average elapsed time for Listing 9-1 was 17 milliseconds. Each time the CPU time and elapsed time for Listing 9-2 was 0 millisecond. Comparing both the CPU time and the elapsed time, we can clearly see that Listing 9-2 performs better than Listing 9-1. It is also important to note that the total elapsed time could be less than the total CPU time if there are multiple CPUs.
Implicit Conversion
The data in SQL Server has a data type. These data types are discussed further in Chapter 1. When using these data types, it is important to make sure you keep the data types consistent. Otherwise, SQL Server must undergo an implicit conversation. This is where SQL Server converts one data type to another behind the scenes. This can cause additional load on SQL Server. In addition, there is a risk that the implicit conversion will fail. I see this most when an identification type column saves mostly integer values but occasionally strings.
SET NOCOUNT ON
Stored Procedure Using SET NOCOUNT ON
When SET NOCOUNT ON is included before the query code is executed, the stored procedure can execute in a couple fewer milliseconds.
NULL Values
Most of the T-SQL Coding Standards exist to help improve overall performance for SQL Server. However, some T-SQL coding standards exist to help improve query results or make it easier for people to write code that works as expected. One of the issues with NULL is that it does not work intuitively. NULL is treated as an unknown value in SQL Server. That means that NULL is neither True nor False. Therefore, where there are fields that can contain NULL values, the way these fields must be treated is different.
In addition, NULL values may indicate that tables were not designed efficiently. Before ending up in these scenarios, it may be beneficial to consider how the table should be designed. You will want to make sure that the table really needs to include nullable columns. This is also due to how data is stored and how indexes are created. You will want to consider how this data will be used.
NOLOCK
There are also factors involved in T-SQL Coding Standards. Some of these include things that you may consider best practices. Some of the very common best practices include highly debated topics like if or when to use the NOLOCK hint in queries. One of the reasons I mention this specifically is that there are at least two very distinct groups involving NOLOCK. On the one hand, many database administrators view not using NOLOCK is a best practice. On the other hand, there are many developers that see the opposite side that the use of NOLOCK is a best practice. This difference in perspective is based on a trade-off between data accuracy and performance.
The idea behind NOLOCK is to improve query performance by reducing the number of locks. In the case of NOLOCK, the reduction in lock activity is on the SELECT statement. Actions such as INSERT, UPDATE, and DELETE are not affected by NOLOCK. However, using NOLOCK on a SELECT statement can return results more quickly when a data modification action is holding a lock on that same table that the SELECT statement is trying to access.
Insert Record into dbo.Recipe
Query to Find Recent Recipes
Query to Find Recent Recipes Using NOLOCK Hint
Recipes Created After August 1, 2019
RecipeName | RecipeDescription |
|---|---|
Lee’s Hamburgers | The best hamburgers |
Acorn Squash | Acorn squash with butter and brown sugar |
Looking at the preceding results, you can see that the record inserted in Listing 9-4 is included in the results. However, this record has not been committed to SQL Server. I could roll back the transaction from Listing 9-4 and undo the insert. This would cause the results from Table 9-1 to be inaccurate. This discrepancy where queries return results that are not fully committed to the database is known as a dirty read. This is the largest risk when using NOLOCK in your queries. Before deciding that you should use NOLOCK in your T-SQL code, make sure that the business, including your end users, understands the potential risks of using this hint.
RECOMPILE
Adding WITH RECOMPILE to a Stored Procedure
While this is a quick fix, it can increase the CPU load on the server. I would suggest trying to redesign the query if possible. If not, you may need to change the stored procedure to only recompile for the values that fall outside of the majority.
For your T-SQL Coding Standards, it is important to include guidelines related to writing T-SQL that performs well. You will also want to include some rules for what actions are or are not allowable in order to try and fix poorly performing T-SQL. The last part of your T-SQL Coding Standards is a catch-all generally related to security, futureproofing, and maintainability.
T-SQL Usability
Having T-SQL Coding Standards that encourage good database design and address performance issues is a good start. However, you are also going to want some coding standards to help with other aspects of T-SQL. Some of this includes having T-SQL Coding Standards for potential security issues. There are other coding standards that you can put in place that are best practices but may not affect performance. They may be in place either to help keep your T-SQL functioning even if the underlying data structure changes or encourage T-SQL commands that help make your T-SQL code easier to read or understand.
Linked Server
Linked servers are one of those things that may be overlooked as part of the coding standards. It seems unlikely that you will need to use linked servers, but linked servers have a way of seeming necessary when you least expect it. That is why it is a good idea to include specifications about how to handle linked servers as part of your T-SQL Coding Standards. Due to the general security risks associated with using linked servers, it is recommended that you do not use linked servers unless necessary. I would suggest clearly defining exactly what is considered necessary. This will help reduce any contention should you find yourself wanting to implement linked servers.
Column Definition
One of the key factors of writing T-SQL is specifying what columns you are using or affecting. While this is related to making sure you only interact with the data you need, there are other benefits to requiring your T-SQL code to explicate state column names. When inserting data into SQL Server, it is possible to not state the column names or order you are using for the insert. If you are inserting data into every column in the table and you are inserting the data in the same order as the columns in the table, you will not have any issues. However, this can cause stored procedures or other T-SQL code to stop functioning if columns are added or removed from the table or if the column order is changed in the future. Therefore, a good habit is to explicitly state your column names on all T-SQL. For these examples, this specifically includes selects and inserts.
BETWEEN
Query with BETWEEN
In terms of readability, you may decide that all ranges that cover inclusive values should be written using BETWEEN. This may be preferable to using greater than or equal to and less than or equal to get the same range.
Stored Procedure Parameters
While improving code readability is important, there are times that improving readability of T-SQL may make debugging the code harder when it comes to performance issues. This type of situation can happen depending on how you configured your stored procedure parameters. You can either define each parameter individually or you can create a user-defined table that you can use to pass in a multitude of variables. If you choose to use a user-defined table, your T-SQL code may be cleaner, but it may take more time to debug performance issues going forward. In addition, your stored procedure will work differently as you are working with a table variable instead of handling each data field individually.
UNION
You will have times where you want to combine data from two different queries. This type of combination involves appending one data set to another. When these two data sets share the same number of columns with the same data types, there is the possibility to use a UNION statement to combine this data. The advantage of using a UNION is that the queries are readable and you can easily tell by reading the T-SQL code what is happening. The challenge with using UNION is that the performance of a UNION may be worse than the performance of each query independently. In addition to UNION, there is also the UNION ALL functionality. The main difference between a UNION and a UNION ALL is how the data is returned. In a UNION statement, a distinct set of records is returned, whereas in a UNION ALL statement, the actual number of records from each separate query is included in the result set. When designing your coding standards, you will want to decide if UNION and UNION ALL statements are allowed. You may also want to decide if there are specific scenarios where they would or would not be allowed.
CAST
When writing code, you find yourself in a situation where you want to change the data type from one value to another. Changing data types can be important with performing mathematical functions on integer values. Due to how SQL Server performs mathematical operations, if you divided one integer by another integer, you will get an integer as the answer. If you want SQL Server to provide a decimal as a result, you will need to change the data type from an integer to a decimal. Similarly, you may want to display a string of text as part of a SQL Server query. However, if you include an integer in the string, you will get an error when SQL Server tries to execute the query. However, if you change the integer to a varchar data type, you will be able to parse the string correctly.
Cursor
Cursors are a source of controversy when it comes to databases. In Chapter 5, I went over set-based operations which is where SQL Server performs best. The issue with cursors is that they do not take advantage of those set-based operations. This can cause considerable overhead when it comes to handling data. To compound the challenges with cursors, they are written in a way that is more alike to how application code is written than T-SQL. This can make it very tempting to use cursors as it may be easier to understand how to write the code to get the desired results. Like other factors of developing your T-SQL Coding Standards, you may not be able to rule out cursors entirely. If so, try to create some criteria where you believe cursors would be beneficial and limit cursor usage to only those situations.
ORDER BY
It is often tempting to want SQL Server to get all of the data exactly how your application needs the data. While SQL Server can do this, you want to consider if it is worth the cost to SQL Server to do all the work you are requesting. Such can be the situation when you want data to be sorted for your application. SQL Server can sort the data using the ORDER BY statement, but it may be a better use of resources to have the application sort this data. If you decide that is how you want to handle sorting data, then it may be a good idea to include in your T-SQL Coding Standards that data should not be sorted in SQL Server. If you know there are specific situations where this will be unavoidable, then you can state in your coding standards the exact scenarios where using an ORDER BY is allowed in your T-SQL code.
Case Statement
T-SQL code can be versatile. While you may choose to keep your T-SQL code limited to application functionality, database code is also used for other purposes such as reporting. In many applications, you may be both processing transactions and allowing your users to search and filter data. This type of activity can be a reporting activity. Oftentimes, users do not want to see data in the same manner the data is stored. For instance, you may have a table that includes a status type. A given record may have several statuses over time. However, a user may want to see one line with all of the statuses for that specific record. This is where you would want to use a case statement so that you could convert multiple rows into various columns. Performing this type of activity is not a purely transactional use of T-SQL, and you may want to limit this type of behavior as part of your coding standards.
TRY… CATCH
Once you have determined what you want to include in your T-SQL Coding Standards, the next step is to get buy-in from your coworkers or other departments that will also be writing T-SQL. Getting everyone to agree to a set standard can solve many issues. First, it helps those that are onboarding to the company. When they review code, they will see a consistent code style. This will allow them to focus on what the T-SQL is doing instead of trying to interpret how the T-SQL code is written.
If you create a thorough and well-defined coding standard, there should be less back and forth during script reviews when there is T-SQL that is not believed to be best practice. With the T-SQL Coding Standard in place, all parties know what is allowable and what code is deemed as undesirable or bad practice. New hires will also know what T-SQL is acceptable. Everyone can be held to the same standard, and it allows others to see that the rules for writing T-SQL are fair.