Errata

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Errata for Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

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
PDF
Page xxvii
1st paragraph

The paragraph describes a file called TSQL2012.sql that is included in the zip file included with the book. When the content of the zip file is extracted, the TSQL2012.sql is missing.

Note from the Author or Editor:
Hi Thomas,

Thanks for letting us know. I notified the editors. In the meanwhile, I've made the source code, including the TSQL2012.sql script, available from the book's companion website here: http://tsql.solidq.com/books/tk70461/.

Cheers,
Itzik

Thomas Brown  Dec 01, 2012  Apr 12, 2013
Printed
Page XXVII
First paragraph, 4th line down

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

Chris  Dec 29, 2012  Apr 12, 2013
Other Digital Version
xxvii
question ID : 461P_3.4_06

On question ID : 461P_3.4_06 in the explanation section it says as

"You use the FLOOR function to return the <---- smallest ---- > integer less than or equal to a specific numeric value."

But as per http://msdn.microsoft.com/en-us/library/ms178531.aspx
it says

"Returns the <---- largest ---- > integer less than or equal to the specified numeric expression"

Please verify.. Thanks

Note from the Author or Editor:
Hi Linus,

Confirmed. You use the FLOOR function to return the largest integer less than or equal to a specific numeric value.

Thanks for reporting this!

Anonymous  Jun 22, 2013 
Printed
Page xxvi
Top bullet list

The second level bullet list under the item "SQL Server 2012 Setup Feature Selection" appears to be formatted incorrectly. Currently all of the entries after "Database Engine Services" are indented, making it seem like all of the other options are subcategories of the first. In reality, only one is. So I believe that only "Full-Text And Semantic Extractions For Search" should be indented.

Note from the Author or Editor:
Hi Scott,

Thanks for spotting and reporting the error. The indentation should be as follows:

- Database Engine Services
- Full-Text And Semantic Extractions For Search
- Documentation Components
- Management Tools?Basic (required)
- Management Tools?Complete (recommended)

In words:

Main item: Database Engine Services
Sub item: Full-Text And Semantic Extractions For Search
Main item: Documentation Components
Main item: Management Tools?Basic (required)
Sub item: Management Tools?Complete (recommended)

Scott  Jan 06, 2014 
Printed, PDF
Page 1
N/A

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

Itzik Ben-Gan
 
Jan 05, 2013 
Printed, PDF
Page 2?
Chapter 3, Lesson 2, Exercise 2, Step #1

"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

Carlos Sepulveda  Jan 16, 2013  Apr 12, 2013
Printed
Page 16
1. The FROM clause

In terms of the sequence of evaluation of the query clauses, does it make sense to think of Step 1 (The FROM clause) as "SELECT * FROM Hr.Employees;", even if that is not the most approved syntax, since that is all that is known about the query at that point (Step 1)?

Note from the Author or Editor:
I would say you should think of it simply as:

1 - FROM Hr.Employees

But in order to see how the virtual table that is returned from this step looks like, it's ok to use the query:

SELECT * FROM Hr.Employees;

Just for the purpose of verification.

Cheers,
Itzik

Rand E. Gerald  Dec 20, 2012 
Printed, PDF
Page 17
Paragraph 3. Group Rows Based on the GROUP BY Clause

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).

Zack Jones  Dec 15, 2012  Apr 12, 2013
Printed
Page 19
2nd paragraph, last sentence

The sentence reads

"In this case, the SELECT list returns for each country and order year group ..."

but it should read

"In this case, the SELECT list returns for each country and hire year group ..."

Note the change from "order year" to "hire year".

Note from the Author or Editor:
Nice catch, Scott! Error and fix confirmed.

Scott  Jan 06, 2014 
Printed, PDF
Page 20
First paragraph under 6. Handle Presentation Order

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

Bart Asard  Feb 26, 2013  Apr 12, 2013
Printed, PDF
Page 28
First sentence

Currently: "One of most typical mistakes that T-SQL developers make..."

Should be: "One of the most typical mistakes that T-SQL developers make..."

Reported by Tom Storm.

Thanks!
Itzik

Itzik Ben-Gan
 
Jan 31, 2013  Apr 12, 2013
Printed
Page 34
2nd Paragraph

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

Motiar Rahman  May 21, 2013  Aug 23, 2013
PDF
Page 34
2nd Paragraph

The 2nd Paragraph on Page 34 says-
When the identifier is ?regular,? delimiting it is optional. In a regular identifier, the identifier
complies with the rules for formatting identifiers. 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 (#).

Here can you clarify if the at sign (@) be used as first character? If I use @ as first character in an alias, the following query returns 'Incorrect syntax error' -

select 10 as @test

Incorrect syntax near '@test'.

Though the other characters works fine as first character i.e.
select 10 as #test

#test
10

select 10 as _test

_test
10

Note from the Author or Editor:
Hi Anurag,

The @ sign is allowed as a first character, but only when the identifier is a variable or parameter name. Books Online explains this:

"?The underscore (_), "at" sign (@), or number sign (#).
Certain symbols at the beginning of an identifier have special meaning in SQL Server. An identifier beginning with the "at" sign denotes a local variable or parameter. An identifier beginning with a number sign denotes a temporary table or procedure. An identifier beginning with double number signs (##) denotes a global temporary object."

Cheers,
Itzik

Anurag  Dec 01, 2013 
PDF
Page 44
6th paragraph

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

Peter Kral  Jan 08, 2013  Apr 12, 2013
PDF
Page 47
2nd line

"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

Duncan Maddox  Mar 18, 2013  Apr 12, 2013
PDF
Page 48
3rd paragraph

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!

Marty Jansen  Nov 29, 2012  Apr 12, 2013
Printed
Page 49
Last sentance within String formatting section

The FORMAT example has 9 zeros, but tells the reader the result will have 10 characters. I believe the FORMAT example needs to have a 0 added to match the indicated result.

FORMAT(1759,'0000000000').


Note from the Author or Editor:
Confirmed.

The last sentence right before the section "CASE Expression and Related Functions" currently has: "FORMAT(1759, '000000000')" and should be: "FORMAT(1759, '0000000000')"

As Kimberly said, the format string should have ten zeros instead of nine.

Thanks for reporting this!

Anonymous  Jan 02, 2013  Apr 12, 2013
Printed, PDF
Page 51
First paragr.

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."

Igor Basmanov  Nov 23, 2013  Dec 13, 2013
Printed
Page 51

About COALESCE vs ISNULL.

Chapter 2, Page 51:

"Observe that the type of the COALESCE expression is determined by the returned element, whereas the type of the ISNULL expression is determined by the first input."

The COALESCE is determined by the high precedence in the list of values, right? Example.:

DECLARE @inteiro int
DECLARE @string varchar(max)
DECLARE @datetime datetime2

SELECT COALESCE(@inteiro,2, @string, @datetime, NULL)

Reading that phrase i was expecting that the returned type should be INT. Because the value 2 is INT.

But i got this error:
Msg 206, Level 16, State 2, Line 5
Operand type clash: int is incompatible with datetime2
Msg 206, Level 16, State 2, Line 5
Operand type clash: int is incompatible with datetime2

Because the high precedence on the list its datetime2 datatype.

Then that phrase should be
".. COALESCE expression is determined by the high precedence data type on the list, whereas...."

Thank you!
Eduardo Moura Schaukoski

Note from the Author or Editor:
Nice catch, Eduardo.

Editors, I suggest a change. Instead of:

"Observe that the type of the COALESCE expression is determined by the returned element, whereas..."

Make it:

"Observe that the type of the COALESCE expression is the type of the input expression with the highest precedence, whereas..."

Thanks for sending your feedback!

Itzik

Eduardo Moura Schaukoski  Jan 30, 2014 
Printed
Page 53
Quick Check Answer #2

The answer contains "NEWSEQUENTIAL ID" (note the space) it should be "NEWSEQUENTIALID" without the space.

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

Zack Jones  Jan 05, 2013  Apr 12, 2013
PDF
Page 61

The exam objectives on page 61, chapter 3 have nothing to do with Filtering and Sorting Data and are copied from the exam objectives on page 29, chapter 2 Getting Started with the SELECT Statement.

Note from the Author or Editor:
Hi Patrick,

I can certainly understand why the exam objectives listed in the TK, and specifically in page 61, might seem confusing. I can tell you that we, the authors, were confused too. :) The detailed OD that we got has OD sub items that in many cases seem to be unrelated to the OD parts and main items.

To explain why we indicated the item Working with functions, and more important, what it is doing under Modifying Data, even though the chapter doesn?t deal with data modifications, here goes (hold your breath)?

Chapter 3 discusses working with functions like COALESCE and ISNULL. The OD specifies these functions under a number of parts and items, but note the part, the main item, and the sub item:

Part 2: Work with Data
Item: 2.1: Query data by using SELECT statements.
Sub items: May include but not limited to: use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; ?case versus isnull versus coalesce?

Part 3: Modify Data
Item 3.3: Combining datasets
Sub items: Combine datasets.
May include but not limited to: difference between UNION and UNION all; ?case versus isnull versus coalesce?; modify data by using MERGE statements

Item 3.4: Work with functions
Sub items: May include but not limited to: understand deterministic, non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs).

The chapter also discusses functions like CAST, CONVERT, PARSE, YEAR, MONTH, and so on, so it certainly seemed suitable to specify the OD sub item Work with functions. It?s just unfortunate that in the OD this sub item appears under the item Modify Data.
As for the sub item Implement data types, the chapter certainly goes into detail on how to work with character string types and date and time values, so it seemed suitable. And this sub item appears under the Item Work with Data.

Finally, filtering and sorting are critical topics for anyone learning about T-SQL Querying, but the OD doesn?t specify them explicitly. So the most natural OD sub item that such topics seemed to fit under was Query data by using SELECT statements, which appears under the item Work with Data.

Hope this makes sense. As you can see, working with the exam OD was no picnic. We tried to do the best we could given the circumstances.

Cheers,
Itzik

Patrick Krootjes  Jun 06, 2013 
Printed
Page 63
Third paragraph (right after the table)

This paragraph references the Sales.Customers table as the example, but the running example being used is the HR.Employees table. For consistency, this paragraph should be rewritten to reference employees instead of customers.

Note from the Author or Editor:
Nice catch, Scott!

Need to replace the first sentence after the first query output from:
"However, when NULLs are possible in the data, things get trickier. Consider the customer location columns country, region, and city in the Sales.Customers table."

To:
"However, when NULLs are possible in the data, things get trickier. Consider the employee location columns country, region, and city in the HR.Employees table."

Scott Mancuso  Jan 08, 2014 
Printed
Page 79
First paragraph, second line

"...one-to-one manner, rather than one-to-many."

should be changed to

"...one-to-one manner, but rather one-to-many."

or something similar. "rather than" just doesn't make sense here.

Note from the Author or Editor:
Confirmed. Thanks, Scott!

Should be: "When DISTINCT is used, duplicates are removed; then the result rows don?t necessarily map to source rows in a one-to-one manner, but rather one-to-many."

Scott Mancuso  Jan 08, 2014 
Printed
Page 85
Last paragraph, first sentence

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."

Scott Mancuso  Jan 08, 2014 
Printed
Page 87
Last SQL Statement on the page

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

Zack Jones  Mar 27, 2013  Apr 12, 2013
PDF
Page 89
Last paragraph

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!

Nicolas Jimenez  Nov 25, 2012  Apr 12, 2013
Printed
Page 89
Middle of page

According to the text the following query should return 3 rows based on an arbitrary order:

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY (SELECT NULL)
OFFSET 0 FETCH FIRST 3 ROWS ONLY;

When I run this query, no matter how many times I run it I always get the same 3 records returned. I modified the query to use a completely different table in another database and it also returns the same three rows. For some reason it's not returning rows in an arbitrary manner. Can you please explain why?

For grins I tried

SELECT Top(3) orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY (SELECT NULL)

and I still get the same 3 rows every time.

FWIW below is the result of select @@version:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Thanks in advance for your assistance!

Note from the Author or Editor:
Hi Zack,

In arbitrary order I mean that the order isn't guaranteed. It's not random order where the order will probably be different every time you run the query. In practice, with arbitrary order, the order is optimization-dependent. It is likely that when you repeat the same query multiple times you will get the same plan, and same access methods used, hence same result (assuming the data hasn't changed). But between what's likely to happen and what's guaranteed to happen, there's a big difference. So as mentioned, in arbitrary order, I simply mean that the order is not guaranteed.

Cheers,
Itzik

Zack Jones  Jan 05, 2013 
Printed
Page 89
Last Paragraph

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!

Motiar Rahman  May 23, 2013 
PDF
Page 103
last query

In the following query from the bottom of the page, the attribute "S.N" should be written "S.n".

SELECT D.n AS theday, S.n AS shiftno
FROM dbo.Nums AS D
CROSS JOIN dbo.Nums AS S
WHERE D.n <= 7
AND S.N <= 3
ORDER BY theday, shiftno;

Note from the Author or Editor:
Hi Ivan,

Confirmed. Thanks!

Itzik

Anonymous  Feb 04, 2014 
Printed
Page 105
Query at bottom of page

Does the order in which the tables are listed in the ON condition matter? Consider the query below

SELECT S.companyname AS supplier
,S.country
,p.productID
,p.productname
,p.unitprice
FROM production.Suppliers AS S
INNER JOIN Production.Products AS P ON S.supplierid = P.supplierid
--INNER JOIN Production.Products as P on P.supplierid = S.supplierid
WHERE S.country = N'Japan';

As listed the suppliers table is listed first in the from clause and then listed first in the ON clause; however, commenting that line out and using the line that is currently commented where products is listed first in the join clause yields the same results and execution plan which tells me the order doesn't matter.

Is there a best practice we should follow for the order we list the tables in the ON clause?

Note from the Author or Editor:
No, the order doesn't matter. Equijoin predicates are commutative, meaning that T1.key = T2.key is the same as T2.key = T1.key. So there's no difference conceptually. Also from an optimization perspective, SQL Server's optimizer realizes that there's no difference and therefore can optimize both cases the same way.
There are no best practices that I know of concerning this, but generally picking a form and sticking to it is recommended for consistency's sake.

Cheers,
Itzik

Zack Jones  Jan 06, 2013 
PDF
Page 114
bottom of 4th paragraph

It seems that the last sentence "... based on the JOIN keyword for cross and outer joins." meant to say "... based on the JOIN keyword for cross and inner joins." It seems redundant to write that the need for new syntax for outer joins caused new syntax to be created for outer joins.
As a complete side note, assuming that the "natural join" is ISO standard, it would be fun to list it as well. But, maybe the next version of the book.
p.s. Although I'm familiar with most of the topics, the book flows very well and I'm enjoying it a great deal.

Note from the Author or Editor:
Confirmed. Last sentence before the Quick Check questions should be:

"Then, probably for consistency?s sake, the standard added support for similar syntax based on the JOIN keyword for cross and inner joins."

And Paul, many thanks for the feedback!

Cheers,
Itzik

Paul Neumann  Apr 03, 2013  Apr 12, 2013
PDF
Page 116
Last sentence on page

The phrase "by using parentheses or by repositioning the ON clauses" should read "by using parentheses and by repositioning the ON clause". Both tasks go hand-in-hand when explicitly establishing JOIN order of operation with parentheses.

Note from the Author or Editor:
Suggested revision confirmed.

Just to clarify, you either 1. Use parentheses and reposition the ON clause, or 2. Just reposition the ON clause. But I think that your suggested revision is good.

Paul Neumann  Apr 03, 2013  Apr 12, 2013
PDF
Page 128
3rd sentence

As an example, consider the recursive CTE from the section about CTEs that retuned the management chain leading to employee 9.

-- Don't understand who is being retuned ;-)

Note from the Author or Editor:
Perhaps revise to: "As an example, consider the recursive CTE from the section about CTEs that retuned the management chain leading all the way up to the CEO for a specified employee."

Lars Utterstrom  May 16, 2013  Aug 23, 2013
Printed, PDF
Page 133
last paragraph

The second row of the result set in Practice Exercise 1 number 3, reads as follows:

categoryid productid productname unitprice
----------- ---------- -------------- ----------
2 3 Product IMEHJ 10.00X

There is an extraneous character "X" after the unitprice of 10.00

Note from the Author or Editor:
Confirmed. Thanks!

Peter Kral  Dec 15, 2012  Apr 12, 2013
Printed, PDF
Page 140
Canada

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

Malcolm  Dec 19, 2012  Apr 12, 2013
PDF
Page 146
Lesson 3, Question 1 Answer

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!

Anonymous  Jan 11, 2013  Apr 12, 2013
Printed, PDF
Page 154
Second code sample

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!

Malcolm  Dec 19, 2012  Apr 12, 2013
PDF
Page 155

The output of the example cited for the discussion of multiple grouping sets is difficult to interpret and ambiguous, for instance there are two result rows for shipperid=3, shipyear=NULL: one has numorders=255 and the other numorders=6. This is due to the Sales.Orders having NULL values for some unshipped orders (shippeddate). One is therefore forced into making an 'educated guess' as to which row is the result of which grouping set.

It might make the example easier to understand if Orders with NULL values for shippeddate are excluded, or coalesced to some arbitrary value. An order by clause also helps to understand which rows pertain to which grouping set e.g.

SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE shippeddate is not null
GROUP BY GROUPING SETS
(
( shipperid, YEAR(shippeddate) ),
( shipperid ),
( YEAR(shippeddate) ),
( )
)
order by shipperid, YEAR(shippeddate);

shipperid shipyear numorders
----------- ----------- -----------
NULL NULL 809
NULL 2006 143
NULL 2007 398
NULL 2008 268
1 NULL 245
1 2006 36
1 2007 130
1 2008 79
2 NULL 315
2 2006 56
2 2007 143
2 2008 116
3 NULL 249
3 2006 51
3 2007 125
3 2008 73

(16 row(s) affected)

Note from the Author or Editor:
Nice catch, Tim!

We do talk about a way to distinguish between a NULL representing a placeholder of a grouping set and an original NULL in the table in page 157. But in page 155 it's too early, and best if the example excluded unshipped order like you suggest to avoid confusion. I still rather not add an ORDER BY clause in the example as to not divert attention to it and remember that the result is relational.

Instructions to editors:

1. In page 155 replace the existing query with the following (added WHERE clause with a comment describing its purpose):

SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE shippeddate IS NOT NULL -- exclude unshipped orders
GROUP BY GROUPING SETS
(
( shipperid, YEAR(shippeddate) ),
( shipperid ),
( YEAR(shippeddate) ),
( )
);

2. Replace the existing query output with the following:

shipperid shipyear numorders
----------- ----------- -----------
1 2006 36
2 2006 56
3 2006 51
NULL 2006 143
1 2007 130
2 2007 143
3 2007 125
NULL 2007 398
1 2008 79
2 2008 116
3 2008 73
NULL 2008 268
NULL NULL 809
3 NULL 249
1 NULL 245
2 NULL 315

Many thanks Tim for spotting the error and taking the time to report it!

Cheers,
Itzik

Tim  Apr 25, 2013  Aug 23, 2013
PDF
Page 157
3rd paragraph

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 );

Peter Kral  Dec 08, 2012  Apr 12, 2013
PDF
Page 166
2nd paragraph

In the code sample:

IF OBJECT_ID('Sales.FreightTotals') IS NOT NULL DROP TABLE Sales.FreightTotals;

The second parameter, object_type, is not specified. This is the first instance (of many to follow) where this second parameter is omitted, interspersed with examples where they are not.

This appears to be bad editing, as one expects an exam training guide to emphasize best practices at all times.

If the inconsistent syntax is intentional, to show that the object_type parameter is optional, that should be explained in the text.

On the same note, on page 307 code sample at the bottom of the page:

IF OBJECT_ID (N'Sales.fn_OrderTotalsByYear', N'IF') IS NOT NULL

The parameters are specified as Unicode. This is the first instance (of many to follow) where the parameters are specified as Unicode, interspersed with examples where they are not.

If the inconsistent syntax is intentional, to show that the function will work regardless of the data types of the input parameters, that should be explained in the text.

Note from the Author or Editor:
Confirmed.

I agree that the book should be consistent in its use of the OBJECT_ID function. We will start by making the use of the function consistent in the source code files. Once we're done revising the files, we will upload them to the book's companion website.

Thanks!

Peter Kral  Dec 22, 2012  Apr 12, 2013
PDF
Page 179
2nd paragraph

In the Window Offset Functions section, the first sentence of the paragraph reads:

"Because an explicit \ wasn?t specified, both functions relied on the default offset of 1."

The slash character should be the word "offset".

Note from the Author or Editor:
Confirmed. Thanks!

The first sentence after the first query output should be: "Because an explicit offset wasn?t specified, both functions relied on the default offset of 1."

Peter Kral  Dec 09, 2012  Apr 12, 2013
Printed
Page 179
Last query

For most of the example queries, the output tends to be in the "expected" order, even though no ORDER BY clause is usually specified. Generally this is convenient because it helps the reader better see the concept being illustrated (for example, in the example query on page 178 demonstrating LAG and LEAD).

However, in the example on page 179 for FIRST_VALUE and LAST_VALUE, the output seems to be ordered by custid, and then by orderid DESC. This makes the example rather ineffective as the partitions were ordered by orderdate, orderid. So the "first_val" column displays what in fact is the last value in this presentation, and the "last_val" column gives the first value.

While this is not technically incorrect, I don't think it is very helpful. I would suggest either adding a note pointing out this behavior and once again emphasizing that order is not guaranteed without an ORDER BY clause, or simply add "ORDER BY custid, orderdate, orderid" to the end of the query.

Note from the Author or Editor:
Hi Scott,

This is a good point, and a good reminder that presentation ordering is not guaranteed without an ORDER BY clause.

Editors, I suggest changing the following:
"This query generates the following output (shown here in abbreviated form)."

To:
"This query generates the following output (shown here in abbreviated form, with presentation ordering not guaranteed)."

Scott Mancuso  Jan 11, 2014 
Printed, PDF
Page 191
United States

Chapter 6 - Querying Full-Text Data

I do not see anywhere in the Skills Being Measured any reference to Full-Text data, whereas in the 2008 test it was specified in the Skills Being Measured. Do you know if this knowledge is actually part of the test? This is about 30 pages of the book.


Below is how it is described in the 2008 test, but for the 2012 test this is not there. Seems a big thing to just group under "Query data using SELECT statements" or "Work with functions"

Implement full-text search.

This objective may include but is not limited to: CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE, STOPLIST

Note from the Author or Editor:
Hi Curt,

The short answer is that the exam Objective Domain (OD) does not contain full-text search explicitly. So if you skip this chapter, it probably won't reduce your chances to pass the exam.

As for the long answer; we got the following mandate when designing the outline for the book:
1. Every item in the exam OD MUST be covered by the book
2. If we think that there are important items that the OD doesn't cover, we can cover those as well

Regarding 1, I can tell you that the task wasn't easy at all. The objectives seemed sensible (besides the fact that they were missing full-text search), but the subobjectives were all over the place. Still we did the best we could under the circumstances.

Regarding 2, all database practitioners obviously need to be proficient with T-SQL querying. And we feel that some knowledge of full-text search is recommended. The topic is specialized, hence it requires some background, and therefore it is hard to cover it in less than 30 pages.

To summarize, if you're very short on time and you want to read only the sections in the book that appear directly in the OD, you can skip this chapter. Full-text search was the only major item that wasn't in the OD and that we felt was important to add.
But if you're not short on time, and you trust the professional opinion of the authors in terms of what's important for SQL Serve practitioners to know, we recommend reading this chapter.

BTW, it's important to note that the book should not be considered the only resource that will prepare you for the exam. After all, the exam is supposed to test your experience as well.

We appreciate the question since others might wonder about this as well. So thanks for asking!

Curt Lansing  Dec 16, 2012 
Printed, PDF
Page 193
Code sample at foot of page

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

Malcolm  Dec 20, 2012  Apr 12, 2013
PDF
Page 193
5th paragraph

'sys.sp_help_fulltext_components' has to be 'sys.sp_help_fulltext_system_components' like on page 192

Note from the Author or Editor:
The fifth paragraph, which reads "You might need to restart SQL Server. After you restart it, check whether the filters were successfully installed by using the sys.sp_help_fulltext_components system procedure again". should be corrected to "You might need to restart SQL Server. After you restart it, check whether the filters were
successfully installed by using the sys.sp_help_fulltext_system_components system procedure again."

Patrick Krootjes  Jun 25, 2013  Aug 23, 2013
Printed, PDF
Page 198
1st paragraph

"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.

Malcolm  Dec 21, 2012  Apr 12, 2013
Printed, PDF
Page 198
1st paragraph

"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

Malcolm  Dec 21, 2012  Apr 12, 2013
ePub
Page 198
United Kingdom

My E Book refers to the following 4 sample files to be used for the Text Indexing exercises but they are not on my CD

Querying Full-Text Data

Page 198 Chapter 6

7. Import the four documents included in the folder for this book.

ColumnstoreIndicesAndBatchProcessing.docx
IntroductionToDataMining.docx
AdditivityOfMeasures.docx
WhyIsBleedingEdgeADifferentConference.docx

Note from the Author or Editor:
We are checking the CD content. Meanwhile, the reader can download the source code from our SolidQ site - http://tsql.solidq.com/books/tk70461/.

Richard Purdon  Mar 11, 2013 
198
Page 198 1st paragraph

The 4 documents in the folder known as C:\TK461 listed as being able to be found on the companion CD.

I am a Safari Books subscriber. It seems that the TK461 folder (or TK70461 as suggested in other errata) is not included in the Source Code bundle at the tsql.solidq site.

Can this please be investigated, doing the hands on would be much smoother.

Note from the Author or Editor:
We, the authors, know about this error, and we have already informed the publisher. However, we do not have a possibility to change the CD content:-) Therefore, we enabled download here: http://tsql.solidq.com/books/tk70461/.

Dejan Sarka

Alnis Bajars  Mar 19, 2013 
PDF
Page 201
top of the page

Hello,

In the following text 'Semantinc' should be 'Semantic':

'You can create full-text catalogs and indexes by using SQL Server Full-Text Search and Sematinc Search.'

Regards,

Patrick Krootjes

Note from the Author or Editor:
The first bullet on the page should be changed from "You can create full-text catalogs and indexes by using SQL Server Full-Text Search and Sematinc Search." to "You can create full-text catalogs and indexes by using SQL Server Full-Text Search and Semantic Search."

Patrick Krootjes  Jul 07, 2013  Aug 23, 2013
PDF
Page 201
3rd bullet point in lesson summary

sys.dm_fts_parser is called a dynamic management view - though I believe it is more accurate to call it a DMO (Database Management Object) or a DMF (Dynamic Management Function) as it requires parameters and views do not (cannot) take parameters.

Note from the Author or Editor:
The third bullet on the top of the page reads: "You can use the sys.dm_fts_parser dynamic management view to check how Full-Text Search breaks your documents into words, creates inflectional forms of words, and more."

Change to

"You can use the sys.dm_fts_parser dynamic management object to check how Full-Text Search breaks your documents into words, creates inflectional forms of words, and more."

Theron Knapp  Aug 06, 2013  Aug 23, 2013
PDF
Page 203
paragraphs 3, 6, 7, 8

The protocol of italicizing the initial usage of a defined term is inconsistent on p 203

In paragraph 3, the second sentence "This is the simplest custom proximity term.",
as the first use of proximity term it should be italicized.

In paragraph 6, the first sentence "This is the generation term format of the predicate.",
as the first use of generation term it should be italicized.

In paragraph 7, the first sentence "This is again the generation term format of the predicate.",
as the second use of generation term it should not be italicized.

In paragraph 8, the first sentence "This is a weighted term.",
as the first use of weighted term it should be italicized.

Note from the Author or Editor:
As the reader suggested, the word "proximity" in the 3rd paragraph, the word "generation" in the 6th paragraph, and the word "weighted" in the 8th paragraph should be in italics, while the word "generation" in the 7th paragraph should not be italicized.

Peter Kral  Dec 11, 2012  Apr 12, 2013
Printed, PDF
Page 222
Introduction to XML

For new users to XML in SSMS, this is a minor but important bit of information. Not likely to be on the exam, but helpful anyway. :-)
"NOTE: XML result sets are easy to "make pretty" - if you know a simple trick. Set the query to send "results to grid", so that the XML result set is generated with a hyperlink. Clicking on that hyperlink then makes SSMS format the XML with indents, color coding, and line breaks."

Malcolm  Dec 29, 2012 
PDF
Page 225
Opening tag for the root element in the XML

In the opening xsd:schema tag, the xmlns:xsd and xmlns:sqltypes attributes do not contain double quotes around their respective URLs. I'm still learning, but it should be like this, right?

<xsd:schema targetNamespace="TK461-CustomersOrders" xmlns:schema="TK461-CustomersOrders"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"

The double quotes for xmlns:schema and xmlns:xsd are missing in the book.

Great book though!

Cheers!

Dan

Note from the Author or Editor:
As these are not delimited names, it looks that it works, although the reader is correct. To go strictly with the rules and have an always correct XML, please replace the 2nd and the 3rd row in the XML document at the bottom half of the page 255. Change the current text

xmlns:xsd=http://www.w3.org/2001/XMLSchema
xmlns:sqltypes=http://schemas.microsoft.com/sqlserver/2004/sqltypes

to

xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"

Dan Mudie  Dec 23, 2013 
PDF
Page 250
last para.

"..., modifying sections within the XML data (the modify() method) as opposed to overriding the whole thing, ..."
Do you mean "overwriting the whole document"? From an OO programming background, overriding to me means creating an alternate method of the same name but with different parameters. Or perhaps this is a US vs British English thing (I'm British!)

Note from the Author or Editor:
The last but one sentence on the page 250 should be changed from "The methods support querying (the query() method), retrieving
atomic values (the value() method), checking existence (the exist() method), modifying
sections within the XML data (the modify() method) as opposed to overriding the whole
thing, and shredding XML data into multiple rows in a result set (the nodes() method)." to
"The methods support querying (the query() method), retrieving
atomic values (the value() method), checking existence (the exist() method), modifying
sections within the XML data (the modify() method) as opposed to overwriting the whole
thing, and shredding XML data into multiple rows in a result set (the nodes() method)."

Tim  Apr 29, 2013  Aug 23, 2013
Printed, PDF
Page 258
Exercises 2 and 3

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) .

Malcolm  Jan 02, 2013  Apr 12, 2013
PDF
Page 274
2nd paragraph

The sentence reads "Many of the TSQL12 tables..." should read "Many of the TSQL2012 tables..."

Also, in paragraph three, the first two sentences include the phrase "an optional way". The second use of the phrase could be eliminated.

Note from the Author or Editor:
Page 274, Line 8
Change:
Many of the TSQL12 tables
To:
Many of the TSQL2012 tables

Peter Kral  Dec 17, 2012  Apr 12, 2013
Printed
Page 278
United States

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.

KBardool  Dec 24, 2012  Apr 12, 2013
Printed
Page 278
Exercise 2, Step 2

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;

Jerrod Early  Feb 19, 2013  Apr 12, 2013
Printed
Page 279
Exercise 2 Step 4

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;

Zack Jones  Jan 01, 2013  Apr 12, 2013
Printed
Page 279
Problem 3

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

Anonymous  Jun 07, 2013  Aug 23, 2013
PDF
Page 284
2nd paragraph

The third paragraph begins:

"Like the primary key constraint, the unique constraint automatically creates a unique index with the same name as the constraint. By default, the index will be nonclustered."

This is the first mention of clustered/nonclustered indexes in Chapter 8. If the default clustering behavior of keys is to be included, it should also have been mentioned in the section on primary keys (p 282).

Also, the way the sentences are sequenced, it could imply that "like a primary key constraint" applies to the second sentence, "By default, the index will be nonclustered.". However, a primary key constraint will not always be nonclustered by default, so this is misleading.

Note from the Author or Editor:
Page 284, Line 8
Change:
By default, the index will be nonclustered.
To:
The unique index can be either clustered or nonclustered.

Peter Kral  Dec 18, 2012  Apr 12, 2013
Printed
Page 284
Quick Check, bottom of page

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.

Bruce Hendry  Feb 18, 2013  Apr 12, 2013
PDF
Page 288
para. 5

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.

Tim  Apr 30, 2013  Aug 23, 2013
PDF
Page 295
Answers - Lesson 1 - Question 3

The answer to question Lesson 1, Question 3 (from p 280) is incorrect.

It reads:
3. correct answer: C
A. incorrect: Specifying NULL must come after the data type.
B. incorrect: PERMIT NULL is not a valid construct in the CREATE TABLE statement.
C. correct: You specify NULL right after the data type.
D. incorrect: ALLOW NULL is not a valid construct in the CREATE TABLE statement.

However, the correct answer is D.

The comment for answer B should be the comment for answer C
The comment for answer C should be the comment for answer D
The comment for answer D should be the comment for answer B

Note from the Author or Editor:
Page 295, Lesson 1 Exercise 3
Change:
3. Correct Answer: C
A. Incorrect: Specifying NULL must come after the data type.
B. Incorrect: PERMIT NULL is not a valid construct in the CREATE TABLE statement.
C. Correct: You specify NULL right after the data type.
D. Incorrect: ALLOW NULL is not a valid construct in the CREATE TABLE statement.

To:
3. Correct Answer: D
A. Incorrect: Specifying NULL must come after the data type.
B. Incorrect: ALLOW NULL is not a valid construct in the CREATE TABLE statement.
C. Incorrect: PERMIT NULL is not a valid construct in the CREATE TABLE statement.
D. Correct: You specify NULL right after the data type.

Peter Kral  Dec 17, 2012  Apr 12, 2013
PDF
Page 296
last para.

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

Tim  Apr 30, 2013  Aug 23, 2013
PDF
Page 300
Lesson Summary

In the shaded lesson summary it reads

"After this lesson, you will be able to:
Use the CREATE VIEW statement to create a table"

Shouldn't it read "Use the CREATE VIEW statement to create a view"

I know views act like tables but aren't really tables.

Note from the Author or Editor:
Yes, shoud read:
Use the CREATE VIEW statement to create a view

Zack Jones  Jan 02, 2013  Apr 12, 2013
Printed
Page 300
1st paragraph

The two sentences "Because the database tables are how . . ." And "In this lesson, you will learn . . ." Are duplicated from Chapter 8, Creating Tables and Enforcing Data Integrity. It makes sense there but not on page 300 which introduces Chapter 9 on Views and inline functions.

Note from the Author or Editor:
Page 300, line 5:
Remove the sentence:
Because database tables are how SQL Server stores data, it is
vital to understand the T-SQL commands for creating and altering tables.

Then on page 300, line 6:
Change:
In this lesson, you
learn about these commands and their related options.
To:
In this lesson, you
learn how to create and manage views and inline functions.

Anonymous  Jan 24, 2013  Apr 12, 2013
Printed, PDF
Page 300
Estimated lesson time: 20 minutes

Consider to increase the "Estimated lesson time" for this lesson from 20 minutes to a longer time. 45 minutes or 60 minutes might be more accurate for this lesson.

Note from the Author or Editor:
Page 300, line 12:
Replace
Estimated lesson time: 20 minutes
With
Estimated lesson time: 45 minutes

Hector Rocha  Nov 23, 2013  Dec 13, 2013
Printed
Page 302
9th

Under 'View Options', under 'with schema binding', it says, "The view cannot have its schema definitions changed unless the view is dropped.". It should be "The table cannot have its schema definitions changed unless the view is dropped."

Jyothsna  Dec 25, 2012  Apr 12, 2013
Printed, PDF
Page 307
1st code sample

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,

Malcolm  Jan 03, 2013  Apr 12, 2013
Printed, PDF
Page 307
Quick Check Answer paragraph. Answer 1.

This answer explains that it's possible to have multiple SELECT statements in case of using the UNION statement.

But is it not also true for the UNION ALL, EXCEPT AND INTERSECT statements ?

Note from the Author or Editor:
On page 307, line 12, change:
(using the
UNION statement)
to:
(using the
UNION, UNION ALL, EXCEPT or INTERSECT statements)

Niklosus  Feb 07, 2014 
Printed
Page 309
Last sentence on bottom of page

In the Inline Function Options section, last sentence on page reads: The referenced objects cannot be altered unless the view is dropped or the WITH SCHEMABINDING option is removed.

It should read: unless the function is dropped

Note from the Author or Editor:
On page 309, 2nd line from the bottom:
Change:
The referenced objects cannot be altered unless the view is dropped or the WITH SCHEMABINDING option is removed.
To:
The referenced objects cannot be altered unless the function is dropped or the WITH SCHEMABINDING option is removed.

Bruce Hendry  Feb 18, 2013  Apr 12, 2013
PDF
Page 309
4th (text-)paragraph

It reads:
You can query the function but pass the year you want to see, ...

I think the word "but" is out of place, and should be "and".
So it should be:
You can query the function and pass the year you want to see, ...

Note from the Author or Editor:
Page 309, line
Change:
You can query the function but pass the year you want to see, as follows.
To:
You can query the function and pass the year you want to see, as follows.

Nicky van Vroenhoven  Mar 01, 2013  Apr 12, 2013
PDF
Page 309
Last sentence

Given that the topic is inline functions, the sentence "The referenced objects cannot be altered unless the view is dropped or ..." should be changed to "The referenced objects cannot be altered unless the function is dropped or ..."

Note from the Author or Editor:
On page 309, last line:
Change
The referenced objects cannot be
altered unless the view is dropped or the WITH SCHEMABINDING option is removed
To
The referenced objects cannot be
altered unless the function is dropped or the WITH SCHEMABINDING option is removed

Paul Neumann  Apr 17, 2013  Aug 23, 2013
PDF
Page 309
Last paragraph

It reads:

"The referenced objects cannot be altered
unless the function is dropped or the WITH SCHEMABINDING option is removed"

It Should read:

"When SCHEMABINDING is specified, the base objects cannot be modified in a way that would affect the function definition."

Note from the Author or Editor:
Page 309, 2nd line from the bottom
Change:
The referenced objects cannot be altered
To:
The schemas of the referenced objects cannot be altered

lenovoparts  May 06, 2013  Aug 23, 2013
PDF
Page 312
Exercise 1

Inconsistent syntax in use of function OBJECT_ID()

Practice Exercise 1 part 6 code sample:

IF OBJECT_ID (N'Sales.OrderTotalsByYearCustShip', N'V') IS NOT NULL

Practice Exercise 1 part 8 code sample:

IF OBJECT_ID(' Sales.OrderTotalsByYearCustShip', N'V') IS NOT NULL

In part 8 code sample, the first parameter has a leading space in the syntax, as well as not specifying the parameter as Unicode.

Note from the Author or Editor:
The code sample in practice Exercise 1 part 6 has the recommended form; namely, the object type should be specified and both inputs should be Unicode literals with the N prefix. Hence, the code sample in Practice Exercise 1 part 8 should be changed from:

IF OBJECT_ID(' Sales.OrderTotalsByYearCustShip', N'V') IS NOT NULL

To:

IF OBJECT_ID(N'Sales.OrderTotalsByYearCustShip', N'V') IS NOT NULL

-- leading space removed and both parameters specified as Unicode

BTW, we compiled a list of all references to the OBJECT_ID function in the book and will send to the editors so that future prints of the book will have a consistent form that follows best practices throughout:

OBJECT_ID(N'object_name', N'object_type')

Thanks for reporting this!

Peter Kral  Dec 22, 2012  Apr 12, 2013
PDF
Page 312
Point 9

Point 9 says "Add two parameters...and add a WHERE clause to filter the results". However, the results use a HAVING clause, not a WHERE clause.

Note from the Author or Editor:
Page 312, 2n line up from the bottom:
Change:
"and add a WHERE clause to filter the results"
to
"and add a HAVING clause to filter the results"

Phillip Burton  Mar 13, 2013  Apr 12, 2013
Printed, PDF
Page 314
Lesson Review, 2.B

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.

Malcolm  Jan 03, 2013  Apr 12, 2013
Printed
Page 317
Abstraction Layer - Paragraph 2

"For example, suppose the database DB01 has a view called Sales.Reports ... "

The T-SQL queries reference ReportsDB, not DB01. As I like the more descriptive name in the examples, I think the paragraph should just be changed to say ReportsDB.

Note from the Author or Editor:
Page 317, Line 9
Change:
For example, suppose the database DB01
To:
For example, suppose the database ReportsDB

Jerrod Early  Feb 20, 2013  Apr 12, 2013
Printed, PDF
Page 320
Exercise 1, Item 6, Note

"Note that there is no unique key on the combination of columns in the GROUP BY of the Sales.OrderValues view. Right now, the number of rows grouped is also the number of orders, but that is not guaranteed. Your feedback to the development team should
be that if this set of columns does define a unique row in the table, they should create a unique constraint (or a unique index) on the table to enforce it."
This does not make sense - the GROUP BY columns are all from Sales.Orders, and among these columns is orderid, the primary key. This alone enforces the uniqueness of the row. There is no need for an additional unique constraint (or unique index) for this group of columns.
Recommend removing the note, before the development team sees it.

Note from the Author or Editor:
On page 320, Line 14:
Change:
Note that there is no unique key on the combination of columns in the GROUP BY of the Sales.OrderValues view.
To:
Note that there is no unique key on the combination of columns, minus the orderid column, in the GROUP BY of the Sales.OrderValues view.

Malcolm  Jan 03, 2013  Apr 12, 2013
PDF
Page 324
Practice 2

Hello,

There is a typo in Chapter 9, on page 324, practice 2:

Products.Categories should be Production.Categories, because the schema name in database TSQL2012 is Production.

Regards,

Patrick Krootjes

Note from the Author or Editor:
Page 324, line 14
Change:
Products.Categories
To:
Production.Categories

Patrick Krootjes  Aug 04, 2013  Aug 23, 2013
Printed
Page 331
First paragraph of "INSERT VALUES" section

"Sales.MyOrderValues"

should be

"Sales.MyOrders"

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

Scott Mancuso  Jan 16, 2014 
PDF, ePub
Page 339
Exercise 1, Item 2

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

MalcolM  Jan 03, 2013  Apr 12, 2013
Printed
Page 339
Step 5 in Exercise 1

Minor mistake in step 5. It reads "...query the Sales.MyOrders table" It should read "Sales.MyCustomers table" the insert statement in step 4 added rows to the table Sales.MyCustomers not Sales.MyOrders.

Note from the Author or Editor:
Confirmed.

Currently:
"5. After executing the previous INSERT statement, query the Sales.MyOrders table..."

Should be:
"5. After executing the previous INSERT statement, query the Sales.MyCustomers table..."

Thanks for reporting this!

Zack Jones  Jan 27, 2013  Apr 12, 2013
Printed
Page 353
Update Statement in Step 3 of Exercise 1

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!

Zack Jones  Jan 27, 2013  Apr 12, 2013
PDF
Page 354
SQL statement

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

Tim  May 02, 2013  Aug 23, 2013
Printed, PDF
Page 358
First paragraph

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!

Bart Asard  Jun 07, 2013  Aug 23, 2013
Printed, PDF
Page 361
Exercise 1, Item 3

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!

Malcolm  Jan 04, 2013  Apr 12, 2013
PDF
Page 368
Top of page - 2.B. explanation

The explanation "The DELETE statement does not rest an IDENTITY property." should read "The DELETE statement does not reset an IDENTITY property."

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

Paul Neumann  Apr 19, 2013  Aug 23, 2013
Printed
Page 369
"Exam objectives in this chapter" -section


The bullet point is :

- Modify data by using INSERT, UPDATE, and DELETE statements.

That one belongs to chapter 10, and is probably copied from page 329. It is identical. This one should probably say something about the SEQUENCE object and the IDENTITY column.


Note from the Author or Editor:
The original item was supposed to be:

Modify Data
-Modify data by using INSERT, UPDATE, and DELETE statements.
--use output statement
-Combining datasets.
--Modify data by using MERGE statements

But it seems like we left the top level items only.
I can suggest the following revision to make it clearer:

Modify Data
-Modify data by using INSERT, UPDATE, and DELETE statements (use output clause).
-Combining datasets.

Cheers,
Itzik

Anonymous  Mar 17, 2014 
PDF
Page 380
part 4

Practice Exercise 1 part 4 result

The column value for is_cycling (1) is missing.

Note from the Author or Editor:
Confirmed. The output of step 4 should be:

type start_value minimum_value
---- ------------ -------------
int 1 1

current_value increment is_cycling
-------------- ---------- ----------
1 1 1

Thanks!

Peter Kral  Dec 22, 2012  Apr 12, 2013
Printed, PDF
Page 388
2nd paragraph

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!

Malcolm  Jan 04, 2013  Apr 12, 2013
Printed
Page 392
Query in Step #3

How is it that you can have a CTE defined as SRC and a Table Alias defined as SRC?

It would seem to me for the merge code to work properly that the line "Using Sales.Orders as SRC" should refer to the CTE defined as source and not the physical table.

As the code is written now you can actually highlight the code starting with merge through ";" and it works. Would changing the code as follows force it to use the CTE? In my testing it appears to work as expected:

With cteSRC as
(
Select * from Sales.orders where ShipCountry = N'Norway'
)
Merge into Sales.MyOrders as TGT
Using cteSRC as src on src.orderid = tgt.orderid
when matched and (tgt.custid <> src.custid or tgt.empid <> src.empid or tgt.orderdate <> src.orderdate) then update
Set TGT.custid = src.custid, tgt.empid = src.empid, tgt.orderdate = src.orderdate
when not matched then insert values (src.orderid, src.custid, src.empid, src.orderdate);

Thanks in advance for the clarification


Note from the Author or Editor:
This is actually a bug in the code. Thanks for catching it!

The first code sample in the page is currently:

WITH SRC AS
(
SELECT *
FROM Sales.Orders
WHERE shipcountry = N'Norway'
)
MERGE INTO Sales.MyOrders AS TGT
USING Sales.Orders AS SRC
ON SRC.orderid = TGT.orderid
WHEN MATCHED AND ( TGT.custid <> SRC.custid
OR TGT.empid <> SRC.empid
OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
SET TGT.custid = SRC.custid,
TGT.empid = SRC.empid,
TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT
VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);

It should be replaced with:

WITH SRC AS
(
SELECT *
FROM Sales.Orders
WHERE shipcountry = N'Norway'
)
MERGE INTO Sales.MyOrders AS TGT
USING SRC
ON SRC.orderid = TGT.orderid
WHEN MATCHED AND ( TGT.custid <> SRC.custid
OR TGT.empid <> SRC.empid
OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
SET TGT.custid = SRC.custid,
TGT.empid = SRC.empid,
TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT
VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);

The change is in the USING clause: instead of "USING Sales.Orders AS SRC" it should be "USING SRC"

Thanks!

Zack Jones  Jan 30, 2013  Apr 12, 2013
Printed
Page 392
Query in Step #3

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

Zack Jones  Jan 30, 2013  Apr 12, 2013
PDF
Page 403
6h Paragraph

Practice Exercise 2 part 4

Syntax error in final code sample.

This:
IF OBJECT_ID('Sales.MyOrdersArchive') IS NOT NULL
DROP TABLE Sales. MyOrdersArchive';


Should be this:
IF OBJECT_ID('Sales.MyOrdersArchive') IS NOT NULL
DROP TABLE Sales.MyOrdersArchive;

Note from the Author or Editor:
Confirmed.

The space between "Sales." and "MyOrdersArchive" should be removed. Also, the extraneous single quote character should be removed from the end of "MyOrdersArchive".

Thanks!

Peter Kral  Dec 22, 2012  Apr 12, 2013
Printed, PDF
Page 412
last sentence previous to the last paragraph

In "Books Online for SQL Server 2012 at http://
msdn.microsoft.com/en-us/library/ms186957(v=sql.90).aspx" the weblink is not the correct.

The right weblink is: http://msdn.microsoft.com/en-us/library/ms186957(v=sql.110).aspx

Note from the Author or Editor:
Page 412, 4th line from the bottom:
Replace:
http://msdn.microsoft.com/en-us/library/ms186957(v=sql.90).aspx
With:
http://msdn.microsoft.com/en-us/library/ms186957(v=sql.110).aspx

Hector Rocha  Nov 27, 2013  Dec 13, 2013
PDF
Page 415
1st paragraph

The typeface conventions are not followed; the bullet points are correctly in bold, but incorrectly italicized.
The first use of the term "system transaction" is not italicized
The first use of the term "user transaction" is not italicized
The term "user transaction" is not actually explicitly defined in the text.
The definitions could be made similar in sentence structure

Rewrite the first sentences of these bullet points:

&#9632; <b>System transactions</b> Transactions created by SQL Server to maintain all its internal persistent system tables are classified as <i>system transactions</i>.
&#9632; <b>User transactions</b> Transactions created by users in the process of changing and reading data, whether automatically, implicitly, or explicitly, are classified as <i>user transactions</i>.

Note from the Author or Editor:
Page 415 , Line 3 change:
SQL Server maintains all its internal persistent system tables by
using transactions that it classifies as system transactions. These transactions are not
under user control.
TO:
SQL Server uses <i>system transactions></i> to maintain all its internal persistent system tables; these types of transactions are not under user control.

Page 415 , Line 6 change:
These are transactions created by users in the process of changing
and even reading data, whether automatically, implicitly, or explicitly.
To:
Transactions created by users in the process of changing or reading data, whether automatically, implicitly, or explicitly, are called <i>user transactions</i>.

Peter Kral  Dec 23, 2012  Apr 12, 2013
PDF
Page 420
Example in 4th paragraph

The book states: "The transaction name is displayed in the name column of sys.dm_tran_active_transactions, as shown in the following
example.

USE TSQL2012;
BEGIN TRANSACTION Tran1;"

However, the example certainly doesn't display that, unless you execute a SELECT query like this:

SELECT * FROM sys.dm_tran_active_transactions;

Note from the Author or Editor:
Page 420, 12th line from the bottom:
Replace
BEGIN TRANSACTION Tran1;
With:
SELECT * FROM sys.dm_tran_active_transactions

Hector Rocha  Nov 27, 2013  Dec 13, 2013
PDF
Page 421
2nd paragraph

In the section "Additional Transaction Options"
The typeface conventions are not followed. The first two bulleted points are correctly in bold and italicized, while the third bullet point is only bold.
The first reference to each of the three defined terms in the text is not italicized.

Note from the Author or Editor:
Page 421, Line 34 Also italicize the bold title
Distributed transactions

Peter Kral  Dec 23, 2012  Apr 12, 2013
PDF
Page 421
3rd line

The command "RESTORE DATABASE TSQ2012 FROM DISK = 'C:SQLBackups ..." should read "RESTORE DATABASE TSQL2012 FROM DISK = 'C:\SQLBackups ..."

Note from the Author or Editor:
On page 421, line 3:
Change
RESTORE DATABASE TSQ2012 FROM DISK = 'C:SQLBackups\TSQL2012.bak'
To
RESTORE DATABASE TSQ2012 FROM DISK = 'C:\SQLBackups\TSQL2012.bak'

Paul Neumann  Apr 23, 2013  Aug 23, 2013
PDF
Page 423
3rd paragraph

The section entitled "Blocking" does not give a direct definition of the term block, unlike the next section "Deadlocking".

Make the the first sentence of the "Blocking" section:
A <i>block</i> results when one session obtains a lock on a resource that prevents another session from obtaining a lock on the same resource.

Note from the Author or Editor:
Page 423, Line 17, change:
If two sessions request an exclusive lock on the same resource, and one is granted the request,
then the other session must wait until the first releases its exclusive lock
To
A block occurs when one session has an exclusive lock on a resource, preventing another session from obtaining any kind of lock on the resource.
The term 'block' is a keyword and should be italicized.

Peter Kral  Dec 23, 2012  Apr 12, 2013
PDF
Page 424
First Quick Chek box

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)?

Mohamad Ali Khourback  Mar 04, 2013  Apr 12, 2013
PDF
Page 433
1st paragraph

In the Note: "NOLOCK is Deprecated"

The text puts a dash break into a keyword, and the keyword is wrong.

This:
"Other than in this exercise, use the READ-
COMMITTED table hint instead."

Should be this:

"Other than in this exercise, use the READUNCOMMITTED table hint instead."

Note from the Author or Editor:
Page 433, Line 3
Change:
Other than in this exercise, use the READ-COMMITTED table hint instead.
To:
Other than in this exercise, use the READUNCOMMITTED table hint instead.

Peter Kral  Dec 23, 2012  Apr 12, 2013
Printed, PDF
Page 433
Table 12.8

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>

Malcolm  Jan 05, 2013  Apr 12, 2013
PDF
Page 437
Second last paragraph

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

Henrik Nordtorp  Jan 07, 2013  Apr 12, 2013
PDF
Page 437
Chapter 12, Lesson 2: Implementing Error Handling, p. 437

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'

Ari Harju  Apr 05, 2013  Apr 12, 2013
PDF
Page 440
2nd sentence in last paragraph

The text "However, custom code for structured error ..." should be "However, custom code for unstructured error ..."

Note from the Author or Editor:
Page
Change
However, custom code for structured error handling can quickly become complex.
To
However, custom code for unstructured error handling can quickly become complex.

Paul Neumann  Apr 23, 2013  Aug 23, 2013
PDF
Page 443
4th paragraph

The last sentence reads, "To return the error number, you could add it to the @error_message string."
This is the first mention of "the @error_message string" in the book. What is it? A parameter, a variable? Either eliminate this sentence or clarify.

Note from the Author or Editor:
Page 443, Line 20:
Change:
To return the error number, you could add it to the @error_message string.
To:
To return the original error number, you could add it to the msg_str parameter of the RAISERROR statement.

Peter Kral  Dec 23, 2012  Apr 12, 2013
Printed
Page 445
Code section in step 2

I realize this is being overly nit-picky but I'll point it out anyway.

In the insert statement the product description parameter is defined as "N'Test1: Ok category id'"

This is the insert statement that fails due to the duplicate primary key error. Perhaps a better product description would be "N'Test1: fails productid'" or something like that.

The same can be said for the second update statement in the batch which is at the top of page 446.

Note from the Author or Editor:
Total of 14 related fixes:

1. Page 428 Line 33:
Change:
VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
To:
VALUES(101, N'Test2: New productid', 1, 1, 18.00, 0);

2. Page 429 Line 10:
Change:
VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
To:
VALUES(101, N'Test2: New productid', 1, 1, 18.00, 0);

3. Page 445, line 15
Change:
VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
To:
VALUES(1, N'Test1: Duplicate productid', 1, 1, 18.00, 0);

4. Page 445, line 41:
Change:
VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
To:
VALUES(1, N'Test1: Duplicate productid', 1, 1, 18.00, 0);

5. Page 446, Line 9
Change:
VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
To:
VALUES(101, N'Test2: New productid', 1, 1, 18.00, 0);

6. Page 446, Line 37:
Change
VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
To:
VALUES(1, N'Test1: Duplicate productid', 1, 1, 18.00, 0);

7. Page 447 Line 23:
Change:
VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
To:
VALUES(1, N'Test1: Duplicate productid', 1, 1, 18.00, 0);

8. Page 447, Line 33
VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
To:
VALUES(101, N'Test2: New productid', 1, 1, 18.00, 0);

9. Page 448, Line 17:
Change:
VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
To:
VALUES(1, N'Test1: Duplicate productid', 1, 1, 18.00, 0);

10. Page 448, Line 19:
Change:
VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
To:
VALUES(101, N'Test2: New productid', 1, 1, 18.00, 0);

11. Page 449 Line 2:
Change:
VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
To:
VALUES(1, N'Test1: Duplicate productid', 1, 1, 18.00, 0);

12. Page 449, Line 5:
VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
To:
VALUES(101, N'Test2: New productid', 1, 1, 18.00, 0);

13. Page 449 Line 25:
Change:
VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
To:
VALUES(1, N'Test1: Duplicate productid', 1, 1, 18.00, 0);

14. Page 449, Line 28:
VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
To:
VALUES(101, N'Test2: New productid', 1, 1, 18.00, 0);

Zack Jones  Feb 03, 2013  Apr 12, 2013
PDF
Page 445
Question 1 code-lines

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

Stefan Wilms  Apr 23, 2013  Aug 23, 2013
Printed, PDF
Page 448
Section 12-2 exercise 3.1, first paragraph

"The first INSERT fails, but the second will succeed because SQL Server by default will not roll back a transaction with a duplicate primary key error."

The 2nd INSERT not only fails but the rest of the paragraph predicts that it will. This is contradictory, what default behavior are you referring to?

Note from the Author or Editor:
Page 448, line 3:
Change:
by default will not roll back a transaction
To
by default will not abort a transaction

Anonymous  Mar 06, 2013  Apr 12, 2013
PDF
Page 453
4th paragraph

In the Exam Tip, the sentence has an extraneous single quote mark before the word ANSI

Current:
You should leave QUOTED_IDENTIFIER set to ON because that is the `ANSI standard and the SQL Server default

Correct:
You should leave QUOTED_IDENTIFIER set to ON because that is the ANSI standard and the SQL Server default

Note from the Author or Editor:
Page 453, line 13, remove extraneous single quote mark:
Change: the `ANSI standard and the SQL Server default
To: the ANSI standard and the SQL Server default

Peter Kral  Dec 23, 2012  Apr 12, 2013
PDF
Page 453
SELECT statements

In both SELECT statements there should be a space between address and FROM.

Note from the Author or Editor:
Page 453, line 20 and line 33 (two places)
Change
addressFROM
To:
address FROM
(add the space!)

Rob Bebbington  Mar 20, 2013  Apr 12, 2013
PDF
Page 459
5th paragraph

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

Peter Kral  Dec 23, 2012  Apr 12, 2013
Printed, PDF
Page 460
Practice exercise 2 Step 5

The sentence:
The user actually types SELECT 1 --',

should be:
The user actually types ' SELECT 1 --,

Note from the Author or Editor:
Page 460, Line 12
Change
SELECT 1 - -
To
SELECT 1 --

Weidong Shen  Feb 27, 2013  Apr 12, 2013
Printed
Page 465
Answer for question 2 in lesson 1.

The answer states that a commit "commits only the innermost level of a nested transaction". If by commit we understand the action of persisting the changes made within the scope of nested transaction, then I think this answer is incorrect because it is clearly stated in the lesson that the commit only decrements the @@trancount system variable. Only the commit in the first upper level transaction actually persists the data.

Note from the Author or Editor:
Page 465, Line 13
Change:
Correct Answers: B and C
To:
Correct Answers: B

Page 465 Line 18
Change:
C. Correct: A single COMMIT commits only the innermost level of a nested transaction.
To:
C. Incorrect: A single COMMIT commits data only at the outermost level of a nested transaction.

Fernando Sibaja  Jan 27, 2013  Apr 12, 2013
PDF
Page 466
Answer to Question 3

Why is A incorrect? If (severity>10 => Abort) then (serverity>16 => Abort) is a logical consequence.

Note from the Author or Editor:
On page 466, line 22:
Change
A. If a T-SQL error with a severity level > 16 occurs, the transaction will be aborted.
To:
A. If a T-SQL error with a severity level > 16 occurs, the transaction will not be aborted.

Stefan Wilms  Apr 26, 2013  Aug 23, 2013
Printed, PDF
Page 469
Whole Chapter

If you have the first print of the book, you could be holding a collector's item. While browsing the book, my wife, Lilach, discovered that Chapter 14 was printed by mistake before Chapter 13! This doesn't really interrupt the flow since the swapped Chapters are independent of each other; but still, an amusing surprise. We notified the editors, and of course, this will be fixed in the next print. But if you are holding a copy with this error, consider yourself lucky--you are holding a limited edition!

Itzik Ben-Gan
 
Dec 11, 2012  Dec 14, 2012
PDF
Page 471
2nd paragraph

The 2nd bullet point is incorrect:

"You cannot use CREATE AGGREGATE, RULE, DEFAULT, CREATE, FUNCTION, TRIGGER, PROCEDURE, or VIEW statements."

I can create a procedure:

CREATE PROC dbo.Sample
AS
CREATE TABLE #sample ( x int CONSTRAINT c DEFAULT 1)

Both the keywords CREATE and DEFAULT can be used within a stored procedure.

More correct would be:
"You cannot use a CREATE statement for any of these object types: AGGREGATE, RULE, DEFAULT, FUNCTION, TRIGGER, PROCEDURE, or VIEW."

Note from the Author or Editor:
page 471, line 27
Suggested rewording is more exact. Change:
"You cannot use CREATE AGGREGATE, RULE, DEFAULT, CREATE, FUNCTION, TRIGGER, PROCEDURE, or VIEW statements."
To:
"You cannot use a CREATE statement with any of the following object types: AGGREGATE, RULE, DEFAULT, FUNCTION, TRIGGER, PROCEDURE, or VIEW."

Peter Kral  Dec 24, 2012  Apr 12, 2013
Printed
Page 471
page numbering

While annotating my copy of the 40-761 training kit, I noticed that not all the corrections give the page numbers in the 1st printed edition (which has chapters 13 and 14 in reverse order).

I'm including a concordance for the currently affected items, in hopes that others can use it to make their corrections more quickly.

The first column lists the page numbers as given in the list of corrections. The second column lists the corresponding page numbers in my copy of the book (1st ed).

Page numbers after 552 (in the list) seem to coincide in any edition.

pg 471 (2 items, same) 503
pg 485 517
pg 492 (3 items) 524
pg 493 525
pg 497 529
pg 498 530
pg 508 540
pg 509 (2 items) 541
pg 513 470
pg 530 (2 items) 482
pg 540 ok

And a minor formatting note:
On pg. 158, the last column name (numorders) in the result set is displayed below 'shipcountry'. This seems to be a typesetting problem.

Note from the Author or Editor:
Thanks for sharing this information, Ed. I'm sure other readers will appreciate it.

As for the typesetting problem, I'm looking at the PDF of the third print and the numorders column appears to be in the right place. Though the underscores do seem to be a bit off, and continue to the next line.

Ed  May 01, 2013  Aug 23, 2013
PDF
Page 477
code sample at bottom of page

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

Paul Neumann  Apr 24, 2013  Aug 23, 2013
Printed
Page 479
2nd Paragraph

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;

Jerrod Early  Mar 01, 2013  Apr 12, 2013
Printed, PDF
Page 485
Exercise 1, Item 7

Code line beginning
THROW 50000, 'dbo.BackupDatabases: @databasename must...
should read
THROW 50000, 'dbo.BackupDatabases: @databasetype must...

Note from the Author or Editor:
Page 485, line 23
Change:
THROW 50000, 'dbo.BackupDatabases: @databasename must be User or System', 0;
To:
THROW 50000, 'dbo.BackupDatabases: @databasetype must be User or System', 0;

Malcolm  Jan 07, 2013  Apr 12, 2013
PDF
Page 489
After Step 8

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.

Paul Neumann  Apr 24, 2013  Aug 23, 2013
Printed, PDF
Page 491
DML Triggers, 2nd paragraph

The sentence:
no matter how may rows may be affected.

should be:
no matter how many rows may be affected.

Note from the Author or Editor:
Page 491, Line 27
Change:
A trigger executes only once for each DML statement, no matter how may rows
To:
A trigger executes only once for each DML statement, no matter how many rows

Weidong Shen  Feb 28, 2013  Apr 12, 2013
PDF
Page 492
5th paragraph

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

Peter Kral  Dec 24, 2012  Apr 12, 2013
Printed, PDF
Page 492
Last paragraph

"First, make sure it's an AFTER trigger. In a trigger definition, the FOR can be replaced with either AFTER or INSTEAD OF to determine the type of trigger."
This change might make this a bit more helpful:
"AFTER is the default type when FOR is specified. FOR can be replaced with either AFTER or INSTEAD OF to explicitly define the type of trigger."

Note from the Author or Editor:
Page 492, Line 26
Change:
In a trigger definition, the FOR can be replaced with
either AFTER or INSTEAD OF to determine the type of trigger.
To:
In a trigger definition, AFTER is the default type of trigger when you specify FOR. But you can replace FOR with either AFTER or INSTEAD OF to determine the type of trigger.

Malcolm  Jan 07, 2013  Apr 12, 2013
PDF
Page 492
last paragraph

"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,

Peter Kral  Jan 24, 2013  Apr 12, 2013
PDF
Page 493
2nd paragraph

This instruction is misplaced:
"Now add an existence test to the OBJECT_ID() function, using 'TR' as the object type. De-
fine it on the TSQL2012 Sales.OrderDetails table, and call it Sales.tr_SalesOrderDetailsDML. "

It appears *after* a code snippet that already contains the code described; it should appear before the code snippet.

Note from the Author or Editor:
Page 493 - Move 2 sentences and add 1 new sentence. Here is the correct result:
Now add an existence test to the OBJECT_ID() function, using 'TR' as the object type. Define
it on the TSQL2012 Sales.OrderDetails table, and call it Sales.tr_SalesOrderDetailsDML.
IF OBJECT_ID('Sales.tr_SalesOrderDetailsDML', 'TR') IS NOT NULL
DROP TRIGGER Sales.tr_SalesOrderDetailsDML;
GO
CREATE TRIGGER Sales.tr_SalesOrderDetailsDML
ON Sales.OrderDetails
AFTER DELETE, INSERT, UPDATE
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN; -- Must be 1st statement
SET NOCOUNT ON;
END;
Now add SELECT statements on the inserted and deleted tables

Peter Kral  Dec 24, 2012  Apr 12, 2013
Printed
Page 495
Code example for Instead Of dml trigger

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

Jerrod Early  Mar 07, 2013  Apr 12, 2013
PDF
Page 495
Instead of Trigger

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

Stefan Wilms  Jun 06, 2013  Aug 23, 2013
PDF
Page 497
1st paragraph

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."

Peter Kral  Dec 24, 2012  Apr 12, 2013
PDF
Page 498
3rd paragraph

Typo in the instruction.

This:
"In this exercise, you create an AFTER trigger to enforce a business rule against the Sales.OrderDetails table in the TSQL12 database."

Should be this:
"In this exercise, you create an AFTER trigger to enforce a business rule against the Sales.OrderDetails table in the TSQL2012 database."

Note from the Author or Editor:
Page 498, Ex. 2 introduction:
This:
"in the TSQL12 database."

Should be this:
" in the TSQL2012 database."

Peter Kral  Dec 24, 2012  Apr 12, 2013
PDF
Page 498
Exercise 2, step 2 - 2nd sentence

Sentence "The trigger finds the violating row, which has a
unitprice of 9.00 and a discount of 0.40." should read "The trigger finds the violating row, which has a
unitprice of 9.00 and a discount of 0.60.".

Note from the Author or Editor:
On page 498, line 44:
Change
The trigger finds the violating row, which has a
unitprice of 9.00 and a discount of 0.40.
To
The trigger finds the violating row, which has a
unitprice of 9.00 and a discount of 0.60.

Paul Neumann  Apr 24, 2013  Aug 23, 2013
Printed
Page 507
Question 1, Part 1, Second line

"Sales.SalesOrder"

should be changed to

"Sales.OrderDetails"

Note from the Author or Editor:
Confirmed. Thanks!

Scott Mancuso  Jan 25, 2014 
PDF
Page 508
4th paragraph

The instruction for Exercise 2 is wrong.

This:
"In this exercise, you write two table-valued UDFs: one as an inline function and the other as a multistatement function."

Should be this:
"In this exercise, you write an inline table-valued UDF."

Note from the Author or Editor:
Current description is incorrect. Page 508, Exercise 2 introduction:
"In this exercise, you write two table-valued UDFs: one as an inline function and the other as a multistatement function."

Change to this:
"In this exercise, you write an inline table-valued UDF."

Peter Kral  Dec 24, 2012  Apr 12, 2013
PDF
Page 509
4th paragraph

The instruction for step 4 has a typo

This:
"Use the variable as the parameters, and assign the name fn_FilteredExtension."

Should be this:
"Use the variables as the parameters, and assign the name fn_FilteredExtension."

Note from the Author or Editor:
In Section 4, page 509:
Replace:
Use the variable as the parameters
With:
Use the variables as parameters

Peter Kral  Dec 24, 2012  Apr 12, 2013
Printed
Page 513
WAITFOR Section

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.

Zack Jones  Jan 20, 2013  Apr 12, 2013
Printed
Page 513
Question 2 Lesson 1

Why is Answer C correct? Answer C states that both temp tables and passed parameters are visible, but on page 482, under Calling Other Stored Procedures, 2nd bullet point says "Variables declared in Proc1 and Proc1's parameters are not visible..."

Note from the Author or Editor:
Page 513, Line 17
Change:
C. Correct: Both passed parameters and temporary tables are visible to called stored procedures.
To:
C. Correct: The called procedure can see temporary tables and parameters passed to it from the calling procedure.

Michelle  May 30, 2013  Aug 23, 2013
PDF
Page 530
bottom of page

The code snippets are not terminated with semicolons, they should be added. Also, there should be a line feed added between the first and second code snippets for readability.

This:
SELECT C.custid, C.companyname,
O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid
SELECT C.custid, C.companyname,
O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE O.custid < 5

Should be this:
SELECT C.custid, C.companyname,
O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid;

SELECT C.custid, C.companyname,
O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE O.custid < 5;

Note from the Author or Editor:
Fully agree with the reader, so I am just copying the changes neeed at the bottom of the page.

This:
SELECT C.custid, C.companyname,
O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid
SELECT C.custid, C.companyname,
O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE O.custid < 5

Should be this:
SELECT C.custid, C.companyname,
O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid;

SELECT C.custid, C.companyname,
O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE O.custid < 5;

Peter Kral  Dec 25, 2012  Apr 12, 2013
Printed, PDF
Page 530
N/A

Spatial aggregates are included with the "Skills Measured" web page for this exam, and the included practice test includes at least one question on spatial datatypes. This is a big, (mostly) new feature of SQL Server 2012. Except for an oblique mention of spatial datatypes in one sentence on page 530, this exam training kit doesn't touch on that topic.
While I can easily pull this from BOL, a note in this book would have been nice to nudge me towards this as preparation for the exam.
Great book though - a capivating walk through the many features of SQL Server 2012.

Note from the Author or Editor:
Hi Malcolm and editors,

Editors: see suggestion below.

Malcolm, as you noticed spatial aggregates are mentioned as a sub-item in the exam?s objective domain (OD). The connection to T-SQL querying is quite loose, and especially for a foundational exam. Nevertheless, since it is mentioned in the OD, it?s probably worthwhile to provide a pointer to books online as you suggested. An appropriate chapter would be Chapter 5 - Grouping and Windowing.

Editors: I suggest adding the following note at the end of page 154 as the item in the section ?Working with a Single Grouping Set.?

Note: SQL Server 2012 allows you to create user defined aggregates (UDA) using .NET code based on the Common Language Runtime (CLR). SQL Server 2012 provides some built-in CLR UDAs for the spatial data types GEOMETRY and GEOGRAPHY and also allows you to create new UDAs operating on spatial types as inputs. For more details please refer to books online.

Cheers,
Itzik

Malcolm  Jan 13, 2013  Apr 12, 2013
Printed
Page 540
Step 4 of exercise 1. Second sentence.

"The function only needs two parameters: unit price and qty." should instead say that it requires three parameters since it also uses discount.

Note from the Author or Editor:
Page 508, line 1
Change:
The function only needs two parameters: unitprice and qty.
To:
The function needs three parameters: @unitprice, @qty, and @discount.

Hillary  Feb 08, 2013  Apr 12, 2013
Printed, PDF
Page 552
last code sample on page

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
);

Malcolm  Jan 09, 2013  Apr 12, 2013
PDF
Page 552
5 th paragraph

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."

Hanna Takla  Apr 09, 2013  Aug 23, 2013
PDF
Page 556
2nd sentence in 2nd to last paragraph

The sentence "Note that the order data ..." should be changed to "Note that the order date ..."

Note from the Author or Editor:
The second sentence in the last but on paragraph on the page 556 should be changed from "Note that the order data (the od column in the figure) is used for the clustering key." to
"Note that the order date (the od column in the figure) is used for the clustering key."

Paul Neumann  Apr 29, 2013  Aug 23, 2013
Printed
Page 557
Intermediate level pages in the figure

The values in the OD column of second and last intermediate level pages seem out of whack.

The second intermediate level page (the one identified as 1:47185) should hold od / unq values from 20050118 / 258 (inclusive) to 20050206 / 210 (exclusive). However, the last row in this page is shown as referencing the value 20040206 / 170, which clearly seems to belong in the first intermediate level page (the one identified as 1:47184).

The last three rows in the last intermediate level page (1:36838) also appear to be out of place, as the od values of 20071231 are below the starting value of 20081225 for this page.

Note from the Author or Editor:
Hi Ira,

Nice catch!

The problems seem to be in the intermediate level of the tree.

Page 1:47184 (first intermediate-level page) should be revised as follows:
The third row is currently: 20041210 1 1:47122
It should be revised to: 20041210 7 1:47122

Page 1:47185 (second intermediate-level page) should be revised as follows:
The last row is currently: 20040206 170 1:47831
It should be revised to: 20050206 170 1:47831

Page 1:36838 (last intermediate-level page) should be revised as follows:
The last three rows are currently:
20071231 496 1:41477
20071231 536 1:41478
20071231 576 1:41479

They should be revised to:
20081231 496 1:41477
20081231 536 1:41478
20081231 576 1:41479

Thanks for taking the time to report this!

Ira Gladnick  Jul 04, 2013  Aug 23, 2013
PDF
Page 564
1st paragraph

In the fourth sentence, the word "organized" is misspelled as "orgnized"

Note from the Author or Editor:
The fourt sentence of the forst paragraph - the word
"orgnized"
should be replaced with
"organized".

Peter Kral  Dec 25, 2012  Apr 12, 2013
PDF
Page 564
Last sentence on page

The sentence portion "... than SQL Server can also ..." should be changed to "... then SQL Server can also ...".

Note from the Author or Editor:
The last sentence in the page, which is "If the clustering key is narrow?for
example a 4-byte integer?than SQL Server can also accommodate more rows on a leaf-level
page than when RID is used as the row locator.", should be changed to
"If the clustering key is narrow?for
example a 4-byte integer?then SQL Server can also accommodate more rows on a leaf-level
page than when RID is used as the row locator."

Paul Neumann  Apr 29, 2013  Aug 23, 2013
Printed, PDF
Page 566
1st paragraph

"order data is used for the clustering key"
The field used is called orderdate, which is similar in spelling, so this may be a typo to refer to it as data.
Knowing what field is actually used for the clustering key helps make the subsequent paragraph make sense.

Note from the Author or Editor:
The second sentence on the page should be changed from

It is the same example of the customers? orders table; order data is used for the clustering key, and order ID is used for the key of the nonclustered index.

to

It is the same example of the customers? orders table; order date is used for the clustering key, and order ID is used for the key of the nonclustered index.

Malcolm  Jan 09, 2013  Apr 12, 2013
PDF
Page 566
2nd sentence on page

The sentence portion "... order data is used for the clustering key ..." should be changed to "... order date is used for the clustering key ..."

Note from the Author or Editor:
The second sentence in the page, in the first paragraph, should be modified from "It is the same example of the
customers? orders table; order data is used for the clustering key, and order ID is used for the
key of the nonclustered index."
to
"It is the same example of the
customers? orders table; order date is used for the clustering key, and order ID is used for the
key of the nonclustered index."

Paul Neumann  Apr 29, 2013  Aug 23, 2013
Printed, PDF
Page 571
Exercise 1, Item 7

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;

Malcolm  Jan 09, 2013  Apr 12, 2013
Printed, PDF
Page 572
Exercise 2, Item 3

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;

Malcolm  Jan 09, 2013  Apr 12, 2013
PDF
Page 573
last paragraph

In the second sentence, verb tense is incorrect.

This:
"You need to know which types of queries can benefit from indexes, and which types of queries does not use indexes, even if indexes exist."

Should be this:
"You need to know which types of queries can benefit from indexes, and which types of queries do not use indexes, even if indexes exist."

Note from the Author or Editor:
In the second sentence, verb tense is incorrect.

Existing:
"You need to know which types of queries can benefit from indexes, and which types of queries does not use indexes, even if indexes exist."

Should be replaced with:
"You need to know which types of queries can benefit from indexes, and which types of queries do not use indexes, even if indexes exist."

Peter Kral  Dec 26, 2012  Apr 12, 2013
PDF
Page 585
last paragraph

The text for bullet point AUTO_UPDATE_STATISTICS_ASYNC should mention whether this is on or off by default, as the two preceding bullet points do. Also, add that "Setting this option to OFF has no effect unless AUTO_UPDATE_STATISTICS is set to ON."

Note from the Author or Editor:
Add these sentence to the last bullet at the bottom of the page: "This option is turned off by default, and changing this option has no effect unless AUTO_UPDATE_STATISTICS is set to ON."

Peter Kral  Dec 26, 2012  Apr 12, 2013
PDF
Page 592
Practice Exercise 2 step 8

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;

Peter Kral  Dec 26, 2012  Apr 12, 2013
Printed
Page 607
bottom paragraph

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

Note from the Author or Editor:
Confirmed. Thanks!

Anonymous  Jan 30, 2013  Apr 12, 2013
Printed
Page 632
2nd paragraph

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'

Gordon Aird  Oct 16, 2013  Dec 13, 2013
Printed, PDF
Page 633
2nd paragraph

"SQL Server uses an allocation order scan for a clustered table if a query does not request any specific order, if the isolation level is Read Uncommitted, or if you are working in a readonly environment."
This does not appear to be true with SQL Server 2012, regardless of Ordered status and Read Uncommitted (or TABLOCK/NOLOCK hints). The clustered index scan is used, even when the data is highly logically fragmented (and thus this "choice" is more expensive than an allocation order scan).
It appears this sentence can be simplified to
"SQL Server uses an index order scan for a clustered table."

Note from the Author or Editor:
Hi Malcolm and editors,

Note to editors: I suggest revising the above sentence to:

"SQL Server may use an allocation order scan for a clustered table if (the table has more than 64 pages and the query does not request any specific order) and (the isolation level is Read Uncommitted or you are working in a readonly environment)."

Malcolm,

It's still true in SQL Server 2012. But it could be that the tables in your tests are too small (e.g., the ones in the sample database TSQL2012). The full set of conditions that would make the storage engine process a Clustered Index Scan operator in the query plan with an allocation order scan are:

the operator has the property Ordered:False (e.g., when running a query without requesting order)
AND the table is large enough (cutoff point is 64 pages)
AND (the isolation level is read uncommitted or the environment is read only)

The reason the table size plays a role here is that there's some cost in interpreting the IAM pages to perform an allocation order scan, vs. no such cost in an index order scan. So the table needs to be sufficiently large to justify an allocation order scan.

You can run the following test to confirm this (I ran it on SQL Server 2012 Enterprise with SP1):

set nocount on;
use tempdb;
if object_id(N'dbo.t1', N'U') is not null drop table dbo.t1;
go
create table dbo.t1
(
col1 int not null constraint dft_t1_col1 default(abs(checksum(newid()))+1) constraint pk_t1 primary key,
filler binary(7000) not null default (0x)
);
go
insert into dbo.t1 default values;
go 60
-- notice query returns rows in index order since table is under cutoff point of 64 pages
select col1 from dbo.t1 with (readuncommitted);
go
insert into dbo.t1 default values;
go 10
-- notice query returns rows in allocation order since table is beyond cutoff point of 64 pages
select col1 from dbo.t1 with (readuncommitted);
go

Output:

Beginning execution loop
Batch execution completed 60 times.
col1
-----------
17052570
40236225
78936274
241188294
281178379
317086439
364748868
367079665
370218676
436777968
...

Beginning execution loop
Batch execution completed 10 times.
col1
-----------
1660147560
1945183350
1215848958
1514331929
845480725
1644447856
1974693894
582033769
1168923393
1943771221
...

Malcolm  Jan 11, 2013  Apr 12, 2013
Printed, PDF
Page 639
2nd paragraph

"Then the algorithm checks the next rows from the other side and adds them to the output until they match the predicate."
I think this should read (change in caps):
"Then the algorithm checks the next rows from the other side and adds them to the output IF they match the predicate."

Note from the Author or Editor:
I would prefer a slightly different change than the reader suggested.

This:
"Then the algorithm checks the next rows from the other side and adds them to the output until they match the predicate."

Should be:
"Then the algorithm checks the next rows from the other side and adds them to the output as long as they match the predicate."

Malcolm  Jan 11, 2013  Apr 12, 2013
Printed
Page 645
Second sentence of step 6 in exercise 1.

The sentence reads ' ... nonclustered covering index on the orderdate column ...'.

Why is orderdate even mentioned here as it is no longer a part of the SELECT statement?

Also, the actual index used, idc_nc_custid, is not a covering index.

Note from the Author or Editor:
Nice catch, thanks.
The second sentence in the step 6 on the page 645, which is currently

"As you probably expected, SQL Server scanned the clustered Sales.Customers table, then scanned the nonclustered covering index on the orderdate column of the Sales.Orders table, and then used the Merge Join iterator to join the data."

should read

"As you probably expected, SQL Server scanned the clustered Sales.Customers table, then scanned the nonclustered index on the custid column of the Sales.Orders table, and then used the Merge Join iterator to join the data."

Mike McCallum  Oct 22, 2013  Dec 13, 2013
Printed
Page 661
Last sentence on page

The last sentence on the page 'You can specify query hints in an INSERT statement except when a SELECT clause is used inside the statement. Under the Remarks section of the msdn article ms181714, it says "Query hints cannot be specified in an INSERT statement except when a SELECT clause is used inside the statement." Which is correct?

Note from the Author or Editor:
Pure tzpo, thanks for noticing it. The last sentence on page 661 should be "You cannot specify query hints in an INSERT statement except when a SELECT clause is used inside the statement."

Michelle  Jun 06, 2013  Aug 23, 2013