the sqlist (3/24/2012)
...Which makes the issue more rhetorical than anything.
Sometimes it is better to keep the things simple in terms of solution and not to think about the effects at the deepest level. But again, sometimes.
It's not really a case of thinking of something at the deepest level - it's a case of finding the fastest tool to add to your toolkit. Developers are always looking to reuse code, we root around in whatever is already there and cherrypick code to do similar jobs. Choosing a slow row generator over a fast one for production code becomes a double whammy - the first job to use it, then every time a developer cherrypicks it for a new job.
Now, imagine importing a bunch of csv files. There are 25 of them, each around 250k rows. The delimiters vary, pipe or comma depending on the file, and the line termination is any combination of CR, LF, or both - so Bulkcopy and Import won't work to resolve the columns out of the rows. You would face a similar problem if the column layout in the csv files was different.
A good solution to this is to import the csv files into a staging table(s) as one column, then split the delimited string in that column using a TSQL splitter. Choosing the right tool for this is important, because you're no longer looking at 23 elements in a single string - you're looking at over 6 million executions of the splitter.
The splitter based on the row generator I posted isn't overcomplicated. Here's a snapshot (a snapshot because I tinker with it from time to time):
ALTER FUNCTION [dbo].[DelimitedSplit8K_CTE]
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
ItemNumber = 0,
Item = LEFT(@pString, CHARINDEX(@pDelimiter,@pString+@pDelimiter,0)-1)
ItemNumber = ROW_NUMBER() OVER(ORDER BY @@SPID),
Item = SUBSTRING(@pString,1+n,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,1+n),0)-(1+n),8000))
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) n = n1+n2+n3+n4
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2))
CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4)
WHERE SUBSTRING(@pString,n,1) = @pDelimiter
4ms per execution adds an extra hour per million rows.
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]
For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and
(II)[/url] Paul White
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]