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.
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.
This is the syntax of the Substring() function – SUBSTRING(string, start, length)
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
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.
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.
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.
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.
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.