SQL Server 2008 RegEx CLR Use

Question

OK, I realize that SS 2008 doesn't have native regex functions. I also see that it's possible to add them using CLR. I found the regex CLR at http://msdn.microsoft.com/en-us/magazine/cc163473.aspx , but I don't know what to do with them.

So I have 2 questions. The regex is only used when importing records (up to 1,000,000 at a time, and up to 50,000,000 a year) against 2 fields.

1st question. I can do it manually 1 record at a time in my code (ASP.NET), and I know it would be a performance hit, but no idea how big of one. Any input on this?

2nd question. Can someone point me to a simple step by step instruction on how to install the CLR files I downloaded? I tried doing a search, but that either didn't show the info I needed, or was too buried in other info for me to find it.

Thanks

P.S. I'm running SS 2008 (not R2) and VS.NET 2008.

Answer 1

See the article here for a complete tutorial.

Answer 2

If you only need REGEX for a dataload then you could do it one record at a time in ASP.NET. Even with SQL CLR integration you would be doing it one record at a time as SQL would pass the value one records at a time. If you want to use REGEX as a condition in the where clause then SQL CLR integration would be the only option.

What I am doing is parsing one line at a time. One line will result in 1 - 5 SQL inserts. Then I do those SQL inserts asynch. If the next parse completes before the insert I wait. In my case it is about a dead heat so I get parallel processing. If SQL calls regex you are limited to serial processing.

I parse and load data a lot. If you are concerned about speed look more at the SQL side than parse. I go so far as to disable SQL indexes parse and load 10 million records and then rebuild indexes. Regex a line at time is typically faster than SQL insert unless the SQL table has no indexes.