In this article, we will briefly explain the SUBSTRING function and then focus on performance tips about it.

SQL Server offers various built-in functions and these functions make complicated calculations easier for us. When we use these functions in the SELECT statement, performance impacts are mostly acceptable. However, scalar functions can affect query performances negatively when it uses after the WHERE clauses. The following rule is generally accepted as a performance practice to improve query performances.

• Note:_ Don’t use scalar-valued functions in the WHERE clause_

The main idea behind this principle is, SQL Server can not know the result of the function without executing the scalar function. Therefore, SQL Server must perform the function individually for each row to find qualified data on the execution time. So, the data engine will read the entire index pages or the entire table rows so it causes more I/O activity.

SUBSTRING function is one of the built-in function and that helps to obtain a particular character data of the text data in the queries. This function is widely used by database developers in the queries, for this reason, we will focus on performance details of this function.

Syntax

This is the syntax of the Substring() function – SUBSTRING(string, start, length)

• **string: **The string expression, from which substring will be obtained
• **start: **The starting position of the value in which the substring will be extracted
• **length: **This parameter specifies how many characters will be extracted after the starting position

Now we will make a very straightforward example :

SELECT SUBSTRING('SAVE THE GREEN',6,3)

The following illustration represents how this function works for the above example.

This function also can be implemented with the SELECT statement that retrieves data from the tables. The following query returns a certain part of the PurchaseOrderNumber column values. In this usage of the SUBSTRING function, it starts to extract from the second character of the column values and continues until the seventh character. So that it extracts five characters as we specified in the length parameter of the function.

SELECT PurchaseOrderNumber, 
    SUBSTRING(PurchaseOrderNumber, 2, 5) 
  AS NewPOrderNumber
FROM Sales.SalesOrderHeader
WHERE SalesOrderID BETWEEN 43682 AND 43694

#development #execution plans #sql commands #t-sql #sql