Inside Microsoft® SQL ServerT 2005: T-SQL Programming Comments and Corrections (923020)

MORE INFORMATION

Sample Code: Updated version of Chapter 8 sample code available

An updated version of the sample file installed to "My Documents\Microsoft Press\Inside T SQL Programming\Chapter 08 - Triggers.sql" is available for download from the Microsoft Download Center:

Ch8Trigr.exe

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591

How to Obtain Microsoft Support Files from Online Services Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.

Page 32: Missing definition of column xml_data in code sample

On page 32, the CREATE TABLE section of the code sample reads:

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

It should read:

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

Page 55: The DivCn method is missing a division by zero check

On page 55, the Division section of the code sample reads:

// Division
public ComplexNumberCS DivCN(ComplexNumberCS c)
{
	// null checking
	if (this._isnull || c._isnull)
		return new ComplexNumberCS(true);
	// 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)
		);
}

It should read:

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

Page 56: Drive letter missing in path to ComplexNumberCS.dl

On page 56, the second line of the first code sample reads:

FROM ' \ComplexNumberCS\ComplexNumberCS\bin\Debug\ComplexNumberCS.dll'

It should read:

FROM ' C:\ComplexNumberCS\ComplexNumberCS\bin\Debug\ComplexNumberCS.dll'

Page 58: Incorrect complex number referenced

On page 58, the first sentence of the first sentence reads:

"You can see that the second complex number (2, 3i) is sorted before the first one (2, 3i)"

It should read:

"You can see that the second complex number (1, 7i) is sorted before the first one (2, 3i)"

Pages 63-64: Missing check for divide by zero error, and error in code comments

On pages 63-64, the #Region "arithmetic operations" section in Listing 1-5 reads:

#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
        ' addition
        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
        ' addition
        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
          ' addition
          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

It should read:

#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

Page 69: .vsd file referenced in place of .vdx

On page 69, the last sentence of the paragraph following Figure 1-7 reads:

"To follow the demonstration, create the folder C:\VisioFiles and copy the .vsd files from the book's CD to that folder."

It should read:

"To follow the demonstration, create the folder C:\VisioFiles and copy the .vdx files from the book's CD to that folder."

Page 74: The phrase "up to three" used in place of "three other types of"

On page 74, the second sentence of the first paragraph reads:

"After creating the Primary XML index, you can create up to three secondary XML Indexes:"

It should read:

"After creating the Primary XML index, you can create three other types of secondary XML Indexes:"

Page 77: Term Hobbie used in place of Hobby

On page 77, the third line of the last code sample reads:

<Hobbie>Beer</Hobbie>

It should read:

<Hobby>Beer</Hobby>

Page 78: Term Hobbie used in place of Hobby

On page 78, the third line of the list of errors reads:

English where element 'Foreign:Hobbie' was specified. Location: /*:Foreigns[1]/*:Hobbie[1]

It should read:

English where element 'Foreign:Hobby' was specified. Location: /*:Foreigns[1]/*:Hobby[1]

Page 82: Term Hobbie used in place of Hobby

On page 82, the fifth line of the last code sample reads:

insert <D:Hobbie>Cigar</D:Hobbie>

It should read:

insert <D:Hobby>Cigar</D:Hobby>

Page 92: Table #T2 referenced in place of table #T1

On page 92, the first line on the page reads:

"though proc2's code creates a table called #T2 with two columns and loads a row with two"

It should read:

"though proc2's code creates a table called #T1 with two columns and loads a row with two"

Pages 93 and 94: Table #T42 referenced in place of table #T

On pages 93 and 94, the sample code reads:

-- Assume @schema and @insert were constructed dynamically
DECLARE @schema AS VARCHAR(1000), @insert AS VARCHAR(1000);
SET @schema = 'col1 INT, col2 DECIMAL(10, 2)';
SET @insert = 'INSERT INTO #T42 VALUES(10, 20.30)';

-- In the outer level, create temp table #T with a single dummy column
CREATE TABLE #T42(dummycol INT);

-- Within a dynamic batch:
-- Alter #T adding the columns you need
-- Alter #T dropping the dummy column
-- Open another level of dynamic execution
-- Populate #T
EXEC('
ALTER TABLE #T42 ADD ' + @schema + ';
ALTER TABLE #T42 DROP COLUMN dummycol;
EXEC(''' + @insert + ''')');
GO

-- Back in the outer level, access #T in a new batch
SELECT * FROM #T42;

-- Cleanup
DROP TABLE #T42;

It should read:

-- Assume @schema and @insert were constructed dynamically
DECLARE @schema AS VARCHAR(1000), @insert AS VARCHAR(1000);
SET @schema = 'col1 INT, col2 DECIMAL(10, 2)';
SET @insert = 'INSERT INTO #T VALUES(10, 20.30)';

-- In the outer level, create temp table #T with a single dummy column
CREATE TABLE #T(dummycol INT);

-- Within a dynamic batch:
-- Alter #T adding the columns you need
-- Alter #T dropping the dummy column
-- Open another level of dynamic execution
-- Populate #T
EXEC('
ALTER TABLE #T ADD ' + @schema + ';
ALTER TABLE #T DROP COLUMN dummycol;
EXEC(''' + @insert + ''')');
GO

-- Back in the outer level, access #T in a new batch
SELECT * FROM #T;

-- Cleanup
DROP TABLE #T;

Page 98: 100000 used instead of 10000 in code sample

On page 98, the tenth line of the code sample reads:

SELECT n, (n - 1) % 100000 + 1 FROM dbo.Nums

It should read:

SELECT n, (n - 1) % 10000 + 1 FROM dbo.Nums

Page 100: 100000 used instead of 10000 in code sample

On page 100, the first line of the code sample reads:

SELECT n AS col1, (n - 1) % 100000 + 1 AS col2,

It should read:

SELECT n AS col1, (n - 1) % 10000 + 1 AS col2,

Page 119: Correction in caption for listing 3-3

On page 119, the caption under listing 3-3 reads:

"Cursor code for custom aggregate"

It should read:

"Cursor code for running aggregations"

Page 121: Correction in inline caption for Figure 3-1

On page 121, the inline caption for Figure 3-1 reads:

"Max Concurrent Sessions Benchmark"

It should read:

"Running Aggregations Benchmark"

Page 148: sp_droplinkedsrvlogin used in place of sp_addlinkedsrvlogin

On page 148, the third bullet point reads:

"Use the sp_droplinkedsrvlogin stored procedure to map local logins to a security account on the remote server."

It should read:

"Use the sp_addlinkedsrvlogin stored procedure to map local logins to a security account on the remote server."

Page 160: The term Inserts used in place of Updates

On page 160, the title of Table 4-6 reads:

"Table 4-6 Contents of Computations Table After Inserts"

It should read:

"Table 4-6 Contents of Computations Table After Updates"

Page 195: Missing terms from the code sample

On page 195, the ninth line of the first code sample reads:

JOIN dbo.VSalesRN AS Prv

It should read:

LEFT OUTER JOIN dbo.VSalesRN AS Prv

Page 202: sys.sql.modules referenced in place of sys.sql_modules

On page 202, the second sentence of the last paragraph on the page reads:

"If you don't specify the ENCRYPTION option, SQL Server stores the text defining the body of the object/routine as clear text in sys.sql.modules (or in syscomments in SQL Server 2000)."

It should read:

"If you don't specify the ENCRYPTION option, SQL Server stores the text defining the body of the object/routine as clear text in sys.sql_modules (or in syscomments in SQL Server 2000)."

Page 335: The captions for tables 8-3 and 8-4 are reversed

On page 335, the caption for table 8-3 reads:

"Table 8-3 Contents of T1Audit"

It should read:

"Table 8-3 Contents of T1"

The caption for table 8-4 reads:

"Table 8-4 Contents of T1"

It should read:

"Table 8-4 Contents of T1Audit"

Page 345: .query method suggested in place of .value

On page 345, the first two sentences of the paragraph following the More Info box read:

"To extract a particular attribute from the XML value, you use the following XQuery expression: xml_value.query('data(//attribute_name)'). xml_value will typically be a variable to which you assigned the XML value returned by the eventdata function, and attribute_name is the name of the attribute you want to extract."

They should read:

"To extract a particular attribute from the XML value, you use the following XQuery expression: xml_value.value('(<path_to_element>)[1]', <sql_type>). xml_value will typically be a variable to which you assigned the XML value returned by the eventdata function, and <path_to_element> is the path to the attribute you want to extract."

Page 346: .query used in place of .value in Listing 8-10

On pages 346, lines 9-11 of Listing 8-10 read:

+ QUOTENAME(CAST(@eventdata.query('data(//SchemaName)') AS sysname))
+ N'.' +
QUOTENAME(CAST(@eventdata.query('data(//ObjectName)') AS sysname));

They should read:

+ QUOTENAME(@eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'))
+ N'.' + 
QUOTENAME(@eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'));

Page 348: .query used in place of .value in Listing 8-11

On page 348, lines 8-13 of the code in listing 8-11 read:

CAST(@eventdata.query('data(//PostTime)') AS VARCHAR(23)),
CAST(@eventdata.query('data(//EventType)') AS sysname),
CAST(@eventdata.query('data(//LoginName)') AS sysname),
CAST(@eventdata.query('data(//SchemaName)') AS sysname),
CAST(@eventdata.query('data(//ObjectName)') AS sysname),
CAST(@eventdata.query('data(//TargetObjectName)') AS sysname),

They should read:

@eventdata.value('(/EVENT_INSTANCE/PostTime)[1]',         'VARCHAR(23)'),
@eventdata.value('(/EVENT_INSTANCE/EventType)[1]',        'sysname'),
@eventdata.value('(/EVENT_INSTANCE/LoginName)[1]',        'sysname'),
@eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]',       'sysname'),
@eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]',       'sysname'),
@eventdata.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'sysname'),

Page 349: .query used in place of .value in code sample

On page 349, the second line of the code sample before Table 8-11 reads:

CAST(eventdata.query('data(//TSQLCommand)') AS NVARCHAR(2000))

It should read:

eventdata.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)')

Page 350: .query used in place of .value in listing 8-12

On page 350, lines 31-34 of Listing 8-12 read:

CAST(@eventdata.query('data(//PostTime)') AS VARCHAR(23)),
CAST(@eventdata.query('data(//EventType)') AS sysname),
CAST(@eventdata.query('data(//LoginName)') AS sysname),
CAST(@eventdata.query('data(//ObjectName)') AS sysname),
CAST(@eventdata.query('data(//LoginType)') AS sysname),

They should read:

@eventdata.value('(/EVENT_INSTANCE/PostTime)[1]',   'VARCHAR(23)'),
@eventdata.value('(/EVENT_INSTANCE/EventType)[1]',  'sysname'),
@eventdata.value('(/EVENT_INSTANCE/LoginName)[1]',  'sysname'),
@eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
@eventdata.value('(/EVENT_INSTANCE/LoginType)[1]',  'sysname'),

Page 373: 'Version 2' referenced rather than 'Version 1'

On page 373, the second sentence from the end of the page reads:

"You get the output 'Version 2', and the process keeps a shared lock on the data because the transaction is still open."

It should read:

"You get the output 'Version 1', and the process keeps a shared lock on the data because the transaction is still open."

Page 374: 'Version 2' referenced rather than 'Version 1'

On page 374, the second sentence on the page reads:

"You still get 'Version 2' back, meaning you got a repeatable read even though another process attempted to change the data in between your reads."

It should read:

"You still get 'Version 1' back, meaning you got a repeatable read even though another process attempted to change the data in between your reads."

Page 388: Column name used is incorrect

On page 388, the second paragraph under Deadlock with a Single Table reads:

"Before I demonstrate such a scenario, let's first run the following UPDATE statement to make sure that T1.col2 is set to 102 where keycol =2:"

It should read:

"Before I demonstrate such a scenario, let's first run the following UPDATE statement to make sure that T1.col1 is set to 102 where keycol =2:"

Microsoft Press is committed to providing informative and accurate books. All comments and corrections listed above are ready for inclusion in future printings of this book. If you have a later printing of this book, it may already contain most or all of the above corrections.