Beginning SQL Server 2005 for Developers: From Novice To Professional

by Dewson, Robin

• ISBN13: 9781590595886
• ISBN10: 1590595882
• Format: Paperback
• Copyright: 2006-01-30
• Publisher: Springer-Verlag New York Inc

Summary

SQL Server 2005 will increase your programming options, productivity, analysis, and database management. If you have some basic knowledge of relational databases and want to start a career as a developer using SQL Server, then this book is your ideal first step. It explains the core jobs and roles for developing a database in both SQL Server 2000 and 2005. This book features practical steps to help you overcome issues you're likely to encounter. You'll learn to use SQL for querying, inserting, updating, and deleting data. You'll also learn how to back up and restore databases for basic administration in SQL Server. Further, you'll cover how to build a complete database, from the fundamentals of relational database design to table and index creation.

Table of Contents

About the Author	xvii
About the Technical Reviewer	xix
Acknowledgments	xxi
Introduction	xxiii
SQL Server 2005 Overview and Installation	1	(24)
Why SQL Server 2005?	2	(1)
Evolution of SQL Server	3	(1)
Hardware Requirements	4	(2)
CPU	4	(1)
Memory	5	(1)
Hard Disk Space	5	(1)
Operating System Requirements	5	(1)
The Example	6	(1)
Installation	6	(1)
A Standard Installation	6	(12)
Preparing to Install	7	(11)
Security	18	(5)
Services Accounts	18	(1)
Looking at the Authentication Mode	18	(4)
The sa Login	22	(1)
Summary	23	(2)
SQL Server Management Studio	25	(28)
A Quick Overview of SSMS	25	(9)
Examining SSMS's Options	34	(11)
Environment Node	34	(3)
Source Control Node	37	(1)
Text Editor Node	38	(2)
Query Execution Node	40	(2)
Query Results Node	42	(3)
Query Editor	45	(2)
Surface Area Configuration Tool	47	(4)
Summary	51	(2)
Database Design and Creation	53	(42)
Defining a Database	54	(1)
Databases Within SQL Server	55	(3)
master	56	(1)
tempdb	56	(1)
model	57	(1)
msdb	58	(1)
AdventureWorks/AdventureWorksDW	58	(1)
Choosing the Database System Type	58	(2)
OLTP	58	(1)
OLAP	59	(1)
Example System Choice	60	(1)
Gathering the Data	60	(2)
Determining the Information to Store in the Database	62	(3)
Financial Products	63	(1)
Customers	64	(1)
Customer Addresses	64	(1)
Shares	64	(1)
Transactions	64	(1)
External and Ignored Information	65	(1)
Building Relationships	65	(6)
Using Keys	65	(2)
Creating Relationships	67	(3)
More on Foreign Keys	70	(1)
Normalization	71	(5)
Each Entity Should Have a Unique Identifier	73	(1)
Only Store Information That Directly Relates to That Entity	73	(1)
Avoid Repeating Values or Columns	73	(1)
Normalization Forms	73	(2)
Denormalization	75	(1)
Creating the Sample Database	76	(18)
Creating a Database in SQL Server Management Studio	76	(13)
Dropping the Database in SQL Server Management Studio	89	(3)
Creating a Database in a Query Pane	92	(2)
Summary	94	(1)
Security	95	(24)
Logins	95	(10)
Server Logins and Database Users	105	(1)
Roles	105	(6)
Fixed Server Roles	105	(2)
Database Roles	107	(1)
Application Roles	108	(3)
Schemas	111	(2)
Before You Can Proceed with Your Solution	113	(4)
Summary	117	(2)
Defining Tables	119	(34)
What Is a Table?	120	(1)
Defining a Table: SQL Server Management Studio	121	(5)
Different Table Data Types	121	(4)
Different Program Data Types	125	(1)
Columns Are More Than Simple Data Repositories	126	(1)
Default Values	126	(1)
Generating Identity Values	126	(1)
The Use of Null Values	127	(1)
Why Define a Column to Allow NULL?	127	(1)
Image and Large Text Storage in SQL Server	127	(1)
Creating a Table in SQL Server Management Studio	128	(6)
Defining a Table Through the Query Editor	134	(2)
Defining a Table: Using a Template	136	(3)
Creating and Altering a Template	139	(2)
The Alter Table Command	141	(1)
Defining the Remaining Tables	142	(1)
Setting a Primary Key	143	(1)
Creating a Relationship	144	(6)
Check Existing Data on Creation	148	(1)
Enforce Foreign Key Constraints	149	(1)
Delete Rule/Update Rule	149	(1)
Using the Alter Table SQL Statement	150	(1)
Summary	151	(2)
Creating Indexes and Database Diagramming	153	(32)
What Is an Index?	153	(3)
Types of Indexes	154	(2)
Uniqueness	156	(1)
Determining What Makes a Good Index	156	(3)
Using Low-Maintenance Columns	156	(1)
Primary and Foreign Keys	157	(1)
Finding Specific Records	157	(1)
Using Covering Indexes	157	(1)
Looking for a Range of Information	158	(1)
Keeping the Data in Order	158	(1)
Determining What Makes a Bad Index	159	(1)
Using Unsuitable Columns	159	(1)
Choosing Unsuitable Data	159	(1)
Including Too Many Columns	159	(1)
Including Too Few Records in the Table	159	(1)
Reviewing Your Indexes for Performance	160	(1)
Creating an Index	160	(13)
Creating an Index with the Table Designer	161	(2)
Indexes and Statistics	163	(1)
The Create Index Syntax	164	(2)
Creating an Index in Query Editor: Template	166	(4)
Creating an Index in Query Editor: SQL Code	170	(3)
Dropping an Index	173	(1)
Altering an Index in Query Editor	174	(2)
Diagramming the Database	176	(7)
Database Diagramming Basics	176	(1)
The SQL Server Database Diagram Tool	177	(1)
The Default Database Diagram	178	(2)
The Database Diagram Toolbar	180	(3)
Summary	183	(2)
Database Backups, Recovery, and Maintenance	185	(60)
Transaction Logs	186	(2)
Backup Strategies	188	(1)
When Problems May Occur	189	(1)
Taking a Database Offline	190	(1)
Backing the Data Up	191	(16)
Backing Up the Database Using T-SQL	196	(8)
Transaction Log Backup Using T-SQL	204	(3)
Restoring a Database	207	(7)
Restoring Using SQL Server Management Studio	207	(3)
Restoring Using T-SQL	210	(4)
Detaching and Attaching a Database	214	(8)
Detaching and Attaching Using SQL Server Management Studio	215	(5)
Detaching and Attaching Using T-SQL	220	(2)
Producing SQL Script for the Database	222	(8)
Maintaining Your Database	230	(1)
Creating a Database Maintenance Plan	230	(13)
Summary	243	(2)
Working with the Data	245	(64)
The T-SQL Insert Command Syntax	246	(1)
Insert SQL Command	247	(7)
Default Values	249	(1)
Using NULL Values	249	(5)
DBCC CHECKIDENT	254	(1)
Column Constraints	255	(7)
ADD CONSTRAINT	256	(6)
Dealing with Several Records at Once	262	(1)
Inserting Several Records in a Query Batch	262	(1)
Retrieving Data	263	(1)
Using SQL Server Management Studio to Retrieve Data	264	(2)
The Select Statement	266	(2)
Naming the Columns	268	(1)
The First Searches	269	(2)
Varying the Output Display	271	(2)
Limiting the Search: The Use of Where	273	(5)
SET ROWCOUNT n	276	(1)
TOP n	277	(1)
TOP n PERCENT	278	(1)
String Functions	278	(2)
Order! Order!	280	(2)
The LIKE Operator	282	(2)
Creating Data: SELECT INTO	284	(2)
Who Can Add, Delete, and Select Data	286	(5)
Securables	291	(1)
Updating Data	291	(5)
The Update Command	292	(1)
Updating Data Within Query Editor	293	(3)
Transactions	296	(7)
Begin Tran	298	(1)
Commit Tran	298	(1)
Rollback Tran	298	(1)
Locking Data	299	(1)
Updating Data: Using Transactions	299	(2)
Nested Transactions	301	(2)
Deleting Data	303	(3)
Delete Syntax	303	(1)
Using the Delete Statement	304	(2)
Truncating a Table	306	(1)
Dropping a Table	307	(1)
Summary	308	(1)
Building a View	309	(26)
What Is a View?	310	(1)
Using Views for Security	310	(1)
Encrypting View Definitions	311	(1)
Creating a View: SQL Server Management Studio	312	(6)
Creating a View Using a View	318	(6)
Create View Syntax	324	(2)
Creating a View: A Query Editor pane	326	(1)
Creating a View: Schemabinding	327	(3)
Indexing a View	330	(2)
Summary	332	(3)
Stored Procedures	335	(24)
What Is a Stored Procedure?	335	(1)
Create Procedure Syntax	336	(3)
Returning a Set of Records	339	(1)
Creating a Stored Procedure: Management Studio	339	(5)
Different Methods of Executing	344	(1)
No EXEC	344	(1)
With EXEC	344	(1)
Using Return	344	(4)
Controlling the Flow	348	(7)
If... Else	349	(1)
Begin...End	349	(1)
While...Break Statement	350	(2)
CASE Statement	352	(3)
Bringing It All Together	355	(2)
Summary	357	(2)
T-SQL Essentials	359	(44)
Using More Than One Table	359	(6)
Variables	365	(2)
Temporary Tables	367	(2)
Aggregations	369	(3)
Count/Count_Big	369	(1)
SUM	370	(1)
MAX/MIN	371	(1)
AVG	372	(1)
GROUP BY	372	(2)
HAVING	374	(1)
Distinct Values	375	(1)
Functions	376	(15)
Date and Time	376	(4)
String	380	(6)
System Functions	386	(5)
RAISERROR	391	(3)
Error Handling	394	(1)
@@Error	395	(1)
Try...Catch	396	(5)
Summary	401	(2)
Advanced T-SQL	403	(24)
Subqueries	403	(4)
IN	405	(1)
EXISTS	406	(1)
The APPLY Operator	407	(2)
CROSS APPLY	407	(1)
OUTER APPLY	408	(1)
Common Table Expressions	409	(3)
Recursive CTE	410	(2)
Pivoting Data	412	(3)
PIVOT	413	(1)
UNPIVOT	414	(1)
Ranking Functions	415	(6)
ROW_NUMBER	416	(2)
RANK	418	(1)
DENSE_RANK	419	(1)
NTILE	420	(1)
Using the MAX Data Type	421	(5)
Image LOB	424	(2)
Summary	426	(1)
Triggers	427	(24)
What Is a Trigger?	427	(1)
The DML Trigger?	428	(1)
Create Trigger Syntax for DML triggers	429	(2)
Why Not Use a Constraint?	431	(1)
Deleted and Inserted Logical Tables	431	(1)
Creating a DML FOR Trigger	432	(4)
Checking Specific Columns	436	(8)
Using Update()	436	(5)
Using COLUMNS_UPDATED()	441	(3)
DDL Triggers	444	(3)
DDL_DATABASE_LEVEL_EVENTS	445	(2)
Dropping a DDL trigger	447	(1)
Eventdata()	447	(3)
Summary	450	(1)
SQL Server 2005 Reporting Services	451	(22)
What Is Reporting Services?	451	(1)
Reporting Services Architecture from 5000 Feet	452	(1)
Reporting Services Architecture: A Closer Look	453	(5)
The Application Layer	454	(2)
The Server Layer	456	(2)
The Data Layer	458	(1)
Building Your First Report Using Report Wizard	458	(4)
Building a Report from Scratch	462	(10)
Data-Related Elements	463	(1)
Report Items	463	(1)
Reports Structure	464	(8)
Summary	472	(1)
Appendix Glossary of Terms	473	(12)
Index	485