Chapter 16 Understanding Cursors, Sets, and Temporary Tables

Question 1

What is meant by the term set-based?

Answer 1

The term set-based is used to describe an approach to handle querying tasks and is based on principles from the relational model.

Remember that the relational model (and hence the foundation of SQL) is based in part on mathematical set theory.

Set-based solutions use T-SQL queries which operate on the input tables as sets of rows.

According to set theory, a set should be considered as a whole. This means that your attention should be focused on the set and not on its individual elements.

Question 2

What is meant by the term iterative solutions?

Answer 2

Iterative solutions use cursors or other iterative constructs to handle one row at a time.

Question 3

Why are set-based solutions generally recommended over iterative solutions?

Answer 3

It is generally recommended to use set-based solutions by default, and leave iterative solutions to exceptional cases.

(1) Set theory is the foundation of the relation model which is in turn the foundation of SQL. By using iterative solutions, you are going against the principles of the foundations of the language.
(2) Iterative solutions tend to be much longer than set based ones and harder to follow and maintain. With set based solutions, you focus on the “what” part of the request and let the database engine worry about the “how”. Iterative solutions contain both the “what” and the “how”.
(3) Iterative constructs in T-SQL are very slow. For one thing, loops in T SQL are much slower than those in other languages (such as .NET). Each record fetch from a cursor by using the FETCH NEXT command has high overhead.

Question 4

What are the exceptional cases where iterative solutions should be preferred?

Answer 4

(1) This could happen when the optimizer doesn’t manage to produce an efficient plan for the query and you cannot find ways to tune the query better. With iterative solutions, you do have more control because you are responsible for the “how” part.
(2) Management tasks that need to be done per object in a set, such as a set of databases, tables, or indexes. For example, if you need to rebuilding indexes that have a higher level of fragmentation.
(3) Suppose that you need to call a stored procedure that can only be called for one “Customer” at a time.

Question 5

Show an example of how a cursor should be used.

Answer 5

DECLARE @custid AS INT;

DECLARE C CURSOR FAST_FORWARD FOR
SELECT custid
FROM Sales.Customers;

OPEN C;

FETCH NEXT FROM C INTO @custid;

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC Sales.Process @custid;

FETCH NEXT FROM C INTO @custid;

END;

CLOSE C;

DEALLOCATE C;
GO

Question 6

What are the commands required to work with a cursor?

Answer 6

DECLARE (C CURSOR FAST_FORWARD FOR), OPEN, FETCH (NEXT), WHILE (@@FETCH_STATUS), CLOSE, DEALLOCATE

Question 7

When declaring a CURSOR, what does the FAST_FORWARD option do?

Answer 7

You can use the FAST_FORWARD option to make a read-only, forward-only cursor (FORWARD_ONLY and READ_ONLY).

FORWARD_ONLY: Specifies that the cursor can only be scrolled from the first to the last row.

READ_ONLY: Prevents updates made through this cursor.

Question 8

What are the possible values returned from @@FETCH_STATUS?

Answer 8

The possible return values from the @@FETCH_STATUS function are:

0 - When the previous fetch was successful

• 1 - When the row is beyond the result set
• 2 - When the row fetched is missing. There could still be more rows.

Question 9

When you fetch rows from a cursor, how do you know when there are no more rows to fetch?

Answer 9

When the @@FETCH_STATUS returns -1.

Question 10

Is there another way to achieve an iterative solution without a cursor?

Answer 10

Yes. You can use a query with a TOP(1) option ordered by the id column to return the minimum id. Then loop while the last query does not return NULL. In each iteration of the loop, execute the stored procedure using the id as input. To get the next id, issue a query with a TOP(1) option where the id column is greater than the previous one.

For example:

DECLARE @custid AS INT;

SET @id = (SELECT TOP(1) custid
FROM Sales.Customers
ORDER BY custid);

WHILE @custid IS NOT NULL
BEGIN

EXEC Sales.ProcessCustomer @custid

SET @custid = (SELECT TOP(1) custid
FROM Sales.Customers
WHERE custid > @custid
ORDER BY custid);

END;

Question 11

What options are available in SQL Server to store data temporarily?

Answer 11

Temporary tables (local and global) as well as table variables.

Question 12

How are the different types of temporary table named?

Answer 12

Local temporary tables are named with a single number sign as the prefix, for example #T1.

Global temporary tables are named with two number signs as a prefix, for example ##T1.

Table variables are declared (as opposed to being created), and they are named with the at sign (@) as a prefix; for example, @T1.

Question 13

Describe the scope of the different types of temporary tables.

Answer 13

Different sessions can actually create local temporary tables with the same name, and each session will see only its table. Behind the scenes, SQL Server adds unique suffixes to make the names unique in the database, but this is transparent to the sessions.

Local temporary tables are visible throughout the level that created them, across batches, and in all inner levels of the call stack. If you don’t drop the temporary tables explicitly, it is destroyed when the level that created it terminates.

Global temporary tables are visible to all sessions. They are destroyed when the session that created them terminates and there are no active references to them.

Table variables are only visible to the batch that declared them and are destroyed automatically at the end of the batch. They are not visible across batches in the same level, and are also not visible to inner levels in the call stack.

Question 14

Can two tables have constraints with the same name in the same schema?

Answer 14

No. As it turns out, constraint names are considered object names in the schema, and object names must be unique per schema - not per table.

Question 15

Where are temporary tables physically created?

Answer 15

Temporary tables are created in the tempdb database in the dbo schema.

Question 16

What happens if you define a constraint for a temporary table without naming it?

Answer 16

If you define a constraint on a temporary table without naming it, SQL Server internally creates a unique name for it. The recommendation therefor is not to name constraints in temporary tables so that there are no naming conflicts:

CREATE TABLE #T1
(
   col1 INT NOT NULL,
   col2 INT NOT NULL,
   col3 DATE NOT NULL
   PRIMARY KEY(col1)
);

Question 17

Can you create indexes on temporary tables after the table is created?

Answer 17

Yes. With SQL Server, you can create indexes on temporary tables after the table is created. For example:

CREATE UNIQUE NONCLUSTERED INDEX idx_col2 ON #T1(col2);

Question 18

When using a table variable, can you explicitly name a constraint?

Answer 18

No. SQL Server doesn’t allow explicit naming of constraints - not even in a single session.

Question 19

When using a table variable, does SQL Server allow the creation of indexes after the table is declared (DDL)?

Answer 19

No. With table variables, you have to do this indirectly by defining constraints, but only at the time the table variable is declared.

Recall that when you define a PK, SQL Server enforces its uniqueness by using a clustered index by default.

When you define a unique constraint, SQL Server enforces it’s uniqueness by using a unique non clustered index.

Question 20

Where are table variables physically created?

Answer 20

Table variables (like temporary tables) have a physical representation in tempdb. They are given an internal name such as #BD095663.

You can find entries in the tempdb.sys.objects view for the internal tables that SQL Server creates in tempdb to implement you temporary tables and table variables.

SELECT name FROM tempdb.sys.objects WHERE name LIKE ‘#%’;

Question 21

Are table expressions such as CTE’s persisted like temporary tables and table variables?

Answer 21

No. When SQL Server optimizes a query against a table expression, it unnests the inner query’s logic and interacts directly with the underlying tables. This means that unlike temporary tables and table variables, tables expressions have no physical side to them.

Question 22

How do temporary tables interact with transactions?

Answer 22

Temporary tables are similar to regular tables in that changes applied to a temporary table are undone if the transaction rolled back.

Question 23

How do table variables interact with transactions?

Answer 23

Table variables are similar to normal variables in that changes applied to a table variable in a transaction are not undone if the transaction rolls back.

Note that a single statement against a table variable must be atomic, so if the statement fails before completion, the partial change is undone. But if a single statement finishes and a user transaction is rolled back, such a change isn’t undone.

Question 24

How do temporary tables and table variables differ when it comes to performance (statistics)?

Answer 24

SQL Server maintains distribution statistics (histograms) for temporary tables but not for table variables. This means that, generally speaking, you tend to get more optimal plans for temporary tables. This comes at the cost of maintaining histograms, and at the cost of recompilations that are associated with histogram refreshes.

When plan efficiency depends on the existence of histograms, you should use temporary tables. Table variables are fine to use in two general cases: One is when the volume of data is so small, like a page or two, that the efficiency of the plan isn’t important. The other case is when the plan is trivial. A trivial plan means that there’s only one sensible plan and the optimizer doesn’t really need histograms to come up with this conclusion.

Question 25

Can you name constraints in local temporary tables and in table variables?

Answer 25

You can name constraints in local temporary tables, although it’s not recommended because it can generate name conflicts. You cannot name constraints in table variables.