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: 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.
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.
Avoid the use of IN clause if the IN clause argument is a select query. Use EXISTS clause instead.
Always wrap all Stored Procedure SQL queries in try/catch block if the SQL Server version is 2008 or above.
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'
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.
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 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.
ASkeyword 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
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.
When retrieving the most recent identity value from a table in a stored procedure, use function
scope_identity() instead of the global variable
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_...
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. If possible, refactor the query to use Union or Union ALL instead.
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 scripts. You can use free online TSQL formatter available at http://www.sql-format.com.
When creating default, specifically name it using
DF_[TableName]_[ColumnName] naming convention instead of letting SQL server generate a random name for it.
Always end your TSQL statements with a semi colon.
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 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.
More from @sqldibia