Advanced Transact-SQL for SQL Server 2000 de Itzik Ben-Gan

Advanced Transact-SQL for SQL Server 2000

Itzik Ben-Gan

Tom Moreau

en Limba Engleză Paperback – 28 noi 2000

In Advanced Transact-SQL for SQL Server 2000, authors Itzik Ben-Gan and Thomas Moreau explore the powerful capabilities of Transact-SQL (T-SQL). Ben-Gan and Moreau offer solutions to common problems encountered using all versions of SQL Server, with a focus on the latest version, SQL Server 2000.
Expert tips and real code examples teach advanced database programmers to write more efficient and better-performing code that takes full advantage of T-SQL. The authors offer practical solutions to the everyday problems programmers face and include in-depth information on advanced T-SQL topics such as joins, subqueries, stored procedures, triggers, user-defined functions (UDFs), indexed views, cascading actions, federated views, hierarchical structures, cursors, and more.

Citește tot Restrânge

Cuprins

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.

Notă biografică

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

Caracteristici

Provides hundreds of solutions to common problems encountered by SQL Server programmers Both authors write regularly for SQL Server Magazine and Pinnacle Publishing's Microsoft SQL Server Professional newsletter Includes expert tips and plenty of real code examples Presents differences between SQL Server 2000 and version 6.X and 7.0 Includes supplementary material: sn.pub/extras