Read an Excerpt
Preface
For years I've wanted a single reference book that contained the MicrosoftSQL Server SQL syntax for most of my common questions when writing SQLprograms. I've needed one book that had not only the specific syntax forMicrosoft SQL Server SQL, called Transact-SQL, with examples but also hadsome hints about the ANSI Standard SQL syntax. So when Mark Taub of PrenticeHall suggested that I write such a book, I was very receptive to the idea.This book has a lot in common with Microsoft SQL Server on-line documentationcalled "Books Online," but it has both more than and less than what's inBOL. Wording is changed in places in a hope to add clarity. It was omitted when afeature is very infrequently used. Also, many different examples are addedThis book is intended as a handy reference for SQL programmers needing toknow how to construct correct SQL statements that will run on Microsoft SQLServer 2000 in a way to maximize portability. Syntax and examples are givenusing Microsoft SQL Server 2000 SQL with comments when the syntax variesfrom ANSI SQL.
This book concentrates on Microsoft SQL Server 2000 implementation ofSQL features from the ANSI SQL-92 standard covering Data Definition Languageand Data Manipulation Language. These features are commonly referred to as4GL (fourth-generation language) constructs. The Microsoft SQL Server 2000implementation of SQL is called Transact-SQL (T-SQL). It encompasses boththese 4GL features plus 3GL programming features, which include IF-ELSE andlooping and other constructs.
Except for Chapter 1, which is provided in tutorial form as a quick RDBMSreview, and Appendix B, which covers some Advanced Queries, this material iswritten as areference, assuming previous exposure to SQL concepts. The book isintended to answer the question of "How exactly do I write a statement to do acertain job?" as well as "What is the full syntax with examples?"It is also essential that the reader refer to the documentation on Web sites and the knowledge base there.
Clearly this book contains much information derived from Microsoft SQLServer Books Online, an excellent resource and one which should be used frequently.This book contains less of some detail than the 1000-plus page BooksOnline but has more explanation of what I consider to be core features and moreexamples of useful applications of these features. Together this book and BooksOnline make a powerful combination.
Additionally, this book gives information about standard ANSI SQL and willcomment on which MSS SQL statements do not abide by the standard so that yourSQL code may maximize portability to other relational database management systems(RDBMSs).
Sometimes SQL Server has two correct ways of doing a job, one that abidesby the standard and is more portable and one that does not. For example, SQLServer has to declare column aliases and two forms of doing an inner join. In bothcases the ANSI standard form is just as efficient but more portable.There are occasions, however, where no ANSI standard approach exists inSQL Server, so it's useful to know that such code must be modified to transport itto another RDBMS. An example is the DATETIME data type in SQL Server thatmust be changed to DATE for use with other systems (except with Sybase, fromwhich SQL Server was derived).
This is not intended to be an exhaustive treatise on all the nooks and cranniesof either ANSI SQL or of Microsoft SQL Server 2000 Transact-SQL. It isintended to be a handy reference that covers the syntax of the SQL statementsmost of the time for most SQL users.
Happy databasing!How to Use this Book
This book was written as a reference for experienced SQL programmerswho will usually go directly to the Table of Contents or the Index and then to thesection of interest. Except for the Chapter 1 tutorial review, topics are organized inchapters by purpose and function, for example, DDL, DML, SELECT, and soforth.
All examples were tested on Microsoft SQL Server 2000.
Chapter 1 is a tutorial summary of relational database management systembackground, terminology and concepts to serve as a refresher. The reader who isout of practice on these subjects may find it useful to read or skim this chapterbefore proceeding. Otherwise Chapter 1 may be skipped.
The remaining chapters contain the reference material intended for directlookup of the subject sought.
- Chapter 1—RDBMS Concepts: A Primer
- Chapter 2—SQL Building Blocks and Server Settings
- Chapter 3—Data Definition Language and Security
- Chapter 4—Data Manipulation Language: INSERT, UPDATE, DELETE, SELECT
- Chapter 5—Transaction Control, Backup and Restore and Transact-SQL Programming
- Appendix A—Transact-SQL Keywords and System Stored Procedures
- Appendix B— SELECT: Advanced Queries