Locking Wrap-Up

Locks differ in scope (row, page, table), exclusivity (optimistic, shared, exclusive), and persistence. You would like the scope of your locks to be absolutely minimal, though SQL Server does not offer row-level locking through its automatic locking mechanisms. While a user holds data onscreen, you want as little exclusivity as possible (this permits users to read data without interruption), but you would like to guarantee that a row has not changed when you return to update it.

Optimistic locking provides the best mix of scope and exclusivity, but you need to be extremely careful to make certain that all users participate in the optimistic locking scheme for updates. Otherwise errors will creep in that undermine the validity of your data.

Understanding Worktables

This section looks at how the server uses worktables to resolve certain queries. Your knowledge and understanding of worktables will allow you to accomplish more in fewer steps with better query performance and less T-SQL code.

A worktable is a temporary table created automatically by SQL Server in order to resolve one of a small set of query types. The worktable is a storage location for maintaining intermediate values that will be used to develop a final result set.

What T-SQL Operations Use Worktables

SQL Server uses worktables to resolve SELECT statements that include DISTINCT, GROUP BY, and ORDER BY clauses. In the case of DISTINCT and ORDER BY, the worktable is used to sort the data. In the case of GROUP BY, the worktable stores temporary aggregate values as SQL Server processes each row in the table.

Can I See a Worktable?

You can’t directly access a worktable, but you can tell when the server is using one. If you turn on SHOWPLAN (you learned about showplan on Day 15), the server lets you know when a worktable is being used. I’ll actually turn on both SHOWPLAN and NOEXEC so that I can look at execution plans without looking at the output

set showplan on
set noexec on
go

Let’s start with DISTINCT. Here’s a query that looks for all unique title_ids in the sales table of bigpubs:

select   distinct   title_id
from   sales

STEP 1
The  type of query is INSERT
The  update mode is direct
Worktable  created  for DISTINCT
FROM TABLE
sales
Nested   iteration
Index  :  titleidind
TO TABLE
Worktable  1
STEP 2
The type of query is SELECT
This step involves sorting
FROM TABLE
Worktable 1
Using GETSORTED Table Scan

In order to find unique rows, SQL Server needs to sort the rows and then remove duplicates. The first step is to store the values in a worktable; that’s why the first step is an INSERT. The Server inserts the rows into Worktable 1. If there are multiple worktables involved in a query, you will see additional worktables (2, 3, and so on). The second step is the sorting step; the Server sorts the contents of the worktable to find unique values for display.

---

Note:  The optimization plan for ORDER BY is identical to the plan for DISTINCT.

---

Grouped aggregates also use a worktable. Here’s a query to show the total sales by title_id and stor_id:

select   title_id,   stor_id,   sum(qty)
from   sales
group   by   title_id,   stor_id

STEP 1
The type of query is SELECT (into a worktable)
GROUP BY
Vector Aggregate
FROM TABLE
sales
Nested iteration
Table Scan
TO TABLE
Worktable 1
STEP 2
The type of query is SELECT
FROM TABLE
Worktable 1
Nested iteration
Table Scan

This time, the worktable is used to hold the working summary values for each combination of stor_id and title_id. These totals are reported in Step 2.

The combination of GROUP BY and ORDER BY will cause an additional step, with a second worktable. Let’s order the last output by the sum(qty) expression:

select   title_id,   stor_id,   sum(qty)
from   sales
group  by   title_id,  stor_id
order  by  sum(qty)

STEP 1
The  type  of  query  is  SELECT (into a worktable)
GROUP BY
Vector Aggregate
FROM TABLE
sales
Nested iteration
Table Scan
TO TABLE
Worktable 1
STEP 2
The type of query is INSERT
The update mode is direct
Vector Aggregate
Worktable created for ORDER BY
FROM TABLE
Worktable 1
Nested iteration
Table Scan
TO TABLE
Worktable 2
STEP 3
The type of query is SELECT
This step involves sorting
FROM TABLE
Worktable 2
Using GETSORTED Table Scan

The first worktable is used to build the aggregate. That worktable is sorted into the second worktable. The final step retrieves the sorted result set from the second worktable.

When Is a Worktable Used?

Did you ever wonder why SELECT queries need to be presented in the order they do? Why do you need to put FROM before WHERE, WHERE before GROUP BY, and ORDER BY always last? This reflects the order of processing. Figure 20.7 shows how a worktable relates to these operations.

Figure 20.7.  How various select predicates interact with worktables.