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

Content preview from Inside Microsoft® SQL Server™ 2005

Using Cursors

In this chapter, I’ll explain the types of problems for which cursors are a reasonable solution, even though such cases are not common. The goal of the chapter is to show you how to use them wisely.

I’ll assume that you have sufficient technical knowledge of the various cursor types and know the syntax for declaring and using them. If you don’t, you can find a lot of information about cursors in Books Online. My focus is to explain why cursors are typically not the right choice and to present the cases in which cursors do make sense.

So why should you avoid using cursors for the most part?

For one, cursors conflict with the main premise of the relational model. Using cursors, you apply procedural logic rather than set-based logic. That ...