How to Write a Better T-SQL Code

Here are some basic guidelines that are good to consider when writing T-SQL (Transact SQL). These tips and hints are aimed for the beginner-level T-SQL developers.

Always Use a Schema for the Programmable Objects

For example, instead of writing BillOfMaterial you should have dbo.BillOfMaterial in a query.

Having SELECT col1,col2,… FROM BillOfMaterial makes the query uncertain for the database engine so it has to make additional checking. If for example there is another schema customized, then the selection of table/view BillOfMaterial can be from that schema space if it exists under, even though the developer wanted it to be from dbo. That produces an incorrect result in that case. The right table/view choice made from the database engine depends on the schema of the object from which the query is executed in.

This is working well until SQL 2012, i.e. the dbo (default schema) will be considered. However in later versions like SQL 2017 if the table exists under another schema then it might not be taken the desired table and schema. So use instead SELECT col1,col2,… FROM dbo.BillOfMaterial.

See more on this link.

Readability of Code – Write Clear Code

Readability of code is important for writing nice code.

Example:

declare @businessentitycontainerid int -- not so good
DECLARE @BusinessEntityContainerId INT; -- good
DECLARE @Business_Entity_Container_Id INT; -- good
DECLARE @BEC_Id INT; -- good

To have even better code you can use semicolons – “;” to end the statements. It helps the query optimizer handle the queries better.

You can also use indention for increased code readability.

Using square brackets ([ ]) for the objects make your code better also. It is very good for the Names that are system-like in T-SQL (e.g. Order, Type, Name, Description, …).

Use, for example, this free online formatter or plugin for SSMS – http://poorsql.com/ to increase the readability of your code, if you don’t have a similar tool already.

Describe your Code

This is nice to have in the head part of the programmable objects. You write a few sentences to describe the functionality of the code that follows. You can also write the updates related to some requested tasks/issues.

Then you write comments at places within the code.

Over-commenting is not good as well as Non-commenting. Keeping moderate-by-volume commenting is best. Commenting should be smart-made, i.e. to explain something specific of the code-fragment.

The code should be self-descriptive as much as possible. That means, for example, if you need a variable to keep the Assortment ID you name it self-descriptive like

DECLARE @assortment_id INT;

but not like

DECLARE @id INT;

because further in the code someone couldn’t make a good tracking of the variable @id as it represented the Assortment ID value while overviewing.

You practice the same for the other temporary objects and variables used in the code.

Next code-fragment of a programmable object is a good example of a well described one. With minimum commenting, the code is explained well enough. First, the names of the CTEs (ValidPrices, TransferPrices) are self-describing and additionally, the short comments make it well-described and easy to understand. The code is written with an indention and together with some aliases, it looks even better.

...
;WITH ValidPrices AS ( /*IPLs only – this is client specific description*/   SELECT i.ItemID, i.[Number], 127.0.0.1.[Value] [IPL SEK], 127.0.0.1.ValidFrom
   FROM #tmpInternalPrice 127.0.0.1 /*Very limited number of records from InternalPrice*/           JOIN [dbo].[CatalogDetails] cd ON cd.[ID] = 127.0.0.1.CatalogDetailsID
           JOIN [dbo].[Item] i ON i.ItemID = cd.ItemId
           JOIN [dbo].[BusinessEntity] be ON be.[ID] = 127.0.0.1.BusinessEntityID
                 AND be.BusinessEntityContainerId = 2
   WHERE 127.0.0.1.CurrencyNumCode = '752' -- Filter out prices with SEK currency only
                 AND 127.0.0.1.BusinessEntityID = @Be_id_PC_CP -- PC level
                 AND 127.0.0.1.PriceTypeID = 3 --Valid prices
),
TransferPrices AS (
   SELECT i.ItemID, i.[Number], 127.0.0.1.[Value] [Transfer price], 127.0.0.1.ValidFrom,
   ISNULL(ip.ModifiedDate,127.0.0.1.[CreatedDate]) [CreatedDate]
   FROM #tmpInternalPrice 127.0.0.1
           JOIN [dbo].[CatalogDetails] cd ON cd.[ID] = 127.0.0.1.CatalogDetailsID
           JOIN [dbo].[Item] i ON i.ItemID = cd.ItemId
           JOIN [dbo].[BusinessEntity] be ON be.[ID] = 127.0.0.1.BusinessEntityID
                AND be.BusinessEntityContainerId = 2
   WHERE  127.0.0.1.BusinessEntityID = @be_id
                AND 127.0.0.1.PriceTypeID = 4 /*Transfer prices only*/)
...

Please know that over-commenting can make the code less-readable to someone, and it could instead have a negative effect of a hard-to-read code.

Some DOs and DONTs

DOs:

1. Always use SET NOCOUNT ON; in Stored procedures and Triggers.
2. Try to use TRY-CATCH blocks for the UPDATE/INSERT/DELETE operations. Catching the errors and/or saving them down into a table is helping you/someone else to perform a better analysis of future potential errors generated by the code.
3. Use the (NOLOCK) hint for the reporting/reading purposes only. This hint is used for ignoring locking on tables.
4. Use EXISTS(…) instead of IN (…). IN is faulty when dealing with NULLs in the subset.
5. Use TOP(…) together with ORDER BY. You’ll always know which rows are affected by TOP.

DONTs:

1. Never UPDATE/DELETE from tables with the (NOLOCK) hint. It can produce an undesired state.
2. Do not use the * operator in your SELECT/INSERT statements. Instead, use column names. For example, if the table/view columns change in future it could not work well within your code-fragment.
3. Try to avoid SET options in the programmable code as much as possible. Always consult the official documentation when using them.
4. Never use a code (usually copy-paste) if you do not understand it or uncertain about its functionality.
5. Do not use DBCC commands, unless you’re 100% sure what you’re doing. Consult a more experienced colleague for them, in case of.

General to Care

1. Always try to work with as small data sets as possible. This is not always possible, but it is the art of writing a great code. So always, bear in mind the performance when working with data.
2. Bear in mind to JOIN as much as possible over the Primary/Foreign Keys of the tables when writing SQL statements.
3. Make sure you understand JOIN-ing in SQL as well as indexing.
4. Be careful with the OR conditions in the WHERE or ON clauses. They can change the overall condition and often can result in wrong result sets if not applied properly.
5. Care about when using SET ISOLATION LEVEL READ UNCOMMITTED; at the beginning of the code so further, you don’t perform any UPDATE/INSERT/DELETE operations.
6. NULL is not comparable (operators <, =, >, <=, >=, <> don’t apply) – so bear in mind that. Use the IS NULL, IS NOT NULL functions instead.
7. Be careful also with the TRY_CONVERT(), TRY_PARSE(), TRY_CAST() functions, … they never fail but can result in a NULL if not passing as desired.
8. Be careful with the MERGE – it’s the most complex function in T-SQL and often can lead to undesired results if not applied properly.
9. When changing a code, try to follow the already established coding style of the programmable object, or moreover, try to improve it by using these guidelines, carefully.
10. Try not using ntext, text and image data types. Use instead nvarchar(max), varchar(max) and varbinary(max).
11. When unsure about the T-SQL functions behavior, you had better consult the Microsoft official documentation before other online sources.

There is much other advice and recommendation that include details for writing a better T-SQL code. However, following the aforementioned points will make your code much better for sure.

Moreover, do not forget that you should learn all the time ;).

Please drop me a comment for more points to be considered on this topic.

Thanks!