TSQL Select and Aggregate Text Column Performance Question

Question

We have a vendor table defined as this:

SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
CREATE TABLE [dbo].[SV000805](  
 [CUSTNMBR] [char](15) NOT NULL,  
 [ADRSCODE] [char](15) NOT NULL,  
 [Reference_ID] [char](31) NOT NULL,  
 [Service_Call_ID] [char](17) NOT NULL,  
 [WS_Note_Type] [char](1) NOT NULL,  
 [Note_Service_Index] [char](25) NOT NULL,  
 [Wennsoft_Affiliate] [char](15) NOT NULL,  
 [Wennsoft_Region] [char](15) NOT NULL,  
 [Wennsoft_Branch] [char](15) NOT NULL,  
 [USERID] [char](15) NOT NULL,  
 [Technician_ID] [char](15) NOT NULL,  
 [Technician_Team] [char](15) NOT NULL,  
 [Note_Author] [char](21) NOT NULL,  
 [Reminder] [tinyint] NOT NULL,  
 [Reminder_Date] [datetime] NOT NULL,  
 [Reminder_Finish] [datetime] NOT NULL,  
 [Printable_Group] [smallint] NOT NULL,  
 [Urgent_Checkbox] [tinyint] NOT NULL,  
 [Send_Notification] [tinyint] NOT NULL,  
 [DATE1] [datetime] NOT NULL,  
 [TIME1] [datetime] NOT NULL,  
 [CUSTNAME] [char](65) NOT NULL,  
 [NOTEINDX] [numeric](19, 5) NOT NULL,  
 [Service_User_Define_1] [char](21) NOT NULL,  
 [Service_User_Define_2] [char](21) NOT NULL,  
 [Service_User_Define_3] [char](31) NOT NULL,  
 [Service_User_Define_4] [char](31) NOT NULL,  
 [Service_User_Define_5] [int] NOT NULL,  
 [Service_User_Define_6] [int] NOT NULL,  
 [Service_User_Define_7] [int] NOT NULL,  
 [Service_User_Define_8] [int] NOT NULL,  
 [Service_User_Define_9] [datetime] NOT NULL,  
 [Service_User_Define_10] [datetime] NOT NULL,  
 [Service_User_Define_11] [datetime] NOT NULL,  
 [Service_User_Define_12] [datetime] NOT NULL,  
 [Service_User_Define_18] [tinyint] NOT NULL,  
 [Service_User_Define_19] [tinyint] NOT NULL,  
 [Service_User_Define_20] [tinyint] NOT NULL,  
 [Service_User_Define_21] [tinyint] NOT NULL,  
 [Service_User_Define_22] [numeric](19, 5) NOT NULL,  
 [Service_User_Define_23] [numeric](19, 5) NOT NULL,  
 [Service_User_Define_24] [numeric](19, 5) NOT NULL,  
 [Service_User_Define_25] [numeric](19, 5) NOT NULL,  
 [USERNAME] [char](31) NOT NULL,  
 [MODIFDT] [datetime] NOT NULL,  
 [Modified_Time] [datetime] NOT NULL,  
 [MDFUSRID] [char](15) NOT NULL,  
 [Time_Zone] [char](3) NOT NULL,  
 [SV_Language_ID] [smallint] NOT NULL,  
 [Base_Currency_ID] [char](15) NOT NULL,  
 [Base_Curr_Conv_Factor] [numeric](19, 7) NOT NULL,  
 [Billing_Currency_ID] [char](15) NOT NULL,  
 [Billing_Curr_Conv_Factor] [numeric](19, 7) NOT NULL,  
 [Local_Currency_ID] [char](15) NOT NULL,  
 [WSReserved_CB1] [tinyint] NOT NULL,  
 [WSReserved_CB2] [tinyint] NOT NULL,  
 [WSReserved_CB3] [tinyint] NOT NULL,  
 [WSReserved_CB4] [tinyint] NOT NULL,  
 [WSReserved_CB5] [tinyint] NOT NULL,  
 [WSReserved_STR1] [char](11) NOT NULL,  
 [WSReserved_STR2] [char](11) NOT NULL,  
 [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,  
 [Record_Notes] [text] NOT NULL,  
 CONSTRAINT [PKSV000805] PRIMARY KEY CLUSTERED   
(  
 [CUSTNMBR] ASC,  
 [ADRSCODE] ASC,  
 [Reference_ID] ASC,  
 [Service_Call_ID] ASC,  
 [WS_Note_Type] ASC,  
 [Note_Service_Index] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO  
  
ALTER TABLE [dbo].[SV000805]  WITH CHECK ADD CHECK  ((datepart(hour,[DATE1])=(0) AND datepart(minute,[DATE1])=(0) AND datepart(second,[DATE1])=(0) AND datepart(millisecond,[DATE1])=(0)))  
GO  
  
ALTER TABLE [dbo].[SV000805]  WITH CHECK ADD CHECK  ((datepart(hour,[MODIFDT])=(0) AND datepart(minute,[MODIFDT])=(0) AND datepart(second,[MODIFDT])=(0) AND datepart(millisecond,[MODIFDT])=(0)))  
GO  
  
ALTER TABLE [dbo].[SV000805]  WITH CHECK ADD CHECK  ((datepart(day,[Modified_Time])=(1) AND datepart(month,[Modified_Time])=(1) AND datepart(year,[Modified_Time])=(1900)))  
GO  
  
ALTER TABLE [dbo].[SV000805]  WITH CHECK ADD CHECK  ((datepart(hour,[Reminder_Date])=(0) AND datepart(minute,[Reminder_Date])=(0) AND datepart(second,[Reminder_Date])=(0) AND datepart(millisecond,[Reminder_Date])=(0)))  
GO  
  
ALTER TABLE [dbo].[SV000805]  WITH CHECK ADD CHECK  ((datepart(hour,[Reminder_Finish])=(0) AND datepart(minute,[Reminder_Finish])=(0) AND datepart(second,[Reminder_Finish])=(0) AND datepart(millisecond,[Reminder_Finish])=(0)))  
GO  
  
ALTER TABLE [dbo].[SV000805]  WITH CHECK ADD CHECK  ((datepart(hour,[Service_User_Define_9])=(0) AND datepart(minute,[Service_User_Define_9])=(0) AND datepart(second,[Service_User_Define_9])=(0) AND datepart(millisecond,[Service_User_Define_9])=(0)))  

We need to extract out notes which are stored in a "text" column. During this extraction and then rollup summary, we aggregate the notes text column into a single row based upon the Service Call Id value.

This works fine, except for the carriage returns which produce a ' ' set of characters for each carriage return within those stored notes, which causes another operation to be required to replace the ' ' with a Char(13)+Char(10). This replace function takes a substantial amount of time to replace. We have many rows in this large table. Is there a more performant solution that would speed up the execution of the statement below:

Select Service_Call_ID as SCID,   
--Max(ModifyDateTime) as   
  
--Note_Service_Index,   
Replace(RecordNote ,'
',CHAR(13)+CHAR(10)) as ResolutionNote,  
MaxModifyDateTime  
from (  
SELECT   
Service_Call_Id=Trim(ST2.Service_Call_Id), ST2.Note_Service_Index, max(ST2.MODIFDT+ST2.Modified_Time) as MaxModifyDateTime,  
            (  
                SELECT   
 convert(varchar(max), ST1.Record_Notes) + ',' AS [text()]  
                FROM  dbo.SV000805 ST1 (nolock)  
 WHERE ST1.Service_Call_Id = ST2.Service_Call_Id  
 and ST1.Note_Service_Index = ST2.Note_Service_Index  
 --and ST1.Service_Call_ID in (Select Service_Call_ID from [dbo].[vwFMP_ServiceCallAll_short] Group By Service_Call_ID )  
  
                ORDER BY ST1.Service_Call_Id, ST1.Note_Service_Index   
                FOR XML PATH ('')  
            ) RecordNote  
        FROM  dbo.SV000805 ST2 (nolock)  
  
 Where   
 WS_Note_Type='S'   
 and Note_Service_Index='Resolution'  
 and Service_Call_ID is not null  
  
  
  
 --and MODIFDT>=convert(datetime,'2019-01-01')   
 Group by Service_Call_Id, Note_Service_Index) A   
  

What improvements can we make here to boost performance as much as possible

Answer 1

Hi @vsslasd ,

1. It is better not use TEXT data type for a column. You can alter the [Record_Notes] column as VARCHAR(MAX) or NVARCHAR(MAX) data types.
2. If your SQL Server version is 2017 or later, use STRING_AGG() function to create a comma separated list.