Microsoft Sams Teach Yourself SQL Server 2005 Express in 24 Hours

by Balter, Alison

• ISBN13: 9780672327414
• ISBN10: 0672327414
• Edition: 1st
• Format: Paperback
• Copyright: 2006-06-16
• Publisher: Sams Publishing

Summary

Written with clarity and a down-to-earth approach,Sams Teach Yourself SQL Server 2005 Express in 24 Hourscovers the basics of Microsoft's latest version of SQL Server. Expert authorAlison Baltertakes you from basic concepts to an intermediate level in 24 one-hour lessons. You will learn all of the basic tasks necessary for the administration of SQL Server 2005. You will also learn how to write and fully utilize: SQL Server stored procedures Functions Triggers using T-SQL, VB.NET, and C# You will also learn how to output SQL Server data to reports and the Internet.Sams Teach Yourself SQL Server 2005 Express in 24 Hoursis a well-organized, authoritative introduction that will quickly have you up and running with SQL Server 2005.

Author Biography

Alison Balter is the founder of InfoTechnology Partners, Inc.

Table of Contents

Introduction	1	(6)
Part I: Introduction to SQL Server
Hour 1: Basic Relational Database Design	7	(18)
Database Basics	7	(1)
System Versus User Objects	8	(1)
Table Basics	9	(1)
Relational Database Design Concepts	9	(8)
Database Diagram Basics	17	(1)
View Basics	17	(1)
Stored Procedure Basics	18	(1)
User-Defined Function Basics	19	(1)
Trigger Basics	20	(1)
SQL Server Stored Procedures Versus Transact-SQL Statements Stored on Local Computers	21	(1)
Summary	21	(1)
Q&A	22	(1)
Workshop	22	(1)
Activities	23	(2)
Hour 2: SQL Server Basics	25	(18)
Versions of SQL Server 2005 Available	25	(3)
Installing SQL Server Express	28	(9)
Getting Started with the SQL Server Management Studio Express	37	(3)
Summary	40	(1)
Q&A	40	(1)
Workshop	40	(1)
Activities	41	(2)
Hour 3: Getting to Know the SQL Server Management Studio	43	(18)
Microsoft SQL Server Management Studio Express	43	(6)
Creating a SQL Server Database	49	(7)
Summary	56	(1)
Q&A	56	(1)
Workshop	57	(1)
Activities	58	(3)
Part II: Working with Tables, Views, and Stored Procedures
Hour 4: SQL Server Tables	61	(18)
Creating SQL Server Tables	61	(3)
Working with Constraints	64	(5)
Identity Columns	69	(1)
Working with Computed Columns	70	(1)
Working with User-Defined Data Types	71	(2)
Adding and Modifying Indexes	73	(3)
Summary	76	(1)
Q&A	76	(1)
Workshop	77	(1)
Activities	78	(1)
Hour 5: Working with Table Relationships	79	(18)
An Introduction to Relationships	79	(2)
Working with Database Diagrams	81	(5)
Working with Table Relationships	86	(2)
How to Designate Table and Column Specifications	88	(2)
How to Add a Relationship Name and Description	90	(1)
How to Determine when Foreign Key Relationships Constrain the Data Entered in a Column	90	(3)
Designating Insert and Update Specifications	93	(2)
Summary	95	(1)
Q&A	95	(1)
Workshop	95	(1)
Activities	96	(1)
Hour 6: Getting to Know the Select Statement	97	(14)
Introducing T-SQL	97	(1)
Working with the Select Statement	98	(1)
Adding on the From Clause	99	(1)
Including the Where Clause	100	(1)
Using the Order by Clause	101	(2)
Adding the Distinct Keyword	103	(2)
Working with the For XML Clause	105	(2)
Creating Top Values Queries	107	(1)
Summary	107	(1)
Q&A	108	(1)
Workshop	108	(1)
Activities	109	(2)
Hour 7: Building SQL Statements Based on Multiple Tables	111	(16)
Working with Join Types	111	(3)
Working with the Group by Clause	114	(1)
Including Aggregate Functions in Your SQL Statements	115	(5)
Taking Advantage of the Having Clause	120	(4)
Summary	124	(1)
Q&A	124	(1)
Workshop	125	(1)
Activities	125	(2)
Hour 8: Modifying Data with Action Queries	127	(4)
Modifying Data with Action Queries	127	(2)
Summary	129	(1)
Q&A	129	(1)
Workshop	130	(1)
Activities	130	(1)
Hour 9: Getting to Know the T-SQL Functions	131	(18)
Working with Numeric Functions	131	(2)
Taking Advantage of String Functions	133	(6)
Exploring the Date/Time Functions	139	(5)
Working with Nulls	144	(2)
Summary	146	(1)
Q&A	147	(1)
Workshop	147	(1)
Activities	148	(1)
Hour 10: Working with SQL Server Views	149	(12)
An Introduction to Views	149	(6)
Using T-SQL to Create or Modify a View	155	(2)
Summary	157	(1)
Q&A	157	(1)
Workshop	158	(1)
Activities	159	(2)
Hour 11: Creating Complex Views	161	(16)
Creating Views Based on Data in Multiple Tables	161	(2)
Views and Subqueries	163	(1)
Views and Outer Joins	163	(1)
Views and Top Values	164	(1)
Sorting View Results	164	(1)
Views and Functions	165	(1)
Views and Built-in Functions	165	(1)
Using Views to Secure Data	165	(1)
Modifying Data in a View	166	(1)
Modifying Views	167	(1)
Renaming Views	167	(1)
Deleting Views	167	(1)
Indexed Views	168	(4)
Optimizing the Views That You Build	172	(3)
Summary	175	(1)
Q&A	175	(1)
Workshop	175	(1)
Activities	176	(1)
Hour 12: Using T-SQL to Design SQL Server Stored Procedures	177	(18)
The Basics of Working with Stored Procedures	178	(3)
Declaring and Working with Variables	181	(1)
Controlling the Flow	182	(10)
Summary	192	(1)
Q&A	192	(1)
Workshop	192	(1)
Activities	193	(2)
Hour 13: Important Stored Procedure Techniques	195	(18)
The Set Nocount Statement	195	(1)
Using the @@ Functions	196	(4)
Working with Parameters	200	(3)
Modifying Data with Stored Procedures	203	(3)
Errors and Error Handling	206	(3)
Stored Procedures and Transactions	209	(1)
Summary	210	(1)
Q&A	210	(1)
Workshop	211	(1)
Activities	212	(1)
Hour 14: Stored Procedure Special Topics	213	(10)
Stored Procedures and Temporary Tables	213	(1)
Stored Procedures and Security	214	(1)
Building and Working with User-Defined Functions	215	(2)
Creating and Working with Triggers	217	(4)
Summary	221	(1)
Q&A	221	(1)
Workshop	222	(1)
Activities	222	(1)
Hour 15: ADO.NET and SQL Server	223	(26)
The History of Data Access	223	(1)
The Basics of ADO.NET	224	(16)
Building Queries That Modify Data	240	(5)
Summary	245	(1)
Q&A	245	(1)
Workshop	246	(1)
Activities	247	(2)
Hour 16: Using VB to Design SQL Server Stored Procedures	249	(22)
Executing a Simple Stored Procedure	249	(5)
Declaring and Working with Variables	254	(2)
Controlling Program Flow	256	(3)
Working with Parameters	259	(5)
Modifying Data with Stored Procedures	264	(5)
Summary	269	(1)
Q&A	269	(1)
Workshop	269	(1)
Activities	270	(1)
Hour 17: Visual Basic .NET Special Topics	271	(16)
Working with Temporary Tables	271	(3)
Including Error Handling in the Stored Procedures that You Build	274	(5)
Using Transactions to Protect Your Data	279	(4)
Summary	283	(1)
Q&A	283	(1)
Workshop	284	(1)
Activities	285	(2)
Hour 18: Using C# to Design SQL Server Stored Procedures	287	(22)
Executing a Simple Stored Procedures	287	(5)
Declaring and Working with Variables	292	(2)
Controlling Program Flow	294	(3)
Working with Parameters	297	(4)
Modifying Data with Stored Procedures	301	(5)
Summary	306	(1)
Q&A	307	(1)
Workshop	307
Activities	305	(4)
Hour 19: C# Special Topics	309	(16)
Working with Temporary Tables	309	(3)
Including Error Handling in the Stored Procedures That You Build	312	(6)
Using Transactions to Protect Your Data	318	(4)
Summary	322	(1)
Q&A	322	(1)
Workshop	322	(1)
Activities	323	(2)
Hour 20: Debugging the Stored Procedures That You Write	325	(24)
Harnessing the Power of the Immediate Window	325	(5)
Invoking the Debugger	330	(1)
Using Breakpoints to Troubleshoot	331	(2)
Stepping Through Code	333	(5)
Setting the Next Statement to Execute	338	(1)
Viewing the Call Stack	339	(1)
Working with the Locals Window	340	(1)
Working with Watch Expressions	341	(3)
Summary	344	(1)
Q&A	344	(1)
Workshop	345	(1)
Activities	345	(4)
Part III: Security and Administration
Hour 21: SQL Server Authentication	349	(20)
The Basics of Security	349	(1)
Types of Authentication Available	350	(1)
How to Create Logins	351	(5)
How to Create Roles	356	(9)
Summary	365	(1)
Q&A	365	(1)
Workshop	366	(1)
Activities	367	(2)
Hour 22: SQL Server Permissions Validation	369	(18)
The Types of Permissions Available	369	(9)
Getting to Know Table Permissions	378	(2)
Getting to Know View Permissions	380	(2)
Getting to Know Stored Procedure Permissions	382	(1)
Getting to Know Function Permissions	382	(1)
Implementing Column-Level Security	382	(2)
Summary	384	(1)
Q&A	384	(1)
Workshop	385	(1)
Activities	385	(2)
Hour 23: Configuring, Maintaining, and Tuning SQL Server	387	(16)
Selecting and Tuning Hardware	387	(2)
How to Configure and Tune SQL Server	389	(7)
How to Maintain Your Databases	396	(5)
How to Maintain Tables and Other Objects	401	(1)
Summary	401	(1)
Q&A	401	(1)
Workshop	402	(1)
Activities	402	(1)
Hour 24: Performance Monitoring	403	(14)
Executing Queries in SQL Server Management Studio Express	403	(3)
Displaying and Analyzing the Estimated Execution Plan for a Query	406	(3)
Adding Indexes to Enable Queries to Execute More Efficiently	409	(3)
Setting Query Options	412	(2)
Summary	414	(1)
Q&A	414	(1)
Workshop	415	(1)
Activities	415	(2)
Appendix A: Using the NorthWind Sample Database	417	(4)
What You Need to Know About Installing the Sample Database Files	417	(1)
Attaching to the NorthWind Sample Database File	418	(3)
Index	421