Jeff,
I think your reasoning is not best. I know George already covered this, but I feel it useful to restate this more strongly.
Sometimes we do things as professionals that not all beginners can understand, but when we do, it should be for a compelling reason. And I think that the tiny performance benefit your method gains is not worth the extra cost (difficulty understanding it, potential collision with existing column values).
In my opinion, inserting values to strings that "shouldn't already be in the data" is a recipe for someone, somewhere, to royally screw up his data. Weighing that against the possibility that some SQL neophyte
might inappropriately use a loop on something because he once saw a loop used for an entirely different problem, I'd any day take the inappropriate loop (whose cure is simply a bit of experience + wit and a recode) over the data corruption, whose penalty is high and cure is difficult or impossible.
Let me say this another way. If your logic is valid that "someone might see you use a loop and then do something not so great later", then it applies to your method, too!
Someone might see your technique for inserting "placeholder characters" into strings and start using it inappropriately. Your only explanation was 'is the nonprintable "bell" character that most cannot type into a string.' Only most? What about those that can type this (such as I)? What about some beginner who looks up nonprintable characters, and upon learning that there are a lot of them from ascii 0 through 31, starts using other nonprintable characters? Try putting an ascii 0 in the middle of a varchar column and then selecting that column plus others in sql 2000 query analyzer. Does it do what you expected?
Fully analyzed, while your method is clever (and for that alone I really
do like it and will remember it) I have to professionally recommend that people not use it.
Finally, while I share your utter distaste for loops, I think you are incorrect to dismiss them entirely. And please note that George's loop is not a case of RBAR, because though repeated, he uses a set-based operation, so your biggest criticism doesn't even apply! And also, please note that behind the scenes, SQL Server itself uses... wait for it...
loops (gasp).
Here are other situations where loops are valuable to the professional SQL programmer:
1. Batching gigantic updates. The idea here is not a loop for every row, but repeatedly performing a set-based operation that is limited to a certain number of rows until it is completed.
So, I spent multiple days searching for a good way to update some multiple million row tables based on very complex joins. My findings were interesting.
First of all, it was to a production OLTP database where locking any object more than a few fractions of a second is not ok. Measuring the cost of my query only by total cpu or reads completely ignores the availability aspect. My boss doesn't care if I used 5 million otherwise-unused clock cycles on the prod CPU box, as long as no one else notices an impact on the system during that time. But he'll be really mad if I cause anyone to stop in his work because "overall I'll save 1 million cycles."
Second, looping and doing small batches actually performed the whole set of work dozens of times faster. The reason is because I made strategic use of temp tables and carefully crafted my query to "walk the clustered index" of the biggest, widest table. So what happened is that my query execution plan was able to use repeated small loop joins instead of a massive, massive hash join.
2. I have a reporting database that sends documents to another system. Data about the reports end up in a SQL database. So I made the system "confirmation-based" instead of "activity-based" so that it doesn't consider the report completed simply because it created the report and sent it, it waits until it sees it arrive in the other system. Well, the best way to compare data between SQL Servers is a linked server query. But again, the biggest concern here is about performance impact to other users, so I did some test queries and ran traces on the remote server to see what the DB library was doing behind the scenes with many different versions of joins. To make a long story short, the best possible performance I could achieve was with a serially applied CROSS APPLY (which in effect forces RBAR access) instead of trying to do a distributed transaction joining multiple remote tables. Performance of my reconciliation SP skyrocketed upward. Essentially, what I was doing was modeling across the linked server what is called a "bookmark lookup" in SQL 2000 execution plan parlance, or a "nonclustered index seek followed by clustered index seek against the same table" in SQL 2005 parlance.
3. In a database where availability didn't matter, I still wanted my queries to perform better for a huge analysis project I was doing, on a database with 3.5 million pages (that I was doing OCR against). Instead of using the complex UDF that ran against each row to calculate the UNC path from a special index value, I found a set-based way to do it. But I still had to batch it to get it to work well. And batching did work well: it increased performance, again by converting hash joins back to loop joins.
I'm reminded of
Benner's phases of Clinical Competence.
• Rules given to novices are context-free and independent of specific cases; hence the rules tend to be applied universally. The rule-governed behavior typical of the novice is extremely limited and inflexible. As such, novices have no "life experience" in the application of rules.
Then skipping the intermediate steps to show the contrast I intend:
• The expert performer no longer relies on an analytic principle (rule, guideline, maxim) to connect her or his understanding of the situation to an appropriate action. The expert, with an enormous background of experience, now has an intuitive grasp of each situation and zeroes in on the accurate region of the problem without wasteful consideration of a large range of unfruitful, alternative diagnoses and solutions. The expert operates from a deep understanding of the total situation.
I am not suggesting you are a beginner, but I do think you are still applying rules too rigidly in your professional thought. Would you ever have considered using loops to solve my three examples above? Loops turnedd out to be, in fact, really good answers. Though I recognize you may not believe me about that.
Erik
P.S. I don't claim to be a master of SQL.