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

sp_executesql

The sp_executesql command was introduced in SQL Server later than the EXEC command, mainly to provide better support for reusing execution plans. In this section, I’ll describe sp_executesql and its enhanced support in SQL Server 2005.

The sp_executesql Interface

The sp_executesql command is more flexible than EXEC(<string>) because it has an interface, which supports both input and output parameters. This capability allows you to create query strings with arguments that can reuse execution plans more efficiently than EXEC. The components of sp_executesql are very similar to those of a stored procedure, with the difference being that you construct the code dynamically. Those components include: a batch of code; parameter declaration ...