Chapter 16 written review

Chapter 16 dealt with cursors, and the explanation of cursors vs set based operations (kind of like in chapter 1).  It talked about loops a little more, and how a loop is more of a cursor then a set based operation, and how a set based operation can be more efficient because it can be tuned and optimized.  The things that I got from lesson 1 was that if you are going to use a cursor you must first declare it, open a statement (batch), fetch in a loop, close, then deallocate the cursor.

Lesson 2 was interesting because it talked about temp tables vs table variables.  Interesting because this was discussed at SQL Saturday a week or so ago.  Brent Ozark talked about this in his seminar "think like an engine" where he compared indexes, and brought up the idea of variable tables.  From what I learned about them from him is that they are virtually useless.  One of the lesson review questions gives choices as to when is a table variable useful (choose all), and the answers were: "when the tables are very small and the plan is trivial, when the tables are very small and the plan is nontrivial, when the tables are large and the plan is trivial"; which to me seems like, pretty much never.  Other things I learned that I thought were useful in this lesson were that if you are going to create a temp table that can be seen on a local level you use the "#" sign; such as:
CREATE TABLE #temp1
(
 col1 INT NOT NULL,
 col2 INT NOT NULL
)
But if you wanted to create one and have it be seen on a global level you use '##'; such as:
CREATE TABLE ##glob1
(
 col1 INT NOT NULL,
 col2 INT NOT NULL
)

An interesting chapter again, I will be reading the last chapter maybe tomorrow (maybe not, it's my birthday), then doing some sand box homework in the next few evenings.

Cheers!!!