Before I start talking about how to write queries for complex scenarios, I want to focus on how to write good T-SQL. This is not about writing code that performs well. This is about writing T-SQL code that reads well. I want to go over how to write code so that it is easily readable to others and to yourself. If you are like me, you’ll get to a point in your career where you look at your own code and do not immediately understand all the logic involved.
Writing T-SQL that is understandable helps you and your company. Many other coding languages have standards or best practices, and I believe that T-SQL should be no different. While the primary goal of writing T-SQL may be to implement a piece of functionality, no less important is the secondary goal of making sure that your T-SQL makes sense. Over time, code changes or bugs are found. The more readable and understandable your T-SQL code is, the easier it is to modify or troubleshoot.
Formatting T-SQL
How T-SQL looks when it is written can be as significant as what is written. Like other application code, there is always a chance that someone will need to look at your code in the future or you will need to look at theirs. If I am not writing new code, I am looking at pre-existing code to understand the purpose, to debug the code, to performance tune the query, or to update business logic. Depending on the reason why I am reviewing the code, I will generally determine what is important to me at the time.
If I am looking at the T-SQL code to understand what the code does, I will look at what tables are involved first to understand what application may be using this T-SQL code. I am not concerned with how tables are joined together as I expect that to be functioning correctly. Though incorrect logic on table joins can be a cause of queries returned very unexpected results. Next, I will look at the criteria used to filter the results of the T-SQL code. Only at the very end of my analysis will I review the column returned in the query. Oftentimes, I am only concerned with the columns if there is special business logic involved. Applying this thought pattern to writing simple
queries, you can see that I list the column names on one line in Listing
3-1.
SELECT IngredientID, IngredientName, DateCreated, DateModified
FROM dbo.Ingredient
I format my code this way because I want to be able to quickly see all the action items that are happening through the FROM clause and the WHERE criteria. If I were to create one line per column, it would be harder for me to see how tables were related and what conditions were being applied to those relationships. As you can see in Listing
3-2, I have altered how the
columns in the SELECT clause are displayed.
SELECT rec.RecipeName,
rec.RecipeDescription,
rec.IsActive AS 'RecipeIsActive',
ingr.IngredientName,
ingr.IsActive AS 'IngredientIsActive'
FROM dbo.Recipe rec
INNER JOIN dbo.RecipeIngredient recingr
ON rec.RecipeID = recingr.IngredientID
LEFT OUTER JOIN dbo.Ingredient ingr
ON recingr.IngredientID = ingr.IngredientID
AND ingr.IngredientName <> 'Italian Sausage'
Listing 3-2Queries with Joins
For this query, the columns are listed line by line. This is because I have altered something about the actual column being pulled back. If there is ever a time I am aliasing a column or adding special logic to a column, I will change how I format the columns in the SELECT statement. For these scenarios, I will create one line or more per column depending on the complexity of the logic. If you also notice on the last two lines of the query, I have two join conditions. I usually indent any join condition after the first one as I want it to be immediately obvious there has been more than one condition applied to a join.
There are many times I am reviewing T-SQL code to troubleshoot why the T-SQL code may be returning incorrect results. When there are issues with the results being returned, I start with a user story indicating what is happening that is incorrect. In these cases, I will go immediately to the WHERE clause to double-check the logic and confirm that it is correct. Once I have confirmed that logic, I look at the join criteria to confirm that the tables are joined correctly. I use the same process to troubleshoot the code as I use to understand T-SQL code; I will look at the SELECT statement last focusing on any primarily columns with any special logic. Looking at the
query in Listing
3-3, I can scan first the FROM clause, then the WHERE clause.
SELECT
(
SELECT rec.RecipeName
FROM dbo.Recipe rec
INNER JOIN dbo.RecipeIngredient recingr
ON rec.RecipeID = recingr.RecipeID
WHERE recingr.IngredientID = ingr.IngredientID
) AS 'RecipeName',
ingr.IngredientName,
ingr.IsActive,
ingr.DateCreated,
ingr.DateModified
FROM dbo.Ingredient ingr
WHERE IngredientName LIKE '%Tomato%'
ORDER BY RecipeName, ingr.IngredientName
Listing 3-3Queries with Subqueries
This allows me to immediately determine this query is dealing with ingredients that are like tomato. With the columns in the SELECT statement listed on individual lines, I am immediately aware that there is some special logic involved in the part of the SELECT statement. I have also indented the subquery portion of the logic which helps that subquery stand out even further. Now that I am trying to troubleshoot potentially inaccurate results, I can quickly dig into what can be causing the issue. Depending on the bug reported, there is a very high possibility the issue is either with the WHERE clause or the first column returned in the SELECT statement. Analyzing the
view created in Listing
3-4 shows a different conclusion.
CREATE VIEW dbo.AvailableMeal
AS
SELECT meal.MealTypeName, rec.RecipeName, rec.ServingQuantity, ing.IngredientName
FROM dbo.Recipe rec
INNER JOIN dbo.MealType meal
ON rec.MealTypeID = meal.MealTypeID
INNER JOIN dbo.RecipeIngredient recing
ON rec.RecipeID = recing.RecipeID
INNER JOIN dbo.Ingredient ing
ON recing.IngredientID = ing.IngredientID
In the T-SQL code for this view, I still go first to the FROM clause. I immediately identify that there are several joins. In addition, there is no WHERE clause, and I can also quickly determine there is no special logic in the SELECT statement as all columns are not on their own line(s). Matching the information about this view to any potential bug I am researching, I know the most complex part of this query is the join logic. If the joins are correct, I can quickly rule out the SELECT statement if the view is returning too many results or I can rule out the WHERE clause if the data returned is incorrect. A similar pattern can be followed when creating a
function as shown in Listing
3-5.
CREATE FUNCTION dbo.IngredientsByRecipe (@RecipeID INT)
RETURNS TABLE
AS
RETURN
(
SELECT meal.MealTypeName, rec.ServingQuantity, ing.IngredientName
FROM dbo.Recipe rec
INNER JOIN dbo.MealType meal
ON rec.MealTypeID = meal.MealTypeID
INNER JOIN dbo.RecipeIngredient recing
ON rec.RecipeID = recing.RecipeID
INNER JOIN dbo.Ingredient ing
ON recing.IngredientID = ing.IngredientID
WHERE rec.RecipeID = @RecipeID
);
Listing 3-5Create a Function
In the preceding function, I can quickly identify several joins in the FROM statement and one criterion in the WHERE clause. If the function is returning only results for the recipe provided, it is highly likely that any bug that has been found is related to the join conditions.
My process for performance tuning queries is handled differently, and I will discuss those differences further in Part II of this book, Building Performant T-SQL. When it comes to reviewing the T-SQL as part of performance tuning, I will focus on what tables are used. If there is more than one table, I will also look at how those tables are joined together. My final focus will be on what columns are being used and how those relate to indexes that already exist.
I also review T-SQL code when updating logic inside of the T-SQL code. Either new functionality has been added, changed, or removed, I will need to modify the T-SQL code to mirror those modifications. Depending on the modifications, it may be as simple as looking at the fields in the SELECT clause and changing what fields are displayed or how calculations are performed. Other times, I may need to look at the FROM clause and add or remove tables from join conditions. In some cases, I need to update criteria in the WHERE clause to handle the new business requirements. Such is the case in Listing
3-6 which shows the creation of a
table-valued parameter. One of the first things I notice is the lack of a WHERE clause in this stored procedure. This is also where some complexity is added when dealing with user-defined table types.
CREATE PROCEDURE dbo.UpdateRecipeMenuType
@RecipeMeal RecipeMealType READONLY
AS
SET NOCOUNT ON
UPDATE rec
SET MealTypeID = meal.MealTypeID
FROM dbo.Recipe rec
INNER JOIN @RecipeMeal recmeal
ON rec.RecipeName = recmeal.RecipeName
INNER JOIN dbo.MealType meal
ON recmeal.MealTypeName = meal.MealTypeName
Listing 3-6Create Table-Valued Parameter
It is very likely that the user-defined table is being used to filter the data on the join. However, just looking at the code, it is very difficult to tell how the application uses this stored procedure. Because of the user-defined table type, the amount of work required to enhance the logic on this stored procedure is significantly increased. I need to be aware of how data is being passed in the table-valued parameter, but I also need to consider how the data being passed to this table-valued parameter can change over time. As database administrators are often the ones managing the T-SQL code long after the application has been deployed, I find it best to design T-SQL code so that it can be easily supported going forward. As you can see in Listing
3-7, when creating a common table expression I use the same method, but I indent the query inside of the
common table expression. Once again, I use this indent to help signify that special logic is happening in a given section.
WITH cte_meal AS
(
SELECT meal.MealTypeName, rec.RecipeName, rec.ServingQuantity, ing.IngredientName
FROM dbo.Recipe rec
INNER JOIN dbo.MealType meal
ON rec.MealTypeID = meal.MealTypeID
INNER JOIN dbo.RecipeIngredient recing
ON rec.RecipeID = recing.RecipeID
INNER JOIN dbo.Ingredient ing
ON recing.IngredientID = ing.IngredientID
)
SELECT meal.MealTypeName, meal.RecipeName, meal.ServingQuantity, meal.IngredientName
FROM cte_meal meal
Listing 3-7Create a Common Table Expression
When defining my own personal style, I have learned that my overall objective is to have a query fit in a small enough area that I can quickly and efficiently find the part of T-SQL code that I am trying to review. When it comes to designing your own standard, you will want to think about what your overall objective is.
In many companies, junior team members will be hired. Some of these junior team members will be new to SQL Server, and it will take any new hire some time to understand how the applications in your business work. When designing an internal T-SQL coding standard, you will want to take into consideration what formatting conventions should be followed that will help new employees quickly learn your company’s systems and data flows.
Another factor in developing a T-SQL formatting standard is creating a standard that employees can easily remember or reference. You want your team members to be able to succeed when implementing a new standard and not be overwhelmed by all the nuances when writing the code. This is especially important if all T-SQL code must be written manually, and your company does not have software that can automatically format the T-SQL code for you.
There are also some formatting considerations for inserts, updates, and deletes. In Listing
3-8, there is a sample INSERT statement. In this example, I have listed all the
column names for an INSERT.
INSERT INTO dbo.IngredientCost (IngredientID, ServingPortionID, Cost, IsActive, DateCreated, DateModified)
VALUES (1, 1, 5.98, 1, GETDATE(), GETDATE())
Listing 3-8Query to Insert Data
While listing the column names may seem unnecessary, this formatting standard makes what data is being inserted easily identifiable, but this format also protects the application code from future issues if columns are added or the column order changes. The format to update data is simple as you can see in Listing
3-9. I am still following the same formatting for reserved
words and referencing user-defined database objects.
UPDATE dbo.AvailableMeal
SET IngredientName = 'Spicy Italian Sausage'
WHERE RecipeName = 'Spaghetti'
Listing 3-9Simple Query to Update Data
You can also see that I consistently pad the operator. I have done this in several examples. Like other decisions I have made for formatting, I believe adding a space before and after the equal sign improves the readability of the T-SQL code. I have also included Listing
3-10 to
show how to format deleting data in T-SQL.
DELETE FROM dbo.Ingredient
WHERE IngredientName LIKE '%tomato%'
Listing 3-10Query to Delete Data
This example is for a simple delete, and when joins are involved, the format to delete data can become even more involved. Deleting data often seems more significant than other data manipulation activities in SQL Server. There are times where you may want to write a query to systematically delete data from a table. When I first started writing queries to delete data from tables, I would start with writing the SELECT statement. This would help with several factors. I could clearly see what data would be affected. I could also get a row count for the number of records I expected to be affected. Once I had the SELECT statement written, I could easily modify the code to delete the necessary records. The query in Listing
3-11 shows the
SELECT statement I would use to prepare for deleting data records.
SELECT rec.RecipeID, rec.RecipeName
FROM dbo.Recipe rec
INNER JOIN dbo.MealType meal
ON rec.MealTypeID = meal.MealTypeID
WHERE meal.MealTypeID = 2
Listing 3-11Select Recipes with MealTypeID of 2
In this case, I am preparing to delete records from the dbo.Recipe table that have a MealTypeID of 2. Using the result from Listing
3-11, I can confirm what data I am deleting and how many records I can expect to delete. After I reviewed the results from Listing
3-11, I can update my T-SQL code to delete the records. In Listing
3-12, I have replaced the SELECT statement with a DELETE
FROM referencing the table alias for dbo.Recipe.
DELETE FROM rec
FROM dbo.Recipe rec
INNER JOIN dbo.MealType meal
ON rec.MealTypeID = meal.MealTypeID
WHERE meal.MealTypeID = 2
Listing 3-12Delete Recipes with MealTypeID of 2
Over the years, I have found it best to wrap complex DELETE statements in an explicit transaction. By default, when we execute T-SQL code on SQL Server, we are using an implicit transaction. That means that SQL Server knows to commit the transaction automatically after we execute the transaction. We also have the option of specifying an explicit transaction. This is where SQL Server will not complete the execution of the T-SQL code until we send the COMMIT to SQL Server. Particularly with deletes, but often with any complex code, I have found that it is best to always be careful. I know I will either run the BEGIN TRAN and the query without the COMMIT or end the whole statement with a ROLLBACK when I first execute a query like the one in Listing 3-12. This allows me to verify the number of records affected. This is my last chance to confirm that my query is working as expected. If I were to get a different number of records returned than the one I expected I could issue a ROLLBACK, and SQL Server would undo the code that I had attempted to run. This is only possible while using explicit transactions. It is also worth mentioning that if you are using multiple levels of transactions, referred to as nested transactions, the functionality of the ROLLBACK may work differently than expected.
This leads to another consideration when designing your T-SQL formatting. You will need to know how your T-SQL code will be written and stored. If all T-SQL code will be written manually and cannot be formatted by a third-party tool, then you may need to keep the coding standard very simple and limit criteria for special situations. However, if you have a third-party tool available, you can create a format that is as complex as that tool can handle.
One of the factors that makes a good developer or engineer is discipline. The same is true when writing T-SQL. To certain extent, it does not matter what specific style of formatting you use. What does matter is being consistent with that formatting. Ideally, you should try to get your entire team to agree to a standard method of formatting T-SQL.
When your whole team writes T-SQL code the same way, it makes it much easier to look at someone else’s code. You no longer must translate both the format and the coding style which can lead to quicker analysis and quicker problem resolution. Formatting your T-SQL code to make it more readable is all about how the code will look.
You should move toward having consistent formatting for all T-SQL code written at your company. Since the formatting will be the same regardless of who writes the code, it also means that everyone reading the code will become more and more familiar with quickly interpreting their code. As you can see in Listing
3-13, I am creating a
user-defined table type.
CREATE TYPE RecipeMealType AS TABLE
(
RecipeName VARCHAR(25),
MealTypeName VARCHAR(25)
);
Listing 3-13Create User-Defined Table Type
I also use a consistent format in Listing
3-14 when creating a
temporary table.
CREATE TABLE #TempAvailableMeal
(
MealTypeName VARCHAR(25),
RecipeName VARCHAR(25),
ServingQuantity TINYINT,
IngredientName VARCHAR(25)
)
Listing 3-14Create a Temporary Table
You can also see I use the same format when creating a
table variable in Listing
3-15.
DECLARE @TempAvailableMeal TABLE
(
MealTypeName VARCHAR(25),
RecipeName VARCHAR(25),
ServingQuantity TINYINT,
IngredientName VARCHAR(25)
)
Listing 3-15Create a Table Variable
Comparing Listings 3-8, 3-9, and 3-10, you can quickly see that I use a consistent format when creating tables. This will allow anyone else and myself to see code in this format and quickly know that this code is creating a table.
There are at least two things to consider when it comes to
capitalization. There is capitalization related to keywords and capitalization for all other terms. I prefer to uppercase all reserved words. The list of T-SQL reserved words can be quite lengthy. I chose to consider any word that is not part of the database schema as considered a reserved word. I capitalize database objects and column names as they were created. When I create table aliases, I use lowercase. You can see in Listing
3-16 how the reserved words are uppercased and the first letter of each word for a database object
name is capitalized.
CREATE TRIGGER dbo.LogIngredientCostHistory
ON dbo.IngredientCost
AFTER INSERT, UPDATE
AS
IF (ROWCOUNT_BIG() = 0)
RETURN;
INSERT INTO dbo.IngredientCostHistory (IngredientCostID, Cost, DateCreated)
SELECT inserted.IngredientCostID, inserted.Cost, GETDATE()
FROM inserted;
Listing 3-16Create a DML Trigger
One of the other factors to consider when determining formatting standards for T-SQL is if and how to use aliasing. Aliasing is a method that allows you to create a shortened name to reference a table. It is also possible to alias column names in your select statements when writing queries. How the aliased values can be used depends on whether a table or a column is being aliased. However, the overall concept is the same. If an alias is created for a table name, the alias must be used in place of the table name for the entirety of the given query. For columns, aliases are often used to rename a column or make the output more user-friendly. Often the column aliases are not referenced.
The only time I will reference a column alias when writing my queries is if I must order by a column that has been aliased, particularly if that column has more logic than only renaming the original column name. It is possible to provide a numeric representation of the column order in the ORDER BY statement instead of proving a column name. While this is a quick method to sort data, this is not recommended to be used as part of permanent application code as the order of columns in the SELECT statement can be changed without the ORDER BY clause getting updated. This would cause the data returned to potentially be ordered differently than intended.
Another debated topic when it comes to T-SQL code formatting is how to format commas when writing code. There are those who prefer to put the commas at the beginning of each line. This can improve readability and help others quickly identify that this is one of multiple lines. Adding the commas at the front of each line does simplify debugging as it is easy to comment out a single line that begins with a comma and the rest of the query will parse correctly. I prefer adding my commas at the end so that I can ignore the commas and focus on the columns being returned in a query.
When it comes to multiple criteria in the WHERE clause, I do prefer the same type of methodology as those that prefer the commas at the beginning. I want the WHERE clause to have the same style as the SELECT statement. However, this does not always mean that the logic for each line is the same as it does for a comma. Some lines in a WHERE clause can have an AND or an OR instead of a single comparison. This additional logic will require you to make some decisions about what is comfortable for you to read. I do like those to stand out that I can be aware of how the logic is being used for each line in the WHERE clause.
There are times when working with T-SQL code that you will write complex code. This code may include subqueries or logic in the WHERE clause involving ANDs or ORs. If there is logic like a subquery or a mixture of ANDs and ORs in the WHERE clause, I will wrap those in parentheses. I will indent all the code inside the parentheses so that it is easy to identify what logic is wrapped together. There are also times where there are multiple join conditions between two tables. If there is more than one join condition between two tables, I will indent every join condition except the first one so that others can easily tell there are multiple join conditions between two tables.
You will also want to consider how to format T-SQL code when adding additional levels of logic to your code. There are various reasons for T-SQL code blocks including TRY… CATCH blocks, IF… ELSE statements, BEGIN…END, or other reasons to segment code. For these scenarios, I indent the interior of the code block. If code blocks end up being nested, I will indent each subsequent code block. I prefer to indent my code blocks so that others reviewing my code can see the parent activity such as the WHILE loop in Listing
3-17. Once you see the first level of indentation, you know that all the logic that has been indented belongs to the same
code block.
DECLARE @RecipeID INT,
@RecipeName VARCHAR(25),
@message VARCHAR(50);
PRINT '-------- Recipe Listing --------';
DECLARE recipe_cursor CURSOR FORWARD_ONLY
FOR
SELECT RecipeID, RecipeName
FROM dbo.Recipe
ORDER BY RecipeID;
FETCH NEXT FROM recipe_cursor
INTO @RecipeID, @RecipeName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Ingredients For Recipe: ' + @RecipeName + '-----'
SELECT ing.IngredientName,
srv.ServingPortionQuantity,
srv.ServingPortionUnit
FROM dbo.Ingredient ing
INNER JOIN dbo.RecipeIngredient recing
ON ing.IngredientID = recing.IngredientID
INNER JOIN dbo.ServingPortion srv
ON recing.ServingPortionID = srv.ServingPortionID
WHERE recing.RecipeID = @RecipeID
FETCH NEXT FROM recipe_cursor INTO @RecipeID, @RecipeName
END
CLOSE recipe_cursor;
DEALLOCATE recipe_cursor;
Listing 3-17Create a Cursor
Formatting T-SQL code consistently improves readability for yourself and anyone in the future who will need to review your code. Well-formatted code can help provide clarity when troubleshooting, performance tuning, and code enhancing. Creating a T-SQL formatting standard in your organization can also help when onboarding new employees or training junior database developers. Once T-SQL formatting standards have been determined, you will want to consider what steps need to be taken to create naming conventions for your T-SQL code.
Naming T-SQL
When you write T-SQL, you have options on how to write that code. You will most likely create persistent objects in T-SQL. Regardless of your purpose, following a good naming policy makes it easier for others to understand the purpose of your T-SQL code. Ideally, your team members should be able to determine the purpose of your code based on the object name where the code lives. This is particularly helpful for new or less experienced employees.
The same practices for formatting T-SQL are similar for when choosing your naming convention strategy. One of the aspects that comes with naming conventions is look and feel. This can involve the capitalization used for objects. There are various options available when providing a case for database objects. The main choices are camel case or pascal case. The primary difference between these casing styles is the first letter of the database object. In Listing
3-18, you will see writing a
query using camel case.
SELECT recipeID, ingredientID, dateCreated, dateModified
FROM dbo.recipeIngredient
Listing 3-18Query with Camel Case
Conversely, you can see in Listing
3-19 what pascal case looks like when writing the same query.
SELECT RecipeID, IngredientID, DateCreated, DateModified
FROM dbo.RecipeIngredient
Listing 3-19Query with Pascal Case
In addition, there is also an option where the first letter is not capitalized and there is an underscore between words. I am generally not a fan of non-alphabetic characters in database names, but I do know some people that prefer underscores. If you want another alternative, Listing
3-20 shows how tables and columns would need to be named has been referred to as snake case.
SELECT recipeID, ingredientID, date_created, date_modified
FROM dbo.recipe_ingredient
Listing 3-20Query with Snake Case
When determining which case to use for your naming convention, make sure to also be aware of the collation for your database and if any tables have a special collation. Being aware of case sensitivity will help you ensure that your naming conventions and your formatting standards line up.
This can also involve where objects appear in Object Explorer. Some of this has to do with what type of object is being named and who will be looking for these objects. If you are wanting to find objects by their use, you may want to specify schema names that group those objects together. This can be particularly useful for applications or services. Depending on what type of troubleshooting is expected, database objects, particularly stored procedures, can be named with the action they are taking. This would allow for an easy search of stored procedures that are selecting data vs. inserting data. However, there is also another option where the main table affected could be the first word in the stored procedure name. This would allow someone to search in Object Explorer by affected table to see all the stored procedures that exist.
Another consideration is whether reserved words can be used when naming database objects. If reserved words are used in database object names, you will want to add another word to the object name so that brackets are not needed when referencing the object name.
When determining naming conventions, you may also want to consider if there are columns in tables that will have the same names as other columns in tables. Some of these types of columns include specifying the date a record was created, the date a record was last updated, and if the record has been soft deleted. For tables that are specifying status or types, these tables can have columns with a status or type name and an associated description. You may decide that you want all these columns to have the exact same name in all tables with these columns.
When writing my queries, I prefer not to alias my column names, if possible. I also am not displaying more than one date created or updated. Therefore, I would use the exact same name for creation date, modified date, and the soft delete flag. However, I also am usually pulling back the name or description field from multiple tables when I write my queries. Therefore, I prefer those columns to include the table name as part of the column name. This allows others to view the select statement and easily identify which table is being referenced. It also means I am aliasing less columns when I write my queries.
Naming persistent database objects can get tricky as well. Naming tables can be different than naming indexes or views or triggers or functions. Once again, naming these objects is more than just giving them a descriptive name. In addition, it may also be about giving them a name that makes it clear what type of object it is. This is because many database engineers and developers use Object Explorer as their primary tool for finding objects. As I have discussed if table names begin with nouns and stored procedures begin with verbs, I next need to figure out how to differentiate other database objects from tables and stored procedures.
One of the options is to precede the object name with an abbreviation for the object type. In the case of indexes, you can use IX_ for non-clustered indexes and CX_ for clustered indexes. When naming indexes, once you specify the IX_ or CX_ the next item should be the table name where the index exists. After the table name should be the list of columns in the index. The columns should be listed in the same order that they are specified when the index is created. In Listing
3-21, I have created a
clustered index. As you can see, the clustered index name begins with CX followed by the table name and then the column name. Each section is separated by an underscore.
CREATE CLUSTERED INDEX CX_Ingredient_IngredientName
ON dbo.Ingredient (IngredientName);
Listing 3-21Create a Clustered Index
Creating a non-clustered index follows the same pattern. In Listing
3-22, you will see the
non-clustered index also includes multiple columns.
CREATE NONCLUSTERED INDEX IX_Ingredient_IngredientName_IsActive
ON dbo.Ingredient (IngredientName ASC, IsActive DESC);
Listing 3-22Create a Non-clustered Index with Multiple Columns
The same is true when creating primary and foreign keys. If you do not specify a name when you create a primary or foreign key in T-SQL, SQL Server will assign a name at random. Therefore, it is best practice to specifically name the primary or foreign key. When naming the primary key, you will want the primary key to begin with PK_ which stands for primary key. Similarly, you will want to precede the foreign key with FK_ and then name the rest of the key. The next part of the name is the table name where the primary or foreign key is assigned. This is followed by the column or columns used to define the primary or foreign key. If there is more than one column specified, the columns listed in the primary or foreign key should be listed in order. Listing
3-23 shows an example of how to add and name a
primary key after table has been created.
ALTER TABLE dbo.Ingredient
ADD CONSTRAINT PK_Ingredient_IngredientID
PRIMARY KEY (IngredientID);
Listing 3-23Add a Primary Key
As you can see, the primary key name begins with PK followed by the table name and then the column name used for the creation of the primary key.
Similarly, you will want to precede the foreign key with FK_ and then name the rest of the key. The next part of the name is the table name where the primary or foreign key is assigned. This is followed by the column or columns used to define the primary or foreign key. If there is more than one column specified, the columns listed in the primary or foreign key should be listed in order. When creating a
foreign key after the table has been created, you can refer to Listing
3-24.
ALTER TABLE dbo.RecipeIngredient
ADD CONSTRAINT FK_RecipeIngredient_IngredientID
FOREIGN KEY (IngredientID)
REFERENCES dbo.Ingredient(IngredientID);
Listing 3-24Add a Foreign Key
Like creating the primary key, the foreign key follows a similar naming structure. The foreign key begins with FK followed by the table name and then the column name.
I can also precede views with vw_ and triggers with tr_. There are other options available that can be less obvious. For instance, you can define a list of nouns or verbs that will be reserved for only views or triggers. This will give you more flexibility, and it will also keep Object Explorer from having all items in the list begin with the same three characters. This will allow anyone to easily be able to tell these objects are neither tables nor stored procedures. This is particularly true when it comes to views and triggers.
One of the biggest challenges is that if they are not named to make it obvious that they are views or triggers, people can spend a significant amount of time looking for these objects without being able to find them. This is because views are used similarly to tables, and triggers operate similarly to stored procedures. This is because views are used in joins like tables, whereas triggers change objects like stored procedures. Triggers are even trickier because they can cause you to spend a significant amount of time researching stored procedures trying to determine why values are changing.
When naming tables, you want to use only nouns. This helps signify that these objects are for storage and not performing any specific activity. When naming objects, one of the naming conventions that is often overlooked until it is too late is whether objects are singular or plural. It is not really an issue until that first time where an object is pluralized. Once a plural database object is in the overall schema, it becomes obvious very quickly the issues with a plural object. This is because once a plural object exists in the database, it becomes increasingly difficult to write queries without specifically remembering which tables are plural.
You also want to make sure that you choose a descriptive name when naming a table. You will want to describe your table in a way that other database engineers and developers will easily know what type of information is stored in the table. Naming tables with nouns will also indicate that the object is for storage and not performing any specific activities.
Most users become easily familiar with tables and stored procedures. It is easy to choose a naming convention that differentiates these objects either by having stored procedure preceded with a verb or having additional high-level criteria as part of the
stored procedure name. Listing
3-25 shows a stored procedure that has a verb at the beginning of a stored procedure name.
EXECUTE dbo.GetRecipeIngredient
Listing 3-25Stored Procedure Beginning with a Verb
If I were to see that stored procedure name, I would expect it to retrieve all recipes and all associated ingredients. The stored procedure in Listing
3-26 would pull back all the ingredients for a given stored procedure.
EXECUTE dbo.GetIngredientByRecipe
Listing 3-26Stored Procedure with Selection Criteria
As you can see, there are many considerations when it comes to naming your T-SQL database objects. In many of these cases, a good name will help you easily determine the purpose of the database object. In special circumstances, it can also help you identify the type of object. While you would normally name your objects after formatting and commenting the T-SQL, I saved the commenting for last as it covers both the creation of the database object and the T-SQL code that goes inside the database object.
Commenting T-SQL
While the primary goal of writing T-SQL is to allow an application to do a specific action regarding data, almost equally important is ensuring that others will understand your T-SQL code going forward. At the very least, this makes sure that you are not the only one responsible for a certain piece of code or business logic going forward. It also helps all your other team members build confidence in their ability to do their job and their understanding of the code behind the scenes.
In many cases, quickly scanning the T-SQL code or looking at the name of a database object is not enough to understand the purpose of the T-SQL code. In Listing
3-27, there is sample T-SQL code for creating a
header section at the top of your database objects.
/*-------------------------------------------------------------*
Name: <name of database object>
Author: <original author name>
Created Date: <original creation date>
Description: <Brief description of database object>
Sample Usage:
<Example of how this database object will be called>
Change Log:
Update on <date> by <user>: <Brief summary of changes>
Update on <date> by <user>: <Brief summary of changes>
*-------------------------------------------------------------*/
Listing 3-27Comment for Header of Persistent Database Object
The purpose of this heading is giving other users a summary of this database object at a glance. Depending on the purpose for reviewing this T-SQL will determine what is important to the user. For those that are not familiar with all the details of the business applications, the description segment will provide a high-level idea of the purpose of the T-SQL database object. Likewise, the sample usage allows those looking into performance issues to understand how the applications are using this piece of T-SQL code. The author of the database object can be useful to determine if the original creator is still with the company to answer more specific questions about the database object. If your databases are in source control, you may decide to omit some of these fields. I will discuss source control further in Chapter
10. In Listing
3-28, you can see how the header information would look while creating the
view originally found in Listing
3-4.
/*-------------------------------------------------------------*
Name: dbo.AvailableMeal
Author: Elizabeth Noble
Created Date: 03/13/2019
Description: Simple view to display all meals with ingredients
Sample Usage:
SELECT MealTypeName, RecipeName FROM dbo.AvailableMeal
Change Log:
Update on 03/31/2019 by enoble: Added header to view
*-------------------------------------------------------------*/
CREATE VIEW dbo.AvailableMeal
AS
SELECT meal.MealTypeName, rec.RecipeName, rec.ServingQuantity, ing.IngredientName
FROM dbo.Recipe rec
INNER JOIN dbo.MealType meal
ON rec.MealTypeID = meal.MealTypeID
INNER JOIN dbo.RecipeIngredient recing
ON rec.RecipeID = recing.RecipeID
INNER JOIN dbo.Ingredient ing
ON recing.IngredientID = ing.IngredientID
Listing 3-28Create a View with a Commented Header
Sometimes it may be enough to write simple T-SQL that is easily readable. However, there are other times where a database object may have T-SQL code that is difficult to understand at a quick glance. I often find one of the challenges in commenting after I’ve written my code is that my comments are too technical to easily describe what I am trying to accomplish. You will want to make sure that someone unfamiliar with your code can easily understand the purpose of your T-SQL code.
If there is any complex logic, you will want to clearly explain how the complex logic works. This is especially true if you are using T-SQL coding practices that are not best practices. Any explanation as to why
non-standard practices were chosen will help save others’ time to try and figure out why non-standard practices were used. This can also save your team members’ time if you have already determined that standard best practices will not perform well enough in the given situation. Looking back to Listing
3-3, there is a subquery in the SELECT statement. First writing this code, it may be obvious why a subquery was used, but you may forget the reason in the future. In addition, it is probably not obvious to others who read my code why I chose to include a subquery for that column. In Listing
3-29, I show how
you can add commenting to help show how adding comments can make it easier for you to quickly understand the purpose or logic of the code that is written.
SELECT
-- This subquery pulls back the recipe name based on
-- the ingredients in the recipe
---- The logic uses a correlated subquery between the
---- where clause in the subquery
---- and the table in the outer query
(
SELECT rec.RecipeName
FROM dbo.Recipe rec
INNER JOIN dbo.RecipeIngredient recingr
ON rec.RecipeID = recingr.RecipeID
WHERE recingr.IngredientID = ingr.IngredientID
) AS 'RecipeName',
ingr.IngredientName,
ingr.IsActive,
ingr.DateCreated,
ingr.DateModified
FROM dbo.Ingredient ingr
WHERE IngredientName LIKE '%Tomato%'
ORDER BY RecipeName, ingr.IngredientName
Listing 3-29Queries with Subqueries
It is often easiest to include comments in your code if you start commenting your T-SQL code even before you begin writing any T-SQL. In Listing
3-30, you can see the header information and the beginning comments. The comments specified indicate the concept behind writing the
stored procedure.
/*-------------------------------------------------------------*
Name: dbo.GetRecipeNutrition
Author: Elizabeth Noble
Created Date: 03/13/2019
Description: Lookup nutritional information for a given recipe
Sample Usage:
DECLARE @RecipeID INT
SET @RecipeID = 1
EXECUTE dbo. GetRecipeNutrition @RecipeID
*-------------------------------------------------------------*/
-- Get the nutrition information for a recipe
-- Since nutrition information is saved per ingredient
-- This will be a summary of nutrition information
-- per ingredient that is specified in the recipe
Listing 3-30Create New Stored Procedure
Once you specify the general logic for the query, you can move forward with writing the T-SQL so that these requirements are met. This is often helpful if you usually find yourself rephrasing the code you have already written instead of explaining the overall purpose of the code.
Writing these comments before writing any T-SQL code ensures the comments explain the purpose of the stored procedure instead of how the code executes.
You should now be prepared to start defining SQL formatting standards for yourself and in your own organization. This will allow you and other members of your team to quickly review your organization’s T-SQL code. In addition, I have discussed strategies to use when providing additional documentation about your T-SQL code. Commenting your T-SQL code will allow others to understand what the T-SQL code should be doing both regarding business logic and advanced technical logic. I have also covered options available when defining naming conventions for your organization. Well-defined naming conventions should make it easier for anyone accessing the database schema to know where to find database objects.
You should now be familiar with SQL Server data types and the best time to use them. You should also be comfortable with some of the various database objects that are available when writing your T-SQL. Now that you are also more familiar with how to style your code to improve readability and understanding, you are ready to learn more about designing T-SQL code using parameters, complex logic, and stored procedures.
