Disagreements are good -- better ideas happen that way. One thing I think that Jeff should take more seriously, though, is that some projects may have constraints that disallow certain techniques (xp_cmdshell for one), whether we like it or not. Suppose the data load has to run at customer sites -- no DBA attending, no elevated privileges on customer machines... I have used bcp and bulk insert in a number of situations and do think they are valuable, just may not fit every job.
Heh... I do take other methods/requirements seriously . That's why I'll recommend Bulk Insert over BCP most of the time or even recommend the use of SSIS.
Let's flip this around a bit... I get harped on for advocating doing most everything having to do with data in the data layer and in T-SQL. Forgive me if I have to harp on the people who want to do data layer stuff everywhere except in the data layer or T-SQL. ;-)
For example, I frequently see posts where people ask about how to loop through files to import, how to keep track of what they imported, how to clean and glean the data once it's imported, and how to do that all with some good bit of performance because they claim something crazy (not so crazy in a lot of cases) like having the requirement to import something like 5 billion rows across hundreds of files per day. Can it be done using SSIS? Absolutely... but then you see folks saying to write scripts in Perl, ActiveX, VBS, DLL's, etc, etc to actually be able to pull if off in SSIS (or DTS) and you'll still see them having problems with flow control, variables, transformations, performance, data integrity, etc, etc. That's when I step in (sometimes well in advance) and suggest alternatives because none of that stuff is actually necessary. GUI code like SSIS and DTS were supposed to make life simpler and faster.. they frequently don't.
The other thing I don't see people doing is taking advantage of some of the power of SQL because of requirements like not using XP_CmdShell. For security reasons, I can certainly see that. BUT, there are also some pretty extreme privs required for ETL systems that use SSIS... you have to be able to read the files names, import the data, and maybe rename the file to identify it's complete or move the file to a "competed" directory. Tell me that doesn't take elevated privs on the operating system. Just like in SSIS, sometimes those privs have to be done through a proxy... same goes with xp_CmdShell... you don't have to give anyone privs to run xp_CmdShell... you can setup proxies and jobs and it's normally worth that small effort because... T-SQL was meant to handle data. Use it.
So far as some projects having requirements go, yep.. I agree. Right up to the point when I ask "why" such a requirement is in place and the answer is "because they want it that way". What that means is that that person hasn't taken the time to suggest and prove that there might be a better way with "better" meaning higher performance, ease of maintenance/modifiation, hands-free/self monitoring qualities, smaller footprint, etc, etc. Even if they do, the reason for the requirement is based on some blanket myth that xp_CmdShell is bad without thinking about how to do it with a proxy or whatever.
Here's some really "great" requirements I've seen due to arbitrary mythological beliefs of some DBA or manager that doesn't have a clue as to what they're talking about...
Cannot use Temp Tables.
Cannot use SELECT/INTO because it locks up system tables.
Cannot use any form of dynamic SQL.
Cannot use Bulk Insert because of the dynamic SQL requirement.
Cannot use Cursors or While Loops to step through databases. Use sp_MsForEachDB instead (which has the world's 2nd ugliest cursor built in).
The code must be "portable".
Cannot use UDF's.
The list of such mad requirements goes on. So, heh... pardon me if I try to offer alternatives even in the face of so called requirements. People say to "think outside the box" and it usually comes from people that don't realize... they're in box. ;-)
I frequently state that "On the fringe of chaos lives innovation." Let me disagree without getting chastized for it and let me disagree when someone says they won't even try to change some of the more ridiculous requirements they've been given by people who don't actually know better.
Like you said, "disagreement is good"... let me disagree without getting knocked just because I disagree, eh? ;-)
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs