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

ISBN: 0072262281
EAN: 2147483647

Year: 2006
Pages: 165

Authors: Dejan Sunderic

BUY ON AMAZON

Table of Contents
Back Cover
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL .NET, Third Edition
Introduction
Who Should Read This Book
What You Will Find in This Book
Requirements
Sample Database and Other Resources
Chapter 1: The SQL Server 2005 Environment and Tools
SQL Server 2005 Tools
Basic Operations in Management Studio
Chapter 2: Stored Procedure Design Concept
Anatomy of a Stored Procedure
Types of Stored Procedures
Managing Stored Procedures
The Role of Stored Procedures in the Development of Database Applications
Chapter 3: Basic Transact-SQL Programming Constructs
Database Object Qualifiers
Data Types
Variables
Flow-control Statements
Cursors
Chapter 4: Functions
Types of Functions
Chapter 5: Composite Transact-SQL ConstructsBatches, Scripts, and Transactions
Batches
Scripts
Transactions
Chapter 6: Error Handling
Using Error Handling
Why Bother?
Error Handling Based on Error
Try-Catch Statement
Xact_Abort
Chapter 7: Special Types of Stored Procedures
User-defined Stored Procedures
System Stored Procedures
CLR Stored Procedures
Extended Stored Procedures
Temporary Stored Procedures
Global Temporary Stored Procedures
Remote Stored Procedures
Chapter 8: Views
Design of Standard SQL Views
Dynamic Views
Temporary ViewsCommon Table Expressions
INFORMATION_SCHEMA Views
Indexed Views
Partitioned Views
Using SQL Views
Chapter 9: Triggers
DDL Triggers
Managing Triggers
Trigger Design Recommendations
Transaction Management in Triggers
Using Triggers
Chapter 10: User-defined Functions
Table-valued User-defined Functions
Managing User-defined Functions in Management Studio
Chapter 11: Fundamentals of.NET Programming in SQL Server 2005
Unmanaged vs. Managed Code
Common Language Runtime (CLR)
CLR Integration in SQL Server 2005
Development, Deployment, and Usage of CLR Database Objects in Visual Studio 2005
Development and Deployment of CLR Database Objects Without Visual Studio 2005
Cataloging Assemblies and CLR Database Objects
Chapter 12: Fundamentals of CLR Stored Procedure Development
Structure of a CLR Stored Procedure
Database Access from CLR Procedures
Basic Operations with Stored Procedures
Operations with Regular ADO.NET Objects
Managing LOB Parameters
Chapter 13: CLR Functions and Triggers
CLR Triggers
Chapter 14: CLR Database Objects Advanced Topics
To UDT or Not to UDT
User-defined Aggregate CLR Functions
Transactions in CLR Code
To CLR or Not to CLR (Design and Performance Considerations)
Don t Say I Didn t Warn You
Chapter 15: Advanced Stored Procedure Programming
Dynamically Constructed Queries
Optimistic Locking Using time stamp Values
Full-text Search and Indexes
Nested Stored Procedures
Using Identity Values
GUIDs
A While Loop with Min() or Max() Functions
Looping with sp_MSForEachTable and sp_MSForEachDb
Property Management
Chapter 16: Debugging
The Debugging Process
Debugging Tools and Techniques
Debugging CLR Database Objects
Poor Man s Debugger
SQL Profiler
Typical Errors
Chapter 17: Source Code Management
Introduction to Microsoft Visual SourceSafe
Administering the Visual SourceSafe Database
Adding Database Objects to Visual SourceSafe in Visual Studio .NET
Managing Create Scripts in Visual Studio .NET
Visual SourceSafe Explorer
Adding Database Objects to Visual SourceSafe: Traditional Approach
Chapter 18: Database Deployment
Deployment of a Complete Database: Traditional Approach
Deployment of Individual Objects
Chapter 19: Security
Implementing Security
Synchronization of Login and Usernames
Managing Application Security Using Stored Procedures, User-defined Functions, and Views
Managing Application Security Using a Proxy User
Managing Application Security Using Application Roles
Chapter 20: Stored Procedures for Web Search Engines
A Simple Solution
and Its Disadvantages
Available Solutions
Chapter 21: Interaction with the SQL Server Environment
Running Programs
Running Windows Script Files
Execution of OLE AutomationCOM Objects
Running SQL Server 2005 Integration Services (SSIS) Packages
Running DTS Packages
RunningLooping Through DTS Packages
Interacting with the NT Registry
Jobs
E-mail
Exposing Stored Procedures As Web Services
Appendix A: Naming Conventions
Naming Objects and Variables
Suggested Convention
Appendix B: Stored Procedure Compilation, Storage, and Reuse
Reuse of Execution Plans
Recompiling Stored Procedures
Storing Stored Procedures
Appendix C: Data Types in SQL Server 2005
Index
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X-Z
List of Figures
List of Tables
List of Sidebars