Errata

Hello,

Would you be able to make the .MDL database file available on the book's companion website here: http://tsql.solidq.com/books/tk70461/.

O'Reilly's companion site does not provide the .MDL database file, but does provide the source code. The link "Download the sample database(s)" on O'Reilly's site (http://go.microsoft.com/fwlink/?linkid=263548) is linked to the TSQL2012.SQL script file. This link should be linked to a .MDF sample database file, not a .SQL script file.

I am currently unable to use this training kit because there is no .MDF database file available to download from O'Really's site or the author's website.

Thank you

Note from the Author or Editor:
Hi Chris,

The page in O'Reilly's website (http://go.microsoft.com/fwlink/?linkid=263548) provides instructions that are not relevant for this book since we provide a .sql script file to create the sample database. I notified the editors that the page should be updated with the following info:

The sections "Download the sample database(s)" and " Attach the sample database(s)" should be replaced with the following section:

Create the Sample Database
----------------------------------
1. Start SQL Server Management Studio (SSMS).
2. Open the script file TSQL2012.sql and execute it.
3. When the script finishes executing, you should find the database TSQL2012 listed in the Available Databases drop down box.

Cheers,
Itzik

"Add shipperid DESC to the ORDER BY clause" should read "Add shippeddate DESC to the ORDER BY clause"

Note from the Author or Editor:
Hi Carlos,

Error confirmed. The error appears in page 82, Exercise 2.1, sentence before query.

Currently: "Add shipperid DESC to the ORDER BY clause, as follows."

Should be: "Add shippeddate DESC to the ORDER BY clause, as follows."

Thanks for writing!

Cheers,
Itzik

The third sentence contains the phrase "YEAR(orderdate) which is wrong. It should read YEAR(hiredate). Minor mistake but a mistake all the same :)

Note from the Author or Editor:
Confirmed. Thanks!
The third sentence in the last paragraph in the page contains a reference to YEAR(orderdate). This reference should be replaced with YEAR(hiredate).

The sentence on page 20 in the paragraph '6. Handle Presenting Order' reads:

"The query indicates that the result rows should be ordered first by
country (in ascending order by default), and then by numemployees, descending, yielding the
following output."

This is incorrect. The ORDER BY is on country and yearhired (descending):

ORDER BY country , yearhired DESC;

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

Cheers,
Itzik

Currently we have on P.34: "The rules say that the first character must be a letter in the range A through Z (lower or uppercase), underscore (_), at sign ..." However, on P.271 we find: "The first character must be a letter defined in the Unicode Standard 3.2 or an underscore ..." Books Online states under Rules for Regular Identifiers: "The first character must be one of the following: A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages." Basically the description currently on P.34 should be revised to clarify that the first letter of a regular identifier can include a letter character from English as well as other Unicode languages.

Note from the Author or Editor:
Confirmed.

Change: "The rules say that the first character must be a letter in the range A through Z (lower or uppercase), underscore (_), at sign (@), or number sign (#)."

To: "The rules say that the first character must be a letter defined by the Unicode Standard 3.2 (a-z, A-Z, and letters from other Unicode languages), underscore (_), at sign (@), or number sign (#)."

Thanks for reporting this!

Cheers,
Itzik

In the last sentence, the text makes reference to a fictitious system function, SYSUTCDATE. Although this comes from BOL, it's incorrect.

Change this sentence to remove mention of SYSUTCDATE :

"The GETUTCDATE function returns the current date and time
in UTC terms as a DATETIME type, and SYSUTCDATE does the same, only returning the result as the more precise DATETIME2 type"

Note from the Author or Editor:
Nice catch! The correct function name is SYSUTCDATETIME. So the last sentence in the 6th paragraph should be changed from:

"The GETUTCDATE function returns the current date and time in UTC terms as a DATETIME type, and SYSUTCDATE does the same, only returning the result as the more precise DATETIME2 type."

To:

"The GETUTCDATE function returns the current date and time in UTC terms as a DATETIME type, and SYSUTCDATETIME does the same, only returning the result as the more precise DATETIME2 type."

Cheers,
Itzik

"by turning off a session option CONCAT_NULL_YIELDS_NULL_INPUT" should refer to CONCAT_NULL_YIELDS_NULL

Note from the Author or Editor:
Confirmed.

Thanks for reporting this, Duncan.

Cheers,
Itzik

The book states:

As an example, the
expression PATINDEX('%[0-9]%', 'abcd123efgh') looks for the first occurrence of a digit (a
character in the range 0?9) in the second input, returning the position 6 in this case.

Should be position 5, a-d are 4 positions so the digit 1 is on the fifth position.

Note from the Author or Editor:
Confirmed. Thanks!

COALESCE function
In the book -
"The COALESCE function accepts a list of expressions as input and returns the first that
is not NULL, or NULL if all are NULLs."
In MSDN-Technet
Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
Practice:
select coalesce(null,null)
Error
Msg 4127, At least one of the arguments to COALESCE must be an expression that is not the NULL constant. how in MSDN

Note from the Author or Editor:
Hi Igor,

That's a good observation. Note though that the error you got will only be generated when the inputs are all untyped NULL constants. The following returns NULL and not an error:

declare @x as int = null, @y as int = null;
select coalesce(@x, @y);

Also, the following doesn't generate an error since the NULL is typed:

select coalesce(cast(null as int), null);

Editors, after "...returns the first that
is not NULL, or NULL if all are NULLs."

Please add: "If all inputs are the untyped NULL constant SQL Server generates an error."

Currently reads "The PERCENT option puts a ceiling on the resulting number of rows if it's not whole."

This is not stated correctly. This sentence suggests that the number of rows returned cannot exceed the given percent, i.e. that percent is a "ceiling" for the number of rows. If this statement were true, then the example that follows would only return 8 rows, as 9/830 is greater than 1%. This paragraph should be reworded to more correctly explain what PERCENT does.

Note from the Author or Editor:
Good point. Replace: "The PERCENT option puts a ceiling on the resulting number of rows if it?s not whole." with: "The PERCENT option rounds up the number of rows if it?s not whole."

In the book the query reads

SELECT TOP (3) WITH TIES orderid.....
ORDER BY orderdate DESC, orderid DESC;

Which now makes the query deterministic because the orderid in the order by clause breaks ties. Given that it's deterministic then the "WITH TIES" clause is no longer needed because there should not be any ties, right?

Shouldn't the query be re-written as follows:

SELECT TOP (3) orderid....
ORDER BY orderdate DESC, orderid DESC;

Note from the Author or Editor:
Hi Zack,

You're right. The WITH TIES option should be removed from the second query. It should be rewritten as:

SELECT TOP (3) orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC;

Cheers,
Itzik

The offset of the query:

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC
OFFSET (@pagesize - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;

should be like:

OFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;

Note from the Author or Editor:
Confirmed. Thanks!

The mistake related to @pagesize vs. @pagenum on the very last line of the page is already reported here and that's great. However, the mistake is not fixed yet in the code download file "Chapter 03 - Code.sql" - it still reads OFFSET (@pagesize - 1) * @pagesize ... whereas it should be OFFSET (@pagenum - 1) * @pagesize ...

Note from the Author or Editor:
Confirmed. I'll fix this and upload the new source code. Thanks for reporting this!

The set operator precedence is described as:
"Finally, set operators have precedence: INTERSECT precedes UNION and EXCEPT, and UNION and EXCEPT are considered equal."
To be clear, this should be:
Finally, set operators have precedence: INTERSECT precedes UNION and EXCEPT, and UNION and EXCEPT are evaluated from left to right based on their position in the expression."

Note from the Author or Editor:
Hi Malcolm,

Suggestion accepted.

Currently:
"Finally, set operators have precedence: INTERSECT precedes UNION and EXCEPT, and UNION and EXCEPT are considered equal."

Change to:

"Finally, set operators have precedence: INTERSECT precedes UNION and EXCEPT, and UNION and EXCEPT are evaluated from left to right based on their position in the expression."

Cheers,
Itzik

The answer lists the Correct Answers as A, B, and C.

However, when explaining why the multiple-choice are incorrect and incorrect, it lists A, C, and D as the correct answers.

Note from the Author or Editor:
Confirmed. Thanks!

SELECT S.shipperid,
MAX(S.companyname) AS numorders,
COUNT(*) AS shippedorders
FROM Sales.Shippers AS S...
should read
SELECT S.shipperid,
MAX(S.companyname) AS companyname,
COUNT(*) AS numorders
FROM Sales.Shippers AS S...

Note from the Author or Editor:
Confirmed. Thanks!

The example grouping sets select query uses an identical column alias "grpcountry" for three columns. This violates relational rules. Better would be grpcountry, grpregion, grpcity. (The example result set at top of page 158 would also need to be updated)

Note from the Author or Editor:
Confirmed. Thanks!

The last query in the page should read:

SELECT
shipcountry, GROUPING(shipcountry) AS grpcountry,
shipregion , GROUPING(shipregion) AS grpregion,
shipcity , GROUPING(shipcity) AS grpcity,
COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY ROLLUP( shipcountry, shipregion, shipcity );

SELECT stoplist_id, names
FROM sys.fulltext_stoplists;...
should read
SELECT stoplist_id, name
FROM sys.fulltext_stoplists;...

Note from the Author or Editor:
Change from

SELECT stoplist_id, names
FROM sys.fulltext_stoplists

to

SELECT stoplist_id, name
FROM sys.fulltext_stoplists

"If the folder is C:\TK461, then you can use the following code directly; otherwise, change the folder in the OPENROWSET functions appropriately."
The folder suggested for installing (on page xxix) was C:\TK70461. This sample code should be in synch with that suggestion.

Note from the Author or Editor:
Change the four references to the C:\TK461 folder in the code to the C:\TK70461 folder.

"Import the four documents included in the folder for this book."
There are no documents included in the folder for this book. Please add them to your website for downloading. Thanks.

Note from the Author or Editor:
Thanks for reporting this! I updated the source code download file in the book's companion website (http://tsql.solidq.com/books/tk70461/). You will find the documents in question in the folder Chapter 06.
I also sent the book's editors the new source code file so that it can be included in the media provided by O'Reilly.

Cheers,
Itzik

Exercises 2, 3, and 4 are all constrained to the "first Customer". In exercise 4, this is done by selecting the first Customer in the XML file. In exercises 2 and 3, this is done by selecting the customer with custid=1. While the result is the same, the method used in Exercise 4 more precisely meets the "first Customer" requirement.
Recommend changing text in Excercises 2 and 3 from "Customer[@custid=1]" to "Customer[1]"

Note from the Author or Editor:
Page 258, step 2, replace the first sentence:
"Return all orders for the first customer as XML."
with
"Return all orders for the customer with @custid equal to 1 (the first customer in the XML document) as XML."

Page 259, step 3, replace the first sentence:
"Shred all orders information for the first customer."
with
Shred all orders information for the customer with @custid equal to 1 (the first customer in the XML document) .

Exercise 2, step 1:

code specifies creating table Production.CategoriesTest specifying a Primary Key constraint of PL_Categories.

This is not permitted, as the database already contains a constraint with that name, defined on the Production.Categories table.

Note from the Author or Editor:
Correct: the line should read:

CONSTRAINT PK_CategoriesTest PRIMARY KEY(categoryid)

Note the additional word Test in the name.

ALTER TABLE Production.CategoriesTest
ALTER COLUMN description NVARCHAR(500);
GO

this overrode the nullability settings of the column definition in my case and set the column to allow nulls. seems consistent with:

"When you create or alter a table with the CREATE TABLE or ALTER TABLE statements, the database and session settings influence and possibly override the nullability of the data type that is used in a column definition. We recommend that you always explicitly define a column as NULL or NOT NULL for non-computed columns."

(src: http://msdn.microsoft.com/en-us/library/ms190273.aspx)

Note from the Author or Editor:
On page 278, line 37 (2nd line from bottom)
Change:
ALTER COLUMN description NVARCHAR(500);
To:
ALTER COLUMN description NVARCHAR(500) NOT NULL;

In step 4 we issue an update to set the description to null. In the line above the update statement it contains "This fails." Actually it doesn't fail. The problem is when we modified the description column in step 2 we did not specify that NULL values were not allowed. Step 2 should be modified to read as follows:

ALTER TABLE Production.CategoriesTest
ALTER COLUMN description NVARCHAR(500) NOT NULL;

Note from the Author or Editor:
Page 279, line 33: Change
ALTER COLUMN description NVARCHAR(500);
to
ALTER COLUMN description NVARCHAR(500) NOT NULL;

Problem 3 says "Test for the existence of NULLs in the description column" but the query does not do that.

This one does:

SELECT categoryid AS category_without_descr
FROM Production.CategoriesTest
WHERE description IS NULL;

Note from the Author or Editor:
Page 279, line 1
Change:
Test for the existence of any NULLs
To
Inspect the table for the existence of any NULLs

Quick Check question #2 asks: Can a primary key on one table have the same name as the primary key in another table in the same database?

Answer: No, all table constraints must have unique names in a database.

Please advise if I am wrong, but the question or the answer should be revised to clarify that the constraint names can share the same name, provided they are in different schemas.

Note from the Author or Editor:
Page 284, line 28
Change:
2. Can a primary key on one table have the same name as the primary key in another table in the same database?
To:
2. Can a primary key on one table have the same name as the primary key in another table in the same database and in the same schema?

On page 284, line 33:
Change:
2. No, all table constraints must have unique names in a database.
To:
2. No, all table constraints must have unique names withing the schema of a database.

Per Bruce Hendry's comment of Feb 18, 2013 Apr 12, 2013, the same rules about uniqueness of constraint names applies to default constraints too.

Default constraint names must be unique within a given schema of a given database. Therefore tables in different schemas can share the same default constraint name. So the following is not entirely true and should be re-worded:

"Remember that default constraints, like all other constraints, are database-wide objects. Their names must be unique across the entire database. No two tables can have default constraints named
the same."

Note from the Author or Editor:
Page 288, 7th line from the bottom:
Change
Remember
that default constraints, like all other constraints, are database-wide objects. Their names
must be unique across the entire database. No two tables can have default constraints named
the same.
To
Remember
that default constraints, like all other constraints, are schema-wide objects. Their names
must be unique across an entire schema. No two tables scoped to the same schema can have default constraints named
the same.

Case scenario 1, point 4: sys.constraints does not exist :-

"However, you can query sys.constraints to monitor the tables to make sure that every table does include a primary key."

should probably read:

"However, you can query sys.key_constraints to monitor the tables to make sure that every table does include a primary key."

Note from the Author or Editor:
Page 296 2nd line from the bottom:
Change
However, you can query sys.constraints
To
However, you can query sys.key_constraints

Select statement
SELECT SCHEMA_NAME, TABLE_NAME, TABLE_TYPE
should read
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE

Note from the Author or Editor:
Page 307, Line 3
Change:
SELECT SCHEMA_NAME,
To:
SELECT TABLE_SCHEMA,

2. What is the result of WITH SCHEMABINDING in a view?
B. The tables referred to in the view cannot be altered unless the view is first altered. (FALSE)
First altering the view to remove the WITH SCHEMABINDING option would make this answer option TRUE
Perhaps changing the wording slightly would remove this potential ambiguity.
B. The tables referred to in the view cannot be altered unless the SELECT statement in the view is first altered. (FALSE)

Note from the Author or Editor:
Page 314, Line
Change:
B. The tables referred to in the view cannot be altered unless the view is first altered.
To:
B. The tables referred to in the view cannot be altered unless the view's SELECT statement is first altered.

The line
ISNULL(CAST(custid AS INT), -1) AS custid,
can be simpified to
ISNULL(custid, -1) AS custid,
as the source and target datatypes are both INT

Note from the Author or Editor:
Hi Malcolm,

The suggested revision is confirmed.

BTW, the original intent of the seemingly unnecessary CAST expression was to result in a target column without an IDENTITY property. Observe in Exercise 1 that this is a requirement. Applying any sort of manipulation to the original column causes the IDENTITY property not to be carried to the target. However, since the ISNULL function is used to cause the target column not to allow NULLs, manipulation is already applied, and hence the target column won't have an IDENTITY property any way. Long story short, Malcolm's suggested revision is approved. The following expression in the query in Exercise 2.1:

SELECT
ISNULL(CAST(custid AS INT), -1) AS custid,
...

Should be revised to:

SELECT
ISNULL(custid, -1) AS custid,
...

Thanks for reporting this!

Cheers,
Itzik

The update statement provided in the book technically isn't correct based on the requirement of paragraph 3 which reads "Write an UPDATE statement that overwrites the values of all nonkey columns in Sales.MyCustomers table with those from the respective rows in the Sales.Customers table."

Observe that the first set pair is

Set TGT.custid = SRC.custid

When the table was created in step 2 the custid column was defined with a Primary key constraint therefore it should be excluded from the list of set pairs in the update statement.

Note from the Author or Editor:
Confirmed.

Currently:

UPDATE TGT
SET TGT.custid = SRC.custid ,
TGT.companyname = SRC.companyname ,
TGT.contactname = SRC.contactname ,
TGT.contacttitle = SRC.contacttitle,
TGT.address = SRC.address ,
TGT.city = SRC.city ,
TGT.region = SRC.region ,
TGT.postalcode = SRC.postalcode ,
TGT.country = SRC.country ,
TGT.phone = SRC.phone ,
TGT.fax = SRC.fax
FROM Sales.MyCustomers AS TGT
INNER JOIN Sales.Customers AS SRC
ON TGT.custid = SRC.custid;

Should be:

UPDATE TGT
SET TGT.companyname = SRC.companyname ,
TGT.contactname = SRC.contactname ,
TGT.contacttitle = SRC.contacttitle,
TGT.address = SRC.address ,
TGT.city = SRC.city ,
TGT.region = SRC.region ,
TGT.postalcode = SRC.postalcode ,
TGT.country = SRC.country ,
TGT.phone = SRC.phone ,
TGT.fax = SRC.fax
FROM Sales.MyCustomers AS TGT
INNER JOIN Sales.Customers AS SRC
ON TGT.custid = SRC.custid;

Thanks for reporting this!

Per Zack Jones' comment on pp 353 of Jan 27 2013, the same error (inclusion of PK column custid) is made here. The SQL should be:

WITH C AS
(
SELECT
TGT.companyname AS tgt_companyname , SRC.companyname AS src_companyname ,
TGT.contactname AS tgt_contactname , SRC.contactname AS src_contactname ,
TGT.contacttitle AS tgt_contacttitle, SRC.contacttitle AS src_contacttitle,
TGT.address AS tgt_address , SRC.address AS src_address ,
TGT.city AS tgt_city , SRC.city AS src_city ,
TGT.region AS tgt_region , SRC.region AS src_region ,
TGT.postalcode AS tgt_postalcode , SRC.postalcode AS src_postalcode ,
TGT.country AS tgt_country , SRC.country AS src_country ,
TGT.phone AS tgt_phone , SRC.phone AS src_phone ,
TGT.fax AS tgt_fax , SRC.fax AS src_fax
FROM Sales.MyCustomers AS TGT
INNER JOIN Sales.Customers AS SRC
ON TGT.custid = SRC.custid
)
UPDATE C
SET
tgt_companyname = src_companyname ,
tgt_contactname = src_contactname ,
tgt_contacttitle = src_contacttitle,
tgt_address = src_address ,
tgt_city = src_city ,
tgt_region = src_region ,
tgt_postalcode = src_postalcode ,
tgt_country = src_country ,
tgt_phone = src_phone ,
tgt_fax = src_fax;

Note from the Author or Editor:
Hi Tim,

Confirmed. Thanks for reporting this!

Cheers,
Itzik

The first line in the first paragraph on that page refers to an example on the previous page (357) stating:

"As you can see, the code uses an infinite loop (WHERE 1 = 1 is always true)."

However, the example uses the WHILE 1=1 to create the infinite loop, not WHERE 1=1.

Note from the Author or Editor:
Confirmed. Should be:

"As you can see, the code uses an infinite loop (WHILE 1 = 1 is always true)."

Thanks for reporting this!

LEFT OUTER JOIN Sales.Orders AS SRC
should read
LEFT OUTER JOIN Sales.MyOrders AS SRC

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

Code sample beginning
TGT.custid = SRC.custid OR
should read
TGT.custid <> SRC.custid OR

Note from the Author or Editor:
Hi Malcolm,

Error and suggested revision confirmed. Again, many thanks for taking the time to report this!

The code at the bottom of step 3 won't run as written

"With SRC AS" appears to be left over from the above CTE example. If you try to run the code with that included you'll get a syntax error. If you delete it then the merge statement runs as expected.

Note from the Author or Editor:
Confirmed.

"With SRC AS" should be removed from the bottom code sample. The statement should start with:

Instead of:

WITH SRC AS
MERGE INTO Sales.MyOrders AS TGT...

Should be:

MERGE INTO Sales.MyOrders AS TGT...

Thanks!

Itzik

Hey,
At page 424 in the Quick Check box you mentioned that readers can?t block readers, but if you use SELECT WITH (XLOCK) and another session with Serializable isolation level attempts to SELECT the same resource, the latest session gets blocked and readers block readers. Maybe I misunderstood the situation but I believe you we should say shared locks cannot block shared locks instead of saying readers cannot block readers. I look forward for your opinion. Thank you so much.

Note from the Author or Editor:
Page 424 line 2:
Change:
1. Can readers block readers?
2. Can readers block writers?
To
1. Can readers (shared locks) block readers?
2. Can readers block writers (exclusive locks)?

There are two sessions open, and one of the sessions contains the command
ALTER DATABASE TSQL2012
SET READ_COMMITTED_SNAPSHOT ON;
This doesn't work, because (from BOL)
"To set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no active connections to the database except for the connection executing the ALTER DATABASE command."
Also, search for "region" in the table, and replace with "postalcode"

Note from the Author or Editor:
Page 433 Table 12-8:

Row 1, column 2
Add:
Use master;

Row 4, column 2
Change:
SELECT lastname, firstname, region
FROM HR.Employees;
To:
SELECT lastname, firstname, postalcode
FROM HR.Employees
WHERE empid = 1;

Row 5, column 2
Change:
<Results returned show region in original state
for empid = 1>
To:
<Results returned show postalcode in original state
for empid = 1>

Row 7, column 1
Change:
<region for empid = 1 rolled back to original
value>
To:
<postalcode for empid = 1 rolled back to original
value>

It is stated than:

"You can issue RAISERROR with a severity level > 20 if you use the WITH LOG option
and if you have the SQL Server sysadmin role. SQL Server will then terminate the connection
when the error is raised."

In fact this should be "severity level > 18".

The following error message is shown:

"Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option."

Note from the Author or Editor:
Page 437, line 27
Change:
You can issue RAISERROR with a severity level > 20
To:
You can issue RAISERROR with a severity level > 18

Type specification %s should be used on RAISERROR examples of page 437 (PDF).

RAISERROR ('Error in % stored procedure', 16, 0, N'usp_InsertCategories');

Prints out message as:
Msg 50000, Level 16, State 0, Line 1
Error in usp_InsertCategoriestored procedure

Note, missing space and first letter of word stored.

RAISERROR ('Error in %s stored procedure', 16, 0, N'usp_InsertCategories');

Prints out message as:
Msg 50000, Level 16, State 0, Line 1
Error in usp_InsertCategories stored procedure

Note from the Author or Editor:
On page 437, lines 11, 14 and 18; page 438, 3rd line from the bottom:
Change:
'Error in % stored procedure'
To:
'Error in %s stored procedure'

the sql leaves the Transaction open. I startet the code to question 2 in an other SSMS-tab and it blocked. There should be a rollback as reaction to the error in the code to question 1.

Note from the Author or Editor:
Page 445, line 22:
Change
GO
To
GO
IF @@TRANCOUNT <> 0 ROLLBACK TRANSACTION

Problems in the Practice Exercise code samples

In the code sample, the line "EXEC sp_executesql @statment = @SQLString" misspells the parameter name @statement

This occurs in:
Practice exercise 2 Step 6
Practice exercise 3 Step 2

In the code sample, the line "SET @SQLString = " is missing the "N" character to indicate a Unicode string.
This occurs in:
Practice exercise 2 Step 1
Practice exercise 2 Step 6
Practice exercise 3 Step 1

In the code sample, the line "EXEC Sales.ListCustomersByAddress @address = " is missing the "N" character to indicate a Unicode string.
This occurs in:
Practice exercise 2 Step 2
Practice exercise 2 Step 3
Practice exercise 2 Step 4
Practice exercise 2 Step 5
Practice exercise 2 Step 7
Practice exercise 2 Step 8

Note from the Author or Editor:
Correct spelling error:
Change @statment to @statement, in
Practice exercise 2 Step 6
Practice exercise 3 Step 2

Add Unicode indicator N before quote mark on SET @SQLString = ' in
Practice exercise 2 Step 1
Practice exercise 2 Step 6
Practice exercise 3 Step 1

Add Unicode indicator N before quote mark on @address = ' in
Practice exercise 2 Step 2
Practice exercise 2 Step 3
Practice exercise 2 Step 4
Practice exercise 2 Step 5
Practice exercise 2 Step 7
Practice exercise 2 Step 8

Given the explanation for this code block on the top of page 478, the "then" section of the IF statement should have a second PRINT statement.

Erroneous code snippet:
...
IF @var1 = @var2
PRINT 'The variables are equal';
ELSE
...

Corrected code snippet:
...
IF @var1 = @var2
PRINT 'The variables are equal';
PRINT '@var1 equals @var2';
ELSE
...

The code sample at the top of page 478 appears to support this correction as well.
It should also been mentioned that the second PRINT statement will generate an "Incorrect syntax near the keyword 'ELSE'" error.

Note from the Author or Editor:
Page 477, line 30:
Change
When the IF and ELSE statements are used without BEGIN/END blocks
To
When the IF or ELSE statements are used without BEGIN/END blocks

I realize this is being overly picky, but anyway:

"The numbers 5 and 6 are skipped because of the CONTINUE statement..."

The continue has nothing to do with the logic of why the numbers are skipped - commenting it out yields the same result, and would indicate to me that, while infinitesimally small, it is adding overhead.

Note from the Author or Editor:
Page 479, line 32:
Change:
The numbers 5 and 6 are skipped because of the CONTINUE statement;
To:
The numbers 5 and 6 are skipped because of the CONTINUE branch;

Based on your previous examples of cleaning up after an exercise, it would appear STEP 9 is missing. It would be:

IF OBJECT_ID('Production.InsertProducts', 'P') IS NOT NULL
DROP PROCEDURE Production.InsertProducts
GO

Note from the Author or Editor:
On page 489, after line 15, insert:
9. Drop the stored procedure.

The first code snippet is incorrect. The actual code snippet produced is:

CREATE TRIGGER TriggerName
ON [dbo].[TableName]
FOR DELETE, INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
END


Instructions on how to rewrite this snippet code to look like the code sample is missing.

Note from the Author or Editor:
On page 492, replace the entire code snippet with:

CREATE TRIGGER TriggerName
ON [dbo].[TableName]
FOR DELETE, INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
END

"When an UPDATE or DELETE occurs and no rows are affected, there is no point in proceeding with the trigger."

This statement holds true for INSERT as well as UPDATE or DELETE.

For example:

CREATE TABLE dbo.blah (x int)
GO
CREATE TRIGGER dbo.tr_blah ON dbo.blah AFTER INSERT
AS
SELECT @@ROWCOUNT as trigger_count
GO
INSERT blah (x) SELECT x FROM blah

Note from the Author or Editor:
Page 492, line 33
Change
When an UPDATE or DELETE occurs and no rows are affected,
To
When an INSERT, UPDATE or DELETE occurs and no rows are affected,

As written:

if exists(
select
count( * )
from
inserted as I
join Production.Categories as C on I.categoryname = C.categoryname
group by
I.categoryname
having
count( * ) > 1 )

As this is an instead of trigger, the rows aren't inserted into the categories table yet, so to detect a duplicate, it should be testing for count( * ) > 0 or count( * ) >= 1. The way it is currently constructed will allow up to two duplicates in the categories table.

Should be:

if exists(
select
count( * )
from
inserted as I
join Production.Categories as C on I.categoryname = C.categoryname
group by
I.categoryname
having
count( * ) > 0 )

Note from the Author or Editor:
Page 495, line 9:
Change
HAVING COUNT(*) > 1
To
HAVING COUNT(*) > 0

the pdf has the same error as confirmed for the print-version. But the solution from "confirmed errata" is not correct. Because it is a INSTEAD OF the rows in INSERTED ar not allready in the target-table that means a duplicate could be:
a) already in the target-table (not checked)
b) in inserted even if intersect(target, inserted) is empty
c) target and inserted are free of duplicates, but intersect(target, inserted) is not empty thus the union will have duplicates.

an ugly solution is to use this "if exists":

IF EXISTS (SELECT COUNT(*)
FROM Inserted AS I
LEFT JOIN Production.Categories AS C ON I.categoryname = C.categoryname
GROUP BY I.categoryname, C.categoryname
HAVING COUNT(*) > 1 OR C.categoryname IS NOT NULL ) BEGIN

Note from the Author or Editor:
Page 495, line 29
Change:
JOIN Production.Categories AS C
To:
LEFT JOIN Production.Categories AS C

The instruction is wrong.

This:
"Also, the feature can be disabled in sp_configure, so to do this exercise, make sure Disallow Results From Triggers is enabled."

Should be this:
"Also, the feature can be disabled in sp_configure, so to do this exercise, make sure Disallow Results From Triggers is disabled."

Note from the Author or Editor:
Page 497
The current text has 'enabled' but should say 'disabled'.
Change:
"Also, the feature can be disabled in sp_configure, so to do this exercise, make sure Disallow Results From Triggers is enabled."
To:
"Also, the feature can be disabled in sp_configure, so to do this exercise, make sure Disallow Results From Triggers is disabled."

In the last paragraph under WAITFOR the last sentence reads:
"For example, the following code wait until 11:45.

WAITFOR TIME '23:46:00';

Actually that waits for 11:46.

Note from the Author or Editor:
Chapter 13, Page 513 (old), or 481 (new) line 17
Change:
For example, the following code waits until 11:45.
To:
For example, the following code waits until 11:46.

This code for heap allocation check is written to only work with objects created in tempdb. This should be explicitly noted in the examples above, or just remove the tempdb constraint by changing the reference from
DB_ID(N'tempdb') to
DB_ID()

Note from the Author or Editor:
Examples here do not depend on any specific database. However, it seems that this is confusing, as this is already the second time that some reader complains about this issue, so I suggest just adding the USE statement. Therefore, the first code in the page, which is now

CREATE TABLE dbo.TestStructure
(
id INT NOT NULL,
filler1 CHAR(36) NOT NULL,
filler2 CHAR(216) NOT NULL
);

should be changed to
USE tempdb;
GO
CREATE TABLE dbo.TestStructure
(
id INT NOT NULL,
filler1 CHAR(36) NOT NULL,
filler2 CHAR(216) NOT NULL
);

You can find out how many pages are allocated for an object from
the sys.dm_db_index_physical_stats dynamic management view or with the help of the
dbo.sp_spaceused system procedure, as shown in the following code.

should be

You can find out how many pages are allocated for an object from
the sys.dm_db_index_physical_stats dynamic management function or with the help of the
dbo.sp_spaceused system procedure, as shown in the following code.

Note from the Author or Editor:
The second sentence in the last paragraph of the text, just before the last code on the page 552, which is "You can find out how many pages are allocated for an object from
the sys.dm_db_index_physical_stats dynamic management view or with the help of the
dbo.sp_spaceused system procedure, as shown in the following code.", should be changed to
"You can find out how many pages are allocated for an object from
the sys.dm_db_index_physical_stats dynamic management function or with the help of the
dbo.sp_spaceused system procedure, as shown in the following code."

The format for provided in the code for the field "filler1" will not produce the data needed to make this exercise work correctly.
Change from
VALUES
(@i, FORMAT(@i,?0000?), 'b');
to
VALUES
(@i, FORMAT(@i,'00000'), 'b');
With this change, row 24473 then causes the number of index levels to change as described in the exercise.

Note from the Author or Editor:
Change the code of the step 7. on the top of the page from

DECLARE @i AS int = 0;
WHILE @i < 24472
BEGIN
SET @i = @i + 1;
INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(@i, FORMAT(@i,?0000?), 'b');
END;

to

DECLARE @i AS int = 0;
WHILE @i < 24472
BEGIN
SET @i = @i + 1;
INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(@i, FORMAT(@i,?00000?), 'b');
END;

The format for provided in the code for the field "filler1" will not produce the data needed to make this exercise work correctly.
Change from
VALUES
(@i, FORMAT(@i,'0000'), 'b');
to
VALUES
(@i, FORMAT(@i,'00000'), 'b');
With this change, row 28865 then causes the number of index levels to change as described in the exercise.

Note from the Author or Editor:
Chenge the code of the step 3. from

DECLARE @i AS int = 0;
WHILE @i < 28864
BEGIN
SET @i = @i + 1;
INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(@i, FORMAT(@i,?0000?), 'b');
END;

to

DECLARE @i AS int = 0;
WHILE @i < 28864
BEGIN
SET @i = @i + 1;
INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(@i, FORMAT(@i,?00000?), 'b');
END;

The code demonstrates bad practice. It is a waste of resources to update statistics on objects that are just about to be dropped anyway.

This:

ALTER DATABASE TSQL2012
SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT;
EXEC sys.sp_updatestats;
DROP STATISTICS Sales.Orders.st_shipcity;
DROP INDEX idx_nc_custid_shipcity ON Sales.Orders;

Should be this:

DROP STATISTICS Sales.Orders.st_shipcity;
DROP INDEX idx_nc_custid_shipcity ON Sales.Orders;
ALTER DATABASE TSQL2012
SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT;
EXEC sys.sp_updatestats;

Note from the Author or Editor:
The reader is correct, the order in the step 8. of the lab should be changed from

ALTER DATABASE TSQL2012
SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT;
EXEC sys.sp_updatestats;
DROP STATISTICS Sales.Orders.st_shipcity;
DROP INDEX idx_nc_custid_shipcity ON Sales.Orders;

to

DROP STATISTICS Sales.Orders.st_shipcity;
DROP INDEX idx_nc_custid_shipcity ON Sales.Orders;
ALTER DATABASE TSQL2012
SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT;
EXEC sys.sp_updatestats;

"f2" in the penultimate line on p. 607 should be f squared.

Note from the Author or Editor:
Confirmed. Thanks!

Access Methods Section states:
'If a table is organized as a heap, then the only access method available to SQL Server is a table scan'
This statement assumes there are no nonclustered indexes available.

TechNet states:
If a table is a heap and does not have any nonclustered indexes, then the entire table must be examined (a table scan) to find any row.

http://technet.microsoft.com/en-us/library/hh213609.aspx

Note from the Author or Editor:
Please replace the sentence in the 2nd paragraph. Instead of

'If a table is organized as a heap, then the only access method available to SQL Server is a table scan'

should be

'If a table is organized as a heap and does not have any nonclustered indexes, then the only access method available to SQL Server is a table scan'