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
- 江东网服务器教程
georgedatacenter：美国VPS可选洛杉矶/芝加哥/纽约/达拉斯机房,$20/年;洛杉矶独立服务器39美元/月
georgedatacenter怎么样?georgedatacenter这次其实是两个促销，一是促销一款特价洛杉矶E3-1220 V5独服，性价比其实最高;另外还促销三款特价vps，大家可以根据自己的需要入手。georgedatacenter是一家成立于2019年的美国vps商家，主营美国洛杉矶、芝加哥、达拉斯、新泽西、西雅图机房的VPS、邮件服务器和托管独立服务器业务。georgedatacen...
妮妮云（119元/季）日本CN2 2核2G 30M 119元/季
妮妮云的知名度应该也不用多介绍了，妮妮云旗下的云产品提供商，相比起他家其他的产品，云产品还是非常良心的，经常出了一些优惠活动，前段时间的八折活动推出了很多优质产品，近期商家秒杀活动又上线了，秒杀产品比较全面，除了ECS和轻量云，还有一些免费空间、增值代购、云数据库等，如果你是刚入行安稳做站的朋友，可以先入手一个119/元季付的ECS来起步，非常稳定。官网地址：www.niniyun.com活动专区...
无忧云-河南洛阳BGP，CEPH集群分布式存储，数据安全可靠，活动期间月付大优惠！
无忧云怎么样？无忧云服务器好不好？无忧云值不值得购买？无忧云是一家成立于2017年的老牌商家旗下的服务器销售品牌，现由深圳市云上无忧网络科技有限公司运营，是正规持证IDC/ISP/IRCS商家，主要销售国内、中国香港、国外服务器产品，线路有腾讯云国外线路、自营香港CN2线路等，都是中国大陆直连线路，非常适合免备案建站业务需求和各种负载较高的项目，同时国内服务器也有多个BGP以及高防节点...
server2008为你推荐
-
网站域名网站域名是什么网站服务器租用哪些网站适合独立服务器租用？价格方面怎么样？国外主机空间2个国外主机空间，都放了BLOG，看看哪个更快？域名备案买域名要备案吗虚拟主机控制面板虚拟主机管理面板与网站后台有什么区别?虚拟主机管理系统如何用win虚拟主机管理系统搭建天津虚拟主机天津有代理店掌柜的公司吗？在哪？windows虚拟主机在windows上怎么安装虚拟机新加坡虚拟主机新加坡虚拟主机无法访问，Godaddy回邮件说是域名的问题？虚拟主机试用哪儿的虚拟主机可以试用？？