Introduction

The by Example Series

How does the by Example series make you a better programmer? The by Example series teaches programming using the best method possible. After a concept is introduced, you'll see one or more examples of that concept in use. The text acts as a mentor by figuratively looking over your shoulder and showing you new ways to use the concepts you just learned. The examples are numerous. While the material is still fresh, you see example after example demonstrating the way you use the material you just learned.

The philosophy of the by Example series is simple: The best way to teach computer programming is by using multiple examples. Command descriptions, format syntax, and language references are not enough to teach a newcomer a programming language. Only by looking at many examples in which new commands are immediately used and by running sample programs can programming students get more than just a feel for the language.

Who Should Use This Book?

Microsoft SQL Server 2000 Programming by Example is targeted toward people with previous experience in any programming language.

As a database programming book, we expect you to have some background knowledge about logical database design. Understanding how to define entities, attributes, and relationships between entities is essential in producing any good database system. We will provide you with some comments about this subject when required, but we will not go into deeper detail. If you feel uncomfortable about this subject, we suggest that you read a general database design book first.

No prior experience in Transact-SQL is necessary; however, if you have experience working with the SQL language, from any other database system, this book can be used as a reference in which you will find a lot of useful examples that you can use to program applications in SQL Server.

If you do have experience with any previous version of SQL Server, you will find many examples that you can use to practice the extended functionality of SQL Server 2000. However, this is not an upgrading book for users of previous versions, so we do not assume any prior knowledge of previous versions.

If you are a Web developer, this book can teach you how to use SQL Server's new XML functionality to access data from the Internet. If you are a SQL Server developer and you want to introduce yourself to the new XML world, you can find in this book some useful examples on how to use this exciting new functionality.

Learning a new programming language is a mixture of theory and practice. We try to provide as many examples as possible about every topic. We advise you to apply these new concepts as soon as possible in a real scenario, because this is the best way to reinforce your learning effort. If you are not working in a database design right now, create your own personal database to manage appointments, books, pictures, or your personal music library.

This Book's Organization

This book provides you with the skills needed to develop and maintain SQL Server applications. Also, it contains the enhancements introduced in SQL Server 2000.

We highly recommend that you go over all the examples in this book. They were designed to help you understand each concept and feature of Transact-SQL. You can use Query Analyzer, which is explained in Appendix B, "Using SQL Query Analyzer," to execute all examples presented in this book.

Commonly, there are some tasks that can be performed using Enterprise Manager instead of Transact-SQL. Be aware that every task that you perform in Enterprise Manager translates to a set of instructions in Transact-SQL executed behind the scenes. Because the purpose of this book is to teach you the Transact-SQL language, examples are based in Transact-SQL and, in some specific cases, the way to perform the task in Enterprise Manager is also explained.

Appendix A, "Using SQL Server Instances," shows you how to use one of the new features of SQL Server 2000, multi-instance support. This appendix is useful to practice the distributed queries examples that appear in Chapter 15, "Working with Heterogeneous Environments: Setting Up Linked Servers."

Chapter 6, "Optimizing Access to Data: Indexes," is an advanced chapter that teaches you how to optimize access to databases using indexes efficiently. The information contained in this chapter, although very important, is not essential to understand the next chapters. You can read this chapter when you feel confident enough using SQL Server 2000 and you want to optimize your database.

As a programming book, we deliberately do not cover administration subjects. However, we do include information about two important administrative subjects:

• Security—Understanding how SQL Server 2000 manages security is crucial to create a secure database. In Chapter 1, "Relational Database Management Systems and SQL Server," we explain this subject in detail from a programmer's point of view.

• Importing and exporting data—Because almost every programmer must import and export data from time to time, we cover this subject in Chapter 14, "Transferring Data to and from SQL Server."

Chapter 16, "Working with XML Data in SQL Server 2000," is not included in this book's printed material. Due to the late availability of the latest version of the SQL Server 2000 Web Release, we had to provide this chapter in an online format only. You can download Chapter 16 from the http://www.mcp.com site (http://www.mcp.com/que/byexample_que.cfm).

Unfortunately, some exciting new features are not covered in this book, such as

• SQL Server 2000 Administration

• Data Transformation Services Development—however, we cover basic creation of DTS packages

• English Query

• Full-Text Search

• Analysis Services

SQL Server 2000 is a vast product, and we decided to focus on what we consider to be the basic set of features that every database developer should know about SQL Server 2000.

An evaluation version of SQL Server 2000 Enterprise Edition is included in this book. If you don't have SQL Server installed on your machine, this evaluation version can be used to practice with all examples shown throughout the book.

This book prepares you for one of the core exams of the Microsoft Certified Database Administrator (MCDBA) certification: Exam 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition. This exam is also an elective of the Microsoft Certified Systems Engineer (MCSE) certification. For details on this exam, you can visit Microsoft's Web site at http://www.microsoft.com/trainingandservices/exams/examasearch.asp?PageID=70-229.

Conventions Used in This Book

Examples are identified by the icon shown at the left of this sentence.

Listing, code, Transact-SQL keywords, and object names appear in monospace font, such as

EXEC sp_help

Many examples contain output, either as warning and error messages, and result sets. In those cases, you can identify the output by the icon shown at the left of this sentence.

We do not show the output for some examples if the output is obvious, irrelevant, or does not offer any benefit to the reader. In general, we prefer to show the output, so you can check whether you executed the example properly.

What's Next?

Microsoft SQL Server 2000 is a powerful tool, capable of managing big-scale databases fast and efficiently. However, not even the most powerful hardware and the best relational database management system can improve a poorly designed database application.

Learning the Transact-SQL language will help you create an efficient, versatile, and feature-rich database application.

Please visit the by Example Web site for code examples or additional material associated with this book:

http://www.mcp.com/que/byexample_que.cfm

You can find comments, error logs, and additional code about this book on its own Web site:

http://www.sqlserverbyexample.com

You can contact the authors by email at

Carlos Eduardo Rojas: [email protected]

Fernando G. Guerrero: [email protected]

The public Microsoft newsgroups represent an amazing learning opportunity as well. You can find free support from other SQL Server colleagues, SQL Server MVPs, and members of the Microsoft SQL Server group:

news://msnews.microsoft.com/microsoft.public.sqlserver.programming
news://msnews.microsoft.com/microsoft.public.sqlserver.ce
news://msnews.microsoft.com/microsoft.public.sqlserver.clients
news://msnews.microsoft.com/microsoft.public.sqlserver.clustering
news://msnews.microsoft.com/microsoft.public.sqlserver.connect
news://msnews.microsoft.com/microsoft.public.sqlserver.datamining
news://msnews.microsoft.com/microsoft.public.sqlserver.datawarehouse
news://msnews.microsoft.com/microsoft.public.sqlserver.dts
news://msnews.microsoft.com/microsoft.public.sqlserver.fulltext
news://msnews.microsoft.com/microsoft.public.sqlserver.mseq
news://msnews.microsoft.com/microsoft.public.sqlserver.odnc
news://msnews.microsoft.com/microsoft.public.sqlserver.olap
news://msnews.microsoft.com/microsoft.public.sqlserver.replication
news://msnews.microsoft.com/microsoft.public.sqlserver.security
news://msnews.microsoft.com/microsoft.public.sqlserver.server
news://msnews.microsoft.com/microsoft.public.sqlserver.setup
news://msnews.microsoft.com/microsoft.public.sqlserver.tools
news://msnews.microsoft.com/microsoft.public.sqlserver.xml
news://msnews.microsoft.com/microsoft.public.es.sqlserver
news://msnews.microsoft.com/microsoft.public.espanol.sqlserver.administracion
news://msnews.microsoft.com/microsoft.public.espanol.sqlserver.olap
news://msnews.microsoft.com/microsoft.public.fr.sqlserver
news://msnews.microsoft.com/microsoft.public.ae.arabic.sqlserver
news://msnews.microsoft.com/microsoft.public.arabic.sqlserver
news://msnews.microsoft.com/microsoft.public.de.sqlserver
news://msnews.microsoft.com/microsoft.public.il.hebrew.sqlserver
news://msnews.microsoft.com/microsoft.public.jp.sqlserver.server

Microsoft provides many white papers on SQL Server at the following address:

http://www.microsoft.com/sql/Index.htm

Go to Chapter 1 and start learning Microsoft SQL Server 2000 Programming by Example today!