SQL Server and CLR, batching SqlFunction
I have a CLR function that returns "n" rows with random data. For example, to prime an empty table with 100 rows of test data I could write
INSERT INTO CustomerInfo(FirstName, LastName, City...) SELECT FirstName, LastName, City... FROM MyCLRFunction(100)
This would return 100 "customers" with random information. If I were to call this with a very high number I would get an out of memory error, since the entire dataset is created before it gets sent to the caller. I can, of course, use the SqlPipe object and send rows as they are created but as far as I can tell you can only use this approach with SqlProcedures. That would mean that I can't use an INSERT INTO approach since you can't SELECT from a stored proc.
I'm hoping that I've just missed something here and that it is actually possible to combine SqlPipe.SendResultRow with a function, or that someone has a clever workaround.
I could leave it as a proc and have that proc put these records into a session-scoped temporary table. Then the caller could use that table in their SELECT clause but I'm hoping for the best of all worlds where I can provide a nice, clean syntax to the caller and still scale to a large number of records.
Frankly, the original solution is probably "good enough" since we will probably never want this much test data and even if we did we could run the INSERT statement multiple times. But I'm trying to get a full understanding of CLR integration and wondering how I would address this if a similar use case presented itself in a business scenario.
Looking into streaming SQLCLR table valued functions - http://msdn.microsoft.com/en-us/library/ms131103.aspx
You basically return an IEnumerable to SQL Server and let it consume it, thereby not needing to materialize all the results before returning them.
I found a solution. Instead of returning the entire list of items, the solution was to use
This causes the FillRowMethod to be fired for each entity processed, effectively streaming the results back to the caller.
Glad to have this figured out but a little embarrased by how simple the final solution was.