SQL SUBSTRING function and its performance tips

SQL SUBSTRING function and its performance tips

This article mentions the fundamentals of the SQL Substring function and how to improve its performance. SQL Server offers various built-in functions and these functions make complicated calculations easier for us.

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)

Syntax of the SUBSTRING function.

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

Basic usage of the SUBSTRING function.

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

What is Geek Coin

What is GeekCash, Geek Token

Best Visual Studio Code Themes of 2021

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Welcome Back the T-SQL Debugger with SQL Complete – SQL Debugger

Debug SQL stored procedures and develop your SQL database project with dbForge SQL Complete, a new add-in for Visual Studio and SSMS. When you develop large chunks of T-SQL code with the help of the SQL Server Management Studio tool, it is essential to test the “Live” behavior of your code by making sure that each small piece of code works fine and being able to allocate any error message that may cause a failure within that code.

Interpreting execution plans of T-SQL queries

In this article, we will analyze a simple T-SQL query execution plan with different aspects. This will help us to improve our practical skills instead of discussing theoretical knowledge.

T-SQL scripts to copy or remove files from a directory in SQL Server 2019

This article explores T-SQL scripts to copy or remove files from a directory in SQL Server 2019. SQL Server 2019 introduced many new stored procedures, functions, dynamic management views(DMV). In this article, we will explore the xp_cmdshell procedure along with the new functions in SQL Server 2019 to copy or remove the files.

How to Modify a SQL Server Graph Edge with T-SQL

Learn how to modify a SQL Server Graph Edge with T-SQL. I can insert, delete, and even update graph tables using normal SQL syntax.