Chapter 16 Understanding Cursors, Sets, and Temporary Tables Flashcards
What is meant by the term set-based?
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.
What is meant by the term iterative solutions?
Iterative solutions use cursors or other iterative constructs to handle one row at a time.
Why are set-based solutions generally recommended over iterative solutions?
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.
What are the exceptional cases where iterative solutions should be preferred?
(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.
Show an example of how a cursor should be used.
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
What are the commands required to work with a cursor?
DECLARE (C CURSOR FAST_FORWARD FOR), OPEN, FETCH (NEXT), WHILE (@@FETCH_STATUS), CLOSE, DEALLOCATE
When declaring a CURSOR, what does the FAST_FORWARD option do?
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.
What are the possible values returned from @@FETCH_STATUS?
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.
When you fetch rows from a cursor, how do you know when there are no more rows to fetch?
When the @@FETCH_STATUS returns -1.
Is there another way to achieve an iterative solution without a cursor?
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;
What options are available in SQL Server to store data temporarily?
Temporary tables (local and global) as well as table variables.
How are the different types of temporary table named?
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.
Describe the scope of the different types of temporary tables.
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.
Can two tables have constraints with the same name in the same schema?
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.
Where are temporary tables physically created?
Temporary tables are created in the tempdb database in the dbo schema.