jonharding2204 (10/31/2014)
I think you should look into normalizing (splitting the strings so that each value has it's own row) the BID and BWG columns in the #MD table so that you can use straightforward joins. As a starting point you'll need to find out what the longest string values are in the BID and BWG columns.Then, I suggest you have a close look at the following articles from Jeff Moden:
The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]
Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]
And read this blog post from Aaron Bertrand:
Split strings the right way – or the next best way
Between them they should put you on the right track for a solution with decent performance.
In seeing that article, I feel compelled to point out a significant difference in the functions. Jeff's articles you posted above use a varchar(8000) as the string parameter to be split and Aaron's function uses a varchar(max). I have gone pretty far down this road and they do not have the same performance.
I've tried this using 1M-row and 10M-row test tables. I used strings up to 7998 characters in length in a varchar(8000) column and got my baseline performance.
I then created a table with varchar(max) and used the exact same strings as I did in the baseline table with a new version of the function to handle the max and the execution time nearly doubled. I tried quite a few approaches to handle the max. While certain approaches produced better results than others, the performance of the original just wasn't there. The max data types are handled differently under the hood and they just don't like to be joined to.
If you need to split a varchar(8000) or nvarchar(4000), the delimited split functions are excellent tools. If you need to split a string that's larger, I would make a separate function and suffer the performance hit only when absolutely necessary.
Of course, the alternative to wrestling with it is to create a CLR and use it for everything.