book

Microsoft® SQL Server™ 2005: Database Essentials Step by Step

Name: Microsoft® SQL Server™ 2005: Database Essentials Step by Step
Author: Solid Quality Learning
ISBN: 9780735622074

by Solid Quality Learning

September 2006

Beginner

336 pages

8h 31m

English

Microsoft Press

Read now

Unlock full access

Microsoft® SQL Server™ 2005: Database Essentials Step by Step
A Note Regarding Supplemental Files
Introduction
Who This Book Is ForHow This Book Is OrganizedFinding Your Best Starting Point in This BookConventions and Features in This BookConventionsOther FeaturesSystem RequirementsSample FilesInstalling the Sample FilesUsing the Sample FilesUninstalling the Sample FilesSupport for This BookQuestions and Comments
Acknowledgments
AuthorsTechnical Editor
I. Introduction to Database Development with Microsoft SQL Server 2005
1. Introducing Database Development with Microsoft SQL Server 2005
The Process of Storing and Managing DataIntroducing Database SystemsUnderstanding the Requirements of a Database SystemDefining the Architecture of a Database ApplicationUsing a Monolithic Application with Data Embedded in the Application CodeUsing a Monolithic Application with Data Stored in an External FileUsing a Database Application with Data Managed by a Database ServerUsing an Application with a Generic Data Access LayerUsing an Application with Separated Presentation, Business, and Data Access LayersUsing a Complex Application with Multiple Options for Each LayerConclusion
2. Installing and Setting Up Your Microsoft SQL Server 2005 Development Environment
Using a Virtual PCOperating System Requirements for Development of SQL Server Database ApplicationsRecommended Productivity ToolsUsing Performance MonitorUsing Network MonitorUsing Upgrade AdvisorRecommended Development ToolsMicrosoft SQL Server 2005 EditionsMicrosoft SQL Server Express EditionMicrosoft SQL Server Workgroup EditionMicrosoft SQL Server Standard EditionMicrosoft SQL Server Enterprise EditionInstalling SQL Server 2005Installing SQL Server 2005Using the Surface Area Configuration ToolSurface Area Configuration For Services And ConnectionsSurface Area Configuration For FeaturesConclusionChapter 2 Quick Reference
3. Reviewing Microsoft SQL Server 2005 Management Tools
IntroductionUsing SQL Server Books OnlineAccessing SQL Server Books OnlineUsing SQL Server Configuration ManagerUsing the SQL Server 2005 Services NodeUsing the SQL Server 2005 Network Configuration NodeStatic Ports, Dynamic Ports, and Windows FirewallStatic portsDynamic portsWindows FirewallWhat is the best configuration?Using the SQL Native Client Configuration NodeClient ProtocolsAliasesUsing SQL Server Surface Area ConfigurationUsing Surface Area Configuration For Services And ConnectionsUsing Surface Area Configuration For FeaturesSac UtilityUsing SQL Server Management StudioAdministering Servers with SQL Server Management StudioConnecting to a ServerRegistering a Server in SQL Server Management StudioUsing Object Explorer in SQL Server Management StudioUsing the Database NodeCreating SELECT QueriesQuerying More than One TableCreating More Complex SELECT QueriesManaging Other ObjectsUsing Database DiagramsCreating a Database DiagramWriting Scripts in SQL Server Management StudioStarting a Query Using the New Query Toolbar ButtonStarting a New Query Using the Database Context MenuRetrieving Information about Your QueriesObtaining Query InformationRetrieving StatisticsWriting Solutions and Projects in SQL Server Management StudioCreating a Project in SQL Server Management StudioUsing SQL Server Management Studio TemplatesPerforming Actions with TemplatesUsing SQL Server ProfilerUsing the SQL Server Profiler Tool and Creating a TraceCreating a Trace with the GUIWhen Should You Use SQL Server Profiler?Accessing Event TypesSelecting Data in Event ClassesRunning SQL Server Profiler and Performance Monitor TogetherRunning SQL Server Profiler and Performance Monitor Side by SideViewing Deadlocks in SQL Server ProfilerExporting Deadlock InformationUsing Database Engine Tuning AdvisorAnalyzing Database Engine Tuning AdvisorWorking with Database Engine Tuning AdvisorStarting Database Engine Tuning AdvisorEvaluating the RecommendationsEvaluating the Impact of Database Engine Tuning Advisor RecommendationsManaging Tuning OptionsManaging Database Engine Tuning Advisor SessionsExporting a Session DefinitionUsing SQLCmdWorking with the SQLCmd UtilityConnecting to a Default SQL Server InstanceExecuting Script FilesExecuting the DTA.sql Script by Using the SQLCmd UtilityConclusionChapter 3 Quick Reference
II. How to Create a Microsoft SQL Server 2005 Database
4. Gathering and Understanding Business Requirements before Creating Database Objects
Understanding Business Processes and User Interaction RequirementsDefining the Business ProblemCapturing RequirementsWriting User SurveysInterviewingShadowingWriting RequirementsUnderstanding Business Data and Its LifetimeArchitecture and Operational RequirementsAvailabilityInteroperabilityManageabilityPerformanceReliabilityScalabilitySecurityPredicting the Volume of Information to Store and Manage and Predicting Database UtilizationUsing Transaction Cost AnalysisStep 1: Compiling a Users ProfileStep 2: Executing Discrete TestsCreating the ScriptStep 3: Measuring the Cost of Each OperationCreating a Windows Performance LogReviewing the Captured InformationStep 4: Calculating the Cost of an Average ProfileStep 5: Calculating the Maximum CapacityStep 6: Verifying the Maximum CapacityConclusionChapter 4 Quick Reference

5. Designing a Database to Solve Business Needs
Designing a Database ConceptuallyValidating Business Requirements through Conceptual ModelsCreating the First ModelDiagramming the ModelCreating a Conceptual ER Diagram Using Microsoft VisioCreating EntitiesModeling Entities in Visio 2003Adding AttributesModeling Attributes in Visio 2003Adding RelationshipsModeling a Parent-Child RelationshipOther Types of RelationshipsModeling intersection relationshipsModeling an Intersection RelationshipModeling multi-intersection relationshipsModeling self-referencing relationshipsModeling a Self-Referencing RelationshipApproving the ModelDesigning a Database Logically to Leverage the Relational EngineCreating Columns to Capture Object AttributesDeclaring a Datatype GraphicallyDefining a Datatype Using Microsoft Visio 2003Using T-SQL Code to Define a DatatypeNumeric AttributesIntegers and quantitiesPrecise numbers and accounting dataScientific and engineering dataString AttributesUnicode and character datatypesFixed and variable-length datatypesString syntaxLong text datatypesChoosing the appropriate datatypeDate and Time AttributesBinary DataComplex AttributesT-SQL user-defined datatypesCommon language runtime-based UDTsValidating the DataNOT NULL ConstraintsDEFAULT ConstraintsPRIMARY KEY ConstraintsUNIQUE ConstraintsCHECK ConstraintsFOREIGN KEY ConstraintsPhysically Creating a DatabaseSelecting an Appropriate Storage Design for a DatabaseWhat Is a Database?How Is Information Stored?Designing Database StorageCreating a DatabaseUsing SQL Server Management Studio to Create a DatabaseUsing T-SQL to Create a DatabaseDesigning Database Schemas to Logically Group Database ObjectsUsing SQL Server Management Studio to Create Database SchemasUsing T-SQL to Create Database SchemasCreating Tables to Implement a DesignUsing SQL Server Management Studio to Create TablesDefining the Primary Key in a Table Using SQL Server Management StudioDefining a CHECK Constraint Using SQL Server Management StudioUsing T-SQL to Create TablesConclusionChapter 5 Quick Reference
III. How to Query Data from Microsoft SQL Server 2005
6. Reading Microsoft SQL Server 2005 Data from Client Applications
Introducing Microsoft Data Access ComponentsAvoiding Deprecated MDAC ComponentsOutlining the MDAC ArchitectureUnderstanding Open Database Connectivity (ODBC)Creating a Data Source NameCreating an ODBC Data SourceProgramming ODBC ApplicationsUnderstanding OLE DB and ADOUsing an ADO Connection ObjectCreating an Appropriate Connection StringUsing an ADO Command ObjectUsing an ADO Recordset ObjectUnderstanding ADO.NETOutlining the ADO.NET ArchitectureUsing ADO.NET NamespacesIntroducing the .NET SQL Server Data ProviderUsing the SqlConnection ClassUsing the SqlCommand ClassUsing the SqlDataReader ClassUsing the SqlDataAdapter ClassCreating a SqlDataAdapter GraphicallyUsing the TableAdapter ClassCreating a TableAdapterIntroducing SQL Native ClientUsing the XML DatatypeUsing Multiple Active Result Sets (MARS)Using Query NotificationCreating an Application that Uses Query NotificationConclusionChapter 6 Quick Reference
7. Selecting the Data You Need
Selecting Data from a Single TableRunning a SELECT Statement in SQL Server Management StudioUsing AND and OR OperatorsComparing NULL ValuesUsing the CASE StatementUsing Search ArgumentsSelecting Data from Multiple TablesUsing AliasesUsing the INNER JOIN SyntaxUsing More than Two TablesUsing LEFT JOINUsing RIGHT JOINUsing FULL JOINReading Single ValuesUsing System-Supplied Scalar FunctionsAggregate FunctionsConfiguration FunctionsCursor FunctionsDate and Time FunctionsMathematical FunctionsMetadata FunctionsSecurity FunctionsString FunctionsSystem FunctionsSystem Statistical FunctionsRanking FunctionsDesigning and Using Scalar UDFsDesigning UDFs that Do Not Access Tables or ViewsDesigning UDFs that Access Tables or ViewsDesigning and Using Stored Procedures to Retrieve Scalar ValuesUsing Output ParametersReading Relational and XML DataViewing XML Results in SQL Server Management StudioSetting the Results Pane ViewConverting Relational Data to XML FormatConverting XML Data to Relational FormatConverting XML Data into a Relational Format Using the nodes() MethodQuerying XML and Relational Data Using XQueryUsing XQUERY in QueriesSorting DataConclusionChapter 7 Quick Reference
8. Creating Views to Encapsulate Queries
Selecting Data from a Single ViewCreating a ViewCreating a View in SQL Server Management StudioCreating a View Using T-SQLObtaining Information about ViewsAccessing Dependency InformationCreating View OptionsModifying a View DefinitionUpdating Data through a ViewPartitioned ViewsMixing Data from Views and TablesWorking with Views within Client ApplicationsCreating a Data Source with Visual Studio 2005ConclusionChapter 8 Quick Reference
9. Retrieving Data Using Programmable Objects
IntroductionWorking through a Simple ProblemUnderstanding Scalar UDFsRetrieving Result SetsUsing In-Line UDFsUsing Phantom TablesUsing Table-Valued UDFsUpdating DataSimplifying ProceduresUsing Stored Procedures and UDFsCommon Language Runtime UDFs and ProceduresWorking with Statistical CalculationsWriting a Factorial Function Using Visual Basic.NETUsing Table-Valued UDFsCreating a Table-Valued UDFWorking with CLR Stored ProceduresPerforming File OperationsCreating a CLR Stored ProcedureConclusionChapter 9 Quick Reference
IV. How to Modify Data in Microsoft SQL Server 2005
10. Inserting Data in Microsoft SQL Server 2005
Using the INSERT StatementCreating an INSERT Sentence with SQL Server Management StudioCreating an INSERT SentenceUsing Special Values for Row InsertionUsing Other Forms of the INSERT StatementInserting Data through ViewsInserting a Value into a ViewUsing the WITH ClauseUsing INSTEAD OF INSERT Triggers on ViewsImporting Data into SQL Server 2005Using the BCP UtilityUsing the BULK INSERT CommandUsing Integration Services to Insert DataCreating an Export PackageEditing the PackageCreating Your Own PackageCreating a New ProjectDefining the SourceDefining the DestinationDefining the TransformationDefining the ProcessEncapsulating Insert Operations in Stored ProceduresTriggering Actions Automatically when Inserting DataDealing with Errors when Inserting DataManaging the ErrorInserting Data from ADO.NETUsing ADO.NET ObjectsConclusionChapter 10 Quick Reference
11. Deleting Data from Microsoft SQL Server 2005
Using the DELETE StatementCreating a DELETE Sentence with SQL Server Management StudioCreating a DELETE SentenceDefining the WHERE ConditionUsing Relationships to Perform DeletionsDeleting Data through ViewsUsing INSTEAD OF DELETE Triggers on ViewsEncapsulating Delete Operations in Stored ProceduresImplementing Pessimistic Concurrency for Delete OperationsChanging Row Information in a TableImplementing Optimistic Concurrency for Delete OperationsMethod 1. Comparing All ColumnsMethod 2. Using the ModifiedDate ColumnMethod 3. Using a Timestamp ColumnUsing a Timestamp column from .NETTesting a Stored ProcedureMethod 4. Using a Unique Identifier ColumnTriggering Actions Automatically when Deleting DataDealing with ErrorsData Does Not ExistData to Be Deleted Is Related to Other TablesOther ErrorsDeleting Data from ADO.NETDeleting Rows Using ADO.NETManaging Errors during the Delete ProcessEvaluating Errors in ADO.NETConclusionChapter 11 Quick Reference
12. Updating Data from Microsoft SQL Server 2005
Using the UPDATE StatementCreating an UPDATE Sentence with SQL Server Management StudioCreating an UPDATE SentenceDefining the WHERE ConditionAssigning New Values to ColumnsUsing Arithmetic to Update InformationChanging the Content of a Large (MAX) ColumnChanging the Content of a Large (BLOB) ColumnUpdating Data through ViewsUsing INSTEAD OF UPDATE Triggers on ViewsUnderstanding the INSTEAD OF UPDATE TriggerDetecting Changes in a ViewEncapsulating Update Operations in Stored ProceduresImplementing Pessimistic and Optimistic Concurrency for Update OperationsTriggering Actions Automatically when Updating DataDealing with ErrorsAssigned Value Is Higher than the Field Datatype LimitColumn Value Violates Referential IntegrityUpdating Data from ADO.NETUpdating a Row Using a Stored Procedure in ADO.NETUsing Datasets and Table AdaptersCreating a Dataset and Table AdapterCreating a User InterfaceUsing Stored Procedures with Table AdaptersCreating Stored Procedures for Table AdaptersCreating an EditorConclusionChapter 12 Quick Reference
Index
About the Author
Copyright

Overview

SQL Server 2005 is Microsoft’s next-generation data management and analysis solution that delivers enhanced scalability, availability, and security features to enterprise data and analytical applications while making them easier to create, deploy, and manage. Now you can teach yourself how to design, build, test, deploy, and maintain SQL Server databases—one step at a time. With STEP BY STEP, you work at your own pace through hands-on, learn-by-doing exercises. Instead of merely focusing on describing new features, this book shows new database programmers and administrators how to use specific features within typical business scenarios. Each chapter puts you to work, providing a highly practical learning experience that demonstrates how to build database solutions to solve common business problems.

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

Microsoft® SQL Server™ 2005 Integration Services Step by Step

Publisher Resources

ISBN: 0735622078Errata

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills