Largeserver2008
server2008 时间:2021-01-12 阅读:()
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
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
- Largeserver2008相关文档
- 文档server2008
- capacityserver2008
- 信华ERP生产管理软件
- aliceserver2008
- Redirectionserver2008
- 江东网服务器教程
TMTHosting：夏季优惠,美国西雅图VPS月付7折,年付65折,美国服务器95折AS4837线路
tmthosting怎么样?tmthosting家本站也分享过多次，之前也是不温不火的商家，加上商家的价格略贵，之到斯巴达商家出现，这个商家才被中国用户熟知，原因就是斯巴达家的机器是三网回程AS4837线路，而且也没有多余的加价，斯巴达家断货后，有朋友发现TMTHosting竟然也在同一机房，所以大家就都入手了TMTHosting家的机器。目前，TMTHosting商家放出了夏季优惠，针对VPS推...
老薛主机入门建站月付34/月,年付345元，半价香港VPS主机
老薛主机怎么样?老薛主机这个商家有存在有一些年头。如果没有记错的话，早年老薛主机是做虚拟主机业务的，还算不错在异常激烈的市场中生存到现在，应该算是在众多商家中早期积累到一定的用户群的，主打小众个人网站业务所以能持续到现在。这不，站长看到商家有在进行夏季促销，比如我们很多网友可能有需要的香港vps主机季度及以上可以半价优惠，如果有在选择不同主机商的香港机房的可以看看老薛主机商家的香港vps。点击进入...
零途云月付31.9元起，香港cn2 gia线路
零途云是一家香港公司，主要产品香港cn2 gia线路、美国Cera线路云主机，美国CERA高防服务器，日本CN2直连服务器;同时提供香港多ip站群云服务器。即日起，购买香港/美国/日本云服务器享受9折优惠，新用户有优惠码：LINGTUYUN，使用即可打折。目前，零途云还推出性价比非常高香港多ip站群云服务器，有需要的，可以关注一下。零途云优惠码：优惠码：LINGTUYUN (新用户优惠，享受9折优...
server2008为你推荐
-
美国vps服务器美国VPS和美国服务器速度快吗域名服务域名系统主要是什么？便宜的虚拟主机免费、便宜的虚拟主机哪里有？要好用的 ，速度快的免费vps服务器有没有便宜的vps，最好是免费的香港虚拟空间香港虚拟空间哪家好?免备案虚拟空间教你怎么看免备案虚拟主机空间北京网站空间网站空间哪里的好，100m虚拟主机一般100-200M虚拟主机一天最多支持多少人访问啊?虚拟主机服务商请问哪个服务商的虚拟主机比较好呀合肥虚拟主机虚拟主机哪里买好一些？