book

Inside Microsoft® SQL Server™ 2005

by Itzik Ben-Gan, Lubor Kollar, Dejan Sarka

May 2006

Intermediate to advanced

536 pages

15h 13m

English

Microsoft Press

Read now

Unlock full access

Storage Format of DATETIMEDatetime ManipulationLiteralsRounding IssuesDATETIME FunctionsNo Separation Between Date and TimeDatetime-Related Querying ProblemsThe Birthday ProblemOverlapsIdentifying OverlapsGrouping OverlapsMaximum Number of Overlapping Sessions (Set-Based Solution)Identifying WeekdayGrouping by the WeekISO WeekWorking DaysGenerating a Series of Dates
Pattern MatchingCase-Sensitive Filters
MAX SpecifierBULK Rowset Provider
Scalar ExpressionsFilter Expressions
Theoretical Introduction to UDTsDomains and RelationsRelations and ClassesDomains and ClassesComplex DomainsWhy Do We Need Complex Classes?Language for Creating UDTsProgramming a UDTUDT RequirementsCreating a UDTDeploying the UDT Using T-SQL
XML Support in a Relational DatabaseIs the Relational Model Obsolete?When Should You Use XML Instead of Relational Representation?XML Serialized Objects in a DatabaseUsing XML with Open SchemaXML Data Type as a Parameter of a Stored ProcedureXQuery Modification Statements
Local Temporary TablestempdbScope and VisibilityTransaction ContextStatisticsTemporary Table Name ResolutionSchema Changes to Temporary Tables in Dynamic BatchesGlobal Temporary Tables
LimitationstempdbScope and VisibilityTransaction ContextStatistics
Custom AggregatesRunning AggregationsMaximum Concurrent SessionsMatching Problems
A Simple EXEC ExampleEXEC Has No InterfaceConcatenating VariablesEXEC AT
The sp_executesql InterfaceStatement Limit
Dynamic Maintenance ActivitiesStoring ComputationsDynamic FiltersDynamic PIVOT/UNPIVOT
SQL Injection: Code Constructed Dynamically at ClientSQL Injection: Code Constructed Dynamically at ServerProtecting Against SQL Injection
ENCRYPTIONSCHEMABINDINGCHECK OPTIONVIEW_METADATA
T-SQL Scalar UDFsPerformance IssuesUDFs Used in ConstraintsDEFAULT ConstraintsCHECK ConstraintsPRIMARY KEY and UNIQUE ConstraintsCLR Scalar UDFsCLR RoutinesRegular ExpressionsExplicit vs. Implicit ConversionsSQL SignatureT-SQL SQL Signature UDFCLR SQL Signature UDFCompare Performance of T-SQL and CLR SQL Signature UDFs
Inline Table-Valued UDFsSplit ArrayT-SQL Split UDFCLR Split UDFCompare Performance of T-SQL and CLR SplitMultistatement Table-Valued UDFs
User-Defined Stored ProceduresSpecial Stored ProceduresSystem Stored ProceduresOther Types of Stored Procedures
Input ParametersOutput Parameters
Reuse of Execution PlansRecompilationsParameter Sniffing Problem
The inserted and deleted Special TablesIdentifying the Number of Affected RowsIdentifying the Type of TriggerNot Firing Triggers for Specific StatementsNesting and RecursionUPDATE and COLUMNS_UPDATEDAuditing Example
Per-Row TriggersUsed with ViewsAutomatic Handling of Sequences
Database-Level TriggersServer-Level Triggers
Read UncommittedRead CommittedRepeatable ReadSerializableNew Isolation LevelsSnapshotConflict DetectionRead Committed Snapshot
Simple Deadlock ExampleDeadlock Caused by Missing IndexesDeadlock with a Single Table
TRY/CATCHNew Exception-Handling FunctionsErrors in TransactionsUsing XACT_STATEUsing Save PointsDeadlocks and Update Conflicts
ConversationsReliableIn OrderAsynchronousMessagesContractsDEFAULTQueuesServicesBegining and Ending DialogsConversation EndpointsConversation GroupsSending and Receiving
Poison Messages
Asymmetric Key AuthenticationConfiguring Dialog Security
Adjacent Broker ProtocolService Broker EndpointsConfiguring Adjacent Broker Protocol ConnectionsWindows AuthenticationCertificate AuthenticationRoutesBroker InstanceIncoming RoutesWildcards and Special AddressesLoad BalancingRoutes and Database MirroringForwarding
Reliable SOAAsynchronous Processing
What Service Broker IsWhat Service Broker Isn’tService Broker and MSMQService Broker and BizTalkService Broker and Windows Communication Foundation
1. Create Project2. Develop Code
3. Deploy and Build Solution4. Test Solution

Overview

A comprehensive, hands-on reference for database developers and administrators, this book focuses on advanced language features of Transact-SQL (T-SQL), the database programming language built into SQL Server. Includes code and table examples.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

Microsoft® SQL Server® 2008 For Dummies®

Mike Chapple

Microsoft® SQL Server® 2008 Bible

Paul Nielsen, Mike White, Uttam Parui

A Developer’s Guide to SQL Server 2005

Bob Beauchemin, Dan Sullivan

Microsoft® SQL Server® 2008 Internals

Paul Randal Kalen Delaney Kimberly Tripp, and Conor Cunningham

Publisher Resources

ISBN: 0735621977Supplemental Content Errata