Inside T-SQL 2005 Errata

Should be: “On the other hand, a UNIQUE constraint, sorting, and grouping treat NULLs as equal:
* You cannot insert into a table two rows with a NULL in a column that has a UNIQUE constraint defined on it.”

Should be:CASE WHEN n % 10 = 0 THEN 1 + ABS(CHECKSUM(NEWID())) % 30 ELSE 0 END AS orderdate

Should be: signal_wait_time_ms

Should be:
counter_name
—————————-
Buffer cache hit ratio
Buffer cache hit ratio base
Page lookups/sec
Free list stalls/sec
Free pages
Total pages
Target pages
Database pages
Reserved pages
Stolen pages

Should be:
“I’ll be discussing some access methods to use against the Orders table, both when it’s structured as a heap and when it’s structured as a clustered table.”

Should be:
“With no fragmentation at all, the performance of an ordered scan of an index should be very close to the performance of anunordered scan, because both will end up reading the data physically in a sequential manner.”

Should be: “An ordered index scan is used not only when you explicitly request the data sorted; rather, it is also used when…”

Should be: “Unordered Nonclustered Index Scan + Lookups”

Should be:
“… the filter generates an empty set and the expression MIN(keycol + 1) yields a NULL.”

Should be: #CustsStage

Should be: #usp_AsyncSeq

Should be: “When you modify the table on the “one” side of a one-to-many join…”

Should be:
“2. Lock @n available new messages using an UPDATE TOP(@n) statement with the READPAST hint to skip locked rows, and change their status to ‘open’. @n represents a configurable input that determines the maximum number of messages to process in each iteration.”

Should be: “Run the code in Listing 9-8 to create the fn_subordinates2 function, which is a revision of fn_subordinates1 that also supports a level limit.”

Should be: “– #SubsSort is a temp table that will hold the final”

Should be: “– Load the rows from #SubsPath to #SubsSort…”

Should be: “– and previous level <= @maxlevels”

Should be: “…and its path is: parent path + employee id…”

Should be: “Let’s review typical requests from a tree. For each request, I’ll provide a sample query followed by its output.
Return the subtree with a given root, generating the output shown in Table 9-35:“

Should be: “…in the BOMTC…”

Error: 36 should also be in the list.

Should be: “All the lamps are off except for lamps number 1, 4, 9, 16, 25, 36, 49, 64, 81, and 100, which are on.”

Error: the first number in the sequence (4) was added by mistake.

Should be: “Here’s the first part of the sequence with a few additional numbers: 3, 3, 5, 4, 4, 3, 5, 5, 4, 3, 6, 6, 8, 8, 7, 7, 9, 8, 8, 6, 9, 9, 11, 10, 10, 9, 11, 11, 10, …”

Should be:

SELECT app,
(SELECT COUNT(*) FROM dbo.Sessions AS C
WHERE C.app = T.app
AND ts >= starttime
AND ts < endtime) AS cnt
FROM (SELECT DISTINCT app, starttime AS ts
FROM dbo.Sessions) AS T;

Should be:

SELECT app, MAX(cnt) AS mx
FROM (SELECT app,
(SELECT COUNT(*) FROM dbo.Sessions AS C
WHERE C.app = T.app
AND ts >= starttime
AND ts < endtime) AS cnt
FROM (SELECT DISTINCT app, starttime AS ts
FROM dbo.Sessions) AS T) AS D
GROUP BY app;

Should be:CREATE TABLE dbo.CustomerData ( custid INT NOT NULL PRIMARY KEY, txt_data VARCHAR(MAX) NULL, ntxt_data NVARCHAR(MAX) NULL, binary_data VARBINARY(MAX) NULL, xml_data XML NULL );

Error: this is mentioned later in the chapter; not earlier.

Should be: “Note that the XML datatype can be adequate to store the state of the objects using XML serialization in a database as well.”

Error: Missing check for divide by zero error.

Should be:
// Division
public ComplexNumberCS DivCN(ComplexNumberCS c)
{
// null checking
if (this._isnull || c._isnull)
return new ComplexNumberCS(true);
    // zero checking
if (c.Real == 0 && c.Imaginary == 0)
throw new ArgumentException();
    // division
return new ComplexNumberCS(
(this.Real * c.Real + this.Imaginary * c.Imaginary)
/ (c.Real * c.Real + c.Imaginary * c.Imaginary),
(this.Imaginary * c.Real – this.Real * c.Imaginary)
/ (c.Real * c.Real + c.Imaginary * c.Imaginary)
);
}

Error: drive letter is missing from the path to the DLL.

Should be:
CREATE ASSEMBLY ComplexNumberCS
FROM ‘C:\ComplexNumberCS\ComplexNumberCS\bin\Debug\ComplexNumberCS.dll’
WITH PERMISSION_SET = SAFE;

Should be:#Region “arithmetic operations” ‘ Addition Public Function AddCN(ByVal c As ComplexNumberVB) As ComplexNumberVB ‘Null(checking) If Me.isNullValue Or c.isNullValue Then Return New ComplexNumberVB(True) End If ‘ addition Return New ComplexNumberVB(Me.Real + c.Real, _ Me.Imaginary + c.Imaginary) End Function ‘ Subtraction Public Function SubCN(ByVal c As ComplexNumberVB) As ComplexNumberVB ‘Null(checking) If Me.isNullValue Or c.isNullValue Then Return New ComplexNumberVB(True) End If ‘ subtraction Return New ComplexNumberVB(Me.Real – c.Real, _ Me.Imaginary – c.Imaginary) End Function ‘ Multiplication Public Function MulCN(ByVal c As ComplexNumberVB) As ComplexNumberVB ‘Null(checking) If Me.isNullValue Or c.isNullValue Then Return New ComplexNumberVB(True) End If ‘ multiplication Return New ComplexNumberVB(Me.Real * c.Real – _ Me.Imaginary * c.Imaginary, _ Me.Imaginary * c.Real + Me.Real * c.Imaginary) End Function ‘ Division Public Function DivCN(ByVal c As ComplexNumberVB) As ComplexNumberVB ‘Null(checking) If Me.isNullValue Or c.isNullValue Then Return New ComplexNumberVB(True) End If ‘Zero checking If c.Real = 0 And c.Imaginary = 0 Then Throw New ArgumentException() End If ‘ division Return New ComplexNumberVB( _ (Me.Real * c.Real + Me.Imaginary * c.Imaginary) _ / (c.Real * c.Real + c.Imaginary * c.Imaginary), _ (Me.Imaginary * c.Real – Me.Real * c.Imaginary) _ / (c.Real * c.Real + c.Imaginary * c.Imaginary) _ ) End Function #End Region

Error: To obtain the same result, it’s not required to remove the schema binding with the XML column, so you can validate the XML fragments against the XSD Schema.

Should be: “To explain other aspects of the XML data type, I’ll need to insert a contact who speaks two foreign languages. Therefore, you will change the schema associated to the OtherAttributes XML column using the minOccurs and maxOccurs XML Representation Attributes.”

You can find more details on the subject in SQL Server MVP Lorenzo Benaglia’s blog entry (http://blogs.dotnethell.it/lorenzo/Post_7895.aspx).

Note: this change will impact the cost estimates of the operators in Figure 2-3 and the statistics I/O measures that you will get when running the code against #T1. But the rest remains the same, and the points made in the discussion are just as valid.

Error: in the lines of code referring to the patterns N’%sp_%’ and N’%xp_%’, _ (underscored) is treated as a wildcard instead of as a character. The fix is to use N’%sp[_]%’ and N’%xp[_]%’.

Should be:
“…
OR UPPER(@cols) LIKE UPPER(N’%xp[_]%’)
OR UPPER(@cols) LIKE UPPER(N’%sp[_]%’)
…”

SELECT Cur.mnth, Cur.qty, SIGN(Cur.qty – Prv.qty) AS sgn
FROM dbo.VSalesRN AS Cur
JOIN dbo.VSalesRN AS Prv
ON Cur.rn = Prv.rn + 1;
GO

Should be:
CREATE VIEW dbo.VSgn
AS

SELECT Cur.mnth, Cur.qty, SIGN(Cur.qty – Prv.qty) AS sgn
FROM dbo.VSalesRN AS Cur
LEFT OUTER JOIN dbo.VSalesRN AS Prv
ON Cur.rn = Prv.rn + 1;
GO

Should be:
…
CSgn AS
(
SELECT Cur.mnth, Cur.qty, SIGN(Cur.qty – Prv.qty) AS sgn
FROM CSalesRN AS Cur
LEFT OUTER JOIN CSalesRN AS Prv
ON Cur.rn = Prv.rn + 1
)
…

Should be: “The stored procedure and the underlying objects belong to the same owner.”

Error: in the lines of code referring to the patterns N’%sp_%’ and N’%xp_%’, _ (underscored) is treated as a wildcard instead of as a character. The fix is to use N’%sp[_]%’ and N’%xp[_]%’.

Should be:
“…
OR UPPER(@cols) LIKE UPPER(N’%xp[_]%’)
OR UPPER(@cols) LIKE UPPER(N’%sp[_]%’)
…”

Should be:

SELECT COLUMN_NAME AS updated_column FROM INFORMATION_SCHEMA.COLUMNS AS C JOIN @UpdCols AS U ON COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)), COLUMN_NAME, ‘ColumnID’) = U.ordinal_position WHERE TABLE_SCHEMA = ‘dbo’ AND TABLE_NAME = ‘T1’ ORDER BY U.ORDINAL_POSITION;