Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET

Introduction

Figure 1-1: A database diagram of the Asset5 database

Chapter 1: The SQL Server 2005 Environment and Tools

Figure 1-1: SQL Server Configuration Manager

Figure 1-2: Network configuration in SQL Server Configuration Manager

Figure 1-3: SQL Server Management Studio

Figure 1-4: Object Explorer

Figure 1-5: Resizable, scriptable, nonmodal window

Figure 1-6: Script and project management in Management Studio

Figure 1-7: Template Explorer

Figure 1-8: SQL CMI command line utility

Figure 1-9: SQL Server Profiler

Figure 1-10: Connect to Server dialog box

Figure 1-11: Executing a stored procedure in Management Studio

Figure 1-12: A window for modifying a stored procedure

Figure 1-13: Creating new stored procedures

Figure 1-14: Execution of stored procedure with two result sets

Figure 1-15: Available databases

Figure 1-16: Example of syntax error

Figure 1-17: Viewing and rows in the Table window

Figure 1-18: A Query window for editing tables

Figure 1-19: Changing a table structure in Management Studio

Chapter 2: Stored Procedure Design Concept

Figure 2-1: Objects in sys.objects

Figure 2-2: Results of sp_depends

Chapter 3: Basic Transact-SQL Programming Constructs

Figure 3-1: Grouping objects using a schema in the Object Browser

Chapter 4: Functions

Figure 4-1: Using table-valued user-defined functions

Figure 4-2: Execution of ap_Terms_List

Figure 4-3: Using Unicode characters

Figure 4-4: Identifying Unicode character

Figure 4-5: Using OpenXML()

Chapter 5: Composite Transact-SQL Constructs—Batches, Scripts, and Transactions

Figure 5-1: Executing selected code in the Query window

Figure 5-2: Generate SQL Server Scripts Wizard

Figure 5-3: Script options

Figure 5-4: Generate Script Progress window

Figure 5-5: Complete failure of attempt to delete records

Chapter 6: Error Handling

Figure 6-1: Using Raiserror

Figure 6-2: Two connections after deadlock

Figure 6-3: Execution of stored procedures with loop for handling deadlock error

Chapter 7: Special Types of Stored Procedures

Figure 7-1: The system procedure works in the context of the current database.

Figure 7-2: Code of extended stored procedure

Figure 7-3: Using the extended stored procedure

Chapter 8: Views

Figure 8-1: View design in the Query window of Management Studio

Figure 8-2: Using INFORMATION_SCHEMA views

Figure 8-3: Federated servers

Figure 8-4: Usage of distributed partitioned view

Figure 8-5: Distributed partitioned view connects to tables on all member servers.

Figure 8-6: Distributed partitioned view routes the query to the remote server.

Figure 8-7: Data-dependent routing

Figure 8-8: Using util.vSpaceUsed

Chapter 9: Triggers

Figure 9-1: Managing triggers in Management Studio

Figure 9-2: Managing database-level DDL triggers

Figure 9-3: Trigger information from sp_helptrigger

Chapter 10: User-defined Functions

Figure 10-1: Limitation on use of built-in functions in user-defined functions

Figure 10-2: Editing user-defined functions

Chapter 11: Fundamentals of.NET Programming in SQL Server 2005

Figure 11-1: CLR assembly process

Figure 11-2: Creating a new database project in Visual Studio 2005

Figure 11-3: Templates of database objects

Figure 11-4: .NET Framework folder

Figure 11-5: Compiled DLL

Figure 11-6: Rowset of assemblies

Chapter 12: Fundamentals of CLR Stored Procedure Development

Figure 12-1: Stored procedure with two custom recordsets

Figure 12-2: Setting the permission level in Visual Studio 2005

Chapter 13: CLR Functions and Triggers

Figure 13-1: Adding a reference to a component of a database project

Chapter 14: CLR Database Objects Advanced Topics

Figure 14-1: User-defined types

Chapter 15: Advanced Stored Procedure Programming

Figure 15-1: The results of Query By Form

Figure 15-2: Use of timestamp data type

Figure 15-3: Percentage of log space used in specified database

Chapter 16: Debugging

Figure 16-1: A stored procedure in Debugging mode

Figure 16-2: SQLCLR debugging

Figure 16-3: Stepping into a procedure written in a different language

Figure 16-4: Result of execution of a stored procedure in "poor man's debugger mode"

Figure 16-5: Trace window

Chapter 17: Source Code Management

Figure 17-1: Visual SourceSafe Explorer

Figure 17-2: File differences in Visual SourceSafe Explorer

Figure 17-3: Database object scripts generated by TbDbScript

Chapter 18: Database Deployment

Figure 18-1: Detach Database in Management Studio

Figure 18-2: Attach Databases window in Management Studio

Figure 18-3: Deployment scripts

Figure 18-4: Content of DAT file

Figure 18-5: Command file for deploying Create scripts

Figure 18-6: Deployment script for incremental build

Chapter 19: Security

Figure 19-1: List of built-in permissions

Figure 19-2: Implying permissions

Figure 19-3: Stored procedures are accessible even when underlying objects are not.

Chapter 20: Stored Procedures for Web Search Engines

Figure 20-1: Surrogate index

Figure 20-2: Connections using snapshot isolation level before transaction completion

Figure 20-3: Connections using snapshot isolation level after transaction completion

Figure 20-4: Conflict of concurrent snapshot transactions

Chapter 21: Interaction with the SQL Server Environment

Figure 21-1: Using xp_cmdshell to run commands and programs

Figure 21-2: A COM object created in Visual Basic 6

Appendix B: Stored Procedure Compilation, Storage, and Reuse

Figure B-1: sys.objects

Figure B-2: sys.syscomments