Largeserver2008

JosephSackSQLServer2008Transact-SQLRecipesSQLServer2008Transact-SQLRecipesCopyright2008byJosephSackAllrightsreserved.
Nopartofthisworkmaybereproducedortransmittedinanyformorbyanymeans,electronicormechanical,includingphotocopying,recording,orbyanyinformationstorageorretrievalsystem,withoutthepriorwrittenpermissionofthecopyrightownerandthepublisher.
ISBN-13(pbk):978-1-59059-980-8ISBN-10(pbk):1-59059-980-2ISBN-13(electronic):978-1-4302-0626-2PrintedandboundintheUnitedStatesofAmerica987654321Trademarkednamesmayappearinthisbook.
Ratherthanuseatrademarksymbolwitheveryoccurrenceofatrademarkedname,weusethenamesonlyinaneditorialfashionandtothebenefitofthetrademarkowner,withnointentionofinfringementofthetrademark.
LeadEditor:JonathanGennickTechnicalReviewer:EvanTerryEditorialBoard:ClayAndres,SteveAnglin,EwanBuckingham,TonyCampbell,GaryCornell,JonathanGennick,MatthewMoodie,JosephOttinger,JeffreyPepper,FrankPohlmann,BenRenow-Clarke,DominicShakeshaft,MattWade,TomWelshProjectManager:SusannahDavidsonPfalzerCopyEditor:AmiKnoxAssociateProductionDirector:KariBrooks-CoponyProductionEditor:LauraCheuCompositor:DinaQuanProofreader:LizWelchIndexer:BrendaMillerArtist:AprilMilneCoverDesigner:KurtKramesManufacturingDirector:TomDebolskiDistributedtothebooktradeworldwidebySpringer-VerlagNewYork,Inc.
,233SpringStreet,6thFloor,NewYork,NY10013.
Phone1-800-SPRINGER,fax201-348-4505,e-mailorders-ny@springer-sbm.
com,orvisithttp://www.
springeronline.
com.
Forinformationontranslations,pleasecontactApressdirectlyat2855TelegraphAvenue,Suite600,Berkeley,CA94705.
Phone510-549-5930,fax510-549-5939,e-mailinfo@apress.
com,orvisithttp://www.
apress.
com.
ApressandfriendsofEDbooksmaybepurchasedinbulkforacademic,corporate,orpromotionaluse.
eBookversionsandlicensesarealsoavailableformosttitles.
Formoreinformation,referenceourSpecialBulkSales—eBookLicensingwebpageathttp://www.
apress.
com/info/bulksales.
Theinformationinthisbookisdistributedonan"asis"basis,withoutwarranty.
Althougheveryprecau-tionhasbeentakeninthepreparationofthiswork,neithertheauthor(s)norApressshallhaveanyliabilitytoanypersonorentitywithrespecttoanylossordamagecausedorallegedtobecauseddirectlyorindirectlybytheinformationcontainedinthiswork.
ContentsataGlanceAbouttheAuthorxxvAbouttheTechnicalReviewer.
xxviiAcknowledgmentsxxixIntroductionxxxiCHAPTER1SELECT.
1CHAPTER2Perform,Capture,andTrackDataModifications63CHAPTER3Transactions,Locking,Blocking,andDeadlocking115CHAPTER4Tables143CHAPTER5Indexes197CHAPTER6Full-TextSearch217CHAPTER7Views239CHAPTER8SQLServerFunctions257CHAPTER9ConditionalProcessing,Control-of-Flow,andCursors307CHAPTER10StoredProcedures325CHAPTER11User-DefinedFunctionsandTypes343CHAPTER12Triggers373CHAPTER13CLRIntegration401CHAPTER14XML,Hierarchies,andSpatialData.
419CHAPTER15Hints.
449CHAPTER16ErrorHandling459CHAPTER17Principals475CHAPTER18Securables,Permissions,andAuditing501CHAPTER19Encryption.
547CHAPTER20ServiceBroker.
579ivCHAPTER21ConfiguringandViewingSQLServerOptions615CHAPTER22CreatingandConfiguringDatabases621CHAPTER23DatabaseIntegrityandOptimization.
669CHAPTER24MaintainingDatabaseObjectsandObjectDependencies687CHAPTER25DatabaseMirroring697CHAPTER26DatabaseSnapshots717CHAPTER27LinkedServersandDistributedQueries723CHAPTER28QueryPerformanceTuning739CHAPTER29BackupandRecovery789INDEX823vContentsAbouttheAuthorxxvAbouttheTechnicalReviewer.
xxviiAcknowledgmentsxxixIntroductionxxxiCHAPTER1SELECT1TheBasicSELECTStatement1SelectingSpecificColumnsfromaTable2SelectingEveryColumnforEveryRow3SelectiveQueryingUsingaBasicWHEREClause.
3UsingtheWHEREClausetoSpecifyRowsReturnedintheResultSet.
.
.
.
4CombiningSearchConditions4NegatingaSearchCondition6KeepingYourWHEREClauseUnambiguous6UsingOperatorsandExpressions.
7UsingBETWEENforDateRangeSearches9UsingComparisons9CheckingforNULLValues.
10ReturningRowsBasedonaListofValues11UsingWildcardswithLIKE11DeclaringandAssigningValuestoVariables12GroupingData.
14UsingtheGROUPBYClause14UsingGROUPBYALL.
15SelectivelyQueryingGroupedDataUsingHAVING.
16OrderingResults.
17UsingtheORDERBYClause17UsingtheTOPKeywordwithOrderedResults19SELECTClauseTechniques21UsingDISTINCTtoRemoveDuplicateValues21UsingDISTINCTinAggregateFunctions22UsingColumnAliases22UsingSELECTtoCreateaScript23PerformingStringConcatenation.
24CreatingaComma-DelimitedListUsingSELECT.
25UsingtheINTOClause.
26viiSubqueries27UsingSubqueriestoCheckforMatches27QueryingfromMoreThanOneDataSource28UsingINNERJoins29UsingOUTERJoins30UsingCROSSJoins31ReferencingaSingleTableMultipleTimesintheSameQuery32UsingDerivedTables.
33CombiningResultSetswithUNION33UsingAPPLYtoInvokeaTable-ValuedFunctionforEachRow.
35UsingCROSSAPPLY35UsingOUTERAPPLY37AdvancedTechniquesforDataSources38UsingtheTABLESAMPLEtoReturnRandomRows38UsingPIVOTtoConvertSingleColumnValuesintoMultipleColumnsandAggregateData39NormalizingDatawithUNPIVOT.
42ReturningDistinctorMatchingRowsUsingEXCEPTandINTERSECT.
.
.
.
44SummarizingData46SummarizingDataUsingCUBE46SummarizingDataUsingROLLUP48CreatingCustomSummariesUsingGroupingSets49RevealingRowsGeneratedbyGROUPING.
51AdvancedGroup-LevelIdentificationwithGROUPING_ID53CommonTableExpressions56UsingaNon-RecursiveCommonTableExpression56UsingaRecursiveCommonTableExpression59CHAPTER2Perform,Capture,andTrackDataModifications.
63INSERT63InsertingaRowintoaTable.
64InsertingaRowUsingDefaultValues65ExplicitlyInsertingaValueintoanIDENTITYColumn.
66InsertingaRowintoaTablewithauniqueidentifierColumn67InsertingRowsUsinganINSERT.
.
.
SELECTStatement.
68InsertingDatafromaStoredProcedureCall70InsertingMultipleRowswithVALUES71UsingVALUESAsaTableSource.
72UPDATE.
73UpdatingaSingleRow74UpdatingRowsBasedonaFROMandWHEREClause75UpdatingLargeValueDataTypeColumns76InsertingorUpdatinganImageFileUsingOPENROWSETandBULK.
78CONTENTSviiiStoringUnstructuredDataontheFileSystemWhileMaintainingSQLServerTransactionalControl.
80AssigningandModifyingDatabaseValues"inPlace"84DELETE86DeletingRows86TruncatingaTable.
88AdvancedDataModificationTechniques89ChunkingDataModificationswithTOP89ExecutingINSERTs,UPDATEs,andDELETEsinaSingleStatement90CapturingandTrackingDataModificationChanges93ReturningRowsAffectedbyaDataModificationStatement.
93AsynchronouslyCapturingTableDataModifications.
96QueryingAllChangesfromCDCTables99QueryingNetChangesfromCDCTables.
103TranslatingtheCDCUpdateMask104WorkingwithLSNBoundaries105DisablingChangeDataCapturefromTablesandtheDatabase.
107TrackingNetDataChangeswithMinimalDiskOverhead.
107CHAPTER3Transactions,Locking,Blocking,andDeadlocking115TransactionControl115UsingExplicitTransactions.
117DisplayingtheOldestActiveTransactionwithDBCCOPENTRAN.
119QueryingTransactionInformationbySession120Locking122ViewingLockActivity.
124ControllingaTable'sLockEscalationBehavior126Transaction,Locking,andConcurrency128ConfiguringaSession'sTransactionLockingBehavior.
129Blocking134IdentifyingandResolvingBlockingIssues134ConfiguringHowLongaStatementWillWaitforaLocktoBeReleased136Deadlocking137IdentifyingDeadlockswithaTraceFlag138SettingDeadlockPriority.
141CHAPTER4Tables143TableBasics143CreatingaTable.
147AddingaColumntoanExistingTable.
147ChanginganExistingColumnDefinition148CONTENTSixCreatingaComputedColumn.
149ReducingStorageforNullColumns.
150DroppingaTableColumn153ReportingTableInformation154DroppingaTable154CollationBasics155ViewingCollationMetadata155DesignatingaColumn'sCollation156Keys.
157CreatingaTablewithaPrimaryKey158AddingaPrimaryKeyConstrainttoanExistingTable.
159CreatingaTablewithaForeignKeyReference160AddingaForeignKeytoanExistingTable161CreatingRecursiveForeignKeyReferences.
162AllowingCascadingChangesinForeignKeys163SurrogateKeys.
165UsingtheIDENTITYPropertyDuringTableCreation165UsingDBCCCHECKIDENTtoViewandCorrectIDENTITYSeedValues166UsingtheROWGUIDCOLProperty168Constraints168CreatingaUniqueConstraint169AddingaUNIQUEConstrainttoanExistingTable.
170UsingCHECKConstraints171AddingaCHECKConstrainttoanExistingTable172DisablingandEnablingaConstraint173UsingaDEFAULTConstraintDuringTableCreation.
174AddingaDEFAULTConstrainttoanExistingTable175DroppingaConstraintfromaTable.
176TemporaryTablesandTableVariables176UsingaTemporaryTableforMultipleLookupsWithinaBatch177CreatingaTableVariabletoHoldaTemporaryResultSet178ManageabilityforVeryLargeTables180ImplementingTablePartitioning181DeterminingtheLocationofDatainaPartition184AddingaNewPartition186RemovingaPartition188MovingaPartitiontoaDifferentTable189RemovingPartitionFunctionsandSchemes.
190EasingVLDBManageabilitywithFilegroups.
191ReducingDiskSpaceUsagewithDataCompression192CONTENTSxCHAPTER5Indexes.
197IndexOverview.
197CreatingaTableIndex.
199EnforcingUniquenessonNon-KeyColumns.
201CreatinganIndexonMultipleColumns.
202DefiningIndexColumnSortDirection203ViewingIndexMetaData203DisablinganIndex205DroppingIndexes.
206ChanginganExistingIndexwithDROP_EXISTING206ControllingIndexBuildPerformanceandConcurrency207IntermediateIndexCreationinTempdb207ControllingParallelPlanExecutionforIndexCreation208AllowingUserTableAccessDuringIndexCreation208IndexOptions209UsinganIndexINCLUDE209UsingPAD_INDEXandFILLFACTOR.
210DisablingPageand/orRowIndexLocking211ManagingVeryLargeIndexes212CreatinganIndexonaFilegroup212ImplementingIndexPartitioning.
213IndexingaSubsetofRows214ReducingIndexSize215CHAPTER6Full-TextSearch.
217Full-TextIndexesandCatalogs217CreatingaFull-TextCatalog217CreatingaFull-TextIndex.
219ModifyingaFull-TextCatalog.
221ModifyingaFull-TextIndex222RetrievingFull-TextCatalogandIndexMetadata.
225DiscardingCommonStringsfromaFull-TextIndex226DroppingaFull-TextIndex229DroppingaFull-TextCatalog230BasicSearching230UsingFREETEXTtoSearchFull-TextIndexedColumns.
231UsingCONTAINSforWordSearching232AdvancedSearching232UsingCONTAINStoSearchwithWildcards.
233UsingCONTAINStoSearchforInflectionalMatches233UsingCONTAINSforSearchingResultsbyTermProximity.
234CONTENTSxiRankedSearching235ReturningRankedSearchResultsbyMeaning.
235ReturningRankedSearchResultsbyWeightedValue.
236CHAPTER7Views.
239RegularViews.
240CreatingaBasicView240QueryingtheViewDefinition242DisplayingViewsandTheirStructures243RefreshingaView'sDefinition244ModifyingaView245DroppingaView.
245ModifyingDataThroughaView246ViewEncryption247EncryptingaView247IndexedViews248CreatinganIndexedView248ForcingtheOptimizertoUseanIndexforanIndexedView251PartitionedViews251CreatingaDistributed-PartitionedView252CHAPTER8SQLServerFunctions257AggregateFunctions257ReturningtheAverageofValues258ReturningRowCounts.
259FindingtheLowestandHighestValuesfromanExpression.
259ReturningtheSumofValues260UsingStatisticalAggregateFunctions.
260MathematicalFunctions261PerformingMathematicalOperations262StringFunctions.
263ConvertingaCharacterValuetoASCIIandBacktoCharacter264ReturningIntegerandCharacterUnicodeValues.
265FindingtheStartPositionofaStringWithinAnotherString266FindingtheStartPositionofaStringWithinAnotherStringUsingWildcards266DeterminingtheSimilarityofStrings.
267TakingtheLeftmostorRightmostPartofaString.
268DeterminingtheNumberofCharactersorBytesinaString.
269ReplacingaPartofaString269StuffingaStringintoaString270ChangingBetweenLower-andUppercase.
270CONTENTSxiiRemovingLeadingandTrailingBlanks271RepeatinganExpressionNNumberofTimes272RepeatingaBlankSpaceNNumberofTimes272OutputtinganExpressioninReverseOrder273ReturningaChunkofanExpression273WorkingwithNULLs274ReplacingaNULLValuewithanAlternativeValue.
274PerformingFlexibleSearchesUsingISNULL.
275ReturningtheFirstNon-NULLValueinaListofExpressions276ReturningaNULLValueWhenTwoExpressionsAreEqual:OtherwiseReturningtheFirstExpression277DateFunctions277ReturningtheCurrentDateandTime278ConvertingBetweenTimeZones279IncrementingorDecrementingaDate'sValue.
280FindingtheDifferenceBetweenTwoDates281DisplayingtheStringValueforPartofaDate.
282DisplayingtheIntegerRepresentationforPartsofaDate.
282DisplayingtheIntegerValueforPartofaDateUsingYEAR,MONTH,andDAY.
283TypeConversion.
284ConvertingBetweenDataTypes284ConvertingDatestoTheirTextualRepresentation.
285RepresentingBinaryDatainStringLiterals286EvaluatingtheDataTypeReturnedbyanExpression287RankingFunctions288GeneratinganIncrementingRowNumber289ReturningRowsbyRank.
290ReturningRowsbyRankWithoutGaps.
292UsingNTILE292ProbingServer,Database,andConnection-LevelSettingsUsingSystemFunctions.
293DeterminingtheFirstDayoftheWeek293ViewingtheLanguageUsedintheCurrentSession294ViewingandSettingCurrentConnectionLockTimeoutSettings295DisplayingtheNestingLevelfortheCurrentStoredProcedureContext295ReturningtheCurrentSQLServerInstanceNameandSQLServerVersion296ReturningtheCurrentConnection'sSessionID(SPID)296ReturningtheNumberofOpenTransactions297RetrievingtheNumberofRowsAffectedbythePreviousStatement297RetrievingSystemStatistics.
298CONTENTSxiiiDisplayingDatabaseandSQLServerSettings299ReturningtheCurrentDatabaseIDandName300ReturningaDatabaseObjectNameandID.
301ReturningtheApplicationandHostfortheCurrentUserSession301ReportingCurrentUserandLoginContext302ViewingUserConnectionOptions303IDENTITYanduniqueidentifierFunctions.
303ReturningtheLastIdentityValue304ReturninganIdentityColumn'sSeedandIncrementingValue.
305CreatingaNewuniqueidentifierValue305CHAPTER9ConditionalProcessing,Control-of-Flow,andCursors.
.
.
.
307ConditionalProcessing307UsingCASEtoEvaluateaSingleInputExpression.
308UsingCASEtoEvaluateBooleanExpressions.
309UsingIF.
.
.
ELSE.
310Control-of-Flow312UsingRETURN313UsingWHILE.
314UsingGOTO316UsingWAITFOR318Cursors319CreatingandUsingTransact-SQLCursors321CHAPTER10StoredProcedures325StoredProcedureBasics.
325CreatingaBasicStoredProcedure326CreatingaParameterizedStoredProcedure.
328UsingOUTPUTParameters330ModifyingaStoredProcedure332DroppingStoredProcedures.
332ExecutingStoredProceduresAutomaticallyatSQLServerStartup333ReportingStoredProcedureMetadata334DocumentingStoredProcedures335StoredProcedureSecurity335EncryptingaStoredProcedure.
336UsingEXECUTEAStoSpecifytheProcedure'sSecurityContext337RecompilationandCaching340RECOMPILE(ing)aStoredProcedureEachTimeItIsExecuted341FlushingtheProcedureCache342CONTENTSxivCHAPTER11User-DefinedFunctionsandTypes343UDFBasics343CreatingScalarUser-DefinedFunctions344CreatingInlineUser-DefinedFunctions.
349CreatingMulti-StatementUser-DefinedFunctions351ModifyingUser-DefinedFunctions.
354ViewingUDFMetadata356DroppingUser-DefinedFunctions356BenefittingfromUDFs357MaintainingReusableCode357Cross-ReferencingNaturalKeyValues359ReplacingViewswithMulti-StatementUDFs362UDTBasics365CreatingandUsingUser-DefinedTypes365IdentifyingColumnsandParameterswithDependenciesonUser-DefinedTypes367DroppingUser-DefinedTypes368PassingTable-ValuedParameters.
369CHAPTER12Triggers373DMLTriggers374CreatinganAFTERDMLTrigger.
375CreatinganINSTEADOFDMLTrigger.
378HandlingTransactionsWithinDMLTriggers.
381ControllingDMLTriggersBasedonModifiedColumns384ViewingDMLTriggerMetadata385DDLTriggers.
386CreatingaDDLTriggerThatAuditsDatabase-LevelEvents.
387CreatingaDDLTriggerThatAuditsServer-LevelEvents389UsingaLogonTrigger390ViewingDDLTriggerMetadata.
392ManagingTriggers.
393ModifyingaTrigger393EnablingandDisablingTableTriggers394LimitingTriggerNesting395ControllingTriggerRecursion.
396SettingTriggerFiringOrder397DroppingaTrigger399CONTENTSxvCHAPTER13CLRIntegration.
401CLROverview402When(andWhenNot)toUseAssemblies402CLRObjectsOverview404CreatingCLRDatabaseObjects404EnablingCLRSupportinSQLServer405WritinganAssemblyforaCLRStoredProcedure405CompilinganAssemblyintoaDLLFile408LoadingtheAssemblyintoSQLServer409CreatingtheCLRStoredProcedure.
410CreatingaCLRScalarUser-DefinedFunction412CreatingaCLRTrigger415AdministeringAssemblies.
417ViewingAssemblyMetadata.
417ModifyinganAssembly'sPermissions417RemovinganAssemblyfromtheDatabase418CHAPTER14XML,Hierarchies,andSpatialData.
419WorkingwithNativeXML419CreatingXMLDataTypeColumns419InsertingXMLDataintoaColumn421ValidatingXMLDataUsingSchemas.
422RetrievingXMLData424ModifyingXMLData427IndexingXMLData.
428ConvertingBetweenXMLDocumentsandRelationalData.
430FormattingRelationalDataAsXML430ConvertingXMLtoaRelationalForm433WorkingwithNativeHierarchicalData435StoringHierarchicalData435ReturningaSpecificAncestor438ReturningChildNodes.
439ReturningaNode'sDepth.
440ReturningtheRootNode.
440DeterminingWhetheraNodeIsaChildoftheCurrentNode441ChangingNodeLocations441NativeSpatialData442StoringSpatialData.
442QueryingSpatialData445CONTENTSxviCHAPTER15Hints.
449UsingJoinHints449ForcingaHASHJoin450UsingQueryHints.
451ForcingaStatementRecompile.
452UsingTableHints.
454ExecutingaQueryWithoutLocking456ForcingaSEEKoveraSCAN.
456CHAPTER16ErrorHandling459System-DefinedandUser-DefinedErrorMessages.
459ViewingSystemErrorInformation459CreatingaUser-DefinedErrorMessage460DroppingaUser-DefinedErrorMessage.
462ManuallyRaisinganError.
462InvokinganErrorMessage463TrappingandHandlingApplicationErrors.
465Old-StyleErrorHandling466ErrorHandlingwithTRY.
.
.
CATCH.
468ApplyingErrorHandlingWithoutRecodingaStoredProcedure470NestingErrorHandling471CHAPTER17Principals475WindowsPrincipals475CreatingaWindowsLogin476ViewingWindowsLogins477AlteringaWindowsLogin478DroppingaWindowsLogin.
479DenyingSQLServerAccesstoaWindowsUserorGroup.
480SQLServerPrincipals480CreatingaSQLServerLogin.
482ViewingSQLServerLogins.
482AlteringaSQLServerLogin483ManagingaLogin'sPassword484DroppingaSQLLogin485ManagingServerRoleMembers485ReportingFixedServerRoleInformation.
486DatabasePrincipals.
488CreatingDatabaseUsers.
489ReportingDatabaseUserInformation490ModifyingaDatabaseUser.
490CONTENTSxviiRemovingaDatabaseUserfromtheDatabase491FixingOrphanedDatabaseUsers.
491ReportingFixedDatabaseRolesInformation493ManagingFixedDatabaseRoleMembership494ManagingUser-DefinedDatabaseRoles.
495ManagingApplicationRoles497CHAPTER18Securables,Permissions,andAuditing501PermissionsOverview502ReportingSQLServerAssignablePermissions.
503Server-ScopedSecurablesandPermissions505ManagingServerPermissions507QueryingServer-LevelPermissions.
508Database-ScopedSecurablesandPermissions509ManagingDatabasePermissions.
510QueryingDatabasePermissions.
511Schema-ScopedSecurablesandPermissions514ManagingSchemas.
516ManagingSchemaPermissions.
517ObjectPermissions519ManagingObjectPermissions521ManagingPermissionsAcrossSecurableScopes522DeterminingaCurrentConnection'sPermissionstoaSecurable522ReportingthePermissionsforaPrincipalbySecurableScope523ChangingSecurableOwnership.
527AllowingSQLLoginstoAccessNon-SQLServerResources.
528AuditingSQLInstanceandDatabase-LevelActivityofPrincipalsAgainstSecurables.
529DefiningAuditDataSources.
530CapturingSQLInstance–ScopedEvents533CapturingDatabase-ScopedEvents535QueryingCapturedAuditData539Managing,Modifying,andRemovingAuditObjects.
543CHAPTER19Encryption547EncryptionbyPassphrase.
547UsingaFunctiontoEncryptbyPassphrase548MasterKeys550BackingUpandRestoringaServiceMasterKey550Creating,Regenerating,andDroppingaDatabaseMasterKey551CONTENTSxviiiBackingUpandRestoringaDatabaseMasterKey553RemovingServiceMasterKeyEncryptionfromtheDatabaseMasterKey554AsymmetricKeyEncryption555CreatinganAsymmetricKey555ViewingAsymmetricKeysintheCurrentDatabase.
556ModifyingtheAsymmetricKey'sPrivateKeyPassword557EncryptingandDecryptingDataUsinganAsymmetricKey557DroppinganAsymmetricKey.
560SymmetricKeyEncryption560CreatingaSymmetricKey560ViewingSymmetricKeysintheCurrentDatabase.
562ChangingHowaSymmetricKeyIsEncrypted562UsingSymmetricKeyEncryptionandDecryption563DroppingaSymmetricKey.
567CertificateEncryption567CreatingaDatabaseCertificate567ViewingCertificatesintheDatabase.
568BackingUpandRestoringaCertificate.
569ManagingaCertificate'sPrivateKey.
570UsingCertificateEncryptionandDecryption.
571AutomaticallyOpeningandDecryptingviaaSymmetricKey573TransparentDataEncryption575EnablingTransparentDataEncryption575ManagingandRemovingTDE576CHAPTER20ServiceBroker.
579ExampleScenario:OnlineBookstore.
580CreatingaBasicServiceBrokerApplication.
580EnablingDatabasesforServiceBrokerActivity581CreatingtheDatabaseMasterKeyforEncryption582ManagingMessageTypes582CreatingContracts.
584CreatingQueues585CreatingServices.
587InitiatingaDialog.
589QueryingtheQueueforIncomingMessages591ReceivingandRespondingtoaMessage591EndingaConversation.
594PrioritizingServiceBrokerConversations596CONTENTSxixCreatingaStoredProceduretoProcessMessages598CreatingtheBookstoreStoredProcedure.
598Remote-ServerServiceBrokerImplementations601EnablingTransportSecurity603EnablingDialogSecurity606CreatingRoutesandRemoteServiceBindings608EventNotifications612CapturingLoginCommands612CHAPTER21ConfiguringandViewingSQLServerOptions.
615ViewingSQLServerConfigurations615ChangingSQLServerConfigurations.
617CHAPTER22CreatingandConfiguringDatabases621Creating,Altering,andDroppingDatabases621CreatingaDatabasewithaDefaultConfiguration622ViewingDatabaseInformation622CreatingaDatabaseUsingFileOptions624CreatingaDatabasewithaUser-DefinedFilegroup627SettingDatabaseUserAccess628RenamingaDatabase631DroppingaDatabase.
632DetachingaDatabase632AttachingaDatabase634ConfiguringDatabaseOptions635ViewingDatabaseOptions636ConfiguringANSISQLOptions636ConfiguringAutomaticOptions.
638CreatingorModifyingaDatabasetoAllowExternalAccess.
640CreatingorChangingaDatabasetoUseaNon-ServerDefaultCollation.
641ConfiguringCursorOptions.
642EnablingDateCorrelationOptimization.
643ModifyingDatabaseParameterizationBehavior.
644EnablingReadConsistencyforaTransaction.
647ConfiguringDatabaseRecoveryModels649ConfiguringPageVerification650ControllingDatabaseAccessandOwnership651ChangingaDatabaseStatetoOnline,Offline,orEmergency.
652ChangingaDatabaseOwner653CONTENTSxxManagingDatabaseFilesandFilegroups654AddingaDataFileorLogFiletoanExistingDatabase654RemovingaDataorLogFilefromaDatabase656RelocatingaDataorTransactionLogFile.
657ChangingaFile'sLogicalName.
658IncreasingaDatabase'sFileSizeandModifyingItsGrowthOptions.
659AddingaFilegrouptoanExistingDatabase660SettingtheDefaultFilegroup660RemovingaFilegroup661MakingaDatabaseorFilegroupRead-Only662ViewingandManagingDatabaseSpaceUsage663ViewingDatabaseSpaceUsage.
663ShrinkingtheDatabaseoraDatabaseFile.
665CHAPTER23DatabaseIntegrityandOptimization669DatabaseIntegrityChecking.
669CheckingConsistencyoftheDiskSpaceAllocationStructureswithDBCCCHECKALLOC.
670CheckingAllocationandStructuralIntegritywithDBCCCHECKDB672TablesandConstraints674CheckingAllocationandStructuralIntegrityofAllTablesinaFilegroupUsingDBCCCHECKFILEGROUP.
675CheckingDataIntegrityforTablesandIndexedViewsUsingDBCCCHECKTABLE676CheckingTableIntegritywithDBCCCHECKCONSTRAINTS.
679CheckingSystemTableConsistencywithDBCCCHECKCATALOG.
681IndexMaintenance.
682RebuildingIndexes.
682DefragmentingIndexes.
685RebuildingaHeap686CHAPTER24MaintainingDatabaseObjectsandObjectDependencies.
687DatabaseObjectMaintenance687ChangingtheNameofaUser-CreatedDatabaseObject.
687ChanginganObject'sSchema689ObjectDependencies.
690IdentifyingObjectDependencies690IdentifyingReferencingandReferencedEntities692ViewinganObject'sDefinition694CONTENTSxxiCHAPTER25DatabaseMirroring.
697DatabaseMirroringinContext697DatabaseMirroringArchitecture698SettingUpDatabaseMirroring700CreatingMirroringEndpoints700BackingUpandRestoringPrincipalDatabases705CreatingaDatabaseMirroringSession.
707SetupSummary709OperatingDatabaseMirroring710ChangingOperatingModes711PerformingFailovers712PausingorResumingaMirroringSession713StoppingMirroringSessionsandRemovingEndpoints714MonitoringandConfiguringOptions714MonitoringMirrorStatus.
714ConfiguringtheConnectionTimeoutPeriod715CHAPTER26DatabaseSnapshots717SnapshotBasics.
717CreatingandQueryingDatabaseSnapshots.
718RemovingaDatabaseSnapshot719RecoveringDatawithaDatabaseSnapshot.
720CHAPTER27LinkedServersandDistributedQueries.
723LinkedServerBasics.
723CreatingaLinkedServertoAnotherSQLServerInstance724ConfiguringLinkedServerProperties725ViewingLinkedServerInformation727DroppingaLinkedServer727LinkedServerLogins.
728AddingaLinkedServerLoginMapping.
728ViewingLinkedLogins.
729DroppingaLinkedServerLoginMapping.
730ExecutingDistributedQueries730ExecutingDistributedQueriesAgainstaLinkedServer.
730CreatingandUsinganAliastoReferenceFour-PartLinkedServerNames.
732ExecutingDistributedQueriesUsingOPENQUERY733ExecutingAdHocQueriesUsingOPENROWSET.
733ReadingDatafromaFileUsingOPENROWSETBULKOptions735CONTENTSxxiiCHAPTER28QueryPerformanceTuning739QueryPerformanceTips740CapturingandEvaluatingQueryPerformance742CapturingExecutingQueriesUsingsys.
dm_exec_requests.
742ViewingEstimatedQueryExecutionPlansUsingTransact-SQLCommands743ViewingExecutionRuntimeInformation746ViewingPerformanceStatisticsforCachedQueryPlans.
748ViewingAggregatedPerformanceStatisticsBasedonQueryorPlanPatterns750IdentifyingtheTopBottleneck752IdentifyingI/OContentionbyDatabaseandFile.
753IndexTuning.
754DisplayingIndexFragmentation.
756DisplayingIndexUsage.
759Statistics760ManuallyCreatingStatistics.
760CreatingStatisticsonaSubsetofRows761UpdatingStatistics.
762GeneratingandUpdatingStatisticsAcrossAllTables763ViewingStatisticsDetails765RemovingStatistics.
766MiscellaneousTechniques766UsinganAlternativetoDynamicSQL767ForcingSQLServertoUseaQueryPlan769ApplyingHintsWithoutModifyingApplicationSQL771CreatingPlanGuidesfromCache775CheckingtheValidityofaPlanGuide777ParameterizingaNon-parameterizedQueryUsingPlanGuides.
778LimitingCompetingQueryResourceConsumption781CHAPTER29BackupandRecovery.
789CreatingaBackupandRecoveryPlan.
789MakingBackups.
791PerformingaBasicFullBackup793CompressingYourBackups794NamingandDescribingYourBackupsandMedia.
796ConfiguringBackupRetention797StripingBackupSets.
799UsingaNamedBackupDevice799MirroringBackupSets.
801CONTENTSxxiiiPerformingaTransactionLogBackup803CreateBackupsWithoutBreakingtheBackupSequence.
804PerformingaDifferentialBackup.
805BackingUpIndividualFilesorFilegroups805PerformingaPartialBackup.
807ViewingBackupMetadata808RestoringaDatabase810RestoringaDatabasefromaFullBackup.
810RestoringaDatabasefromaTransactionLogBackup812RestoringaDatabasefromaDifferentialBackup815RestoringaFileorFilegroup.
816PerformingaPiecemeal(PARTIAL)Restore818RestoringaPage819IdentifyingDatabaseswithMultipleRecoveryPaths820INDEX823CONTENTSxxivAbouttheAuthorJOSEPHSACKisadedicatedsupportengineerintheMicrosoftPremierFieldEngineeringorganizationandhasworkedwithSQLServersince1997.
HeistheauthorofSQLServer2005T-SQLRecipes(Apress,2005)andSQLServer2000FastAnswersforDBAsandDevelopers(Apress,2005).
HecoauthoredProSQLServer2005(Apress,2005)andBeginningSQLServer2000DBA:FromNovicetoProfessional(Apress,2004).
Josephgraduatedwithanassociate'sdegreeinartsfromBardCollegeatSimon'sRockandearnedabachelor'sdegreeinpsychologyfromtheUniversityofMinnesota.
YoucanreachJosephonhisblog,www.
joesack.
com.
xxvAbouttheTechnicalReviewerEVANTERRYisthechieftechnicalconsultantforTheCleggCompany,specializingindatamanagementandinformationarchitecture.
HispastandcurrentclientsincludetheStateofIdaho,Albertsons,AmericanHondaMotors,ToyotaMotorSales,ThePolkCompany,andGeneralMotors.
HeisthecoauthorofApress'sBeginningRelationalDataModeling,haspublishedarticlesinDMReview,andhaspresentedattheIAIDQandDAMAInterna-tionalconferences.
Forquestionsorconsultingneeds,Evancanbecontactedatevan_terry@cleggcompany.
com.
xxviiAcknowledgmentsThisbookisdedicatedtoDavidHatch,andtothefamilymembers,friends,andcoworkerswhohelpedusgetthroughaverychallengingyear.
FromGuillain-Barrésyndrometoabrokenfoot—youwerethereforus,andweareveryluckytohaveyouinourlives.
Duringthe9-monthwritingprocess,theApressteamhelpedfacilitateaverypositiveandsmoothexperience.
Iwanttothanktheleadeditor,JonathanGennick,whowasresponsive,collab-orative,andanall-aroundgreatguytoworkwith.
IalsoappreciateEvanTerry'sastuteanddetailedtechnicalediting—thanksforcomingbackforasecondround!
IalsowanttothanktheamazingSusannahDavidsonPfalzerforherexcellentprojectmanage-mentskillsandpositivevoice.
Thankyoualsotothekeen-eyedAmiKnox,whoputthecriticalfinishingtouchesonthiswork,andalsotoLauraCheu,fortheproductioneditingandpatiencewithmylast-minutechanges.
Lastly—thankyoutotherestofthebehind-the-scenesApressteamwhoImaynothavemetovere-mailorthephone,butwhostilldeservecreditforbringingthisbooktothemarket.
xxixIntroductionThepurposeofthisbookistoquicklyprovideyouwiththeskillsyouneedtosolveproblemsandperformtasksusingtheTransact-SQLlanguage.
Iwrotethisbookinaproblem/solutionformatinordertoestablishanimmediateunderstandingofataskanditsassociatedTransact-SQLsolution.
Youcanusethisbooktolookupthetaskyouwanttoperform,readhowtodoit,andthenperformthetaskonyourownsystem.
Whilewritingthisbook,Ifollowedafewkeytenets:Keepitbrief,providingjustenoughinformationneededtogetthejobdone.
Allowrecipesandchapterstostandalone—keepingcross-referencesanddistractionstoatolerableminimum.
FocusonfeaturesthataretypicallyimplementedentirelyusingTransact-SQL.
Forexample,IcoverthenewResourceGovernorfeaturebecauseitwilltypicallybedeployedbyDBAsusingTransact-SQL—whereasIdonotcoverPolicy-BasedManagementduetoitsunderlyingdependenciesonSQLServerAgent,SQLServerManagementObjects(SMO),andSQLServerManagementStudio.
Fortunately,mostofthenewSQLServerengineimprovementsareentirelyTransact-SQLbased,andthereforeareincludedinthisbook.
Writerecipesthathelparangeofskillsets,fromnovicetoprofessional.
Ibegineachchapterwithbasicrecipesandprogressivelyworkuptomoreadvancedtopics.
RegardingnewSQLServer2008features,Ihaveinterwoventhemthroughoutthebookinthechapterswheretheyapply.
IfyouarejustlookingforarefreshonnewTransact-SQLfeatures,Ispecificallycallthemoutatthebeginningofeachchapterinwhichtheyexist.
Althoughakeytenetofthisbookistokeepthingsbrief,you'llnoticethatthisbookisstillquitelarge.
ThisisaconsequenceofthecontinuallyexpandingSQLServerfeatureset;however,restassuredthattherecipescontainedwithinarestillsuccinctandconstructedinsuchawayastoquicklygiveyoutheanswersyouneedtogetthejobdone.
I'vewrittenthisbookforSQLServerdevelopers,administrators,applicationdevelopers,andITgeneralistswhoaretaskedwithdevelopingdatabasesoradministeringaSQLServerenvironment.
Youcanreadthisbookfromstarttofinishorjumparoundtotopicsthatinterestyou.
Youcanusethisbooktobrushupontopicsbeforeajobintervieworanexam.
EvenforthemoreexperiencedSQLServerprofessionals,memoryfades—andthisbookcanhelpquicklyrefreshyourmemoryontheusageofacommandortechnique.
Thanksforreading!
xxxi