Advanced Transact-SQL for SQL Server 2000

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".

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.