Advanced Transact-SQL for SQL Server 2000

by Itzik Ben-Gan, Tom Moreau

Overview

Both authors regularly write articles for the two most important magazines in the SQL Server market: SQL Server Magazine and Microsoft SQL Server Professional. The authors paid special attention to practical needs and problems raised in SQL Server programming newsgroups. Numerous examples are provided throughtout each chapter. Puzzles are presented through the book to encourage the reader to apply skills learned to difficult problems.

Product Details

ISBN-13:	9781893115828
Publisher:	Apress
Publication date:	11/28/2000
Edition description:	First Edition
Pages:	864
Product dimensions:	7.52(w) x 9.25(h) x 0.10(d)

About the Author

Itzik Ben-Gan is a senior SQL Server instructor and consultant with 10 years of experience in networking, development, databases, and data warehousing. He is a popular columnist and contributing editor for SQL Server Magazine. Highly certified, he is also a Microsoft Certified Trainer (MCT), a Microsoft Certified Systems Engineer + Internet (MCSE+1), a Microsoft Certified Solution Developer (MCSD), a Microsoft Certified Database Administrator (MCDBA), and a Microsoft SQL Server "Most Valuable Professional".

Table of Contents

1 Joins in T-SQL.- 2 Subqueries and Derived Tables.- 3 Populating Tables.- 4 Other Data Manipulation Issues.- 5 Summarizing Data.- 6 Special Datatypes and Properties.- 7 Writing Code in Transact-SQL.- 8 Views.- 9 Stored Procedures.- 10 Triggers—the Hidden Stored Procedures.- 11 User-Defined Functions.- 12 Temporary Tables.- 13 Horizontally Partitioned Views.- 14 Implementing Referential Integrity and Cascading Actions.- 15 Server-Side Cursors—the SQL of Last Resort.- 16 Expanding Hierarchies.- 17 Tips and Tricks.- SQL Puzzle Solutions.- Appendix A DML Basics.- Using the SELECT Statement.- The SELECT Clause.- The INTO Clause.- The FROM Clause.- The WHERE Clause.- The GROUP BY Clause.- The HAVING Clause.- The ORDER BY Clause.- Using the INSERT Statement.- The INSERT Clause.- The Column List.- The VALUES Clause.- The Derived Table Clause.- The Execute Clause.- The DEFAULT VALUES Clause.- Using the UPDATE Statement.- The UPDATE Clause.- The SET Clause.- The FROM Clause.- The WHERE Clause.- Using the DELETE Statement.- The DELETE Clause.- The FROM Clause.- The WHERE Clause.- Appendix B Checking for ANSI Compatibility with SET FIPS_FLAGGER.- Appendix C Analyzing Query Performance.- Tools.- System Monitor (Performance Monitor).- Profiler.- SQL Enterprise Manager.- Query Analyzer.- DBCC.- System Stored Procedures.- SET Options.- Methodology.- Is It a SQL Server Problem?.- Which Query Is Slow?.- Is the Query Blocked or Deadlocked?.- What Objects Does the Query Use?.- Are All Objects Actually Being Used?.- How Does the Optimizer Access the Objects?.- How Much Data Is Being Accessed?.- Will an Index Help or Hinder?.- Are Loops and Cursors Necessary?.- Volume and Load Testing.- Appendix D Ownership Chains.- Granting Permissions.- Guidelines for Granting Permissions.- Ownershipof Objects in the Chain.- Use of Statements in Stored Procedures.- Dynamic Execution.- Object References.- Appendix E Pubs and Northwind Database Schemas.- Appendix F Dynamically Creating Triggers for Cascade Actions.- Creating a Delete Cascade Trigger on the Primary (Referenced) Table.- Creating a Prevent Delete Trigger on the Primary (Referenced) Table.- Creating an Update Cascade Trigger on the Primary (Referenced) Table.- Creating a Prevent Update Trigger on the Primary (Referenced) Table.- Creating a Prevent Insert or Update Trigger on the Secondary (Referencing) Table.- Encapsulating the Logic.- Testing the sp_CreateRelationship Stored Procedure.- Appendix G References.- Articles.- SQL Server Magazine (http://www.sglmag.com).- SQL Server Professional (http://www.pinnaclepublishing.com/SQ).- White Papers.- Books.