Database Developer's Companion Errata

The following changes should be made to the Microsoft SQL Server Database Developer's Companion for Microsoft SQL Server 6.0.

• On page 47, the example for deleting records with the DELETE statement should be:

  DELETE titles
  FROM authors, titles, titleauthor
  WHERE titles.title_id = titleauthor.title_id
  AND authors.au_id = titleauthor.au_id
  AND city = 'Oakland'
    

• On page 58, the SELECT statement example before the "Choosing Specific Columns" topic should be:

  SELECT pub_id, pub_name, city, state, country
  FROM publishers

• On page 66, in the "Datatype Precedence in Mixed-mode Arithmetic" section, the sentence that begins with "The only execption" should be:

  The only exeception to this rule is when converting money and other number datatypes such as float, real, decimal, and numeric; money takes precedence, even though its type is lower, except when using float where the result is float.

• On page 72, the "Note" about WHERE conditions should be:

  Note The only WHERE conditions that you can use on text columns with certain functions such as patindex() are IS NULL, IS NOT NULL, LIKE, and NOT LIKE.

• On page 72, in the WHERE clause of the SELECT statement, the list of search_condition should be:
• Comparison Operators (such as =, <>, <, and >)
• All records (=ALL, >ALL)

  SELECT au_lname, au_fname, zip FROM authors where zip = all
  (SELECT zip FROM authors WHERE zip = '94609')
    
  SELECT  t1.title, t1.advance
  FROM  titles t1
  WHERE t1.advance > ALL
  (SELECT ISNULL(t2.advance,$0.0)
  FROM publishers p2, titles t2
  WHERE t2.pub_id = p2.pub_id
  AND p2.pub_name = 'Algodata Infosystems')
    

• Ranges (BETWEEN and NOT BETWEEN)
• Lists (IN, NOT IN)
• Pattern Matches (LIKE and NOT LIKE)
• Unknown values (IS NULL and IS NOT NULL)
• Combinations of these conditions (AND, OR)
• On page 78, the IS NOT NULL clause supports Wildcards and the LIKE clause. For example, the following query retrieves telephone numbers from the authors table where the telephone number begins with 415 and IS NOT NULL:

  SELECT phone
  FROM authors
  WHERE phone LIKE '415%' and phone IS NOT NULL
    

• On page 175, the sentence that begins "Narrow indexes are often more effective" should be:

  Narrow indexes are often more effective than multicolumn, composite indexes.

• On page 175, the sentence that begins "Note that if the first column" should be:

  Note that if the first column of a composite index has poor selectivity, the optimizer may not use the index for some queries.

• On page 184, add the following text to the "Analyzing Locks" topic:

  Intent locks are used to let high-level lock granules such as table locks know about the intentions of those, such as page locks, placing locks at lower levels in the lock hierarchy.

  Two transactions A and B execute concurrently. Transaction A uses page locking, acquires page locks, and does not encounter any lock conflicts. Transaction B protects its activity in the same table with a table lock and requests the table lock without lock conflict because Transaction A is placing locks on pages and Transaction B is placing a lock on the entire table object. Because both transactions believe they have exclusive access to the table, incorrect behavior results. To prevent problems, Transaction A, which locks at the page level, acquires an intent lock on the table. Transaction A's table intent lock is incompatible with transaction B's table lock request.

  Note There are different types of intent locks: Intent Share (IS), Intent Exclusive (IX), and Share with Intent Exclusive (SIX).

  Update locks prevent a common form of deadlock. A typical update pattern consists of a transaction reading a record, acquiring a share mode lock (S) on the resource (page or row), and then modifying the row, which requires lock conversion to an exclusive lock (X). Share mode locks are compatible with other share mode locks, so if the two transactions acquire share mode locks on a resource and then attempt to update data concurrently, both transactions will get the share mode lock and one transaction attempts the lock conversion to an exclusive lock. The share mode to exclusive lock conversion must wait since the exclusive lock for the one transaction is not compatible with the other transaction's share mode lock. A lock wait occurs. The second transaction involved attempts to acquire an exclusive lock for its update. Because both transactions converting to exclusive locks are waiting for the other transaction to release their share mode lock, a deadlock occurs.

  To resolve the deadlock problem, update locks are used. Because an update lock in one transaction is not compatible with an update lock in another transaction, only one transaction can obtain an update lock to a resource at a time. If a transaction modifies a resource, the update lock is converted to an exclusive lock. Otherwise, the lock is converted to a share mode lock.

• On page 203, under Stored Procedure Rules, the sixth bullet should be:
  • If you execute a procedure that calls another procedure, the called procedure can access all objects created by the first procedure, including temporary tables.