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

Table Expressions

In this chapter’s opening paragraphs, I mentioned that there might be cases in which you need "logical" temporary tables–that is, only virtual materialization of interim sets, as opposed to physical materialization in temporary tables and table variables. Table expressions give you this capability. These include derived tables, CTEs, views, and inline table-valued UDFs. Here I’ll point out the scenarios in which these are preferable compared with other temporary objects and provide an example.

You should use table expressions in cases where you need a temporary object mainly for simplification–for example, when developing a solution in a modular approach, a step at a time. Also, use table expressions when you need to access the ...