In the second parameter, you specify the delimiter which separates the first parameter. The STRING_AGG ( ) is an aggregate function comprised of two parameters the first parameter takes input values and concatenates the values into strings. In this article, we will be discussing the STRING_AGG ( ) function.
POSTGRESQL SUBSTRING CODE
The purpose of using aggregate functions in your code is to determine output from a set of values. At first, we will add a computed column to the SalesOrderHeaderEnlarged table.In PostgreSQL, there are built-in aggregate functions including SUM, COUNT, ARRAY_AGG ( ), STRING_AGG ( ), AVG ( ), MAX, MIN etc. Now we will apply this solution method to our example and observe performance improvement. So that we can improve the performance of these types of queries. However, we can create computed columns and create Queries that include scalar-valued function in the WHERE clauses. SQL Server does not support function-based indexes so it is a bit complicated to improve the performance of the So the total read amountĮquals 28416*8KB = 227.328 KB and it approximately 227 MB. Now we will make aġ Logical I/O read indicates a reading of a data page and a data page stores 8 kb data. When we right-click on the Clustered Index Scan operator and expand the Actual I/O Statistics property and find out the I/O measurement of this operator.Īctual Logical Reads value indicates how many pages read from the buffer pool. For this execution plan, this value is 148434. Actual Number of Rows indicates how many rows transferred to the next The Number of Rows Read option shows how many rows have been read by the operator and for thisĮxample Clustered Index Scan operator has read the whole table and this situation is not a good WHERE SUBSTRING ( AccountNumber, 4, 4 ) = '4020' Defined Values subattribute shows every detail about the performed scalar-valued function for the following query on the execution plan. Now we will execute the following query and analyze the property details of the compute scalar operator.
The property detail of this operator givesĭetailed information about the performed function to us. Operator represented with the following image on the execution plans. When weĮxecute this script it will create the SalesOrderDetailEnlarged and SalesOrderHeaderEnlarged tables under the Sales schema.Ĭompute scalar operator performs computation and returns the value of this computation. In the following examples, we will use the AdventureWorks sample database and use an enlarging script ( Create Enlarged AdventureWorks Tables ) which helps to create a large amount of data. Now, let’s talk about the performance details of this function. You can direct to this article, Substring function overview to learn more interesting facts about the Substring() function. Now we will make a very straightforward example :
length: This parameter specifies how many characters will be extracted after the starting position.start: The starting position of the value in which the substring will be extracted.string: The string expression, from which substring will be obtained.This is the syntax of the Substring() function – SUBSTRING(string, start, length) Reason, we will focus on performance details of this function. This function is widely used by database developers in the queries, for this SUBSTRING function is one of the built-in function and that helps to obtain a particular characterĭata of the text data in the queries. So, the data engine will read the entire index pages or the entire table rows so it causes more Therefore, SQL Server must perform the function individually for each row to find qualified data on
The main idea behind this principle is, SQL Server can not know the result of the function without executing the