book

Microsoft® SQL Server 2005 Unleashed

Name: Microsoft® SQL Server 2005 Unleashed
ISBN: 0672328240

by Ray Rankins, Paul Bertucci, Chris Gallelli, Alex T. Silverstein, Tudor Trufinescu, John Kane

April 2007

Intermediate to advanced

1752 pages

53h 43m

English

Sams

Read now

Unlock full access

Copyright
Dedication
About the Lead Authors
About the Contributing Authors
Acknowledgments
We Want to Hear from You!
Reader Services
Introduction
Who This Book Is ForWhat This Book CoversConventions Used in This BookGood Luck!
I. Welcome to Microsoft SQL Server
1. SQL Server 2005 Overview
SQL Server Components and FeaturesThe SQL Server Database EngineReliable StorageRapid Data AccessConsistent Data AccessAccess ControlData IntegritySQL Server 2005 Administration and Management ToolsSQL Server Management Studio (SSMS)The SQL Server Surface Area Configuration ToolSQL Server Configuration ManagerSQL Server AgentSQL Server ProfilerReplicationSnapshot ReplicationTransactional ReplicationMerge ReplicationImmediate UpdatingDatabase MirroringFull-Text SearchSQL Server Integration Services (SSIS)SQL Server Analysis Services (SSAS)SQL Server 2005 Reporting ServicesSQL Server Notification ServicesSQL Server Service BrokerSQL Server 2005 EditionsSQL Server 2005 Standard EditionSQL Server 2005 Enterprise EditionDifferences Between the Enterprise and Standard Editions of SQL ServerOther SQL Server 2005 EditionsWorkgroup EditionDeveloper EditionExpress EditionMobile EditionSQL Server Licensing ModelsDeveloper Edition LicensingExpress Edition LicensingMobile Edition LicensingChoosing a Licensing ModelMixing Licensing ModelsPassive Server/Failover LicensingVirtual Server LicensingSummary
2. What’s New in SQL Server 2005
New SQL Server 2005 FeaturesSQL Server Management StudioSQL Server Configuration ManagerCLR/.NET Framework IntegrationDynamic Management ViewsSystem Catalog ViewsSQL Server Management ObjectsDedicated Administrator ConnectionSQLCMDDatabase MailOnline Index and Restore OperationsNative EncryptionDatabase MirroringDatabase SnapshotsService BrokerSQL Server Integration ServicesTable and Index PartitioningSnapshot IsolationBusiness Intelligence Development StudioQuery NotificationMultiple Active Result SetsNew SQL Server Data TypesThe xml Data Typevarchar(max) and varbinary(max)SQL Server 2005 EnhancementsDatabase Engine EnhancementsIndex EnhancementsT-SQL EnhancementsSecurity EnhancementsBackup and Restore EnhancementsSQL Server Agent EnhancementsRecovery EnhancementsReplication EnhancementsFailover Clustering EnhancementsNotification Services EnhancementsFull-Text Search EnhancementsWeb Services EnhancementsAnalysis Services EnhancementsReporting Services EnhancementsSummary
II. SQL Server Tools and Utilities
3. SQL Server Management Studio
What’s New in SSMSThe Integrated EnvironmentWindow ManagementIntegrated HelpAdministration ToolsUsing Registered ServersUsing Object ExplorerUsing Activity MonitorUsing Log File ViewerDevelopment ToolsThe Query EditorQuery Editor TypesDisconnected EditingEditing SQLCMD Scripts in SSMSRegular Expressions and Wildcards in SSMSEnhanced Performance OutputUsing the Query Designer in the Query EditorManaging Projects in SSMSIntegrating SSMS with Source ControlUsing SSMS TemplatesSummary

4. SQL Server Command-Line Utilities
What’s New in SQL Server Command-Line UtilitiesThe sqlcmd Command-Line UtilityExecuting the sqlcmd utilityUsing scripting variables with sqlcmdThe dta Command-Line UtilityThe tablediff Command-Line UtilityThe sac Command-Line UtilityThe bcp Command-Line UtilityThe sqldiag Command-Line UtilityThe sqlservr Command-Line UtilityRemoved or Deprecated Utilities in SQL Server 2005Summary
5. SQL Server Profiler
What’s New with SQL Server ProfilerSQL Server Profiler ArchitectureCreating TracesEventsData ColumnsFiltersExecuting Traces and Working with Trace OutputSaving and Exporting TracesSaving Trace Output to a FileSaving Trace Output to a TableSaving the Profiler GUI OutputImporting Trace FilesImporting a Trace File into a Trace TableAnalyzing Trace Output with the Database Engine Tuning AdvisorReplaying Trace DataDefining Server-Side TracesMonitoring Running TracesStopping Server-Side TracesProfiler Usage ScenariosAnalyzing Slow Stored Procedures or QueriesDeadlocksIdentifying Ad Hoc QueriesIdentifying Performance BottlenecksMonitoring Auto-Update StatisticsMonitoring Application ProgressSummary
III. SQL Server Administration
6. SQL Server System and Database Administration
What’s New in SQL Server System and Database AdministrationSystem Administrator ResponsibilitiesSystem DatabasesThe master DatabaseThe resource DatabaseThe model DatabaseThe msdb DatabaseThe distribution DatabaseThe tempdb DatabaseMaintaining System DatabasesSystem TablesSystem ViewsCompatibility ViewsCatalog ViewsInformation Schema ViewsDynamic Management ViewsSystem Stored ProceduresUseful System Stored ProceduresSummary
7. Installing SQL Server 2005
What’s New in Installing SQL Server 2005Installation RequirementsHardware RequirementsSoftware RequirementsWindows Service RequirementsNetwork Protocol SupportFailover Cluster and Windows Vista SupportHow to Handle Previously Installed Community Technology Preview (CTP) Editions of SQL Server 2005Running Multiple Simultaneous EditionsInstallation WalkthroughInstall Screens, Step-by-StepViewing the Installation Log FilesInstallation PathsUnattended InstallationRemote InstallationInstalling SP1Unattended SP1 InstallationSummary
8. Upgrading to SQL Server 2005
What’s New in Upgrading SQL ServerUsing the SQL Server Upgrade Advisor (UA)Getting Started with the UAThe Analysis WizardThe Report ViewerDestination: SQL Server 2005Side-by-Side MigrationAvoiding an Unintentional In-Place Upgrade During SetupUsing the SQL Server Client ToolsMigrating DatabasesThe Copy Database WizardDatabase Compatibility LevelsMigrating Analysis ServicesMigrating DTS PackagesMigrating Reporting ServicesMigrating Notification ServicesUpgrading In-PlaceThe SQL Server 2005 Upgrade MatrixOther Installed, Upgraded, and Discontinued ComponentsUpgrading DatabasesUpgrading Analysis ServicesUpgrading DTSUpgrading Reporting ServicesUpgrading Notification ServicesUnattended UpgradesSummary
9. Client Installation and Configuration
What’s New in Client Installation and ConfigurationClient/Server Networking ConsiderationsServer Network ProtocolsThe Server Endpoint LayerClient Access ProvisioningThe Role of SQL BrowserPorts, Pipes, and InstancesClient InstallationInstallation RequirementsInstalling the Client ToolsInstalling SNACRedistributing SNAC with Custom Client ApplicationsClient ConfigurationClient Configuration Using SSCMServer AliasesConnection EncryptionClient Data Access TechnologiesProvider ChoicesDriver ChoicesConnecting Using the Various Providers and DriversUsing SNACUsing OLE DB with SNACUsing ODBC with SNACUsing ADO with SNACUsing the .NET Framework Data Provider for SQL ServerThe SQLCLR Context ConnectionUsing MDACUsing ODBC with MDACUsing OLE DB with MDACUsing JDBCGeneral Networking Considerations and TroubleshootingFirewall ConsiderationsTools for Testing ConnectionsSummary
10. Security and User Administration
What’s New in Security and User AdministrationAn Overview of SQL Server SecurityAuthentication MethodsWindows Authentication ModeMixed Authentication ModeSetting the Authentication ModeManaging PrincipalsLoginsSQL Server Security: UsersThe dbo UserThe guest UserThe INFORMATION_SCHEMA UserThe sys UserUser/Schema SeparationRolesFixed-Server RolesFixed-Database RolesThe public RoleUser-Defined RolesApplication RolesManaging SecurablesManaging PermissionsManaging SQL Server LoginsUsing SSMS to Manage LoginsUsing T-SQL to Manage LoginsManaging SQL Server UsersUsing SSMS to Manage UsersUsing T-SQL to Manage UsersManaging Database RolesUsing SSMS to Manage Database RolesUsing T-SQL to Manage Database RolesManaging SQL Server PermissionsUsing SSMS to Manage PermissionsUsing SSMS to Manage Permissions at the Server LevelUsing SSMS to Manage Permissions at the Database LevelUsing SSMS to Manage Permissions at the Object LevelUsing T-SQL to Manage PermissionsThe Execution ContextExplicit Context SwitchingImplicit Context SwitchingSummary
11. Database Backup and Restore
What’s New in Database Backup and RestoreDeveloping a Backup and Restore PlanTypes of BackupsFull Database BackupsDifferential Database BackupsPartial BackupsDifferential Partial BackupsFile and Filegroup BackupsCopy-Only BackupsTransaction Log BackupsRecovery ModelsFull RecoveryBulk-Logged RecoverySimple RecoveryBackup DevicesDisk DevicesTape DevicesNetwork SharesMedia Sets and FamiliesCreating Backup DevicesBacking Up a DatabaseCreating Database Backups with SSMSCreating Database Backups with T-SQLBacking Up the Transaction LogCreating Transaction Log Backups with SSMSCreating Transaction Log Backups with T-SQLThe NO_TRUNCATE OptionThe NORECOVERY | STANDBY= undo_file_name OptionsThe NO_LOG | TRUNCATE_ONLY OptionsBackup ScenariosFull Database Backups OnlyFull Database Backups with Transaction Log BackupsDifferential BackupsPartial BackupsFile/Filegroup BackupsMirrored BackupsCopy-Only BackupsSystem Database BackupsRestoring Databases and Transaction LogsRestores with T-SQLDatabase Restores with T-SQLTransaction Log Restores with T-SQLRestoring by Using SSMSRestore InformationThe RESTORE FILELISTONLY CommandThe RESTORE HEADERONLY CommandThe RESTORE VERIFYONLY CommandBacking Up and Restoring System TablesRestore ScenariosRestoring to a Different DatabaseRestoring a Transaction LogRestoring to the Point of FailureBacking Up the Tail of the Transaction LogRecovering the Full Database RecoveryRestoring the Transaction Log BackupRestoring to a Point in TimeOnline RestoresRestoring the System DatabasesAdditional Backup ConsiderationsFrequency of BackupsUsing a Standby ServerSnapshot BackupsConsiderations for Very Large DatabasesMaintenance PlansSummary
12. Database Mail
What’s New in Database MailSetting Up Database MailCreating Mail Profiles and AccountsUsing T-SQL to Update and Delete Mail ObjectsSetting Systemwide Mail SettingsTesting Your SetupSending and Receiving with Database MailThe Service Broker ArchitectureSending EmailReceiving EmailUsing SQL Server Agent MailJob Mail NotificationsCreating an OperatorEnabling SQL Agent MailCreating the JobTesting the Job-Completion NotificationAlert Mail NotificationsCreating an AlertTesting the Alert NotificationRelated Views and ProceduresViewing the Mail Configuration ObjectsViewing Mail Message DataSummary
13. SQL Server Scheduling and Notification
What’s New in Scheduling and NotificationConfiguring the SQL Server AgentConfiguring SQL Server Agent PropertiesConfiguring the SQL Server Agent Startup AccountConfiguring Email NotificationSQL Server Agent Proxy AccountViewing the SQL Server Agent Error LogSQL Server Agent SecurityManaging OperatorsManaging JobsDefining Job PropertiesDefining Job StepsDefining Multiple Jobs StepsDefining Job SchedulesDefining Job NotificationsViewing Job HistoryManaging AlertsDefining Alert PropertiesDefining Alert ResponsesScripting Jobs and AlertsMultiserver Job ManagementCreating a Master ServerEnlisting Target ServersCreating Multiserver JobsEvent ForwardingSummary
14. SQL Server High Availability
What’s New in High AvailabilityWhat Is High Availability?The Fundamentals of HAHardwareBackupOperating SystemVendor AgreementsTrainingQuality AssuranceStandards/ProceduresServer Instance IsolationBuilding Solutions with One or More HA OptionsMicrosoft Cluster Services (MSCS)SQL ClusteringData ReplicationLog ShippingDatabase MirroringCombining Failover with Scale-Out OptionsOther HA Techniques That Yield Great ResultsHigh Availability from the Windows Server Family SideMicrosoft Virtual Server 2005Virtual Server 2005 and Disaster RecoverySummary
15. Replication
What’s New in Data ReplicationWhat Is Replication?The Publisher, Distributor, and Subscriber MetaphorPublications and ArticlesFiltering ArticlesReplication ScenariosThe Central Publisher Replication ModelThe Central Publisher with Remote Distributor Replication ModelThe Publishing Subscriber Replication ModelThe Central Subscriber Replication ModelThe Multiple Publishers or Multiple Subscribers Replication ModelThe Updating Subscribers Replication ModelThe Peer-to-Peer Replication ModelSubscriptionsAnonymous Subscriptions (Pull Subscriptions)The Distribution DatabaseReplication AgentsThe Snapshot AgentThe Snapshot Agent SynchronizatonSnapshot Agent ProcessingThe Log Reader AgentThe Distribution AgentThe Merge AgentOther Specialized AgentsPlanning for SQL Server Data ReplicationAutonomy, Timing, and Latency of DataMethods of Data DistributionSQL Server Replication TypesSnapshot ReplicationTransactional ReplicationMerge ReplicationPreparing for Merge ReplicationBasing the Replication Design on User RequirementsData CharacteristicsSetting Up ReplicationCreating a Distributor and Enabling PublishingCreating a PublicationHorizontal and Vertical FilteringCreating SubscriptionsScripting ReplicationMonitoring ReplicationReplication Monitoring SQL StatementsMonitoring Replication within SQL Server Management StudioTroubleshooting Replication FailuresThe Performance MonitorReplication in Heterogeneous EnvironmentsBackup and Recovery in a Replication ConfigurationSome Thoughts on PerformanceLog ShippingData Replication and Database Mirroring for Fault Tolerance and High AvailabilitySummary
16. Database Mirroring
What’s New in Database MirroringWhat Is Database Mirroring?Copy-on-Write TechnologyWhen to Use Database MirroringRoles of the Database Mirroring ConfigurationPlaying Roles and Switching RolesDatabase Mirroring Operating ModesSetting Up and Configuring Database MirroringGetting Ready to Mirror a DatabaseCreating the EndpointsGranting PermissionsIdentifying the Other Endpoints for Database MirroringCreating the Database on the Mirror ServerConfiguring Database Mirroring by Using the WizardMonitoring a Mirrored Database EnvironmentRemoving MirroringTesting Failover from the Principal to the MirrorClient Setup and Configuration for Database MirroringUsing Replication and Database Mirroring TogetherUsing Database Snapshots from a Mirror for ReportingSummary
17. SQL Server Clustering
What’s New in SQL Server ClusteringHow Microsoft SQL Server Clustering WorksUnderstanding MSCSExtending MSCS with NLBHow MSCS Sets the Stage for SQL Server ClusteringInstalling SQL Server ClusteringConfiguring SQL Server Database DisksInstalling Network InterfacesInstalling MSCSInstalling SQL ServerFailure of a NodeThe Connection Test Program for a SQL Server ClusterPotential Problems to Watch Out for with SQL Server ClusteringSummary
IV. Database Administration
18. Creating and Managing Databases
What’s New in Creating and Managing DatabasesData Storage in SQL ServerDatabase FilesPrimary FilesSecondary FilesUsing FilegroupsUsing PartitionsTransaction Log FilesHow the Transaction Log WorksCreating DatabasesUsing SSMS to Create a DatabaseUsing T-SQL to Create DatabasesSetting Database OptionsThe Database OptionsUsing T-SQL to Set Database OptionsRetrieving Option InformationManaging DatabasesManaging File GrowthExpanding DatabasesShrinking DatabasesUsing DBCC SHRINKDATABASE to Shrink DatabasesUsing DBCC SHRINKFILE to Shrink DatabasesShrinking the Log FileUsing SSMS to Shrink DatabasesMoving DatabasesRestoring a Database Backup to a New LocationUsing ALTER DATABASEDetaching and Attaching DatabasesSummary
19. Creating and Managing Tables
What’s New in SQL Server 2005Creating TablesUsing Object Explorer to Create TablesUsing Database Diagrams to Create TablesUsing T-SQL to Create TablesDefining ColumnsData TypesNew Large-Value Data TypesLarge Row SupportThe xml Data TypeUser-Defined Data TypesCLR User-Defined TypesColumn PropertiesThe NULL and NOT NULL KeywordsIdentity ColumnsROWGUIDCOL ColumnsComputed ColumnsDefining Table LocationDefining Table ConstraintsModifying TablesUsing T-SQL to Modify TablesChanging a Column PropertyAdding and Dropping ColumnsUsing Object Explorer and the Table Designer to Modify TablesUsing Database Diagrams to Modify TablesDropping TablesPartitioned TablesCreating a Partition FunctionCreating a Partition SchemeCreating a Partitioned TableAdding and Dropping Table PartitionsAdding a Table PartitionDropping a Table PartitionSwitching Table PartitionsCreating Temporary TablesSummary
20. Creating and Managing Indexes
What’s New in Creating and Managing IndexesTypes of IndexesClustered IndexesNonclustered IndexesCreating IndexesCreating Indexes with T-SQLCreating Indexes with SSMSManaging IndexesManaging Indexes with T-SQLManaging Indexes with SSMSDropping IndexesOnline Indexing OperationsIndexes on ViewsSummary
21. Implementing Data Integrity
What’s New in Data IntegrityTypes of Data IntegrityDomain IntegrityEntity IntegrityReferential IntegrityEnforcing Data IntegrityImplementing Declarative Data IntegrityImplementing Procedural Data IntegrityUsing ConstraintsThe PRIMARY KEY ConstraintThe UNIQUE ConstraintThe FOREIGN KEY Referential Integrity ConstraintCascading Referential IntegrityThe CHECK ConstraintCreating ConstraintsUsing T-SQL to Create ConstraintsUsing SSMS to Create ConstraintsManaging ConstraintsGathering Constraint InformationDropping ConstraintsDisabling ConstraintsRulesDefaultsDeclarative DefaultsBound DefaultsWhen a Default Is AppliedRestrictions on DefaultsSummary
22. Creating and Managing Views in SQL Server
What’s New in Creating and Managing ViewsDefinition of ViewsUsing ViewsSimplifying Data ManipulationFocusing on Specific DataData AbstractionControlling Access to DataCreating ViewsCreating Views Using T-SQLENCRYPTIONSCHEMABINDINGVIEW_METADATAWITH CHECK OPTIONCreating Views Using the View DesignerManaging ViewsAltering Views with T-SQLDropping Views with T-SQLManaging Views with SSMSData Modifications and ViewsPartitioned ViewsModifying Data Through a Partitioned ViewDistributed Partitioned ViewsIndexed ViewsCreating Indexed ViewsIndexed Views and PerformanceTo Expand or Not to ExpandSummary
23. Creating and Managing Stored Procedures
What’s New in Creating and Managing Stored ProceduresAdvantages of Stored ProceduresCreating Stored ProceduresCreating Procedures in SSMSCreating Custom Stored Procedure TemplatesTemporary Stored ProceduresExecuting Stored ProceduresExecuting Procedures in SSMSExecution Context and the EXECUTE AS ClauseDeferred Name ResolutionIdentifying Objects Referenced in Stored ProceduresViewing Stored ProceduresModifying Stored ProceduresModifying Stored Procedures with SSMSUsing Input ParametersSetting Default Values for ParametersPassing Object Names As ParametersUsing Wildcards in ParametersUsing Output ParametersReturning Procedure StatusUsing Cursors in Stored ProceduresUsing CURSOR Variables in Stored ProceduresNested Stored ProceduresRecursive Stored ProceduresUsing Temporary Tables in Stored ProceduresTemporary Table Performance TipsUsing the table Data TypeUsing Remote Stored ProceduresDebugging Stored Procedures Using Microsoft Visual Studio .NETUsing System Stored ProceduresStored Procedure PerformanceQuery Plan CachingThe SQL Server Procedure CacheShared Query PlansAutomatic Query Plan RecompilationMonitoring Stored Procedure RecompilationForcing Recompilation of Query PlansUsing sp_recompileUsing Dynamic SQL in Stored ProceduresUsing sp_executesqlUsing Output Parameters with sp_executesqlStartup ProceduresT-SQL Stored Procedure Coding GuidelinesCalling Stored Procedures from TransactionsHandling Errors in Stored ProceduresUsing Source Code Control with Stored ProceduresCreating and Using CLR Stored ProceduresAdding CLR Stored Procedures to a DatabaseT-SQL or CLR Stored Procedures?Using Extended Stored ProceduresAdding Extended Stored Procedures to SQL ServerObtaining Information on Extended Stored ProceduresExtended Stored Procedures Provided with SQL ServerUsing xp_cmdshellSummary
24. Creating and Managing User-Defined Functions
What’s New in SQL Server 2005Why Use User-Defined Functions?Types of User-Defined FunctionsScalar FunctionsTable-Valued FunctionsInline Table-Valued FunctionsMultistatement Table-Valued FunctionsCreating and Managing User-Defined FunctionsCreating User-Defined FunctionsCreating T-SQL FunctionsUsing SSMS to Create FunctionsCreating Custom Function TemplatesViewing and Modifying User-Defined FunctionsUsing T-SQL to View FunctionsUsing T-SQL to Modify FunctionsUsing SSMS to View and Modify FunctionsManaging User-Defined Function PermissionsSystemwide Table-Valued FunctionsRewriting Stored Procedures as FunctionsCreating and Using CLR FunctionsAdding CLR Functions to a DatabaseDeciding Between Using T-SQL or CLR FunctionsSummary
25. Creating and Managing Triggers
What’s New in Creating and Managing TriggersUsing DML TriggersCreating DML TriggersUsing AFTER TriggersExecuting AFTER TriggersSpecifying Trigger Firing OrderSpecial Considerations with AFTER TriggersUsing inserted and deleted TablesChecking for Column UpdatesEnforcing Referential Integrity by Using DML TriggersCascading DeletesCascading UpdatesINSTEAD OF TriggersExecuting INSTEAD OF TriggersUsing AFTER Versus INSTEAD OF TriggersUsing AFTER and INSTEAD OF Triggers TogetherUsing Views with INSTEAD OF TriggersINSTEAD OF Trigger RestrictionsUsing DDL TriggersCreating DDL TriggersManaging DDL TriggersUsing CLR TriggersUsing Nested TriggersUsing Recursive TriggersSummary
26. Transaction Management and the Transaction Log
What’s New in Transaction ManagementWhat Is a Transaction?How SQL Server Manages TransactionsDefining TransactionsAutoCommit TransactionsExplicit User-Defined TransactionsSavepointsNested TransactionsImplicit TransactionsImplicit Transactions Versus Explicit TransactionsTransaction Logging and the Recovery ProcessThe Checkpoint ProcessAutomatic CheckpointsManual CheckpointsThe Recovery ProcessManaging the Transaction LogShrinking the Log FileTransactions and BatchesTransactions and Stored ProceduresTransactions and TriggersTriggers and Transaction NestingTriggers and Multistatement TransactionsUsing Savepoints in TriggersTransactions and LockingREAD_COMMITTED_SNAPSHOT IsolationCoding Effective TransactionsLong-Running TransactionsBound ConnectionsCreating Bound ConnectionsBinding Multiple ApplicationsDistributed TransactionsSummary
27. Database Snapshots
What’s New with Database SnapshotsWhat Are Database Snapshots?Limitations and Restrictions of Database SnapshotsCopy-on-Write TechnologyWhen to Use Database SnapshotsReverting to a Snapshot for Recovery PurposesSafeguarding a Database Prior to Making Mass ChangesProviding a Testing (or Quality Assurance) Starting Point (Baseline)Providing a Point-in-Time Reporting DatabaseProviding a Highly Available and Offloaded Reporting Database from a Database MirrorSetup and Breakdown of a Database SnapshotCreating a Database SnapshotBreaking Down a Database SnapshotReverting to a Database Snapshot for RecoveryReverting a Source Database from a Database SnapshotUsing Database Snapshots with Testing and QASetting Up Snapshots Against a Database MirrorReciprocal Principal/Mirror Reporting ConfigurationDatabase Snapshots Maintenance and Security ConsiderationsSecurity for Database SnapshotsSnapshot Sparse File Size ManagementNumber of Database Snapshots per Source DatabaseSummary
28. Database Maintenance
What’s New in Database MaintenanceThe Maintenance Plan WizardBacking Up DatabasesChecking Database IntegrityShrinking DatabasesMaintaining Indexes and StatisticsScheduling a Maintenance PlanManaging Maintenance Plans Without the WizardExecuting a Maintenance PlanMaintenance Without a Maintenance PlanSummary
V. SQL Server Performance and Optimization
29. Indexes and Performance
What’s New for Indexes and PerformanceUnderstanding Index StructuresClustered IndexesNonclustered IndexesIndex UtilizationIndex SelectionEvaluating Index UsefulnessIndex StatisticsThe Statistics HistogramHow the Statistics Histogram Is UsedIndex DensitiesEstimating Rows Using Index StatisticsGenerating and Maintaining Index and Column StatisticsAuto-Update StatisticsAsynchronous Statistics UpdatingManually Updating StatisticsColumn-Level StatisticsCreating StatisticsString Summary StatisticsSQL Server Index MaintenanceSetting the Fill FactorReapplying the Fill FactorDisabling IndexesManaging Indexes with SSMSIndex Design GuidelinesClustered Index IndicationsNonclustered Index IndicationsIndex CoveringIncluded ColumnsWide Indexes Versus Multiple IndexesIndexed ViewsIndexes on Computed ColumnsChoosing Indexes: Query Versus Update PerformanceSummary
30. Understanding Query Optimization
What’s New in Query OptimizationWhat Is the Query Optimizer?Query Compilation and OptimizationCompiling DML StatementsOptimization StepsQuery AnalysisIdentifying Search ArgumentsIdentifying OR ClausesIdentifying Join ClausesRow Estimation and Index SelectionEvaluating SARG and Join SelectivitySARGs and Inequality OperatorsSARGs and LIKE ClausesSARGS on Computed ColumnsEstimating Access Path CostClustered Index CostNonclustered Index CostCovering Nonclustered Index CostTable Scan CostUsing Multiple IndexesIndex IntersectionThe Index Union StrategyIndex JoinsOptimizing with Indexed ViewsJoin SelectionJoin Processing StrategiesNested Loops JoinsMerge JoinsHash JoinsGrace Hash JoinsDetermining the Optimal Join OrderSubquery ProcessingIN, ANY, and EXISTS SubqueriesMaterialized SubqueriesCorrelated SubqueriesExecution Plan SelectionQuery Plan CachingQuery Plan ReuseSimple Query ParameterizationQuery Plan AgingRecompiling Query PlansForcing Query Plan RecompilesMonitoring the Plan Cachesys.dm_exec_cached_planssys.dm_exec_sql_textsys.dm_exec_query_statssys.dm_exec_plan_attributesOther Query Processing StrategiesPredicate TransitivityGROUP BY OptimizationQueries with DISTINCTQueries with UNIONParallel Query ProcessingParallel Query Configuration OptionsIdentifying Parallel QueriesCommon Query Optimization ProblemsOut-of-Date or Unavailable StatisticsPoor Index DesignSearch Argument ProblemsUsing Optimizable SARGsNo SARGsUnknown Values in WHERE ClausesData Type MismatchesLarge Complex QueriesTriggersManaging the OptimizerOptimizer HintsForcing Index Selection with Table HintsForcing Join Strategies with Join HintsSpecifying Query Processing HintsGROUP BY HintsUNION HintsJoin HintsMiscellaneous HintsUsing the USE PLAN Query HintUsing Plan GuidesCreating Plan GuidesManaging Plan GuidesThe sys.plan_guides Catalog ViewPlan Guide Best PracticesVerifying That a Plan Guide Is Being AppliedForced ParameterizaionGuidelines for Using Forced ParameterizationLimiting Query Plan Execution with the Query GovernorSummary
31. Query Analysis
What’s New in Query AnalysisQuery Analysis in SSMSExecution Plan ToolTipsLogical and Physical Operator IconsAssertClustered Index Delete, Insert, and UpdateNonclustered Index Delete, Insert, and UpdateClustered Index Seek and ScanNonclustered Index Scan and SeekSplit and CollapseCompute ScalarConcatenationConstant ScanDeleted Scan and Inserted ScanFilterHash MatchNonclustered Index Spool, Row Count Spool, and Table SpoolEager Spool or Lazy SpoolLog Row ScanMerge JoinNested LoopsParameter Table ScanRemote Delete, Remote Insert, Remote Query, Remote Scan, and Remote UpdateRID LookupSequenceSortStream AggregateTable Delete, Table Insert, Table Scan, and Table UpdateTable-valued FunctionTopParallelism OperatorsDistribute StreamsGather StreamsRepartition StreamsAnalyzing Stored ProceduresSaving and Viewing Graphical Execution PlansSSMS Client StatisticsUsing the SET SHOWPLAN OptionsSHOWPLAN_TEXTSHOWPLAN_ALLSHOWPLAN_XMLUsing sys.dm_exec_query_planQuery StatisticsSTATISTICS IOScan CountLogical ReadsPhysical ReadsRead-Ahead ReadsLOB ReadsAnalyzing STATISTICS IO OutputSTATISTICS TIMEUsing datediff() to Measure RuntimeSTATISTICS PROFILESTATISTICS XMLQuery Analysis with SQL Server ProfilerSummary
32. Locking and Performance
What’s New in Locking and PerformanceThe Need for LockingTransaction Isolation Levels in SQL ServerRead Uncommitted IsolationRead Committed IsolationRead Committed Snapshot IsolationRepeatable Read IsolationSerializable Read IsolationSnapshot IsolationThe Lock ManagerMonitoring Lock Activity in SQL ServerQuerying the sys.dm_tran_locks ViewViewing Locking Activity with SSMSViewing Locking Activity with SQL Server ProfilerMonitoring Locks with Performance MonitorSQL Server Lock TypesShared LocksUpdate LocksExclusive LocksIntent LocksSchema LocksBulk Update LocksSQL Server Lock GranularitySerialization and Key-Range LockingKey-Range Locking for a Range SearchKey-Range Locking When Searching Nonexistent RowsUsing Application LocksIndex LockingRow-Level Versus Page-Level LockingLock EscalationThe locks Configuration SettingLock CompatibilityLocking Contention and DeadlocksIdentifying Locking ContentionSetting the Lock Timeout IntervalMinimizing Locking ContentionDeadlocksAvoiding DeadlocksHandling and Examining DeadlocksThe 1204 Trace FlagThe 1222 Trace FlagMonitoring Deadlocks with SQL Server ProfilerTable Hints for LockingTransaction Isolation–Level HintsLock Granularity HintsLock Type HintsOptimistic LockingOptimistic Locking Using the timestamp Data TypeOptimistic Locking with Snapshot IsolationSummary
33. Database Design and Performance
What’s New in Database Design and PerformanceBasic Tenets of Designing for PerformanceLogical Database Design IssuesNormalization ConditionsNormalization FormsFirst Normal FormSecond Normal FormThird Normal FormBenefits of NormalizationDrawbacks of NormalizationDenormalizing a DatabaseDenormalization GuidelinesEssential Denormalization TechniquesRedundant DataComputed ColumnsSummary DataHorizontal Data PartitioningVertical Data PartitioningPerformance Implications of Zero-to-One RelationshipsDatabase Filegroups and PerformanceRAID TechnologyRAID Level 0RAID Level 1RAID Level 10RAID Level 5Summary
34. Monitoring SQL Server Performance
What’s New in Monitoring SQL Server PerformanceA Performance Monitoring ApproachPerformance MonitorPerformance Monitor ViewsMonitoring ValuesWindows Performance CountersMonitoring the Network InterfaceDynamic Management Views or System Views for Monitoring Network ItemsMonitoring the ProcessorsDynamic Management Views or System Views for Monitoring Processor ItemsMonitoring MemoryDynamic Management Views or System Views for Monitoring Memory ItemsMonitoring the Disk SystemDynamic Management Views or System Views for Monitoring Disk System ItemsSQL Server Performance CountersMSSQL$:Plan Cache ObjectMonitoring SQL Server’s Disk ActivityLocksUsersThe Procedure CacheUser-Defined CountersDynamic Management Views or System Views: Access to Perfmon CountersUsing DBCC to Examine PerformanceSQLPERFPERFMONSHOWCONTIGPROCCACHEINPUTBUFFER and OUTPUTBUFFERThe Top 100 Worst-Performing QueriesOther SQL Server Performance ConsiderationsSummary
VI. SQL Server Application Development
35. What’s New for Transact-SQL in SQL Server 2005
The xml Data TypeThe max SpecifierTOP EnhancementsThe OUTPUT ClauseCommon Table ExpressionsRecursive Queries with CTEsUsing Recursive CTEs for Expanding a HierarchySetting the MAXRECURSION OptionRanking FunctionsThe ROW_NUMBER FunctionPartitioning by ROW_NUMBER()The RANK and DENSE_RANK FunctionsThe NTILE FunctionUsing Row Numbers for Paging ResultsPIVOT and UNPIVOTThe APPLY OperatorCROSS APPLYOUTER APPLYTRY...CATCH Logic for Error HandlingThe TABLESAMPLE ClauseSummary
36. SQL Server and the .NET Framework
What’s New in SQL Server 2005 and the .NET FrameworkWorking with ADO.NET 2.0 and SQL ServerADO.NET: Advanced BasicsWhat’s New in ADO.NET for SQL Server 2005Multiple Active Result Sets (MARS)Additions to System.Data.SqlTypesConnection PoolingQuery NotificationDeveloping Custom Managed Database ObjectsAn Introduction to Custom Managed Database ObjectsManaged Object PermissionsThe Three Permission SetsDeveloping Managed Objects with Visual Studio 2005Using Managed Stored ProceduresAttributes and the Implementation ContractThe Context ConnectionObjects in Microsoft.SqlServer.ServerSetting Up the Server for Managed Code ExecutionBuilding and Deploying the AssemblyDebugging Managed CodeUsing Managed User-Defined Functions (UDFs)Scalar UDFsTable-Valued UDFs (TVFs)Using Managed User-Defined Types (UDTs)Using Managed User-Defined Aggregates (UDAs)Using Managed TriggersUsing TransactionsUsing the Related System CatalogsSummary
37. Using XML in SQL Server 2005
What’s New in Using XML in SQL Server 2005Understanding XMLRelational Data as XML: The FOR XML ModesRAW ModeWorking with Binary ColumnsAUTO ModeEXPLICIT ModePATH ModeFOR XML and the New xml Data TypeXML as Relational Data: Using OPENXMLUsing the New xml Data TypeDefining and Using xml ColumnsUsing XML Schema CollectionsThe Built-in xml Data Type MethodsSelecting XML by Using query()The for ClauseThe where ClauseThe order by ClauseThe return ClauseTesting XML by Using exist()Converting a Node Value to a T-SQL Data Type by Using value()Accessing Relational Columns and T-SQL Variables in XQuery ExpressionsUsing the nodes() Method to Shred XMLUsing modify() to Insert, Update, and Delete XMLRemoving XML Nodes by Using deleteModifying XML with insert and replace value ofIndexing and Full-Text Indexing of xml ColumnsIndexing xml ColumnsUnderstanding XML IndexesThe Primary XML IndexThe Secondary XML IndexesThe PATH Secondary XML IndexThe VALUE Secondary XML IndexThe PROPERTY Secondary XML IndexXML Index Performance ConsiderationsFull-Text IndexingSummary
38. SQL Server Web Services
What’s New in SQL Server Web ServicesWeb Services History and OverviewThe Web Services PatternBuilding Web ServicesThe AS HTTP Keyword GroupThe FOR SOAP Keyword GroupExamples: A C# Client ApplicationExample 1: Running a Web Method Bound to a Stored Procedure from C#Example 2: Running Ad Hoc T-SQL Batches from a SQL Server Web ServiceExample 3: Calling a Web Method–Bound Stored Procedure That Returns XMLUsing Catalog Views and System Stored ProceduresControlling Access PermissionsSummary
VII. SQL Server Business Intelligence Features
39. SQL Server 2005 Analysis Services
What’s New in SSASUnderstanding SSAS and OLAPUnderstanding the SSAS Environment WizardsOLAP Versus OLTPMOLAPROLAPHOLAPAn Analytics Design MethodologyAn Analytics Mini-MethodologyRequirements PhaseDesign PhaseConstruction PhaseImplementation PhaseMaintenance PhaseAn OLAP Requirements Example: CompSales InternationalCompSales International RequirementsOLAP Cube CreationUsing SQL Server BIDSCreating an OLAP DatabaseAdding a Data SourceCreating Data Source ViewsDefining Dimensions and HierarchiesCreating the CubeBuilding and Deploying the CubePopulating the Cube with DataAggregating Data Within the CubeBrowsing Data in the CubeDelivering Data to UsersMultidimensional ExpressionsADO MDCalculated Members (Calculations)Query Analysis and OptimizationGenerating a Relational DatabaseLimitations of a Relational DatabaseCube PerspectivesKPIsData MiningSSISOLAP PerformanceSecurity and RolesSummary
40. SQL Server Integration Services
What’s New with SSISSSIS BasicsSSIS Architecture and ConceptsSSIS Tools and UtilitiesA Data Transformation RequirementRunning the SSIS WizardThe SSIS DesignerThe Package Execution UtilityThe dtexec UtilityRunning PackagesRunning Package ExamplesThe dtutil Utilitydtutil examplesUsing bcpFundamentals of Exporting and Importing DataFile Data TypesFormat FilesCustomizing a Format File by Using Interactive bcpCreating a Format File by Using the format SwitchFile Storage TypesPrefix LengthsField LengthsField TerminatorsDifferent Numbers of Columns in a File and a TableRenumbering ColumnsUsing ViewsLogged and Non-Logged OperationsBatchesParallel LoadingSupplying Hints to bcpThe ROWS_PER_BATCH HintThe CHECK_CONSTRAINTS HintThe FIRE_TRIGGER HintThe ORDER HintThe KILOBYTES_PER_BATCH HintThe TABLOCK HintSummary
41. SQL Server 2005 Reporting Services
What’s New in Reporting Services 2005Report BuilderThe Report Viewer ControlsInstalling and Configuring Reporting ServicesThe Reporting Services System ArchitectureInstalling Reporting ServicesThe Default InstallationCustom (Files-Only) InstallationReporting Services Configuration Options and ToolsThe Reporting Services Configuration ToolReport Server EncryptionConfiguring the Report Server Windows ServiceConfiguring the Report Server Web ServiceCreating and Configuring the Report Server Database CatalogThe Surface Area Configuration ToolDesigning ReportsDesigning Reports by Using the BIDS Report DesignerDesigning QueriesMaking Layout ChoicesBuilding ReportsCreating a Simple ListGrouping and Sorting in a ReportUsing Tables and HierarchiesAdding InteractivityDeploying the Sample ReportDesigning Reports Using Report BuilderModels and the Model DesignerA Model Design ExampleDefining InheritancePromoting PropertiesThe Report Builder Design SurfaceDiscourage GroupingRoles and Drill-Through ReportsDebugging Models and Model QueriesSorting in Report BuilderGenerating Drill-Through ReportsSaving and Opening ReportsCustomizing Drill-Through ReportsOther Model and Model Designer FeaturesModel SecurityEnabling Ad Hoc ReportingGranting Execute Report Definitions—A Global PermissionSetting Permissions on the ModelManagement and SecurityDeploying ReportsScripting Support in Reporting ServicesSecuring ReportsBuilt-in Roles and PermissionsSystem Roles and System PermissionsAuthentication of Report Server Users: Windows and FormsSubscriptionsData-Driven SubscriptionsSubscription and Delivery ArchitectureReport Execution OptionsLive Reports and SessionsCached ReportsExecution SnapshotsHistory SnapshotsLimitations for Cached Reports and Execution Snapshots and History SnapshotsPerformance and Monitoring ToolsThe Server Trace LogThe Execution LogEvent Log EntriesPerformance CountersBuilding Applications for SQL Server Reporting Services 2005 Using the Report Viewer ControlsUsing the ASP.NET Report Controls in a WebsiteSummary
VIII. Bonus Chapters on the CD
42. Managing Linked and Remote Servers
What’s New in Managing Linked and Remote ServersManaging Remote ServersRemote Server SetupLinked ServersDistributed QueriesDistributed TransactionsAdding, Dropping, and Configuring Linked Serverssp_addlinkedserversp_linkedserverssp_dropserversp_serveroptionMapping Local Logins to Logins on Linked Serverssp_addlinkedsrvloginsp_droplinkedsrvloginsp_helplinkedsrvloginObtaining General Information About Linked ServersExecuting a Stored Procedure via a Linked ServerSetting Up Linked Servers Through SQL Server Management StudioSummary
43. Configuring, Tuning, and Optimizing SQL Server Options
What’s New in Configuring, Tuning, and Optimizing SQL Server OptionsSQL Server Instance ArchitectureConfiguration OptionsFixing an Incorrect Option SettingSetting Configuration Options with SSMSObsolete Configuration OptionsConfiguration Options and PerformanceAd Hoc Distributed Queriesaffinity I/O maskaffinity maskAWE EnabledCLR EnabledCost Threshold for ParallelismCursor ThresholdDefault Full-Text LanguageDefault LanguageFill FactorIndex Create Memory (KB)Lightweight PoolingLocksMax Degree of ParallelismMax Server Memory and Min Server MemoryMax Text Repl SizeMax Worker ThreadsMin Memory Per QueryNested TriggersNetwork Packet SizePriority BoostQuery Governor Cost LimitQuery WaitRecovery IntervalRemote Admin connectionsRemote Login timeoutRemote Proc TransRemote Query timeoutScan for Startup ProcsShow Advanced OptionsUser ConnectionsUser OptionsXP-Related Configuration OptionsMiscellaneous OptionsDatabase Engine Tuning AdvisorThe Database Engine Tuning Advisor GUIThe Database Engine Tuning Advisor Command LineSummary
44. Administering Very Large SQL Server Databases
What’s New for Administering Very Large SQL Server DatabasesDo I Have a VLDB?VLDB Maintenance IssuesBacking Up and Restoring VLDBsSnapshot BackupsChecking VLDB ConsistencyDeveloping a Consistency Checking PlanData MaintenanceUpdating StatisticsRebuilding IndexesPurging/Archiving DataLocking and Performance ConsiderationsLogging ConsiderationsReferential Integrity ConsiderationsTransactional Integrity ConsiderationsStorage/Retrieval of Archived DataPossible Archive SolutionsData Archival and Database Design ChangesLogical Purging/ArchivingVLDB Database Design ConsiderationsDatabase Partitioning Options and IssuesVertical Data PartitioningHorizontal Data PartitioningUsing Distributed Partitioned ViewsPartitioning Databases Across ServersTable and Index Partitioning for VLDBsSummary
45. SQL Server Disaster Recovery Planning
What’s New in SQL Server Disaster Recovery PlanningHow to Approach Disaster RecoveryDisaster Recovery PatternsActive/Passive DR Sites PatternActive/Active DR Sites PatternActive Multisite DR PatternChoosing a Disaster Recovery PatternRecovery ObjectivesA Data-Centric Approach to Disaster RecoveryMicrosoft SQL Server Options for Disaster RecoveryData ReplicationLog ShippingDatabase Mirroring and SnapshotsThe Overall Disaster Recovery ProcessThe Focus of Disaster RecoverySQLDIAG.EXEPlanning and Executing a Disaster RecoveryHave You Detached a Database Recently?Third-Party Disaster Recovery AlternativesSummary
46. Transact-SQL Programming Guidelines, Tips, and Tricks
General T-SQL Coding RecommendationsProvide Explicit Column ListsQualify Object Names with Schema NameAvoiding SQL Injection Attacks When Using Dynamic SQLComment Your T-SQL CodeGeneral T-SQL Performance RecommendationsUNION Versus UNION ALL PerformanceUse IF EXISTS Instead of SELECT COUNT(*)Avoid Unnecessary ORDER BY or DISTINCT ClausesUsing Temp Tables Versus Table Variables Versus Common Table ExpressionsAvoid Unnecessary Function ExecutionsCursors and PerformanceWhen to Use CursorsVariable Assignment in UPDATE StatementsT-SQL Tips and TricksDate CalculationsCalculating the First Day of MonthCalculating the First Day of the YearCalculating the First Day of the QuarterCalculating Midnight for the Current DayCalculating Monday of the Current WeekCalculating Other DatesConverting Dates for ComparisonSorting Results with the GROUPING FunctionUsing CONTEXT_INFOWorking with Outer JoinsOUTER JOIN Versus WHERE Clause MatchingNested Outer JoinsWorking with Full Outer JoinsGenerating T-SQL Statements with T-SQLWorking with @@ERROR and @@ROWCOUNTDe-Duping Data with Ranking FunctionsSummary
47. SQL Server Notification Services
What’s New in SQL Server Notification ServicesRequirements and Editions of SSNSMaking the Business Case for Using SSNSUnderstanding the SSNS Platform ArchitectureUnderstanding EventsUnderstanding Event ProvidersUnderstanding Subscribers and SubscriptionsUnderstanding Event RulesUnderstanding ActionsUnderstanding the Notification CycleUnderstanding InstancesBuilding an Effective SSNS ApplicationChoosing a Programming MethodUsing XMLUsing NMOWorking with XML Using Management StudioLearning the Essentials of ADFsUse Case for the Sample ApplicationUsing the ParameterDefaults ADF ElementUsing the Database ADF ElementUsing the EventClasses ADF ElementUsing the SubscriptionClasses ADF ElementUsing the NotificationClasses ADF ElementFormatting NotificationsUsing the Providers ADF ElementUsing the Generator ADF ElementUsing the Distributors ADF ElementLearning the Essentials of ICFsUsing the ParameterDefaults, InstanceName, SqlServerSystem, and Applications ICF ElementsUsing the Database ICF ElementUsing the DeliveryChannels ICF elementCompiling and Running the Sample ApplicationCreating the Instance and Application via SSMSCreating SubscriptionsProviding Events to the ApplicationSummary
48. SQL Server Service Broker
What’s New in Service BrokerUnderstanding Distributed MessagingThe Basics of Service BrokerDesigning an Example SystemUnderstanding Service Broker ConstructsDefining Messages and Choosing a Message TypeSetting Up Contracts for CommunicationCreating Queues for Message StorageDefining Services to Send and Receive MessagesPlanning Conversations Between ServicesCreating the Conversation InitiatorCreating the Conversation TargetService Broker Routing and SecurityUsing Certificates for Conversation EncryptionBuilding Routes to Map Conversations Between SQL Server InstancesCreating Remote Service Bindings for ConversationsA Final Note on the Example SystemRelated System CatalogsSummary
49. SQL Server Full-Text Search
What’s New in SQL Server 2005 Full-Text SearchHow SQL Server FTS WorksSetting Up a Full-Text IndexUsing T-SQL Commands to Build Full-Text Indexes and CatalogsCREATE FULLTEXT CATALOGON FILEGROUPIN PATHWITH ACCENT_SENSITIVITYAS DEFAULTAUTHORIZATIONCREATE FULLTEXT INDEXCOLUMN NAMETYPE COLUMNFull-Text Indexing of Blobs and XMLLANGUAGEON FULLTEXT CATALOGKEY INDEXPOPULATION TYPEALTER FULLTEXT INDEXENABLE and DISABLESET CHANGE_TRACKINGADDDROPSTART and STOPManaging MSFTESQLDiagnosticsUsing the Full-Text Indexing Wizard to Build Full-Text Indexes and CatalogsFull-Text SearchesCONTAINS and CONTAINSTABLESearch PhraseGenerationProximityWeightedLANGUAGECONTAINSTABLEFREETEXT and FREETEXTTABLENoise WordsFull-Text Search MaintenanceBackup and Restore of Full-Text CatalogsAttachment and Detachment of Full-Text CatalogsFull-Text Search PerformanceSummary

Overview

Microsoft SQL Server 2005 Unleashed offers comprehensive coverage of SQL Server 2005 that goes beyond the basic syntax and information you’ll find in the product manuals, providing in-depth information derived from the authors’ real-world experience to help you build upon your working knowledge of the product and take your experience and knowledge to a higher level. This book focuses primarily on the information needed by system and database administrators, as well as for users with overlapping duties as both DBA and developer. Included is extensive coverage of the new features and upgraded tools and capabilities of SQL Server 2005, including .NET Framework integration, Integration Services, Service Broker, Database Mirroring and Snapshots, and Reporting Services.

Detailed information on…

Installing, upgrading, and administering SQL Server 2005

Database maintenance, backup, and recovery

Creating and managing tables, views, stored procedures, triggers, and user-defined functions

Database and index design

SQL Server query optimization, and performance monitoring and tuning

Transactions, transaction management, and distributed transactions

SQL Server Management Studio—NEW!

SQL Server Notification Services; Integration Services—NEW!; Analysis Services; Reporting Services—NEW!; Web Services—NEW!

SQL Server Service Broker—NEW!

SQL Server and Microsoft .NET Framework integration, including CLR-based stored procedures, functions, and triggers—NEW!

SQL Server high availability and SQL Server clustering and replication

Database Mirroring—NEW!

Database Snapshots—NEW!

Using XML in SQL Server 2005

SQL Server tools and utilities

CD-ROM includes

8 bonus chapters covering topics such as Notification Services, Service Broker, and Full-Text Search

Code samples, scripts, and sample databases

A PDF version of the entire book

Introduction

Part I Welcome to Microsoft SQL Server

1 SQL Server 2005 Overview

2 What’s New in SQL Server 2005

Part II SQL Server Tools and Utilities

3 SQL Server Management Studio

4 SQL Server Command-Line Utilities

5 SQL Server Profiler

Part III SQL Server Administration

6 SQL Server System and Database Administration

7 Installing SQL Server 2005

8 Upgrading to SQL Server 2005

9 Client Installation and Configuration

10 Security and User Administration

11 Database Backup and Restore

12 Database Mail

13 SQL Server Scheduling and Notification

14 SQL Server High Availability.

15 Replication

16 Database Mirroring

17 SQL Server Clustering

Part IV Database Administration

18 Creating and Managing Databases

19 Creating and Managing Tables

20 Creating and Managing Indexes

21 Implementing Data Integrity

22 Creating and Managing Views in SQL Server

23 Creating and Managing Stored Procedures

24 Creating and Managing User-Defined Functions

25 Creating and Managing Triggers

26 Transaction Management and the Transaction Log

27 Database Snapshots

28 Database Maintenance

Part V SQL Server Performance and Optimization

29 Indexes and Performance

30 Understanding Query Optimization

31 Query Analysis

32 Locking and Performance

33 Database Design and Performance

34 Monitoring SQL Server Performance

Part VI SQL Server Application Development

35 What’s New for Transact-SQL in SQL Server 2005

36 SQL Server and the .NET Framework

37 Using XML in SQL Server 2005

38 SQL Server Web Services

Part VII SQL Server Business Intelligence Features

39 SQL Server 2005 Analysis Services

40 SQL Server Integration Services

41 SQL Server 2005 Reporting Services

Bonus Chapters on the CD

42 Managing Linked and Remote Servers

43 Configuring, Tuning, and Optimizing SQL Server Options

44 Administering Very Large SQL Server Databases

45 SQL Server Disaster Recovery Planning

46 Transact-SQL Programming Guidelines, Tips, and Tricks

47 SQL Server Notification Services

48 SQL Server Service Broker

49 SQL Server Full-Text Search

Index

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0672328240Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills