Skip to main content

Professional SQL Server 2000 Programming

Professional SQL Server 2000 Programming

Robert Vieira

ISBN: 978-0-764-55857-3

Aug 2004

1440 pages

Select type: E-Book

Product not available for purchase


What is this book about?

SQL Server 2000 is the latest and most powerful version of Microsoft's data warehousing and relational database management system. This new release is tightly integrated with Windows 2000 and offers more support for XML, as well as improved Analysis Services for OLAP and data mining.

Professional SQL Server 2000 provides a comprehensive guide to programming with SQL Server 2000, from a complete tutorial on Transact-SQL to an in-depth discussion of new features, such as indexed views, user-defined functions, and the wealth of new SQL Server features to support XML. Whether you're coming to SQL Server 2000 from another relational database management system, upgrading your existing system, or perhaps wanting to add programming skills to your DBA knowledge, you'll find what you need in this book to get to grips with SQL Server 2000 development.

What does this book cover?

Here are just a few of the things covered in this book:

  • A complete introduction to Transact-SQL
  • Database design issues
  • Creating and using views, stored procedures, and user- defined functions
  • Responding to events with triggers
  • Making your SQL Server secure
  • Retrieving your data as XML
  • An introduction to Analysis Services
  • Moving data using Data Transformation Services and the bulk copy program
  • Maintaining the integrity of distributed data with replication

Who is this book for?

This book is aimed at the SQL Server developer who wants to make the most out of the new features of SQL Server 2000. No knowledge of SQL Server is assumed, although in order to follow this book, you do need to have an understanding of programming basics such as variables, data types, and procedural programming. Database administration is also covered but only as it affects the SQL Server developer.


Chapter 1: SQL Server 2000 – Particulars and History.

Chapter 2: RDBMS Basics: What Makes Up a SQL Server Database?

Chapter 3: Tools of the Trade.

Chapter 4: The Foundation Statements of T-SQL.

Chapter 5: Joining Tables.

Chapter 6: Creating and Altering Tables.

Chapter 7: Constraints.

Chapter 8: Normalization and Other Basic Design Issues.

Chapter 9: SQL Server Storage and Index Structures.

Chapter 10: Views.

Chapter 11: Writing Scripts and Batches.

Chapter 12: Stored Procedures.

Chapter 13: User Defined Functions.

Chapter 14: Transactions and Locks.

Chapter 15: Triggers.

Chapter 16: Advanced Queries.

Chapter 17: Distributed Queries and Transactions.

Chapter 18: SQL Cursors.

Chapter 19: A Brief XML Primer.

Chapter 20: Integration of XML into SQL Server.

Chapter 21: The Bulk Copy Program (bcp).

Chapter 22: Introduction to Data Transformation Services.

Chapter 23: Replication.

Chapter 24: Advanced Design.

Chapter 25: Analysis Services.

Chapter 26: Full-Text Search.

Chapter 27: English Query.

Chapter 28: Security.

Chapter 29: Performance Tuning.

Chapter 30: Administration Overview.

Chapter 31: Advanced DTS.

Chapter 32: Scripting Administrative Functions with WMI.

Appendix A: System Functions.

Appendix B: Function Listing.

Appendix C: Tools for Our Time.

Appendix D: Access Upsizing.

Appendix E: Microsoft Data Transformation Services Package Object Library Reference.


Download the source code for Professional SQL Server 2000 Programming
Download an amended version of the sp_DBLocks stored procedure for chapter 14
Support Files for NorthwindTriggers
ChapterPageDetailsDatePrint Run
25Typo in Text

On page 25 you'll notice that in the second paragraph discussing Remote Data Objects, the text reads as:

It was created by using a very thing wrapper around ODBC...

This is of course a typo, and should actually read:

It was created by using a very thin wrapper around ODBC...

In short, we need to replace thing with thin .


40-41Error in Concept
Bottom of page 40 states: (Unlike rules, constraints are not really objects unto themselves, but rather pieces of meta data describing a particular table.)

Top of page 41, second statement states: There is the default that is an object unto itself, and the default that is not really an object, but rather meta data describing a particular column in a table (in much the same way as we have constraints, which are objects, and rules, which are not objects but meta data).

The first citation says rules are objects while constraints are not; the second one states that constraints are objects while rules are not.


Last paragraph tells the reader that multiprotocol with respect to accessing SQL directly over the internet will be covered in the security chapter. This is not the case.


67Results in Grid/Results in Text

In the line above the figure on page 67, the text reads:

Choose the Results in Grid option and re-run the previous query...

The text here should actually read as:

Choose the Results in Text option and re-run the previous query...


78Typo in italicized paragraph at bottom of page 78

In this paragraph, you may have noticed how the text reads as:

In addition, SQL Server must go out and figure out...

Of course, this should simply read as:

In addition, SQL Server must go and figure out...


79Unclear statement in 2nd paragraph from bottom of page 79

You may have found yourself being left a little perplexed by the text here - should this be the case, we offer our sincerest apologies. In order to clarify, customer name in this paragraph should actually read company name .

This modification also helps to address any confusion that may have arisen regarding the sentence in the paragraph below the screenshot on page 80, which starts:

The other two columns are...


82Quote use in operator usage examples on page 82

On this page, you'll find a large table, illustrating the various ways in which operators can be used in conjuction with the WHERE clause. Each operator has an associated Example Usage and you'll notice that in some of these examples, any strings that are used, are contained within DOUBLE QUOTES, for example:

<Column1> LIKE ROM%

However, this is erroneous, and if you are experiencing problems here, this could well be the cause. You see, all strings that are to be used in a manner similar to this should actually be enclosed in SINGLE QUOTES, for example:

<Column1> LIKE 'ROM%'


171Moving a column in a SQL Server database

Despite the claims about being unable to change the order of columns in a SQL Server 2000 table, you can reorder columns from the Enterprise Manager (EM) simply by dragging and dropping to any desired location in the table design view.


196Making a Table Self-Referencing

The self-referencing foreign key can be added to the table without using a 'primer' row by simply adding WITH NOCHECK to the ALTER TABLE command shown in the middle of the page. (also see the ALTER TABLE command in SQL Server Books Online.) This is much quicker and simpler than either creating a primer row or only doing this type of contraint on a nullable column.

     ADD CONSTRAINT FK_EmployeeHasManager
     FOREIGN KEY (ManagerEmpID) REFERENCES Employees(EmployeeID)

237Mistake on page 237

On page 237, paragraph 1, the first sentence states:

With the addition of our new column...

When it should actually read as:

With the addtion of our new table...


9276Logical Reads

In the first paragraph on page 276, in the sentences .......larger size cuts down on the number of page changes, which in turn cuts down the logical reads. Fewer logical reads usually means less time and effort spent in I/O, and better performance. the words logical reads should be physical reads. Fewer physical reads will indeed mean better performance.



On the first code snippet of page 328, you need to ensure that 'QUOTED_IDENTIFIER' is set to 'ON' with SET QUOTED IDENTIFIER ON


340Possible quotation mark error

On page 340, in an example, the book used double quotaions for print command. It did not work and gave me errors and changed them to single quotations. Dong.


457"Affect"/"Effect" word misuse on page 457

On page 457, on the second line of the COMMIT TRAN section, the text currently reads as:

That is, the affect of the transaction...

This should actually read as:

That is, the effect of the transaction...



Between pages 529 and 534, you may have noticed that UPDATED_COLUMNS and COLUMNS_UPDATED seem to be used and interchanged when referring to functions, without any explanation. This interchanging of function names is actually erroneous, and you should note that all instances of UPDATED_COLUMNS should be replaced with COLUMNS_UPDATED. Here are some more specific details as to where exactly this errata applies:

Page 529:

UPDATED_COLUMNS in the 2nd bullet point should read as COLUMNS_UPDATED.

Page 532:

The headings at top and further down the page are incorrect, and should in fact refer to COLUMNS_UPDATED. Also, the instance of UPDATED_COLUMNS in the text paragraph at the bottom of the page should be replaced with COLUMNS_UPDATED.



Thing being selected should read CategoryID , not CategoryI



line 6
Thing being selected should read CategoryID , not Cate



Second paragraph needs a correction to the font of a character.

The last line of the paragraph read:
ensuring that our <code>id</code> and <code>idrefs</code> will be enforced.

The last line should read:
ensuring that our <code>id</code> and <code>idref</code>s will be enforced.

Where <code> means the text is in Courier.


676Typo on page 676

The second bullet should contain the word xmltext instead of xmldata , as it currently stands.

Hence the final sentence should read as:

If an attribute is provided with the xmltext directive...

Rather than:

If an attribute is provided with the xmldata directive...


782bcp example

The bcp example at the top of the pages uses a comma as delimiter between the records. Bad idea, because the company names contain commata. An re-import would be nearly impossible.



In the second indented paragraph on page 882, the second sentence should have the word 'opinion' instead of the word 'option'. Therefore the sentence should read: 'Well, that's a garbage recommendation in my not so humble opinion...'


1373ODBC index reference

You may have noticed that in the index of this book, on page 1373, there is an index entry that states:

Open Database Connectivity see ODBC

In turn, you may then have found that the entry for ODBC is missing from the index. Introductory information regarding ODBC and other data access models are in fact discussed on pages 24 and 25. However, for a more detailed discussion of this subject, you may find the supplementary titles that are referred to on page 25 to be of particular interest.