Girish Bhat (4/15/2009)

---

Hi Thomas,

Your point is well taken. For the specific example you have mentioned, you could use:

sp_MSforeachtable
@command1 = "Alter Table ? Alter Column Foo nvarchar(10)",
@whereand = "and OBJECT_ID in (select object_ID from sys.columns where name = 'Foo')"

Tom.Thomson (4/29/2009)

---

That's far too slow

Try instead:
select power(count(*),2) from master.sys.columns

Comparing equivalent code on SQL Server 2000 indicates that this takes about 25% of the time your cross join method takes, if there are about 5000 rows in the columns table.

gserdijn (4/14/2009)

---

select square(count(1)) From master.sys.columns C1

RBarryYoung (4/15/2009)

---

Just because pure set-based SQL may not be sufficient for a particular task does not mean that that justifies Cursors or loops in SQL. There are still a lot of miles between the two and a lot of other options that are not even on the same scale.

WayneS (4/18/2009)

---

I just thought of something that I wish I had included with my original post.
Programming languages have evolved. 15 years ago, you had plain old programming languages, complete with procedural ways of doing things. Then came object-oriented programming languages. To be a good OOP programmer, one had to start thinking of things as objects, and you had to deal with OOP principals, such as events, properties, encapusaltion and polymorphism. This thought shift did not occur overnight - you had to immerse yourself in it and work at it. Not all programmers could make this shift, and for others it took a long time before the light clicked on behind their eyes and they understood it.

Tom.Thomson (4/29/2009)

---

The trouble with this statement is that Barry made it absolutely clear which sense of the word "declarative" he was using. There are numerous different definitions of "declarative", but Barry was very clear: declarative in his sense means specifying the result you want (chocolate chip cookies) rather than specifying a method of achieving it (make the mix, bake it). You've fallen into the trap of saying that "the desired result is cookies with chocolate chips" means the same as "make a cookie mix and bake it to get cookies with chocolate chips" but they are clearly different: the former leaves you the choice between buying a bag of cookies, cooking your own cookies, or commissioning someone else to cook them for you (and a few other methods, of course); the latter requires you to cook them, not buy them or get someone else to make them. So the former is not at all procedural - how can it be, when it doesn't constrain the procedure for achieving the end result, it only constrains the end result?

However, I don't completely agree that SQL is actually declarative in Barry's sense, although a very large subset of it is. There are things that I can only express by an ordered sequence of statements - for example if I want to update several tables (unless there are some new language extensions I haven't heard about yet) - particularly if later updates depend on some characteristic of the earlier ones (total number of rows affected so far, for example).

Something that SQL could usefully acquire from other declarative languages is a MAP operator (to map a stored procedure or an extended stored procedure over a table where each row represents a set of parameters for it, with no defined order in the absence of an order by clause). This would be much cleaner than the current game of building a string of commands and using exec SQL on it, and on cases where the order clause was absent it would allow parallelism that isn't achievable with exec(SQL) (since teh execution of a series of SQL statements is serial, although parallelism can happen within an individual statement). It might also be good to pick up the REDUCE operator from the same school - seems rather better than the current "select @v = expr involving @v from ..." way of doing reduce, and may add extra flexibility (and why does @v in that have to be a scalar variable? perhaps it's because tables are no "first class objects" in SQL?).

Tom.Thomson (4/30/2009)

---

Often the best solution is a loop -

... in fact I can't see a way of doing batching/chunking with delays between batches without using a loop.

I usually express my loop as a schedule in MSDB, and I usualy use SQL to create that schedule rather than going through the job creation wizard, since the job may have to be created at every customer site and running some SQL is a lot less time consuming than using the wizard. Would it be reasonable to claim that I'm not writing loops in SQL? Maybe, but a loop is what I want for my solution and I'm telling the SQL system what loop I want, so if SQL as a declarative language that's writing a loop in SQL.

I'm being a bit pedantic here, I know, but I,m doing it because think your over-strong denial of the usefulness of loops detracts from your message.

Now if instead of loops you had said "while loops" that would be a different story, of course (I use those too sometimes, in SQL 2000, but only because 8000 isn't big enough no need in SQL 2005 and onwards).

... declarative in his sense means specifying the result you want (chocolate chip cookies) rather than specifying a method of achieving it (make the mix, bake it). You've fallen into the trap of saying that "the desired result is cookies with chocolate chips" means the same as "make a cookie mix and bake it to get cookies with chocolate chips" but they are clearly different: the former leaves you the choice between buying a bag of cookies, cooking your own cookies, or commissioning someone else to cook them for you (and a few other methods, of course); the latter requires you to cook them, not buy them or get someone else to make them. So the former is not at all procedural - how can it be, when it doesn't constrain the procedure for achieving the end result, it only constrains the end result?