Errata

Microsoft SQL Server 2012 T-SQL Fundamentals

Errata for Microsoft SQL Server 2012 T-SQL Fundamentals

The errata list is a list of errors and their corrections that were found after the product was released. If the error was corrected in a later version or reprint the date of the correction will be displayed in the column titled "Date Corrected".

The following errata were submitted by our customers and approved as valid errors by the author or editor.

Color key: Serious technical mistake Minor technical mistake Language or formatting error Typo Question Note Update

Version Location Description Submitted By Date submitted Date corrected
Printed, PDF
Page 1
N/A

You can find the book's companion website here: http://tsql.solidq.com/books/tsqlfund2012/.

 Itzik Ben-Gan
 		 Jul 11, 2012 
7
Offset Window Functions, 7th paragraph

As an example, the following query uses the FIRST_VALUE function to return the value of the first customer's order and the LAST_VALUE function to return the value of the last customer's order.

should be

As an example, the following query uses the FIRST_VALUE function to return the value of the first order for each customer and the LAST_VALUE function to return the value of the last order for each customer.

Note from the Author or Editor:
Confirmed. Thanks.

 Mark  Apr 12, 2013 
Printed
Page 16
3rd paragraph

"You can create as many user databases as you need within an instance."

This isn't true. SQL Server 2012 limits the number of databases per instance to 32767. Source: Administering Microsoft SQL Server 2012 Databases (ISBN 978-0-7356-6607-8), page 15.

Note from the Author or Editor:
Confirmed. Should be: You can create multiple user databases within an instance (up to 32767).

 Anonymous  Oct 16, 2012  Nov 02, 2012
Printed
Page 17
Fig 1-7

In Fig 1-7, filegroups FG1 and FG2 depict constituent data files as having the extension ".mdf". I believe the extensions should be ".ndf".

Note: the extensions were ".ndf" in the previous edition.

Note from the Author or Editor:
Confirmed. Extension for all secondary data files in filegroups FG1 and FG2 should be changed to .ndf.

 Anonymous  Oct 16, 2012  Nov 02, 2012
PDF
Page 24
3rd paragraph from the bottom of the page 24

On the page 20, the table dbo.Employees is created as follows:

CREATE TABLE dbo.Employees
(
empid INT NOT NULL,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
hiredate DATE NOT NULL,
mgrid INT NULL,
ssn VARCHAR(20) NOT NULL,
salary MONEY NOT NULL
);

Then, on the page 24, the author adds a check constraint to the attribute salary as follows:

ALTER TABLE dbo.Employees
ADD CONSTRAINT CHK_Employees_salary
CHECK(salary > 0.00);

Then, on the page 24, the author says "For example, salary ?1000 will be rejected, whereas salaries 50000 and NULL will both
be accepted." However, the column salary does NOT allow nulls as it is specified in the CREATE TABLE command. In a nutshell, the sentence "For example, salary ?1000 will be rejected, whereas salaries 50000 and NULL will both be accepted." is not accurate because NULL cannot be assign to the column salary.

Note from the Author or Editor:
That's a good point. Thanks for reporting this.

Suggested revision:
Instead of: "For example, salary ?1000 will be rejected, whereas salaries 50000 and NULL will both be accepted."
Use: "For example, salary ?1000 will be rejected, whereas salaries 50000 and NULL (if the column allowed NULLs) will both be accepted."

 Hakan Haberdar  Sep 27, 2013 
Printed
Page 27
Listing 2-1, First line of code

Chapter 2, Page 27, Listing 2-1, First line of code

Currently: SE TSQL2012;
Should be: USE TSQL2012;

 Anonymous  Jul 27, 2012  Nov 02, 2012
Printed
Page 55
4th paragraph

For example, the expression IIF(col2 <> 0, col2/col1, NULL)...
should read
For example, the expression IIF(col1 <> 0, col2/col1, NULL)...

This is a standard check to avoid a divide by zero error.

Note from the Author or Editor:
Confirmed.

 Michael Kirkpatrick  Oct 09, 2012  Nov 02, 2012
Printed, PDF
Page 59
3rd paragraph, 4th paragraph

3rd paragraph:
that is, it doesn't bother to evaluate the expression 10/col1 > 2 because at this point it is known that the whole expression is
FALSE.

Should be:
that is, it doesn't bother to evaluate the expression col2/col1 > 2 because at this point it is known that the whole expression is
FALSE.


4th paragraph:
You can see that if SQL Server decides to process the expression 10/col1 > 2 first, this query might fail because of a divide-by-zero error.

Should be:
You can see that if SQL Server decides to process the expression col2/col1 > 2 first, this query might fail because of a divide-by-zero error.

Note from the Author or Editor:
Confirmed. Need to revise in both the third and fourth paragraphs "10/col1 > 2" to "col2/col1 > 2"

Also, the page with the error is page 60.

 Leiming Yu  Apr 21, 2013 
Printed, PDF
Page 60, 61
Chapter 2, Page 60, Paragraph above last query, Second sentence, and Page 61, Last sentence before new heading

This was sent to me by Manuel Lopez.

Page 60: ...the order in which the WHEN clauses of a CASE expression are evaluated is guaranteed.
Page 61: ...SQL Server guarantees the processing order of the WHEN clauses in a CASE expression.

Clarification: It should be noted that the above statements apply to scalar expressions, not to aggregate expressions. This is documented in Books Online under "CASE (Transact-SQL)" in the Remarks section. Here's the URL for this topic: http://msdn.microsoft.com/en-us/library/ms181765.aspx.

Itzik Ben-Gan
 		 Jan 05, 2013 
PDF
Page 64
Last line in the "Quoted Identifiers" box

In the example ' abc' 'de ' the space between the two consecutive single quotes should not be there. SQL Server treats that as two separate strings.

Note from the Author or Editor:
Confirmed. There are actually three spaces that don't belong there.

Instead of: "For example, to express the literal abc?de, specify ? abc? ?de ?."
Should be: "For example, to express the literal abc?de, specify 'abc''de'."

Thanks for reporting this!

 Bogdan Galiceanu  Jul 25, 2012  Nov 02, 2012
Printed
Page 70
1st sentence

The STUFF function allows you to remove a substring from a string and insert a new substring instead.

Based on this description, it sounds like the function requires you to specify a substring to be replaced by another substring, which is very similar to the description of the REPLACE function.

I think for clarity it should be described differently. For example: The STUFF function allows you to remove a specified number of characters starting at the specified index position and insert a substring instead.

Note from the Author or Editor:
Confirmed. Thanks!

 Tharon  Apr 18, 2013 
Printed, PDF
Page 71
Top

The FORMAT function on this page should be identified as being introduced in SQL2012. It would be worth mentioning that composite formats are not allowed.

Note from the Author or Editor:
Confirmed. Though this isn't a mistake rather additional info that as you say is worth mentioning. I updated the type of error to "Update." Thanks!

 Tom Groszko  Jul 18, 2012 
Printed
Page 71
Under "The FORMAT Functon' the select statement line

The statement currently reads
SELECT FORMAT(1759, '000000000');

should say
SELECT FORMAT(1759, '0000000000');

i.e. the format string only has nine zeros in it.

Note from the Author or Editor:
Confirmed. Thanks for reporting this!

Cheers,
Itzik

 Gordon Ayars  Oct 08, 2013 
Printed
Page 80
4th paragraph

"Note that you need to specify empty parentheses with all functions that should be specified without parentheses..."

The last word in the phrase above should probably be "parameters".

Note from the Author or Editor:
Confirmed.

 Anonymous  Oct 21, 2012  Nov 02, 2012
Printed
Page 96
in solution 4 in two places

in the improper WHERE clause:
WHERE quantity * unitprice > 10000

should read
WHERE qty * unitprice > 10000

the same for the HAVING clause below it

should read:

HAVING SUM(qty*unitprice) > 10000

i.e. the column in the Sales.OrderDetails, defined by the script that installed TSQL2012, is named 'qty' not 'quantity'.

May also want to change the problem statement on page 92 (quantity * unitprice) to be consistent with the table definition.

Note from the Author or Editor:
Hi Gordon, nice catch!

Errors confirmed in all cases, and suggested revisions including the one in page 92 accepted.

Cheers,
Itzik

 Gordon Ayars  Oct 08, 2013 
Printed
Page 119
1st Paragraph

"Remember to use a column that can only be NULL, in case ..."

I believe this should say:
"Remember to use a column that can not be NULL, in case ..."

Note from the Author or Editor:
Confirmed. Nice catch! Thanks for reporting this.

Suggest revision approved.

 Anonymous  Jan 11, 2013 
Printed, PDF
Page 128
Solution 7, third sentence

This was sent to me by Nagy Laszlo.

Currently: So you can use a simple CASE expression that checks whether the current row is an outer one, in which case it returns Yes; otherwise, it returns No.

Should be: So you can use a simple CASE expression that checks whether the current row is not an outer one, in which case it returns Yes; otherwise, it returns No.

 Itzik Ben-Gan
 		 Jan 05, 2013 
Printed, PDF
Page 145
last paragraph

last paragraph, line 2:
"The column in the MyShippers table is called shipper_id with an"

The table name 'MyShippers ' should be printed in italics.

Note from the Author or Editor:
Confirmed.

The second sentence in the last paragraph starts with: "The column in the MyShippers table..."
The reference to the MyShippers table should be italicized.

Thanks!

 Leiming Yu  Apr 29, 2013 
Printed, PDF
Page 186
Exercise 5-2, first sentence

This was sent to me by Niles Luo.

Currently: Using the CROSS APPLY operator and the function you created in Exercise 4-1...

Should be: Using the CROSS APPLY operator and the function you created in Exercise 5-1...

 Itzik Ben-Gan
 		 Jan 05, 2013 
Printed
Page 214
Select query

The Select query has the argument to the NTILE function as 100 implying 100 tiles are required, while the query output as well as further commentary on the query and query results imply that the argument has to be 10.

NTILE(100) should be NTILE(10)

Note from the Author or Editor:
Hi Prakash,

Error and suggested revision confirmed.

Thanks for taking the time to report this!

Cheers,
Itzik

 Prakash Mehrotra  Feb 08, 2013 
Printed, PDF
Page 224
Caption of table

This was sent to me by Pelle Bergkvist.

Currently: Table 1-1

Should be: Table 7-1

 Itzik Ben-Gan
 		 Jan 05, 2013 
Printed
Page 304
Query output table.

The last two columns' data are misaligned and appear under the "resid" column.

Note from the Author or Editor:
Confirmed. All text after the database names (TSQL2012) should be shifted 12 characters to the right. Following is the correct output:

spid restype dbid dbname res resid mode status
---- -------- ---- --------------------- -------------- ----------------- ---- ------
53 DATABASE 8 TSQL2012 0 S GRANT
52 DATABASE 8 TSQL2012 0 S GRANT
51 DATABASE 8 TSQL2012 0 S GRANT
54 DATABASE 8 TSQL2012 0 S GRANT
53 PAGE 8 TSQL2012 1:127 72057594038845440 IS GRANT
52 PAGE 8 TSQL2012 1:127 72057594038845440 IX GRANT
53 OBJECT 8 TSQL2012 133575514 IS GRANT
52 OBJECT 8 TSQL2012 133575514 IX GRANT
52 KEY 8 TSQL2012 (020068e8b274) 72057594038845440 X GRANT
53 KEY 8 TSQL2012 (020068e8b274) 72057594038845440 S WAIT

 Anonymous  Oct 27, 2012  Nov 02, 2012
PDF
Page 309
Last paragraph

SNAPSHOT and READ COMMITTED SNAPSHOT are in a sense the
optimistic-concurrency-based counterparts of READ COMMITTED and SERIALIZABLE, respectively.

should read (inverting serializable and read committed...):

SNAPSHOT and READ COMMITTED SNAPSHOT are in a sense the
optimistic-concurrency-based counterparts of SERIALIZABLE and READ COMMITTED, respectively.

Note from the Author or Editor:
Hi Sam,

Confirmed.

Thanks for reporting this!

Itzik

 Sam  Mar 15, 2014 
Printed
Page 323
2nd paragraph, last sentence

I think

"If SQL does not intervene..."

should be:

"If SQL did not intervene..."

Note from the Author or Editor:
Confirmed.

 Anonymous  Nov 02, 2012