Jeff Moden (10/17/2010)
My second take on it is that a SQLCLR splitter would probably be the best way to split up large "matrices" of data passed to the database. Of course, right after that I ask "except for certain process monitoring devices (which should be captured in a file to begin with), why would anyone need to pass so much data (ie. a million rows) from an app to a database?
Jeff, the speed of large sets in CLR depends on which version of SQL Server one is using. I have noticed that in SQL Server 2005 trying to split a million items from a comma-delimited list starts out fast but slows down over time. So for SQL Server 2005 a T-SQL UDF based on a Numbers Table is the fastest way that I have seen. However, the CLR issue was fixed in SQL Server 2008 and the SQLCLR-based split function is just as fast as the T-SQL / Numbers Table version. Of course, if someone is splitting a complex set and not a simple-delimited list then CLR might certainly be the way to go in either case.
As far as to "why" anyone would need to send a million rows, it could be reading in log files to parse the data and update one or more tables that are not a simple BCP operation. In this case, as I noted above, the fastest way to send in large sets of rows is to use the streaming function combined with a Table-Valued Parameter that will allow someone to do a set-based approach that can start processing with the first row received as opposed to having to receive the entire set of data first which can be several MB if you are talking about 1 million+ rows.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR