Let's say I have the following simple table variable:
declare @databases table
(
DatabaseID int,
Name varchar(15),
Server varchar(15)
)
-- insert a bunch rows into @databases
Is declaring and using a cursor my only option if I wanted to iterate through the rows? Is there another way?
Best Answer
First of all you should be absolutely sure you need to iterate through each row — set based operations will perform faster in every case I can think of and will normally use simpler code.
Depending on your data it may be possible to loop using just
SELECT
statements as shown below:Another alternative is to use a temporary table:
The option you should choose really depends on the structure and volume of your data.
Note: If you are using SQL Server you would be better served using:
Using
COUNT
will have to touch every single row in the table, theEXISTS
only needs to touch the first one (see Josef's answer below).