SELECTTOP 10 substring(O.Functions,2,500) FROM dbo.Tp_Person
P CROSS APPLY
( SELECT',' + CAST(FunctionName ASvarchar) AS [text()] FROM dbo.Tp_Function
F WHERE F.Personid
= P.Personid FOR XML
PATH('')
) O( Functions )



This trick actually reduced the time of the procedure on 5000 people from about 30
seconds to 1,5 seconds. Made my day. 

Checking if a email addresses is valid in SQL can be very convinient from time to time. Here are two ways to do it in SQL, and a bit about them.

Here is a full SQL way to do this:

          
            Create
            function VALIDEMAIL(@s varchar(255)) 

--Returns
true if the string is a valid email address.

returns bit
as
BEGIN
DECLARE @u VARCHAR(60),
@v VARCHAR(60),
@x VARCHAR(60),
@i int,
@j int,
@result bit
SET @result=1
set @s
= rtrim(ltrim(@s))
SET @i=len(@s)-CHARINDEX('@',reverse(@s))+1
if @i
<= 1 orlen(@s)
< 5
begin

  set @result
= 0

  goto done
end
SET @u=LEFT(@s,@i-1)
SET @j=len(@s)-CHARINDEX('.',reverse(@s))+1
SET @v=RIGHT(@s,LEN(@s)-@j)
if @j
< 3 or @j
< @i
begin

  set @result
= 0

  goto done
end
SET @x=substring(@s,@i+1,@j-@i-1)
IFLEN(@x)<2

 BEGIN

 SET @result=0

 GOTo done

 END
IF (LEN(@x)=3) AND (@x NOTLIKE'[a-zA-Z][a-zA-Z][a-zA-Z]'Collate 
Latin1_General_BIN)

 BEGIN

 SET @result=0

 GOTo done

 END
IF (LEN(@x)=2) AND (@x NOTLIKE'[a-zA-Z][a-zA-Z]'Collate 
Latin1_General_BIN)

 BEGIN

 SET @result=0

 GOTo done

 END
SET @i=1
WHILE (@i<LEN(@u))

 BEGIN

 IFSUBSTRING(@u,@i,1) NOTLIKE'[a-zA-Z0-9_\-.+=/!#$&''*%?^`{|}"@\\
]'escape'\'Collate 
Latin1_General_BIN

  BEGIN

  SET @result=0

  GOTo done

  END

 SET @i=@i+1

 END
SET @i=1
WHILE (@i<LEN(@v))

 BEGIN

 IFSUBSTRING(@v,@i,1) NOTLIKE'[a-zA-Z]'Collate Latin1_General_BIN

  BEGIN

  SET @result=0

  GOTo done

  END

 SET @i=@i+1

 END
SET @i=1
WHILE (@i<LEN(@x))

 BEGIN

 IFSUBSTRING(@x,@i,1) NOTLIKE N'[a-zA-Z\-.0-9]'escape'\'Collate 
Latin1_General_BIN

  BEGIN

  SET @result=0

     goto done

  END

 SET @i=@i+1

 END

done:
return @result
END

GO

        

Here is another way, where you insert .NET CLR code in the database. Here I use the .NET RegEx.Ismatch, so I can use it for tons of other stuff as well with the right input.

          
            using System; using System.Collections.Generic; using System.Text; using Microsoft.SqlServer.Server; using System.Data.SqlTypes; using System.Text.RegularExpressions; namespace SQLSignature
{ publicclass SqlFunctions
{ [SqlFunction(IsDeterministic =true,
DataAccess = DataAccessKind.None)] publicstatic SqlBoolean
fn_RegexIsMatch(SqlString input, SqlString pattern) { return (SqlBoolean)Regex.IsMatch(input.Value,
pattern.Value); } } } 
        

CREATE ASSEMBLY
SQLSignature FROM'C:\CLR
Assemblies\Performance\SQLFunctions\bin\SQLFunctions.dll'; GO --
Create fn_SQLSigCLR and fn_RegexReplace functionsCREATEFUNCTION dbo.fn_RegexIsMatch(@input ASNVARCHAR(MAX),@pattern ASNVARCHAR(MAX))RETURNS BITWITH RETURNS NULLONNULL INPUT
EXTERNAL NAME SQLSignature.SQLFunctions.fn_RegexIsMatch; GO 

    select *
    from [AW].[person].emailaddress as p
    where dbo.fn_RegexIsMatch(RTRIM(LTRIM(lower(P.emailaddress))),
 '^[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$')=1 

I did some timings on the two functions and as you can see below there are big differences. The .NET CLR one is almost 8 times faster(cpu time) than the T-SQL one. It's also a whole lot easier to read, and can be used for all other RegEx's needed. You do however need to get out of management studio to make it, and 1.5 seconds is for processing almost 20000 email addresses still should be fast enough for most uses.

SETSTATISTICS TIME ON     select *
    from [AW].[person].emailaddress as p
    where dbo.VALIDEMAIL(P.emailaddress)=1
    select *
    from [AW].[person].emailaddress as p
    where dbo.fn_RegexIsMatch(RTRIM(LTRIM(lower(P.emailaddress))),
'^[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$')=1
(19929 row(s) affected) SQL Server Execution Times: CPU time = 1466 ms, elapsed time
= 1911 ms. (19972 row(s) affected) SQL Server Execution Times: CPU time = 203 ms,
elapsed time = 380 ms. 

Last week I had a problem where I needed a hierarchical chain of results. I had to get results of all organisations above my organsiation in a hierarcy. I knew how I would do this by recursion in C#, but had no idea how to get it done in SQL. But asking a bit around, and searching the web I got a really nice solution using the to me new WITH-command. And it seems quite fast aswell. Here is a SQL script with some testdata:

          
            Create
            table #Relations
( ID intPRIMARYKEY,
ChildId int,
name varchar(50)
) insertinto #Relations values (5,
4, 'Me') insertinto #Relations values (4,
3, 'Father') insertinto #Relations values (3,
2, 'Grandpa') insertinto #Relations values (2,
1, 'Great
Grandpa') insertInto #Relations values (1, null, 'Great
Old Grandpa') declare @startid intset @startid
= 5 ;WITH Names AS ( --Gets
the starting lineSELECT * FROM #Relations where id
= @startid UNIONALLSELECT r.* FROM #Relations
r INNERJOIN names
n ON n.ChildId=r.id
) select * from names
        

Here are the results. As you can see, it works;)

ID ChildId name
---------- ----------- --------------------------------------------------
5 4 Me
4 3 Father
3 2 Grandpa
2 1 Great Grandpa
1 NULL Great Old Grandpa
(5 row(s) affected)