Hi I have a view which is used in lots of search queries in my application. The issue is application queries which use this view is is running very slow.I am investigating this and i found out a particular portion on the view definition which is making it slow.
create view Demoview AS Select p.Id as Id, ----------, STUFF((SELECT ',' + [dbo].[OnlyAlphaNum](colDesc) FROM dbo.ContactInfoDetails cd WHERE pp.FormId = f.Id AND ppc.PageId = pp.Id FOR XML PATH('')), 1, 1, '') AS PhoneNumber, p.FirstName as Fname, From ---
This is one of the column in the view.
The scalar function
[OnlyAlphaNum] is making it slow,as it stops parallel execution of the query.
The function is as below;
CREATE FUNCTION [dbo].[OnlyAlphaNum] ( @String VARCHAR(MAX) ) RETURNS VARCHAR(MAX) WITH SCHEMABINDING AS BEGIN WHILE PATINDEX('%[^A-Z0-9]%', @String) > 0 SET @String = STUFF(@String, PATINDEX('%[^A-Z0-9]%', @String), 1, '') RETURN @String END
How can i convert it into an inline function.? I tried with CASE ,but not successful.I learned that while loop functions cannot me converted to ITVF.It can be converted to MTVF.Another option is Tally Table which i have no idea about. Can someone help me to convert it to TVF?