so i went for the SQL CLR just to see if i could get it to work (been on a learning kick right now so im going with it) and here are my results.
Duration for CHARINDEX = 00:00:21:963
Duration for LEN = 00:00:16:217
Duration for CHARINDEX with COLLATE = 00:00:10:260
Duration for PARSENAME = 00:00:11:043
Duration for LIKE = 00:00:11:293
Duration for LIKE with Collate = 00:00:10:707
Duration for SQLCLR = 00:00:14:427
What i added to the run
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
DECLARE @HOLDER INT, @Duration CHAR(12), @StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @HOLDER = ID
FROM #testEnvironment
WHERE dbo.RegexCLR(ISNULL(yourData,'')) = 1
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Duration for SQLCLR = %s',0,1,@Duration) WITH NOWAIT;
and the code for the SQLCLR
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction()]
public static bool RegexCLR(string input)
{
return Regex.IsMatch(input,@"^[^\.]+\.[^\.]+$");
}
};
of course when we change the NTEXT to NVARCHAR(MAX) i get the following:
Duration for CHARINDEX = 00:00:06:030
Duration for LEN = 00:00:03:253
Duration for CHARINDEX with COLLATE = 00:00:00:943
Duration for PARSENAME = 00:00:01:147
Duration for LIKE = 00:00:07:253
Duration for LIKE with Collate = 00:00:01:353
Duration for SQLCLR = 00:00:04:040
they are all really close over 1 million records.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]