0

T-SQL Coding Best Practices and Performance Improvement Tips

Emeka OkekeDec 25, 2019

Code quality which consists of code performance and code readability is often neglected by developers in the course of building or maintaining software. This is especially true of startups. In a bid to quickly move their products to the market, they often sacrifice code quality.

However, the importance of code performance in database programming cannot be overemphasized. A slow-running stored procedure or badly written code could spell disaster for any organization. Data is everything in IT. It is at the center of all IT efforts, so the proper management of it is very crucial.

Avoid non-sargable queries/predicates

Avoid non-sargable queries: These are queries with predicates written in such a way that makes it difficult or impossible for SQL server query engine to perform index seek using index defined on the table or tables being searched. Use of functions in predicate is the most common cause of non-sargable queries. Avoid using functions in predicates, for example:


--Non-sargable query
SELECT CustName
FROM  CustTable
WHERE LEFT( CustName,1 ) = 'A';

--Sargable query
SELECT CustName
FROM  CustTable
WHERE CustName LIKE 'A%';

If you are not sure if your query or code is going to result in index seek or table scan, use the estimated execution plan button in SQL Server Management Studio to check.

CONTAINS vs LIKE

Use CONTAINS instead of the LIKE operator if the FULLTEXT catalog is enabled (You can check if this is enabled by running the following query: SELECT SERVERPROPERTY('IsFullTextInstalled');) and if the column being searched is not numeric.

IN vs EXISTS

Avoid the use of IN clause if the IN clause argument is a select query. Use EXISTS clause instead.

Use TSQL try/catch block

Always wrap all Stored Procedure SQL queries in try/catch block if the SQL Server version is 2008 or above.

Proper Way to Generate CSVs or Comma Seperated Values

Avoid the use of COALESCE function in generating CSVs from T-SQL queries. For example:


--Use this:
	SET @RecipientTypeList = (SELECT STUFF((SELECT ',' + RecipientTypeValue
	FROM #Recipients WHERE RecipientType = 'UserGroup'
	FOR XML PATH('')),1,1,'')

--Instead of this:
	SELECT @RecipientTypeList = COALESCE(@RecipientTypeList + ',', '') + RecipientTypeValue
	FROM #Recipients WHERE RecipientType = 'UserGroup'

Use T-SQL functions as necessary

Functions should be used sparingly. They should be used only where necessary and for single transactions that return scalar values. Functions should not be used solely for coding convenience or code reusability.

If the transaction is complex and could potentially require better transaction management, stored procedure should be used instead.

Use ISNULL Function as necessary

ISNULL function should only be used on nullable columns and parameters. When checking parameters for null values, the ISNULLfunction should be used once before the first transaction block in an SP or function.

Avoid dynamic SQL

Avoid dynamic SQL in transactional queries - queries that are used frequently and not infrequently or for database administrative purpose - used as ad-hoc queries or as part of stored procedure and/or function.

Dynamic SQL is rarely needed in transactional queries. It should be used as the last resort when all other alternatives are not applicable. Using dynamic SQL makes it difficult for query optimizer to generate the best execution plan for the T-SQL query.

Use AS keyword to properly denote aliases

It is easier to read a TSQL query that looks like this:

SELECT COL1 AS col1 FROM TAB1

than a query that looks like this:

SELECT COL1 col1 FROM TAB1

Avoid "*" in SELECT statements

When writing SELECT statements, avoid using "*", instead explicitly specify the columns and column aliases you are selecting. Example:


--Do this:
SELECT A.ID, B.NAME FROM Table1 A INNER JOIN Table2 B ON A.ID=B.ID
--Instead of this:
SELECT * FROM Table1 INNER JOIN Table2 ON Table1.ID=Table2.ID

Doing this cuts out the additional evaluation that the query engine will have to undertake to figure out the columns that are enclosed in the "*" selection, therefore improving the performance of the query.

Use scope_identity() instead of @@identity

When retrieving the most recent identity value from a table in a stored procedure, use function scope_identity() instead of the global variable @@identity.

Proper naming convention for SQL Server objects

Use appropriate prefixes when naming your SQL server objects like SPs and functions in order to properly categorize them. For example:

For SPs and functions that do not have any identifiable sub category:

Functions ==> udf_...
SPs ==> usp_...
Reporting SPs ==> rpt_...

For SPs and functions that have identifiable sub category:

Task service ==> Usp_tsv_...
External order ==> Usp_eo_...
Service request form ==> Usp_srf_...

Use proper column properties

When creating non-nullable columns in a table or adding non-nullable columns to existing table, the not null column property should be used. This way the unnecessary use of ISNULL could also be avoided.

Limit the use of OR operator

Limit the use of OR operator. If possible, refactor the query to use Union or Union ALL instead.

Limit the use of correlated and non-correlated subqueries

Limit the use of correlated and non-correlated subqueries. Use derived tables instead. For example:


--Instead of this:
SELECT MIN(Salary)  FROM Employees  WHERE EmpID IN (SELECT TOP 2 EmpID 
FROM Employees  ORDER BY Salary Desc) 

--Do this:
SELECT MIN(Salary) FROM (SELECT TOP 2 Salary FROM Employees ORDER BY Salary DESC) AS A

Properly format your TQSL codes

Properly format your TQSL scripts. You can use free online TSQL formatter available at http://www.sql-format.com.

Use proper naming convention for default constraint

When creating default, specifically name it using DF_[TableName]_[ColumnName] naming convention instead of letting SQL server generate a random name for it.

Terminate TSQL statements with semi colon

Always end your TSQL statements with a semi colon.

Avoid implicit type conversions

Avoid implicit type conversions. For example when using SELECT INTO... query, do not add columns to the table like the below sample query:


SELECT COL1, COL2, NULL AS COL_ALIAS1, CAST (NULL AS VARCHAR (10)) AS COL_ALIAS2 INTO TABLE2 FROM TABLE1

Instead write your SELECT INTO... query as follows:


SELECT COL1, COL2 INTO TABLE2 FROM TABLE1
ALTER TABLE TABLE2 ADD COL_ALIAS1 INT
ALTER TABLE TABLE2 ADD COL_ALIAS2 VARCHAR (10)

Avoid TVP or table valued parameters

Avoid TVP or table valued parameters for multiple stored procedure input parameters. Use JSON's OPENJSON and FOR JSON PATH/AUTO or XML's .nodes() and .value() methods along with FOR XML PATH/AUTO instead. For more on this visit the linked post.

Emeka Okeke@sqldibia+ Follow
0
Emeka Okeke+ Follow
locationPennsburg, PennsylvaniajoinedDec 11, 2019

More from @sqldibia