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
- 江东网服务器教程
3G流量免费高防CDN 50-200G防御
简介酷盾安全怎么样？酷盾安全，隶属于云南酷番云计算有限公司，主要提供高防CDN服务，高防服务器等，分为中国境内CDN，和境外CDN和二个产品，均支持SSL。目前CDN处于内测阶段，目前是免费的，套餐包0.01一个。3G流量（高防CDN）用完了继续续费或者购买升级包即可。有兴趣的可以看看，需要实名的。官方网站： ：点击进入官网云南酷番云计算有限公司优惠方案流量3G，用完了不够再次购买或者升级套餐流量...
个人网站备案流程及注意事项（内容方向和适用主机商）
如今我们还有在做个人网站吗？随着自媒体和短视频的发展和兴起，包括我们很多WEB2.0产品的延续，当然也包括个人建站市场的低迷和用户关注的不同，有些个人已经不在做网站。但是，由于我们有些朋友出于网站的爱好或者说是有些项目还是基于PC端网站的，还是有网友抱有信心的，比如我们看到有一些老牌个人网站依旧在运行，且还有新网站的出现。今天在这篇文章中谈谈有网友问关于个人网站备案的问题。这个也是前几天有他在选择...
PacificRack 端午节再来一款年付$38 VPS主机 2核4GB内存1TB流量
这不端午节和大家一样回家休息几天，也没有照顾网站的更新。今天又出去忙一天没有时间更新，这里简单搜集看看是不是有一些商家促销活动，因为我看到电商平台各种推送活动今天又开始一波，所以说现在的各种促销让人真的很累。比如在前面我们也有看到PacificRack 商家发布过年中活动，这不在端午节（昨天）又发布一款闪购活动，有些朋友姑且较多是端午节活动，刚才有看到活动还在的，如果有需要的朋友可以看看。第一、端...
server2008为你推荐
-
域名注册网站有没有免费的域名注册？免费云主机有永久的免费云主机么免费云主机永久免费的云主机哎或者空间或者vps海外服务器租用外国服务器怎么租用？免费网站空间如何免费做网站 免费域名+免费空间+免费网站手机网站空间QQ空间技巧的手机网站啊？合肥虚拟主机虚拟主机怎么弄！重庆虚拟主机万网M3型虚拟主机怎么样？速度如何？虚拟主机mysql如何连接虚拟主机中的MYSQL虚拟主机99idc如何选择虚拟主机的的操作系统以及更换操作系统是注意事项