Perhaps people are missing the point about the While loop for populating a set of numbers vs. the cross join. First, let's all agree that this would normally be a...

RBarryYoung (4/15/2009)

---

Bruce W Cassidy (4/14/2009)

---

Thomas (4/14/2009)

---

Set-based operations do not natively provide for chunking and thus we have cursors. Of course there are other ways around this problem, but at the...

RBarryYoung (4/15/2009)

---

Thomas (4/14/2009)

---

A simple example of where set based operations break down is in very large data sets. I want to take a table of 10 million rows and copy...

Apparently our feelings of disagreement are mutual. Is it not the case that in 4GL languages you tell the system what you want not how you want...

but that seems to be the problem with the use of cursors throughout a database solution. Many developers do not know these "ingeneous" ways of solving a problem. I...

I stand corrected. Cross joining on syscolumns does appear to be a bit faster. Perhaps this is one loop based solution that now has a reasonable set-based solution.

Couple of very simple solutions.

First one is SQL 2005/2008 only, won't work in 2000:

create table dbo.Numbers (Number int primary key);

go

insert into dbo.Numbers (Number)

select top 10000 row_number() over (order by t1.object_id)

from...

Girish Bhat (4/15/2009)

---

Thomas (4/14/2009)

---

This type of discussion reminds me of heated arguments I have had with Mr Celko about SQL standards......

A simple example of where set based operations break down...

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...

[font="Verdana"]Agreed (both on SSIS and on the necessity for "chunking", although I call this batching (same concept). I wouldn't use a cursor for that though -- I would...

This type of discussion reminds me of heated arguments I have had with Mr Celko about SQL standards. Yes, we should strive to use set based SQL syntax (and SQL...

IMO, the best way to think about this is using the Fiefdom analogy. Think of a fiefdom as a self-contained system. Messages/data can be sent...

First, some comments on items in the article or posts...

 

> I will acknowledge how tempting...

The heat dissapation of the bulbs might throw off Amit's or my solution:

A variant on Amit's answer:

Turn on switch one and wait a minute then turn it off.

Turn on switch...

The simple answer to your question Calvin is that tables are far less amenible to change than data. In most cases, that lack of flexibility works in your favor but...