What You Will Find in This Book

Each chapter in this book will provide conceptual grounding in a specific area of the SQL Server development landscape. The first ten chapters are dedicated to Transact-SQL stored procedure programming. Chapters 11 to 14 cover development of stored procedures (and other database objects) in .NET languages such as Visual Basic .NET and C#. The rest of the book is focused on advanced topics such as security, debugging, deployment, performance issues, and the interaction of Transact-SQL code with environments.

Chapter 1, "The SQL Server 2005 Environment and Tools", is a quick introduction to tools that are part of SQL Server 2005 and that will be used through the rest of the chapters.

Chapter 2, "Stored Procedure Design Concepts", explores Transact-SQL stored procedure design in greater detail, with particular attention paid to the different types of stored procedures, their uses, and their functionality.

Chapter 3, "Basic Transact-SQL Programming Constructs", describes Transact-SQL, the ANSI SQL-92-compliant programming language used to write scripts in SQL Server. This chapter summarizes data types, variables, flow control statements, and cursors in the context of SQL Server 2005.

Chapter 4, "Functions", describes the extensive set of built-in functions available in SQL Server 2005 and how to use them in various common situations.

Chapter 5, "Composite Transact-SQL Constructs: Batches, Scripts, and Transactions", describes the various ways in which you can group Transact-SQL statements for execution.

Chapter 6, "Error Handling", provides a coherent strategy for handling errors as they occur.

Chapter 7, "Special Types of Stored Procedures", describes user-defined, system, extended, temporary, global temporary, and remote stored procedures.

Chapter 8, "Views", presents all types of views—standard SQL views, indexed views, INFORMATION_SCHEMA views, and local and distributed partitioned views.

Chapter 9, "Triggers", presents Transact-SQL triggers—After triggers and Instead-of triggers.

Chapter 10, "User-defined Functions", describes the design and use of Transact-SQL scalar and table-valued user-defined functions.

Chapter 11, "Fundamentals of .NET Programming in SQL Server 2005", introduces architecture, concepts, and techniques for coding SQL Server 2005 programmatic database object in .NET-compatible programming languages.

Chapter 12, "Fundamentals of CLR Stored Procedure Development", focuses on the development and management of stored procedures in C# and Visual Basic .NET and usage of ADO.NET to implement database access.

Chapter 13, "CLR Functions and Triggers", focuses on the development and management of managed user-defined functions and triggers in C# and Visual Basic .NET.

Chapter 14, "CLR Database Objects Advanced Topics", starts with the development and management of managed user-defined types and managed aggregate functions.

Then it covers implementing transactions in all types of CLR database objects. The end of the chapter is dedicated to architectural guidelines for justifiable uses of managed database objects.

Chapter 15, "Advanced Stored Procedure Programming", introduces some advanced techniques for coding stored procedures, such as dynamically constructed queries, optimistic locking using timestamps, and nested stored procedures.

Chapter 16, "Debugging", presents tools and methods for debugging both Transact-SQL and .NET database objects in Visual Studio .NET and SQL Server Management Studio.

Chapter 17, "Source Code Management", demonstrates how you can manage Transact-SQL source code in a repository such as Visual SourceSafe.

Chapter 18, "Database Deployment", demonstrates how you can manage and deploy Transact-SQL source code from development to the test and production environments. It explains and demonstrates two alternative approaches—one using Visual Studio .NET and the other, more traditional, using scripts developed in Transact-SQL, VBScript, or .NET.

Chapter 19, "Security", starts with a description of security-related features and concepts in SQL Server 2005. It later describes typical security architectures and implementation on SQL Server.

Chapter 20, "Stored Procedures for Web Search Engines", presents an example of how to use stored procedures in a web application that queries the database system. Several optimization techniques are used to avoid typical design problems and improve the performance.

Chapter 21, "Interaction with the SQL Server Environment", focuses on the ways in which you can use system stored procedures, functions, and commands to interact with the SQL Server environment, and also discusses the ways in which user-defined stored procedures can help you leverage the existing functionality of various elements within the SQL Server environment.

Appendix A, "Naming Conventions", provides you with a suggested naming convention for database objects that you can use on SQL Server projects.

Appendix B, "Stored Procedure Compilation, Storage, and Reuse", explains how to manage and process Transact-SQL stored procedures in SQL Server.

Appendix C, "Data Types in SQL Server 2005", provides you with a list of data types in use in SQL Server 2005, their sizes, ranges, and sample constants.