This is the SQL reference you'll reach for first, every time!
If you're an SQL programmer, you can easily picture how useful a comprehensive, single-volume Transact-SQL command reference manual would be in streamlining your work. And if that book was rich in examples, was written with users of all levels of expertise in mind, and was even designed to fit comfortably on your desktop, you'd probably buy it right away, wouldn't you? Well, look no further—Transact-SQL Desk Reference, by noted programming instructor Deac Lancaster, is exactly the book you need.
As it turns out, Transact-SQL Desk Reference is just the book Deac himself was looking for. The problem was, no such book existed, so he saved programmers everywhere a lot of trouble and he wrote it himself! The book answers the question, "How exactly do I write a statement to do a particular job?" by providing the full syntax of each command along with examples showing each command in action. Then, to assist you in making your code more portable, Transact-SQL Desk Reference shows where Transact-SQL diverges from the ANSI SQL standard. The commands are arranged by functional area and are well indexed and cross-referenced, making it as easy as possible to find what you're looking for-even if you don't know the command's name. Inside, you'll find:
This book is intended for programmers of all levels working with Microsoft SQL Server.
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.
Preface.
Acknowledgment.
1. RDBMS Concepts — A Primer.
Database Management System Background. Brief History of Relational DBMSs and SQL Language. SQL Language. Where Does SQL Fit In? Chronology of SQL Standards. SQL Server 2000 Product Line. Client-Server Architecture. Three-Tier Client-Server Architecture. RDBMS: Tables and Databases. Tables in a Database. Databases in Microsoft SQL Server. The SQL Language and MSS's Transact-SQL. Comments. Data Types — First Look. Domain and NULL Values. Metadata and Data. Data Definition Language (DDL) and Data Manipulation Language (DML). DDL Examples—CREATE TABLE, ALTER TABLE, DROP TABLE. DML Examples—INSERT, UPDATE, DELETE, SELECT. Case Sensitivity in SQL Statements. RDBMS Utility to Show Table Structure. Lab Exercises. Lab Exercise 1: Introduction to Using Microsoft SQL Server. Lab Exercise 2: CREATE TABLE. Lab Exercise 3.
2. SQL Building Blocks and Server Settings.
SQL Server Instance. The SQL Server Database. Data Representation. General Data Type Information. Domain and NULL Value. Constant (Literal) Defined. Identifiers—Naming SQL Objects. Using Identifiers as Qualified Object Names. Expressions in an SQL Statement. Operators. SQL Server 2K Data Types. Numeric Data Type Overview. String Data Type Overview. Other Data Type Overview. Transact-SQL Data Type Precedence. Transact-SQL Data Type Hierarchy. BIGINT, INT, SMALLINT and TINYINT Details. BIT Data Type Details. DECIMAL and NUMERIC Data Type Details. MONEY and SMALLMONEY Data Type Details. FLOAT and REAL Data Type Details. DATETIME and SMALLDATETIME Data Type Details. CHAR and VARCHAR Data Type Details. NCHAR and NVARCHAR Data Type Details. SYSNAME Data Type Details. TEXT, NTEXT and IMAGE Data Type. BINARY(n) and VARBINARY(n) Data Type Details. CURSOR Data Type Details. SQL_VARIANT Data Type Details. TABLE Data Type Details. TIMESTAMP (ROWVERSION) Data Type Details. UNIQUEIDENTIFIER Data Type Details. User-Defined Data Types. Enterprise Manager—Create and Manage a User-Defined Data Type. Transact-SQL—Create and Manage a User-Defined Data Type. Transact-SQL Functions. Aggregate Functions. Cursor Functions. Configuration Functions. Date and Time Functions. Mathematical Functions. Metadata Functions. Rowset Functions. Security Functions. String Functions — for CHAR or VARCHAR expressions. System Functions. System Statistical Functions. Text and Image Functions and Statements. Deterministic and Nondeterministic Functions. CASE Expression. CAST and CONVERT. System Stored Procedures and DBCC. System Stored Procedures. DBCC. Server, Database and Session Settings. Settings Overview. Database Configuration (Database Properties). Session (Connection) Configuration Settings. Default Nullability of New Columns in a Table. Collation.
3. Data Definition Language and Security.
Data Definition Language (DDL). Metadata and Data. Types of Objects Managed by DDL. sp_detach_db and sp_attach_db. CREATE, ALTER, DROP DATABASE. sp_bindefault and sp_unbindefault. CREATE, DROP DEFAULT. CREATE, ALTER, DROP FUNCTION. Query Analyzer Debugger Functions (UDF). CREATE, DROP INDEX. CREATE, ALTER, DROP PROCEDURE. Query Analyzer Stored Procedure DebuggerDebugger. CREATE RULE, DROP RULE, sp_bindrule. CREATE SCHEMA. CREATE, UPDATE, DROP STATISTICS. STATS_DATE. CREATE, ALTER, DROP TABLE. CREATE, ALTER, DROP TRIGGER. CREATE, ALTER, DROP VIEW. Indexed View. PARTITIONED VIEWS. Displaying Metadata. System Stored Procedures to Display Metadata. ANSI Information Schema Views. Metadata Functions. SELECT statements on the system tables. Using Enterprise Manager to Observe Metadata. Security. Client Program Login to SQL Server. SQL Server Authentication Mode Options. Database Level Access. Roles. Creating Logins and Roles. Chapter Lab Exercises. Lab Exercise 1: CREATE TABLE emps3, depts3.
4. Data Manipulation Language—INSERT, UPDATE, DELETE, SELECT.
INSERT. INSERT Syntax. INSERT and SELECT with an IDENTITY Column. UPDATE. UPDATE—Basic Syntax. UPDATE—Complete Syntax. DELETE. DELETE Syntax. TRUNCATE TABLE. TRUNCATE TABLE Syntax. SELECT. SELECT Statement Syntax. Column Aliases. SET Operations. SET Operations — UNION, UNION ALL. SET Operations — INTERSECTION. SET Operations — SET DIFFERENCE. Hints—Join Hints, Query Hints and Table Hints.
5. Transaction Control, Backup and Restore and Transact-SQL Programming.
Transaction Control—ANSI SQL and MSS. Transaction Concepts. Starting and Ending a Transaction. ISOLATION Is Obtained through Locking. Automatic (Implicit) Transaction Mechanisms. Explicit Transaction Mechanisms. Transaction Control: Isolation Levels. Defining Isolation Levels. Implementation of the Isolation Levels Using Locks (MSS). Setting Isolation Level. Server Shutdown. MSS 2000. MSS 7. @@version. Identifying Version and Service Pack. BACKUP AND RESTORE. File System Backup vs Database Backup. Database Backup. Types of SQL Server Database Backup. Transaction Log Backup (TLBU). MSS 2000 Recovery Models. BACKUP DATABASE, BACKUP LOG. BACKUP DATABASE. BACKUP LOG. Truncate Log—Emptying the Transaction Log. BACKUP—1. Backup Dialog. RESTORE DATABASE. RESTORE LOG. Restore Dialog. RESTORE Command. Restore Using Example Backup Strategy. Comments about Backup and Restore. Database Layout for Performance and Restorability. RAID. Production Database—Suggested Disk Layout 1. Production Database—Suggested Disk Layout 2. Introduction to Transact-SQL Programming. Batches. EXEC[UTE]. Two forms of SQL Comments. PRINT. Variables. Program Flow Control. BEGIN - END. RETURN. IF-ELSE. WHILE, BREAK, CONTINUE. GOTO and Labels. WAITFOR. Example TRANSACT-SQL Stored Procedure. Error Handling. Managing Error Messages. sp_addmessage, sp_altermessage, sp_dropmessage. @@ERROR. RAISERROR. Cursors. SQL-92 Cursor. Transact-SQL Extended Cursor (T-SQL Cursor). Commands Used with Cursors. Cursor Functions and Stored Procedures. Dynamic SQL.
Appendix A.
Appendix B.
Bibliography.
Index.
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 a reference, 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!
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.
Anonymous
Posted Fri Nov 28 00:00:00 EST 2003
You know some SQL or you want to learn it? Otherwise why are you pondering this book and this review? But suppose you have never used Microsoft SQL Server. The title may then be puzzling. You see, Transact-SQL is Microsoft's name for the 'Microsoft SQL Server SQL' [sic]. The latter quoted phrase is actually logically correct. It parses as Microsoft's extension of SQL2 or SQL3 that is implemented by its SQL Server. But the phrase looks ridiculous. Hence Transact-SQL. Like their database competitors, IBM and Oracle, Microsoft supplies online documentation and hardcopy. But the sheer complexity of all the SQL options and the proprietary extensions, plus the undoubted opacity of the official documentation has spawned an independent sideline of books that provide alternative and presumably clearer views of the databases. Ideally, therefore, this book should not exist! It assumes you already know the theory of SQL and relational databases. But that at times you will be casting around for the precise syntax of a command. So the book emphasises quick easy lookup of commands. Numerous examples are provided in bold font, to help you quickly get there. By contrast, the full syntax of a query is written in regular font, in BNF. [So you need to know BNF, but that is easy.] This may differ from some texts, where the full syntax is visually emphasised instead of its examples. Logically, the latter formulation makes sense. But the pragmatic pedagogy here is actually often more useful. Most of us will understand a few examples far quicker than perusing the abstraction of a full BNF description of a command. A simple display decision. But you may find that this is a nice usability feature that gives the book utility to you.
Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Overview
This is the SQL reference you'll reach for first, every time!
If you're an SQL programmer, you can easily picture how useful a comprehensive, single-volume Transact-SQL command reference manual would be in streamlining your work. And if that book was rich in examples, was written with users of all levels of expertise in mind, and was even designed to fit comfortably on your desktop, you'd probably buy it right away, wouldn't you? Well, look no further—Transact-SQL Desk ...