rons-605185 (12/1/2009)
Per Lynn's quoted reply below. Being new to SQL (my background was some light VB programming) I am unclear by what is meant by a set based solution. Most of the time I am working in SQL to derive specific rows of data meeting a specific criteria.In the task I am currently working on, we have a table with Notes - basic fields are note entry date, note author, author's e-mail address, note type, the note itself, and the associated companyname. Normally I would search row by row for notes that meet a specified criteria, usually using a cursor. Are you stating to run a different type of query and pull all the row results into a "set"? If so, what is the difference between running a cursor and pulling rows together in a set? Sorry, but I am unclear about the "set-based solution".
Please advise.
This is a very common issue among VB programmers. VB uses loops for... everything. SQL does not operate well in a loop. SQL is a set based language. Will the same loops / procedural constructs that you're so used to using in VB *work* in SQL? Yes, they will. But they will be extremely inefficient and could take exponentially longer than a "set based" solution in SQL would. It's not a question of function, it's a question of performance.
It's probably going to be hard to start thinking of it in a different manner (I work with a lot of VB / .NET programmers and they often have a hard time really getting the concept of 'set based' as opposed to prodecural), but if you're going to be writing much T-SQL, you really need to start trying to think that way.