Article Surfing Archive

The biggest hurdle a developer has to overcome when moving from traditional 3rd generation languages to T_SQL is moving the mindset from CURSOR based solutions to SET based solutions.

Don't get me wrong, I believe CURSOR based solutions have their place, and when working with small datasets you won't notice much of a difference in speed. It is only when working with the lart datasets (100,000+ rows) you will notice a dramatic improvement in speed.

I believe there is nothing like a few good examples so I will endeavour to provide multiple examples of the next few posts.

Simple Example 1:

We have two tables one contains a subset of the other; we need to move all the data from the main table to the subset table.

Customer--------IDNameStreetAddressSuburbCityZIPSexMartialStatus

MailingList-----------NameStreetSuburbCityZIP

The CURSOR based solution to do this is:

DECLARE @Name varchar(50), @Street varchar(50)DECLARE @Suburb varchar(50), @City varchar(50)DECLARE @ZIP varchar(50)DECLARE CustomerCursor CURSOR FOR  SELECT Name, StreetAddress, Suburb, City, ZIP    FROM Customer        OPEN CustomerCursor        FETCH NEXT FROM CustomerCursor INTO @Name, @Street, @Suburb, @City, @ZIPWHILE @@FETCH_STATUS = 0BEGIN  INSERT INTO MailingList    (Name, Street, Suburb, City, ZIP)  VALUES    (@Name, @Street, @Suburb, @City, @ZIP)  FETCH NEXT FROM CustomerCursor INTO @Name, @Street, @Suburb, @City, @ZIPENDCLOSE CustomerCursorDEALLOCATE CustomerCursor

The SET based solution to do this is:

INSERT INTO MailingList  SELECT Name, StreetAddress, Suburb, City, ZIP    FROM Customer

As you can probably see from the above example the simple SET based solution is much more efficient and easier to read. The SET based solution also has one other advantage, if the field sizes are to change then you don't need to worry about the code.