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

ISBN-10: 0072262281
ISBN-13: 9780072262285
Edition: 3rd 2006 (Revised)
Authors: Dejan Sunderic
List price: $62.00 Buy it from $3.00
eBook available
This item qualifies for FREE shipping

*A minimum purchase of $35 is required. Shipping is provided via FedEx SmartPost® and FedEx Express Saver®. Average delivery time is 1 – 5 business days, but is not guaranteed in that timeframe. Also allow 1 - 2 days for processing. Free shipping is eligible only in the continental United States and excludes Hawaii, Alaska and Puerto Rico. FedEx service marks used by permission."Marketplace" orders are not eligible for free or discounted shipping.

30 day, 100% satisfaction guarantee

If an item you ordered from TextbookRush does not meet your expectations due to an error on our part, simply fill out a return request and then return it by mail within 30 days of ordering it for a full refund of item cost.

Learn more about our returns policy

Used Starting from $28.24
New Starting from $44.57
eBooks Starting from $62.00
Buy
what's this?
Rush Rewards U
Members Receive:
coins
coins
You have reached 400 XP and carrot coins. That is the daily max!
You could win $10,000

Get an entry for every item you buy, rent, or sell.

Study Briefs

Limited time offer: Get the first one free! (?)

All the information you need in one place! Each Study Brief is a summary of one specific subject; facts, figures, and explanations to help you learn faster.

Add to cart
Study Briefs
SQL Online content $4.95 $1.99
Add to cart
Study Briefs
MS Excel® 2010 Online content $4.95 $1.99
Add to cart
Study Briefs
MS Word® 2010 Online content $4.95 $1.99
Add to cart
Study Briefs
MS PowerPoint® 2010 Online content $4.95 $1.99

Customers also bought

Loading
Loading
Loading
Loading
Loading
Loading
Loading
Loading
Loading
Loading

Book details

List price: $62.00
Edition: 3rd
Copyright year: 2006
Publisher: McGraw-Hill Education
Publication date: 6/8/2006
Binding: Paperback
Pages: 664
Size: 7.00" wide x 9.00" long x 1.50" tall
Weight: 2.464
Language: English

Dejan Sunderic (Toronto, Ontario) MCSD is the Principal Consultant at Sage Information Consultants, Inc., a consulting company based in Toronto specializing in development and infrastructure solutions.

Acknowledgments
Introduction
The SQL Server 2005 Environment and Tools
SQL Server 2005 Tools
SQL Server Configuration Manager
SQL Server Management Studio
SQLCMD Utility
SQL Server Profiler
The Help Subsystem and SQL Server Books OnLine
Basic Operations in Management Studio
What Are Stored Procedures?
Execution of Stored Procedures in Management Studio
Editing Stored Procedures
Creating Stored Procedures
Editing Stored Procedures in the Query Window
Syntax Errors
Viewing and Editing Tables
Changing Table Structure
Stored Procedure Design Concepts
Anatomy of a Stored Procedure
Composition
Functionality
Syntax
Types of Stored Procedures
Managing Stored Procedures
Listing Stored Procedures
Viewing Code of Stored Procedures
Renaming Stored Procedures
Deleting Stored Procedures
Listing Dependent and Depending Objects
The Role of Stored Procedures in the Development of Database Applications
Enforcement of Data Integrity
Consistent Implementation of Complex Business Rules and Constraints
Modular Design
Maintainability
Reduced Network Traffic
Faster Execution
Enforcement of Security
Basic Transact-SQL Programming Constructs
T-SQL Identifiers
Database Object Qualifiers
Data Types
Character Strings
Unicode Character Strings
Date and Time Data Types
Integer Numbers
Approximate Numbers
Exact Numbers
Monetary Data Types
Binary Data Types
Special Data Types
Transact-SQL User-defined Data Types
Variables
Local Variables
Global Variables
Table Variables
Flow-control Statements
Comments
Statement Blocks: Begin...End
Conditional Execution: The If Statement
Looping: The While Statement
Unconditional Execution: The GoTo Statement
Scheduled Execution: The WaitFor Statement
Cursors
Transact-SQL Cursors
Cursor-related Statements and Functions
Problems with Cursors
The Justified Uses of Cursors
Functions
Using Functions
In Selection and Assignment
As Part of the Selection Criteria
In Expressions
As Check and Default Constraints
Instead of Tables
Types of Functions
Scalar Functions
Rowset Functions
Composite Transact-SQL Constructs: Batches, Scripts, and Transactions
Batches
Using Batches
Batches and Errors
DDL Batches
Self-sufficient Content
Scripts
Database Scripting
Trasactions
Autocommit Transactions
Explicit Transactions
Implicit Transactions
Transaction Processing Architecture
Nested Transactions
Named Transactions
Savepoints
Locking
Distributed Transactions
Typical Transaction-related Problems
Error Handling
Raiserror()
Using Error Handling
Why Bother?
Error Handling Based on @@Error
Error Handling Architecture: Based on @@Error
Try - Catch Statement
What Errors Are Trapped
Functions of the Catch Block
Try - Catch Statement with Explicit Transactions
Deadlocks
Deadlock Retries
Try - Catch Statement Nesting
Error Handling Architecture: With Try - Catch Statements
Xact_Abort
Error Handling Architecture: Based on Set Xact_Abort On
Error Handling Architecture: Xact_Abort + No Transaction Nesting
Special Types of Stored Procedures
User-defined Stored Procedures
System Stored Procedures
CLR Stored Procedures
Extended Stored Procedures
Design of Extended Stored Procedures
Registering the Extended Stored Procedure
Temporary Stored Procedures
Global Temporary Stored Procedures
Remote Stored Procedures
Views
Design of Standard SQL Views
Syntax
Design View in Enterprise Manager
Security
Standard SQL Views in Execution Plans
Limitations of Views
Editing Data Using Views
Dynamic Views
Temporary Views-Common Table Expressions
Limitations of Nonrecursive CTEs
Recursive CTEs
Information_Schema Views
Indexed Views
Indexed View Limitations
Indexed Views in Execution Plans
Nonclustered Indexes on Views
Performance Implications
Partitioned Views
Horizontal and Vertical Partitioning
Distributed Partitioned Views
Execution Plans of Distributed Partitioned Views
Updateable Distributed Partitioned Views
Scalability and Performance of Distributed Systems
A Poor Man's Federated Server
Using SQL Views
Export and Import
Security Implementation
Reduce Complexity
Performance Improvement
Triggers
DML Triggers
Physical Design of After Triggers
Inserted and Deleted Virtual Tables
What Triggers a Trigger?
Full Syntax of After Triggers
Handling Changes on Multiple Records
Nested and Recursive Triggers
After Trigger Restrictions
Instead-of Triggers
Triggers on Views
DML Trigger Order of Execution
DDL Triggers
Scope and Events of DDL Triggers
EventData()
Syntax of DDL Triggers
Managing Triggers
Managing DML Triggers in Management Studio
Managing DDL Triggers from Management Studio
Managing Triggers Using Transact-SQL Statements
Trigger Design Recommendations
Go Out ASAP
Make It Simple
Divide and Conquer
Do Not Use Select and Print Inside a Trigger
Do Not Use Triggers at All
Transaction Management in Triggers
Using Triggers
Cascading Deletes
Aggregates
Enforce Schema Integrity Among Objects on Different Servers or Databases
User-defined Functions
Design of Scalar User-defined Functions
Side Effects
Use of Built-in Functions
Encryption
Schema-binding
Table-valued User-defined Functions
Inline Table-valued User-defined Functions
Managing User-defined Functions in Management Studio
Fundamentals of .NET Programming in SQL Server 2005
Unmanaged vs. Managed Code
Common Language Runtime (CLR)
CLR Integration in SQL Server 2005
Pros and Cons
Supported and Not Supported
Minimally Required Namespaces
Development, Deployment, and Usage of CLR Database Objects in Visual Studio 2005
Development and Deployment of CLR Database Objects Without Visual Studio 2005
Develop Code
Compile Program
Load Assembly to SQL Server Database
Create SQLCLR Database Objects
Cataloging Assemblies and CLR Database Objects
Cataloging Assemblies
Cataloging Procedures
Version of .NET Framework
Fundamentals of CLR Stored Procedure Development
Structure of a CLR Stored Procedure
Database Access from CLR Procedures
CLR Data Types Mapping
Basic Operations with Stored Procedures
Returning Value
Connection Context
Returning a Result
Returning Custom Records
Parameters in CLR Stored Procedures
Operations with Regular ADO.NET Objects
SqlCommand
SqlDataReader
Managing LOB Parameters
CLR Functions and Triggers
CLR Functions
Scalar-valued CLR Functions
SqlFunction Attribute
Table-valued CLR Functions
CLR Triggers
Supported Features
Creation of DML Triggers
Creation of DDL Triggers
CLR Database Objects Advanced Topics
CLR User-defined Types
Structure of Managed UDT
Deploying CLR UDT Without Visual Studio 2005
Using CLR User-defined Types
Cross-database CLR User-defined Types
To UDT or Not to UDT
User-defined Aggregate CLR Functions
Structure of Managed Aggregates
Transactions in CLR Code
Using TransactionScope Class
Transaction Options
Distributed Transactions
Explicit Transactions
Benefits of New Transaction Paradigm
To CLR or Not to CLR (Design and Performance Considerations)
Don't Say I Didn't Warn You
Advanced Stored Procedure Programming
Dynamically Constructed Queries
Executing a String
Query by Form
Data Script Generator
Using the sp_executesql Stored Procedure
Security Implications
Optimistic Locking Using timestamp Values
timestamp
TSEqual() Function
Full-text Search and Indexes
Nested Stored Procedures
Using Temporary Tables to Pass a Recordset to a Nested Stored Procedure
Using a Cursor to Pass a Recordset to a Nested Stored Procedure
How to Process the Result Set of a Stored Procedure
Using Identity Values
A Standard Problem and Solution
Identity Values and Triggers
GUIDs
A While Loop with Min() or Max() Functions
Looping with sp_MSForEachTable and sp_MSForEachDb
Property Management
Debugging
What Is a "Bug"?
The Debugging Process
Identification
Resolution
Debugging Tools and Techniques
Transact-SQL Debugger in Visual Studio 2005
Debugging CLR Database Objects
Poor Man's Debugger
Execution in the Production Environment
Nested Stored Procedures
Output Clause
SQL Profiler
Using SQL Profiler
Typical Errors
Handling Null
Assignment of Variable from the Result Set
No Records Affected
Wrong Size or Data Type
Default Length
Rollback of Triggers
Warnings and Lower-priority Errors
Return Codes vs. Raiserror
Nested Comments
Deferred Name Resolution
Cursors
Overconfidence
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
History
Labels and Versions
Adding Database Objects to Visual SourceSafe: Traditional Approach
Creating Scripts Using Script Wizard
Creating Scripts Using TbDbScript
Putting Scripts to Visual SourceSafe Using TbDir2Vss.vbs
Database Deployment
Deployment of a Complete Database: Traditional Approach
Detach and Reattach the Database in Transact-SQL
Attach and Detach in Management Studio
Backup and Restore
Potential Problems
Deployment of Individual Objects
Deployment Scripts: Traditional Approach
Scripting Data: Traditional Approach
Scripting Data in Visual Studio .NET
Deploying Scripts: Traditional Approach
Deploying Create Scripts in Visual Studio .NET
Incremental Build: Traditional Approach
Incremental Build in Visual Studio .NET
Security
Security Architecture
Authentication and Authorization
Principals
Securables
Access Levels
Roles
Ownership Chains
Cross-database Ownership Chains
Switching of Execution Context
Implementing Security
Selection of Authentication Mode
Managing Logins
Granting Database Access
Assigning Permissions
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
Stored Procedures for Web Search Engines
Characteristics of the Environment
A Simple Solution...
... and Its Disadvantages
Available Solutions
Result Splitting
Quick Queries
Advanced Queries
Fancy New Solution: Row Versioning
Interaction with the SQL Server Environment
Running Programs
Running Windows Script Files
Execution of OLE Automation/COM Objects
Data Type Conversion
Running SQL Server 2005 Integration Services (SSIS) Packages
Running DTS Packages
Running/Looping Through DTS Packages
Interacting with the NT Registry
xp_regread
xp_regwrite
Jobs
Administration of Jobs
An Alternative to Job Scheduler
Stored Procedures for Maintaining Jobs
Operators and Alerts
E-mail
Database Mail
Configuring Database Mail
Sending E-mails
Check Status
Exposing Stored Procedures As Web Services
Endpoints
Naming Conventions
Why Bother?
Naming Objects and Variables
Entity Description
Name Length
Abbreviations
Name Formatting
Suggested Convention
Variables
Database Objects
Triggers
Stored Procedures
Stored Procedure Compilation, Storage, and Reuse
The Compilation and Execution Process
Parsing
Get Statistics
Compilation
Execution
Reuse of Execution Plans
Levels of Execution Plans
Caching Levels
Reuse of Query Execution Plans
Parameterized Queries
Autoparameterization
Reuse of Stored Procedure Execution Plans
Recompiling Stored Procedures
Storing Stored Procedures
Data Types in SQL Server 2005
Index

×
Free shipping on orders over $35*

*A minimum purchase of $35 is required. Shipping is provided via FedEx SmartPost® and FedEx Express Saver®. Average delivery time is 1 – 5 business days, but is not guaranteed in that timeframe. Also allow 1 - 2 days for processing. Free shipping is eligible only in the continental United States and excludes Hawaii, Alaska and Puerto Rico. FedEx service marks used by permission."Marketplace" orders are not eligible for free or discounted shipping.

Learn more about the TextbookRush Marketplace.

×