Overview

The Aeries SQL Server database definitions have for a long time included a scalar-valued function named dbo.Get_Description. Until recently, the Query feature in Aeries Web Version utilized this function to return code descriptions requested by a query via the “?” operator. To increase the performance of Aeries Query, a new inline table-valued function named dbo.Get_Description_New was added. Using this new function allows Aeries Query to return code descriptions much more efficiently.

Recognizing that some customers may have their own SQL scripts that utilize the dbo.Get_Description function, Aeries technical staff have created this document to explain the difference between the two functions and provide assistance in transitioning your SQL scripts to use the new function.

There is no immediate plan to delete the old function, but we strongly encourage customers to transition to using the new function in their own development work.

The Functions Explained

The Old Function – dbo.Get_Description

The old dbo.Get_Description function is scalar-valued, meaning it returns a single value. The typical usage is to call the function as a column in the SELECT list. The function accepts four parameters:

The Aeries school code (or 0 for district-based)
The table name (e.g., ‘CON’)
The column/field name (e.g., ‘RL’)
The code value to look up

In the example below, the function is called for each row’s value of CON.RL, and the result is output as its own column.

SELECT CON.PID, CON.LN, CON.FN, CON.RL, dbo.Get_Description(0, 'CON', 'RL', CON.RL)
FROM CON
WHERE DEL = 0

Here is a partial result set from the above statement.

The New Function – dbo.Get_Description_New

The dbo.Get_Description_New function is inline table-valued, meaning it returns a table. Specifically, it returns a table consisting of a single row with one column named [DE]. The [DE] columns contains the code description. The difference may seem subtle, but using an inline table-valued function results in a significant performance increase: less CPU usage, I/O, and elapsed time. The new function accepts the same four parameters as the old function.

Because the new function returns a table, it cannot be called directly in the SELECT column list. Instead, it is best used with the CROSS APPLY operator in SQL. What this means is that the function is evaluated for each row of the outer query, and the resulting rows are combined for the final output. The CROSS APPLY operator should be placed after the FROM clause and before the WHERE clause. In order to output the code description in the column list, the function call must be given a table alias, and this alias must be included in the SELECT column list, as shown in the example below.

This example returns the same results as the previous example but has been rewritten to use the new function.

SELECT CON.PID, CON.LN, CON.FN, CON.RL, [GetDescr_CON_RL].DE
FROM CON
  CROSS APPLY dbo.Get_Description_New(0, 'CON', 'RL', CON.RL) AS [GetDescr_CON_RL]
WHERE CON.DEL = 0

Converting Scripts to Use the New Function

The process for converting existing SQL statements to use the new function is straightforward and methodical. We will start with the following, more complex example involving Assertive Discipline data and walk through the steps to convert this statement to use the new function.

SELECT [STU].[ID] as [StudentID]
  , [STU].[LN] as [LastName]
  , [STU].[FN] as [FirstName]
  , [STU].[MN] as [MiddleName]
  ,  [STU].[GR] AS [StuGrd]
  , [STU].[SX] AS [StuGender]
  , CONVERT(VARCHAR(11),[ADS].[DT],101) AS [IncidentDate]
  , [ADS].[IID] AS [IncidentID]
  , [ADS].[SCL] AS [SchoolOfIncident]
  , [ADS].[CD] AS [Violation Code 1]
  , dbo.Get_Description(994,'ADS','CD',[ADS].[CD]) AS [Code 1 Description]
  , [ADS].[CD2] AS [Violation Code 2]
  , dbo.Get_Description(994,'ADS','CD',[ADS].[CD2]) AS [Code 2 Description]
  , [ADS].[CD3] AS [Violation Code 3]
  , dbo.Get_Description(994,'ADS','CD',[ADS].[CD3]) AS [Code 3 Description]
  , [ADS].[CD4] AS [Violation Code 4]
  , dbo.Get_Description(994,'ADS','CD',[ADS].[CD4]) AS [Code 4 Description]
  , [ADS].[CD5] AS [Violation Code 5]
  , dbo.Get_Description(994,'ADS','CD',[ADS].[CD5]) AS [Code 5 Description]
  , [ADS].[LCN] AS [Location Code]
  , dbo.Get_Description(994,'ADS','LCN',[ADS].[LCN]) AS [Location Description]
  , CONVERT(VARCHAR(8),[ADS].[ET],108) AS [Exact Time]
  , [ADS].[AXT] AS [Apprx Time Code]
  , dbo.Get_Description(994,'ADS','AXT',[ADS].[AXT]) AS [Apprx Time Description]
FROM (STU INNER JOIN ADS ON [STU].[ID] = [ADS].[PID])
WHERE STU.DEL = 0
  AND ADS.DEL = 0
  AND ADS.DT <= '3/24/2017'
  AND ADS.DT >= '7/11/2016'
  AND [ADS].SCL = 994
  AND STU.SC = 994
ORDER BY CONVERT(VARCHAR(11),[ADS].[DT],101), ADS.IID;

Step 1: Identify all uses of the old function

In this example, the old function is used seven times.

Step 2: Create a CROSS APPLY operation for each function call

Each function call is rewritten as a CROSS APPLY operation. All the CROSS APPLY operations will be after the FROM clause and before the WHERE clause. Also, each function call is given a table alias, which appears after the AS keyword. Because each table alias must be unique, we use the naming convention [GetDescr_TableName_ColumnName].

The snippet below has been abbreviated to illustrate the addition of the CROSS APPLY operations.

SELECT [STU].[ID] as [StudentID]
  , …
FROM (STU INNER JOIN ADS ON [STU].[ID] = [ADS].[PID])
  CROSS APPLY dbo.Get_Description_New(994,'ADS','CD',[ADS].[CD]) AS [GetDescr_ADS_CD]
  CROSS APPLY dbo.Get_Description_New(994,'ADS','CD',[ADS].[CD2]) AS [GetDescr_ADS_CD2]
  CROSS APPLY dbo.Get_Description_New(994,'ADS','CD',[ADS].[CD3]) AS [GetDescr_ADS_CD3]
  CROSS APPLY dbo.Get_Description_New(994,'ADS','CD',[ADS].[CD4]) AS [GetDescr_ADS_CD4]
  CROSS APPLY dbo.Get_Description_New(994,'ADS','CD',[ADS].[CD5]) AS [GetDescr_ADS_CD5]
  CROSS APPLY dbo.Get_Description_New(994,'ADS','LCN',[ADS].[LCN]) AS [GetDescr_ADS_LCN]
  CROSS APPLY dbo.Get_Description_New(994,'ADS','AXT',[ADS].[AXT]) AS [GetDescr_ADS_AXT]
WHERE STU.DEL = 0
  AND …
ORDER BY CONVERT(VARCHAR(11),[ADS].[DT],101), ADS.IID;

Step 3: Replace each old function call

Each old function call is replaced with the selection of the [DE] column from the aliased function results. Now that the CROSS APPLY operations are in place, the results of each function call are treated similarly to a table that has been JOINed, and the code description we want is in the [DE] column of each table.

The snippet below has been abbreviated to illustrate the method of accessing the function results.

SELECT [STU].[ID] as [StudentID]
  , …
  , [GetDescr_ADS_CD].[DE] AS [Code 1 Description]
  , [ADS].[CD2] AS [Violation Code 2]
  , [GetDescr_ADS_CD2].[DE] AS [Code 2 Description]
  , [ADS].[CD3] AS [Violation Code 3]
  , [GetDescr_ADS_CD3].[DE] AS [Code 3 Description]
  , [ADS].[CD4] AS [Violation Code 4]
  , [GetDescr_ADS_CD4].[DE] AS [Code 4 Description]
  , [ADS].[CD5] AS [Violation Code 5]
  , [GetDescr_ADS_CD5].[DE] AS [Code 5 Description]
  , [ADS].[LCN] AS [Location Code]
  , [GetDescr_ADS_LCN].[DE] AS [Location Description]
  , CONVERT(VARCHAR(8),[ADS].[ET],108) AS [Exact Time]
  , [ADS].[AXT] AS [Apprx Time Code]
  , [GetDescr_ADS_AXT].[DE] AS [Apprx Time Description]
FROM …

Here is the complete script rewritten to use the new function.

SELECT [STU].[ID] as [StudentID]
  , [STU].[LN] as [LastName]
  , [STU].[FN] as [FirstName]
  , [STU].[MN] as [MiddleName]
  ,  [STU].[GR] AS [StuGrd]
  , [STU].[SX] AS [StuGender]
  , CONVERT(VARCHAR(11),[ADS].[DT],101) AS [IncidentDate]
  , [ADS].[IID] AS [IncidentID]
  , [ADS].[SCL] AS [SchoolOfIncident]
  , [ADS].[CD] AS [Violation Code 1]
  , [GetDescr_ADS_CD].[DE] AS [Code 1 Description]
  , [ADS].[CD2] AS [Violation Code 2]
  , [GetDescr_ADS_CD2].[DE] AS [Code 2 Description]
  , [ADS].[CD3] AS [Violation Code 3]
  , [GetDescr_ADS_CD3].[DE] AS [Code 3 Description]
  , [ADS].[CD4] AS [Violation Code 4]
  , [GetDescr_ADS_CD4].[DE] AS [Code 4 Description]
  , [ADS].[CD5] AS [Violation Code 5]
  , [GetDescr_ADS_CD5].[DE] AS [Code 5 Description]
  , [ADS].[LCN] AS [Location Code]
  , [GetDescr_ADS_LCN].[DE] AS [Location Description]
  , CONVERT(VARCHAR(8),[ADS].[ET],108) AS [Exact Time]
  , [ADS].[AXT] AS [Apprx Time Code]
  , [GetDescr_ADS_AXT].[DE] AS [Apprx Time Description]
FROM (STU INNER JOIN ADS ON [STU].[ID] = [ADS].[PID])
  CROSS APPLY dbo.Get_Description_New(994,'ADS','CD',[ADS].[CD]) AS [GetDescr_ADS_CD]
  CROSS APPLY dbo.Get_Description_New(994,'ADS','CD',[ADS].[CD2]) AS [GetDescr_ADS_CD2]
  CROSS APPLY dbo.Get_Description_New(994,'ADS','CD',[ADS].[CD3]) AS [GetDescr_ADS_CD3]
  CROSS APPLY dbo.Get_Description_New(994,'ADS','CD',[ADS].[CD4]) AS [GetDescr_ADS_CD4]
  CROSS APPLY dbo.Get_Description_New(994,'ADS','CD',[ADS].[CD5]) AS [GetDescr_ADS_CD5]
  CROSS APPLY dbo.Get_Description_New(994,'ADS','LCN',[ADS].[LCN]) AS [GetDescr_ADS_LCN]
  CROSS APPLY dbo.Get_Description_New(994,'ADS','AXT',[ADS].[AXT]) AS [GetDescr_ADS_AXT]
WHERE STU.DEL = 0
  And ADS.DEL = 0
  And ADS.DT <= '3/24/2017'
  AND ADS.DT >= '7/11/2016'
  AND [ADS].SCL = 994
  AND STU.SC = 994
ORDER BY CONVERT(VARCHAR(11),[ADS].[DT],101), ADS.IID;

Step 4: Test the new script

After following the above steps, your script should output the same results as before, but it now takes advantage of this optimized inline table-valued function for faster performance. Be sure to test the new script to ensure there are no errors and that the output is as expected.

Support

Using the Get Description Function in SQL Server Print

Overview

The Functions Explained

The Old Function – dbo.Get_Description

The New Function – dbo.Get_Description_New

Converting Scripts to Use the New Function

Step 1: Identify all uses of the old function

Step 2: Create a CROSS APPLY operation for each function call

Step 3: Replace each old function call

Step 4: Test the new script

Using the Get Description Function in SQL Server Print

Overview

The Functions Explained

The Old Function – dbo.Get_Description

The New Function – dbo.Get_Description_New

Converting Scripts to Use the New Function

Step 1: Identify all uses of the old function

Step 2: Create a CROSS APPLY operation for each function call

Step 3: Replace each old function call

Step 4: Test the new script

Related Articles