Corrections

Advanced Transact-SQL for SQL Server 2000

Chapter 1

General

The "Advantage" boxes in pages: 12, 13, 18, 21, 28, are mistakenly numbered from 8 and on, instead if being numbered from 1 and on.

Pages 22-24

In the steps that explain the result shown in Table 1-14 of the query shown in Listing 1-25, Input1 refers to the Employees table and Input2 refers to the Departments table. Instead, Input1 should refer to the Departments table and Input2 should refer to the Employees table. The correct steps are:

Step 1. Let Input1 = all rows from Departments.

deptno	deptname
100	Engineering
200	Production
300	Sanitation
400	Management

Step 2. Let Input2 = all rows from Employees WHERE deptno IS NULL.

empid	empname	deptno	jobid	salary
6	Jeffrey	NULL	30	5000.00

Step 3. Let Result = Input1 LEFT OUTER JOIN Input2.

A LEFT OUTER JOIN returns: all matching rows between the two inputs, and also, all rows from the left input that don’t have matches in the right input, with NULLs in the columns of the right input. Since there are no matching rows between Input1 and Input2, all of the rows from Input1 are returned, with NULLs in the columns of Input2.

deptno	deptname	empid	empname	deptno	jobid	salary
100	Engineering	NULL	NULL	NULL	NULL	NULL
200	Production	NULL	NULL	NULL	NULL	NULL
300	Sanitation	NULL	NULL	NULL	NULL	NULL
400	Management	NULL	NULL	NULL	NULL	NULL

Pages 25-27

In the steps that explain the result shown in Table 1-18 of the query shown in Listing 1-27, Input1 refers to the Employees table and Input2 refers to the Departments table. Instead, Input1 should refer to the Departments table and Input2 should refer to the Employees table. The correct steps are:

Step 1. Let Input1 = all rows from Departments.

deptno	deptname
100	Engineering
200	Production
300	Sanitation
400	Management

Step 2. Let Input2 = all rows from Employees.

empid	empname	deptno	jobid	salary
1	Leo	400	30	10000.00
2	George	200	20	1000.00
3	Chris	100	10	2000.00
4	Rob	400	30	3000.00
5	Laura	400	30	3000.00
6	Jeffrey	NULL	30	5000.00

Step 3. Let Input3 = Input1 LEFT OUTER JOIN Input2.

deptno	deptname	empid	empname	deptno	jobid	salary
100	Engineering	3	Chris	100	10	2000.00
200	Production	2	George	200	20	1000.00
300	Sanitation	NULL	NULL	NULL	NULL	NULL
400	Management	1	Leo	400	30	10000.00
400	Management	4	Rob	400	30	3000.00
400	Management	5	Laura	400	30	3000.00

Step 4. Let Result = filter Input2—WHERE empid IS NULL.

deptno	deptname	empid	empname	deptno	jobid	salary
300	Sanitation	NULL	NULL	NULL	NULL	NULL

Page 37

Last sentence in paragraph after Listing 1-40. "You can also use the table qualifier after the first DELETE clause" Should be: "You can also use the table qualifier after the first FROM clause"

Chapter 2

Page 47

Last sentence in first paragraph. “…if any y is NULL, then the entire predicate is FALSE, because x <> NULL is FALSE.” Should be: “…if any y is NULL, then the entire predicate is UNKNOWN, because x <> NULL is UNKNOWN.

Page 54

Listing 2-15 in the book contains the following UPDATE statement:

UPDATE C
  SET F.Owner
FROM
    Feed AS F
  JOIN
    Cars AS C ON C.License = C.License

Both the SET clause and the join condition are incorrect. The correct UPDATE statement should look like this:

UPDATE C
  SET Owner = F.Owner
FROM
    Feed AS F
  JOIN
    Cars AS C ON F.License = C.License

Page 63

Listing 2-25. The column P.AvgPrice should be replaced with A.AvgPrice.

Listing 2-26. The WHERE clause “A.CategoryID = A.CategoryID” should be replaced with “A.CategoryID = P.CategoryID”.

Page 78

The following INSERT statement:

INSERT INTO Users VALUES(1, 'Bruce')

Should be replaced with:

INSERT INTO Users VALUES(1, 'BPMargolin')

To comply with the results of the puzzle shown in page 681.

Chapter 4

Page 136

Third sentence in last paragraph. “One of the bits is used as the sign bit.” This sentence is inaccurate as the format used to store integer values in SQL Server is the twos-complement format and not a format that uses a sign bit. However, positive values have the same bit representation when using a sign bit and when using the twos-complement format, so the discussion that follows still applies.

Chapter 6

Page 197

Last sentence in third paragraph. “Finally, the IDENTITY properties of tables…” IDENTITY is a column property and not a table property.

Page 203

First sentence in the second paragraph after Listing 6-10. The stored procedure sp_dboption should be replaced with sp_tableoption.

Page 209

Last sentence in page.
"As for the WRITETEXT statement, you need UPDATE permissions on a table in order to use the UPDATE statement."

Should be:
"As with the WRITETEXT statement, you need UPDATE permissions on a table in order to use the UPDATETEXT statement."

Page 210

Third sentence in the second paragraph under the section “Using the bigint Datatype.” The function BIG_COUNT() should be replaced with COUNT_BIG().

Page 216

Unbulleted list at top should include datetime. Last sentence in page. The function OBJECTPROPERTY() should be replaced with SQL_VARIANT_PROPERTY().

Page 227

Last paragraph. “Note that the new_reseed_value changes the current IDENTITY value for the table. Hence, the next inserted value will be new_reseed_value + increment.” The explanation in the book applies if at least one row was inserted into the table since it was created or was last truncated. If no rows have been inserted into the table since it was created or was last truncated, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity and not new_reseed_value + increment.

Chapter 9

Page 311

In Listing 9-12, there should be a comma after the word DEFAULT:

EXEC usp_DefaultProc
  DEFAULT,
  909,
  50

Page 335

On page 335, under "Security and Stored Procedures", in the second paragraph about the roles db_datareader, as well as db_denydatareader and db_denydatawriter. The phrase:

"...those roles to read (INSERT) or write (INSERT, UPDATE, DELETE) data in all tables..."

Should be:

"...those roles to read (SELECT) or write (INSERT, UPDATE, DELETE) data in all tables..."

This occurs twice in the paragraph.

Page 339

Listing 9-51. The comments "-- No existing transaction" and "-- Transaction in progress" should be swapped.

Chapter 10

Page 359

First sentence on page: Replace the phrase "each table uses" with the phrase "the Child and GrandChild tables use".

Page 365

Second bullet: "IDENTITY INSERT" should read "IDENTITY_INSERT". Pages 404-405

Listings 11-46 and 11-48 are supposed to have the recursive and non-recursive implementations of the fibonacci algorithm as UDFs. Currently the functions implement a simple summary of all consecutive integer numbers from zero to n where n is the value provided to the function as an argument. The correct recursive implementation of the fibonacci algorithm is:

CREATE FUNCTION dbo.fibonacci
(
  @n AS int
)
RETURNS int
AS
BEGIN
  RETURN CASE
    WHEN @n > 1 THEN dbo.fibonacci(@n - 1) + dbo.fibonacci(@n - 2)
    WHEN @n IN (0, 1) THEN @n
    ELSE NULL
  END
END
GO

The correct non-recursive implementation of the fibonacci algorithm is:

CREATE FUNCTION dbo.fibonacci2
(
@n AS int
)
RETURNS int
AS
BEGIN
  IF @n < 0
    RETURN NULL
  ELSE
    IF @n in (0, 1)
      RETURN @n
    ELSE
    BEGIN
      DECLARE
        @i AS int,
        @f1 AS int,
        @f2 AS int,
        @t AS int
      SET @i = 2
      SET @f1 = 0
      SET @f2 = 1
      WHILE @i <= @n
      BEGIN
        SET @t = @f2
        SET @f2 = @f1 + @f2
        SET @f1 = @t
        SET @i = @i + 1
      END 
    END 
  RETURN @f2
END 
GO

page 453 Listing 12-17: Calculating Discounts.

SELECT
  OrderID,
  Total,
  Previous4,
  CASE
    WHEN Previous4 < 10000.00 THEN 5
    WHEN Previous4 BETWEEN 10000.00 and 15000.00 THEN 10
    ELSE 20
  END AS Discount
FROM
(
  SELECT
    T1.OrderID,
    T1.Total, -- The book reads "T2.Total"
    (
      SELECT
        SUM(T2.Total) AS Total
      FROM 
        #Totals AS T2
      WHERE
          T2.Sequence >= T1.Sequence - 4 -- The book reads "T2.Sequence = T1.Sequence - 4" 
        AND
          T2.Sequence < T1.Sequence
    ) AS Previous4
  FROM
    #Totals AS T1
  WHERE
    0 = T1.Sequence % 5
) AS X

Page 606

There is a certain problem with the UPDATE trigger, but it isn't related to modifying the manager id to NULL. The problem might appear when you update more than one employee in the same UPDATE statement and at least one employee is a subordinate of another employee. For example, if you issue the following UPDATE statement:

UPDATE Employees
  SET mgrid = 1
WHERE empid IN(7, 11)

David (empid 14) belongs to Robert's (empid 7) subtree. The problem occurs because in the following UPDATE statement within the trigger:

  UPDATE E
    SET lvl = E.lvl - I.lvl + CASE
                                WHEN I.mgrid IS NULL THEN 0
                                ELSE M.lvl + 1
                              END,
        hierarchy = ISNULL(M.hierarchy, '.') +
                    CAST(I.empid AS varchar(10)) + '.' +
                    right(E.hierarchy, len(E.hierarchy) - len(I.hierarchy))
  FROM
      Employees AS E
    JOIN
      inserted  AS I ON E.hierarchy LIKE I.hierarchy + '%'
    LEFT OUTER JOIN
      Employees AS M ON I.mgrid = M.empid

David's row qualifies twice; once as a row in inserted which was directly modified, and another time as a child of Robert. It can be quite complex to write a trigger that handles all kinds of multi-row updates. So, there are a couple of options that you can choose from:

1. Don't allow multi-row updates. The trigger works fine for single row updates:

CREATE TRIGGER trg_employees_u_calchierarchy ON Employees FOR UPDATE
AS

-- begin revised part
DECLARE @rc AS int
SET @rc = @@ROWCOUNT

IF @rc = 0
  RETURN

IF @rc > 1
BEGIN
  RAISERROR('Only single row updates are allowed!', 16, 1)
  ROLLBACK TRAN
END
-- end revised part

IF UPDATE(empid)
BEGIN
  RAISERROR('Updates to empid not allowed!', 16, 1)
  ROLLBACK TRAN
END
ELSE IF UPDATE(mgrid)
BEGIN
  UPDATE E
    SET lvl = E.lvl - I.lvl + CASE
                                WHEN I.mgrid IS NULL THEN 0
                                ELSE M.lvl + 1
                              END,
        hierarchy = ISNULL(M.hierarchy, '.') +
                    CAST(I.empid AS varchar(10)) + '.' +
                    right(E.hierarchy, len(E.hierarchy) - len(I.hierarchy))
  FROM
      Employees AS E
    JOIN
      inserted  AS I ON E.hierarchy LIKE I.hierarchy + '%'
    LEFT OUTER JOIN
      Employees AS M ON I.mgrid = M.empid
END

2. Only allow multi-row updates that do not modify a parent and a subordinate in the same UPDATE statement. The trigger should be revised as follows:

CREATE TRIGGER trg_employees_u_calchierarchy ON Employees FOR UPDATE
AS

-- begin revised part
DECLARE @rc AS int
SET @rc = @@ROWCOUNT

IF @rc = 0
  RETURN

IF EXISTS(SELECT *
          FROM
              inserted AS E
            JOIN
              inserted AS M ON E.hierarchy LIKE M.hierarchy + '_%')
BEGIN
  RAISERROR('Updates to an employee and his/her subordinates are not
allowed!', 16, 1)
  ROLLBACK TRAN
END
-- end revised part

IF UPDATE(empid)
BEGIN
  RAISERROR('Updates to empid not allowed!', 16, 1)
  ROLLBACK TRAN
END
ELSE IF UPDATE(mgrid)
BEGIN
  UPDATE E
    SET lvl = E.lvl - I.lvl + CASE
                                WHEN I.mgrid IS NULL THEN 0
                                ELSE M.lvl + 1
                              END,
        hierarchy = ISNULL(M.hierarchy, '.') +
                    CAST(I.empid AS varchar(10)) + '.' +
                    right(E.hierarchy, len(E.hierarchy) - len(I.hierarchy))
  FROM
      Employees AS E
    JOIN
      inserted  AS I ON E.hierarchy LIKE I.hierarchy + '%'
    LEFT OUTER JOIN
      Employees AS M ON I.mgrid = M.empid
END

After implementing the above trigger, the following UPDATE should be successful:

UPDATE employees
  SET mgrid = 1
WHERE empid IN(2, 3)

And the following shouldn't:

UPDATE employees
  SET mgrid = 1
WHERE empid IN(3, 7)

Thank you

We would like to express our sincere gratitude to the following people who sent us comments and corrections to the book:
BP Margolin
Dejan Sarka

Contact us

If you have any comments or corrections we will be glad to hear from you. You can contact us through the following e-mail addresses:
Itzik Ben-Gan: mailto:itzikb@hi-tech.co.il
Tom Moreau: tom@cips.ca

[ Home ] [ About ] [ SolidQualityLearning ] [ Schedule ] [ Magazines ] [ T-SQL Book ] [ ISQL Users Group ] [ Resources ]