Home Page Icon
Home Page
Table of Contents for
Cover
Close
Cover
by Dejan Sarka, Lubor Kollar, Itzik Ben-Gan
Inside Microsoft® SQL Server™ 2005
Inside Microsoft® SQL Server™ 2005: T-SQL Programming
A Note Regarding Supplemental Files
Foreword
Preface
Acknowledgments
Introduction
Organization of This Book
System Requirements
Installing Sample Databases
Updates
Code Samples
Support for This Book
1. Datatype-Related Problems, XML, and CLR UDTs
DATETIME Datatypes
Storage Format of DATETIME
Datetime Manipulation
Literals
Rounding Issues
DATETIME Functions
No Separation Between Date and Time
Datetime-Related Querying Problems
The Birthday Problem
Overlaps
Identifying Overlaps
Grouping Overlaps
Maximum Number of Overlapping Sessions (Set-Based Solution)
Identifying Weekday
Grouping by the Week
ISO Week
Working Days
Generating a Series of Dates
Character-Related Problems
Pattern Matching
Case-Sensitive Filters
Large Objects
MAX Specifier
BULK Rowset Provider
Implicit Conversions
Scalar Expressions
Filter Expressions
CLR-Based User-Defined Types
Theoretical Introduction to UDTs
Domains and Relations
Relations and Classes
Domains and Classes
Complex Domains
Why Do We Need Complex Classes?
Language for Creating UDTs
Programming a UDT
UDT Requirements
Creating a UDT
Deploying the UDT Using T-SQL
XML Data Type
XML Support in a Relational Database
Is the Relational Model Obsolete?
When Should You Use XML Instead of Relational Representation?
XML Serialized Objects in a Database
Using XML with Open Schema
XML Data Type as a Parameter of a Stored Procedure
XQuery Modification Statements
Conclusion
2. Temporary Tables and Table Variables
Temporary Tables
Local Temporary Tables
tempdb
Scope and Visibility
Transaction Context
Statistics
Temporary Table Name Resolution
Schema Changes to Temporary Tables in Dynamic Batches
Global Temporary Tables
Table Variables
Limitations
tempdb
Scope and Visibility
Transaction Context
Statistics
tempdb Considerations
Table Expressions
Comparison Summary
Summary Exercise—Relational Division
Conclusion
3. Cursors
Using Cursors
Cursor Overhead
Dealing with Each Row Individually
Order-Based Access
Custom Aggregates
Running Aggregations
Maximum Concurrent Sessions
Matching Problems
Conclusion
4. Dynamic SQL
EXEC
A Simple EXEC Example
EXEC Has No Interface
Concatenating Variables
EXEC AT
sp_executesql
The sp_executesql Interface
Statement Limit
Environmental Settings
Uses of Dynamic SQL
Dynamic Maintenance Activities
Storing Computations
Dynamic Filters
Dynamic PIVOT/UNPIVOT
SQL Injection
SQL Injection: Code Constructed Dynamically at Client
SQL Injection: Code Constructed Dynamically at Server
Protecting Against SQL Injection
Conclusion
5. Views
What Are Views?
ORDER BY in a View
Refreshing Views
Modular Approach
Updating Views
View Options
ENCRYPTION
SCHEMABINDING
CHECK OPTION
VIEW_METADATA
Indexed Views
Conclusion
6. User-Defined Functions
Some Facts About UDFs
Scalar UDFs
T-SQL Scalar UDFs
Performance Issues
UDFs Used in Constraints
DEFAULT Constraints
CHECK Constraints
PRIMARY KEY and UNIQUE Constraints
CLR Scalar UDFs
CLR Routines
Regular Expressions
Explicit vs. Implicit Conversions
SQL Signature
T-SQL SQL Signature UDF
CLR SQL Signature UDF
Compare Performance of T-SQL and CLR SQL Signature UDFs
Table-Valued UDFs
Inline Table-Valued UDFs
Split Array
T-SQL Split UDF
CLR Split UDF
Compare Performance of T-SQL and CLR Split
Multistatement Table-Valued UDFs
Per-Row UDFs
Conclusion
7. Stored Procedures
Types of Stored Procedures
User-Defined Stored Procedures
Special Stored Procedures
System Stored Procedures
Other Types of Stored Procedures
The Stored Procedure Interface
Input Parameters
Output Parameters
Resolution
Compilations, Recompilations, and Reuse of Execution Plans
Reuse of Execution Plans
Recompilations
Parameter Sniffing Problem
EXECUTE AS
Parameterizing Sort Order
Dynamic Pivot
CLR Stored Procedures
Conclusion
8. Triggers
AFTER Triggers
The inserted and deleted Special Tables
Identifying the Number of Affected Rows
Identifying the Type of Trigger
Not Firing Triggers for Specific Statements
Nesting and Recursion
UPDATE and COLUMNS_UPDATED
Auditing Example
INSTEAD OF Triggers
Per-Row Triggers
Used with Views
Automatic Handling of Sequences
DDL Triggers
Database-Level Triggers
Server-Level Triggers
CLR Triggers
Conclusion
9. Transactions
What Are Transactions?
Locks
Isolation Levels
Read Uncommitted
Read Committed
Repeatable Read
Serializable
New Isolation Levels
Snapshot
Conflict Detection
Read Committed Snapshot
Save Points
Deadlocks
Simple Deadlock Example
Deadlock Caused by Missing Indexes
Deadlock with a Single Table
Conclusion
10. Exception Handling
Exception Handling Prior to SQL Server 2005
Exception Handling in SQL Server 2005
TRY/CATCH
New Exception-Handling Functions
Errors in Transactions
Using XACT_STATE
Using Save Points
Deadlocks and Update Conflicts
Conclusion
11. Service Broker
Dialog Conversations
Conversations
Reliable
In Order
Asynchronous
Messages
Contracts
DEFAULT
Queues
Services
Begining and Ending Dialogs
Conversation Endpoints
Conversation Groups
Sending and Receiving
Sample Dialog
Poison Messages
Dialog Security
Asymmetric Key Authentication
Configuring Dialog Security
Routing and Distribution
Adjacent Broker Protocol
Service Broker Endpoints
Configuring Adjacent Broker Protocol Connections
Windows Authentication
Certificate Authentication
Routes
Broker Instance
Incoming Routes
Wildcards and Special Addresses
Load Balancing
Routes and Database Mirroring
Forwarding
Scenarios
Reliable SOA
Asynchronous Processing
Where Does Service Broker Fit?
What Service Broker Is
What Service Broker Isn’t
Service Broker and MSMQ
Service Broker and BizTalk
Service Broker and Windows Communication Foundation
Conclusion
A. Companion to CLR Routines
Create the CLRUtilities Database: SQL Server
Development: Visual Studio
1. Create Project
2. Develop Code
Deployment and Testing: Visual Studio and SQL Server
3. Deploy and Build Solution
4. Test Solution
About the Contributors
Itzik Ben-Gan
Dejan Sarka
Roger Wolter
Steve Kass
Lubor Kollar
Index
About the Authors
Copyright
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Inside Microsoft® SQL Server™ 2005: T-SQL Programming
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset