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
- 江东网服务器教程
PacificRack(年付低至19美元),夏季促销PR-M系列和多IP站群VPS主机
这几天有几个网友询问到是否有Windows VPS主机便宜的VPS主机商。原本他们是在Linode、Vultr主机商挂载DD安装Windows系统的，有的商家支持自定义WIN镜像，但是这些操作起来特别效率低下，每次安装一个Windows系统需要一两个小时，所以如果能找到比较合适的自带Windows系统的服务器那最好不过。这不看到PacificRack商家有提供夏季促销活动，其中包括年付便宜套餐的P...
易探云服务器怎么过户/转让?云服务器PUSH实操步骤
易探云服务器怎么过户/转让?易探云支持云服务器PUSH功能，该功能可将云服务器过户给指定用户。可带价PUSH，收到PUSH请求的用户在接收云服务器的同时，系统会扣除接收方的款项，同时扣除相关手续费，然后将款项打到发送方的账户下。易探云“PUSH服务器”的这一功能，可以让用户将闲置云服务器转让给更多需要购买的用户！易探云服务器怎么过户/PUSH?1.PUSH双方必须为认证用户：2.买家未接收前，卖家...
DediPath（$1.40），OpenVZ架构 1GB内存
DediPath 商家成立时间也不过三五年，商家提供的云服务器产品有包括KVM和OPENVZ架构的VPS主机。翻看前面的文章有几次提到这个商家其中机房还是比较多的。其实对于OPENVZ架构的VPS主机以前我们是遇到比较多，只不过这几年很多商家都陆续的全部用KVM和XEN架构替代。这次DediPath商家有基于OPENVZ架构提供低价的VPS主机。这次四折的促销活动不包括512MB内存方案。第一、D...
server2008为你推荐
-
免费虚拟主机哪个网站有比较稳定的免费虚拟主机提供？网速要快的，空间大点的。谢谢！云主机租用云主机服务器租用费用怎么算php虚拟主机如何用虚拟主机建PHP论坛？网络服务器租用服务器租用 使用方法国外空间租用好用的国外空间vps试用请问有什么网站可以提供免费vps试用的？想用它来刷一下外国pt站域名服务域名服务有何作用?如何设置?网站服务器租用哪些网站适合独立服务器租用？价格方面怎么样？jsp虚拟空间JSP虚拟目录及虚拟路径的配置方法jsp虚拟空间jsp虚拟主机有支持的吗