Home Page Icon
Home Page
Table of Contents for
II. How to Create a Microsoft SQL Server 2005 Database
Close
II. How to Create a Microsoft SQL Server 2005 Database
by Solid Quality Learning
Microsoft® SQL Server™ 2005: Database Essentials Step by Step
Microsoft® SQL Server™ 2005: Database Essentials Step by Step
A Note Regarding Supplemental Files
Introduction
Who This Book Is For
How This Book Is Organized
Finding Your Best Starting Point in This Book
Conventions and Features in This Book
Conventions
Other Features
System Requirements
Sample Files
Installing the Sample Files
Using the Sample Files
Uninstalling the Sample Files
Support for This Book
Questions and Comments
Acknowledgments
Authors
Technical 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 Data
Introducing Database Systems
Understanding the Requirements of a Database System
Defining the Architecture of a Database Application
Using a Monolithic Application with Data Embedded in the Application Code
Using a Monolithic Application with Data Stored in an External File
Using a Database Application with Data Managed by a Database Server
Using an Application with a Generic Data Access Layer
Using an Application with Separated Presentation, Business, and Data Access Layers
Using a Complex Application with Multiple Options for Each Layer
Conclusion
2. Installing and Setting Up Your Microsoft SQL Server 2005 Development Environment
Using a Virtual PC
Operating System Requirements for Development of SQL Server Database Applications
Recommended Productivity Tools
Using Performance Monitor
Using Network Monitor
Using Upgrade Advisor
Recommended Development Tools
Microsoft SQL Server 2005 Editions
Microsoft SQL Server Express Edition
Microsoft SQL Server Workgroup Edition
Microsoft SQL Server Standard Edition
Microsoft SQL Server Enterprise Edition
Installing SQL Server 2005
Installing SQL Server 2005
Using the Surface Area Configuration Tool
Surface Area Configuration For Services And Connections
Surface Area Configuration For Features
Conclusion
Chapter 2 Quick Reference
3. Reviewing Microsoft SQL Server 2005 Management Tools
Introduction
Using SQL Server Books Online
Accessing SQL Server Books Online
Using SQL Server Configuration Manager
Using the SQL Server 2005 Services Node
Using the SQL Server 2005 Network Configuration Node
Static Ports, Dynamic Ports, and Windows Firewall
Static ports
Dynamic ports
Windows Firewall
What is the best configuration?
Using the SQL Native Client Configuration Node
Client Protocols
Aliases
Using SQL Server Surface Area Configuration
Using Surface Area Configuration For Services And Connections
Using Surface Area Configuration For Features
Sac Utility
Using SQL Server Management Studio
Administering Servers with SQL Server Management Studio
Connecting to a Server
Registering a Server in SQL Server Management Studio
Using Object Explorer in SQL Server Management Studio
Using the Database Node
Creating SELECT Queries
Querying More than One Table
Creating More Complex SELECT Queries
Managing Other Objects
Using Database Diagrams
Creating a Database Diagram
Writing Scripts in SQL Server Management Studio
Starting a Query Using the New Query Toolbar Button
Starting a New Query Using the Database Context Menu
Retrieving Information about Your Queries
Obtaining Query Information
Retrieving Statistics
Writing Solutions and Projects in SQL Server Management Studio
Creating a Project in SQL Server Management Studio
Using SQL Server Management Studio Templates
Performing Actions with Templates
Using SQL Server Profiler
Using the SQL Server Profiler Tool and Creating a Trace
Creating a Trace with the GUI
When Should You Use SQL Server Profiler?
Accessing Event Types
Selecting Data in Event Classes
Running SQL Server Profiler and Performance Monitor Together
Running SQL Server Profiler and Performance Monitor Side by Side
Viewing Deadlocks in SQL Server Profiler
Exporting Deadlock Information
Using Database Engine Tuning Advisor
Analyzing Database Engine Tuning Advisor
Working with Database Engine Tuning Advisor
Starting Database Engine Tuning Advisor
Evaluating the Recommendations
Evaluating the Impact of Database Engine Tuning Advisor Recommendations
Managing Tuning Options
Managing Database Engine Tuning Advisor Sessions
Exporting a Session Definition
Using SQLCmd
Working with the SQLCmd Utility
Connecting to a Default SQL Server Instance
Executing Script Files
Executing the DTA.sql Script by Using the SQLCmd Utility
Conclusion
Chapter 3 Quick Reference
4. Gathering and Understanding Business Requirements before Creating Database Objects
Understanding Business Processes and User Interaction Requirements
Defining the Business Problem
Capturing Requirements
Writing User Surveys
Interviewing
Shadowing
Writing Requirements
Understanding Business Data and Its Lifetime
Architecture and Operational Requirements
Availability
Interoperability
Manageability
Performance
Reliability
Scalability
Security
Predicting the Volume of Information to Store and Manage and Predicting Database Utilization
Using Transaction Cost Analysis
Step 1: Compiling a Users Profile
Step 2: Executing Discrete Tests
Creating the Script
Step 3: Measuring the Cost of Each Operation
Creating a Windows Performance Log
Reviewing the Captured Information
Step 4: Calculating the Cost of an Average Profile
Step 5: Calculating the Maximum Capacity
Step 6: Verifying the Maximum Capacity
Conclusion
Chapter 4 Quick Reference
5. Designing a Database to Solve Business Needs
Designing a Database Conceptually
Validating Business Requirements through Conceptual Models
Creating the First Model
Diagramming the Model
Creating a Conceptual ER Diagram Using Microsoft Visio
Creating Entities
Modeling Entities in Visio 2003
Adding Attributes
Modeling Attributes in Visio 2003
Adding Relationships
Modeling a Parent-Child Relationship
Other Types of Relationships
Modeling intersection relationships
Modeling an Intersection Relationship
Modeling multi-intersection relationships
Modeling self-referencing relationships
Modeling a Self-Referencing Relationship
Approving the Model
Designing a Database Logically to Leverage the Relational Engine
Creating Columns to Capture Object Attributes
Declaring a Datatype Graphically
Defining a Datatype Using Microsoft Visio 2003
Using T-SQL Code to Define a Datatype
Numeric Attributes
Integers and quantities
Precise numbers and accounting data
Scientific and engineering data
String Attributes
Unicode and character datatypes
Fixed and variable-length datatypes
String syntax
Long text datatypes
Choosing the appropriate datatype
Date and Time Attributes
Binary Data
Complex Attributes
T-SQL user-defined datatypes
Common language runtime-based UDTs
Validating the Data
NOT NULL Constraints
DEFAULT Constraints
PRIMARY KEY Constraints
UNIQUE Constraints
CHECK Constraints
FOREIGN KEY Constraints
Physically Creating a Database
Selecting an Appropriate Storage Design for a Database
What Is a Database?
How Is Information Stored?
Designing Database Storage
Creating a Database
Using SQL Server Management Studio to Create a Database
Using T-SQL to Create a Database
Designing Database Schemas to Logically Group Database Objects
Using SQL Server Management Studio to Create Database Schemas
Using T-SQL to Create Database Schemas
Creating Tables to Implement a Design
Using SQL Server Management Studio to Create Tables
Defining the Primary Key in a Table Using SQL Server Management Studio
Defining a CHECK Constraint Using SQL Server Management Studio
Using T-SQL to Create Tables
Conclusion
Chapter 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 Components
Avoiding Deprecated MDAC Components
Outlining the MDAC Architecture
Understanding Open Database Connectivity (ODBC)
Creating a Data Source Name
Creating an ODBC Data Source
Programming ODBC Applications
Understanding OLE DB and ADO
Using an ADO Connection Object
Creating an Appropriate Connection String
Using an ADO Command Object
Using an ADO Recordset Object
Understanding ADO.NET
Outlining the ADO.NET Architecture
Using ADO.NET Namespaces
Introducing the .NET SQL Server Data Provider
Using the SqlConnection Class
Using the SqlCommand Class
Using the SqlDataReader Class
Using the SqlDataAdapter Class
Creating a SqlDataAdapter Graphically
Using the TableAdapter Class
Creating a TableAdapter
Introducing SQL Native Client
Using the XML Datatype
Using Multiple Active Result Sets (MARS)
Using Query Notification
Creating an Application that Uses Query Notification
Conclusion
Chapter 6 Quick Reference
7. Selecting the Data You Need
Selecting Data from a Single Table
Running a SELECT Statement in SQL Server Management Studio
Using AND and OR Operators
Comparing NULL Values
Using the CASE Statement
Using Search Arguments
Selecting Data from Multiple Tables
Using Aliases
Using the INNER JOIN Syntax
Using More than Two Tables
Using LEFT JOIN
Using RIGHT JOIN
Using FULL JOIN
Reading Single Values
Using System-Supplied Scalar Functions
Aggregate Functions
Configuration Functions
Cursor Functions
Date and Time Functions
Mathematical Functions
Metadata Functions
Security Functions
String Functions
System Functions
System Statistical Functions
Ranking Functions
Designing and Using Scalar UDFs
Designing UDFs that Do Not Access Tables or Views
Designing UDFs that Access Tables or Views
Designing and Using Stored Procedures to Retrieve Scalar Values
Using Output Parameters
Reading Relational and XML Data
Viewing XML Results in SQL Server Management Studio
Setting the Results Pane View
Converting Relational Data to XML Format
Converting XML Data to Relational Format
Converting XML Data into a Relational Format Using the nodes() Method
Querying XML and Relational Data Using XQuery
Using XQUERY in Queries
Sorting Data
Conclusion
Chapter 7 Quick Reference
8. Creating Views to Encapsulate Queries
Selecting Data from a Single View
Creating a View
Creating a View in SQL Server Management Studio
Creating a View Using T-SQL
Obtaining Information about Views
Accessing Dependency Information
Creating View Options
Modifying a View Definition
Updating Data through a View
Partitioned Views
Mixing Data from Views and Tables
Working with Views within Client Applications
Creating a Data Source with Visual Studio 2005
Conclusion
Chapter 8 Quick Reference
9. Retrieving Data Using Programmable Objects
Introduction
Working through a Simple Problem
Understanding Scalar UDFs
Retrieving Result Sets
Using In-Line UDFs
Using Phantom Tables
Using Table-Valued UDFs
Updating Data
Simplifying Procedures
Using Stored Procedures and UDFs
Common Language Runtime UDFs and Procedures
Working with Statistical Calculations
Writing a Factorial Function Using Visual Basic.NET
Using Table-Valued UDFs
Creating a Table-Valued UDF
Working with CLR Stored Procedures
Performing File Operations
Creating a CLR Stored Procedure
Conclusion
Chapter 9 Quick Reference
IV. How to Modify Data in Microsoft SQL Server 2005
10. Inserting Data in Microsoft SQL Server 2005
Using the INSERT Statement
Creating an INSERT Sentence with SQL Server Management Studio
Creating an INSERT Sentence
Using Special Values for Row Insertion
Using Other Forms of the INSERT Statement
Inserting Data through Views
Inserting a Value into a View
Using the WITH Clause
Using INSTEAD OF INSERT Triggers on Views
Importing Data into SQL Server 2005
Using the BCP Utility
Using the BULK INSERT Command
Using Integration Services to Insert Data
Creating an Export Package
Editing the Package
Creating Your Own Package
Creating a New Project
Defining the Source
Defining the Destination
Defining the Transformation
Defining the Process
Encapsulating Insert Operations in Stored Procedures
Triggering Actions Automatically when Inserting Data
Dealing with Errors when Inserting Data
Managing the Error
Inserting Data from ADO.NET
Using ADO.NET Objects
Conclusion
Chapter 10 Quick Reference
11. Deleting Data from Microsoft SQL Server 2005
Using the DELETE Statement
Creating a DELETE Sentence with SQL Server Management Studio
Creating a DELETE Sentence
Defining the WHERE Condition
Using Relationships to Perform Deletions
Deleting Data through Views
Using INSTEAD OF DELETE Triggers on Views
Encapsulating Delete Operations in Stored Procedures
Implementing Pessimistic Concurrency for Delete Operations
Changing Row Information in a Table
Implementing Optimistic Concurrency for Delete Operations
Method 1. Comparing All Columns
Method 2. Using the ModifiedDate Column
Method 3. Using a Timestamp Column
Using a Timestamp column from .NET
Testing a Stored Procedure
Method 4. Using a Unique Identifier Column
Triggering Actions Automatically when Deleting Data
Dealing with Errors
Data Does Not Exist
Data to Be Deleted Is Related to Other Tables
Other Errors
Deleting Data from ADO.NET
Deleting Rows Using ADO.NET
Managing Errors during the Delete Process
Evaluating Errors in ADO.NET
Conclusion
Chapter 11 Quick Reference
12. Updating Data from Microsoft SQL Server 2005
Using the UPDATE Statement
Creating an UPDATE Sentence with SQL Server Management Studio
Creating an UPDATE Sentence
Defining the WHERE Condition
Assigning New Values to Columns
Using Arithmetic to Update Information
Changing the Content of a Large (MAX) Column
Changing the Content of a Large (BLOB) Column
Updating Data through Views
Using INSTEAD OF UPDATE Triggers on Views
Understanding the INSTEAD OF UPDATE Trigger
Detecting Changes in a View
Encapsulating Update Operations in Stored Procedures
Implementing Pessimistic and Optimistic Concurrency for Update Operations
Triggering Actions Automatically when Updating Data
Dealing with Errors
Assigned Value Is Higher than the Field Datatype Limit
Column Value Violates Referential Integrity
Updating Data from ADO.NET
Updating a Row Using a Stored Procedure in ADO.NET
Using Datasets and Table Adapters
Creating a Dataset and Table Adapter
Creating a User Interface
Using Stored Procedures with Table Adapters
Creating Stored Procedures for Table Adapters
Creating an Editor
Conclusion
Chapter 12 Quick Reference
Index
About the Author
Copyright
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
3. Reviewing Microsoft SQL Server 2005 Management Tools
Next
Next Chapter
4. Gathering and Understanding Business Requirements before Creating Database Objects
Part II. How to Create a Microsoft SQL Server 2005 Database
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset