Beginning SQL Server 2000 Programming

by Dewson, Robin

• ISBN13: 9781590592526
• ISBN10: 1590592522
• eBook ISBN(s): 9781430208044
• Format: Paperback
• Copyright: 2003-09-01
• Publisher: Springer-Verlag New York Inc
• More Book Details
• Purchase Benefits

Summary

Microsoft SQL Server 2000 provides enterprise-class database solutions, offering interactive development tools and complete management of your relational database applications.In this book youll build a sample database with SQL Server and in the process learn how to design with solutions in mind. If you are migrating from a desktop solution such as Access to SQL Server, this book will provide you with clear and practical demonstrations of the relevant tasks at every stage in the process. Once youve mastered the basics of SQL Server, youll move on to learn more advanced techniques. By the end of this book, youll have a firm grasp of the most important SQL Server features and be able to develop your own effective database solutions.

Table of Contents

Introduction	1	(6)
What's Covered in this Book	1	(2)
Who is this Book For?	3	(1)
What You Need to Use this Book	3	(1)
Conventions	4	(1)
Tell Us What You Think	5	(1)
Errata & Updates	5	(2)
Chapter 1: SQL Server 2000 Installation	7	(52)
Why SQL Server 2000?	8	(1)
SQL Server Versions	9	(3)
Enterprise Edition	9	(1)
Enterprise Evaluation Edition	10	(1)
Developer Edition	10	(1)
Standard Edition	11	(1)
Personal Edition	11	(1)
SQL Server CE Edition	11	(1)
Desktop Edition	12	(1)
Hardware Requirements	12	(2)
CPU	12	(1)
Memory	13	(1)
Hard Disk	13	(1)
Screen Resolution	13	(1)
Other Hardware Considerations	14	(1)
Operating System Requirements	14	(2)
How Can You Tell?	16	(4)
Which Edition for this Book?	20	(1)
The Example	20	(1)
Installation	20	(36)
Installing SQL Server 2000 Developer Edition	21	(11)
Advanced Options	32	(2)
Other Types of Installation	34	(3)
The Connectivity Only Option	34	(1)
Client Tools Only	34	(1)
Multiple Instances	34	(3)
Typical, Minimum, or Custom?	37	(3)
Typical	37	(1)
Minimum	38	(1)
Custom	38	(1)
Destination Folder	39	(1)
Security	40	(50)
Services Accounts	40	(8)
Authentication Mode	48	(6)
Mixed Mode	54	(1)
The sa Login	55	(1)
Summary	56	(3)
Chapter 2: Managing SQL Server	59	(30)
Enterprise Manager	60	(8)
Service Manager	68	(2)
Configuring SQL Server Installations	70	(11)
Registering a New SQL Server	81	(5)
Summary	86	(3)
Chapter 3: Query Analyzer	89	(48)
Starting Query Analyzer	90	(6)
Starting from Enterprise Manager	90	(6)
The Object Browser	96	(4)
System Objects	96	(2)
Common Objects	98	(1)
Templates	98	(2)
The Query Pane	100	(1)
The Results Pane	101	(1)
Menu Options	101	(12)
The File Menu	101	(1)
The Edit Menu	101	(1)
Query Menu	101	(12)
The Tools Menu	113	(8)
The Query Analyzer Toolbar	121	(6)
Using Query Analyzer	127	(8)
Creating a New Login Account	127	(3)
Altering the Default Database for a User	130	(5)
Summary	135	(2)
Chapter 4: Database Analysis and Design	137	(14)
Gathering the Data	138	(2)
The information to Record	140	(2)
Club Details	140	(1)
Players	141	(1)
Matches	141	(1)
League	141	(1)
Society Groups	142	(1)
Newsletters	142	(1)
External and ignored information	142	(1)
Normalization	143	(4)
Each Entity Should Have a Unique Identifier	143	(1)
Only Store Information that Directly Relates to that Entity	143	(1)
Avoid Repeating Values or Columns	144	(1)
First Normal Form (INF)	144	(1)
Second Normal Form (2NF)	145	(1)
Third Normal Form (3NF)	145	(1)
Denormalization	146	(1)
Relationships	147	(1)
Database Diagram	147	(1)
Using the System, OLTP or OLAP?	148	(1)
OLTP	148	(1)
Data Placement	148	(1)
Backups	148	(1)
Indexes	148	(1)
OLAP	149	(1)
Summary	149	(2)
Chapter 5: Creating the Sample Database	151	(38)
Defining a Database	152	(1)
Transaction Logs	153	(1)
Databases within SQL Server	154	(3)
master	154	(1)
tempdb	155	(1)
model	156	(1)
msdb	156	(1)
pubs	156	(1)
Northwind	157	(1)
Creating our Sample Database	157	(30)
Creating a Database in Enterprise Manager	158	(12)
Viewing within Enterprise Manager	164	(6)
Dropping the Database in Enterprise Manager	170	(3)
Creating a Database - Using a Wizard	173	(5)
Creating a Database in Query Analyzer	178	(7)
Viewing in Query Analyzer	183	(1)
Using Commands - Query Analyzer	184	(1)
Dropping a Database in Query Analyzer	185	(2)
Summary	187	(2)
Charter 6: Defining Tables	189	(40)
What is a Table?	190	(1)
Before You Can Define a Table	191	(15)
Server Roles	204	(2)
Defining a Table - Enterprise Manager	206	(6)
Defining a Table Through Query Analyzer	212	(3)
Generating IDENTITY values	215	(1)
The Use of NULL Values	215	(1)
Why Define a Column to Allow NULL?	216	(1)
Defining a Table - Using a Template	216	(5)
Checking if a Table has Already Been Created	220	(1)
The ALTER TABLE Command	221	(1)
Defining the Remaining Tables	221	(1)
Different Data Types	222	(4)
Image and Large Text Storage in SQL Server	226	(1)
Summary	227	(2)
Chapter 7: Creating Indexes	229	(36)
What is an Index?	230	(3)
What Types of Indexes are There?	231	(1)
Clustered	231	(1)
Non-Clustered	231	(1)
Primary	231	(1)
Foreign/Referencing Key	232	(1)
Candidate/Alternate Key	232	(1)
Unique Key	232	(1)
Uniqueness	232	(1)
Determining What Makes a Good Index	233	(2)
Low Maintenance	233	(1)
Primary and Foreign Keys	234	(1)
Finding Specific Records	234	(1)
Covered Indexes	234	(1)
Looking for a Range of Information	235	(1)
Keeping the Data in Order	235	(1)
Determining What Makes a Bad Index	235	(2)
Poor Choice of Columns	236	(1)
Poor Selectivity of Data	236	(1)
Including Many Columns in an Index	236	(1)
Small Number of Records in the Table	236	(1)
Constantly Review	237	(1)
Good, Bad, How do I Tell?	237	(1)
Creating an Index	237	(25)
Creating an Index - Table Designer	237	(4)
Indexes and Statistics	241	(1)
Setting a Primary Key	241	(4)
Creating an Index - Using a Wizard	245	(4)
Creating an Index - Tasks	249	(3)
The CREATE INDEX Syntax	252	(2)
IGNORE_DUP_KEY	253	(1)
DROP_EXISTING	254	(1)
Creating an Index in Query Analyzer - Template	254	(4)
Creating an Index - Query Analyzer - SQL Code	258	(2)
Dropping an Index	260	(1)
Altering an Index in Query Analyzer	260	(2)
Summary	262	(3)
Chapter 8: Building Relationships	265	(20)
What is a Relationship?	266	(1)
What is Referential Integrity?	266	(1)
Types of Relationships	267	(2)
What is a Foreign Key?	269	(1)
Creating a Relationship	270	(6)
Check Existing Data on Creation	275	(1)
Enforce Relationship for Replication	275	(1)
Enforce Relationship for INSERTs and UPDATEs	275	(1)
Creating a Relationship - Missing Keys	276	(3)
Using the Alter Table SQL Statement	279	(2)
Display Dependencies	281	(2)
Summary	283	(2)
Chapter 9: Diagramming the Database	285	(38)
What is a Database Diagram?	286	(1)
The Database Diagramming Tool	287	(1)
The Default Database Diagram	288	(1)
Creating a Database Diagram	289	(6)
Altering the Layout	295	(9)
Working on the Database from the Diagram	304	(7)
Adding in Further Relationships	311	(4)
Creating the Default Diagram	315	(4)
Summary	319	(4)
Chapter 10: Database Backups and Recovery	323	(44)
Backup Strategies	324	(2)
When Problems May Occur	326	(1)
Taking a Database Offline	326	(3)
Bringing a Database Back Online	329	(1)
Backing Up the Data	329	(6)
Backing up the Transaction Log with a Wizard	335	(9)
Restoring a Database	344	(6)
Detaching and Attaching a Database	350	(10)
Producing SQL Script for the Database	360	(4)
Summary	364	(3)
Chapter 11: Maintaining Your Database	367	(20)
Database Maintenance	368	(1)
Creating a Database Maintenance Plan	368	(10)
Data Optimization Information	378	(1)
Database Integrity Check	379	(1)
Database Backup Plan and Transaction Log Backup Plan	379	(1)
Reports	379	(1)
New Operator	379	(1)
Maintenance Plan History	380	(1)
Checking the Maintenance Plan	381	(3)
Summary	384	(3)
Chapter 12: Inserting Data	387	(32)
Who Can Add Data	388	(1)
The T-SQL INSERT Command Syntax	389	(1)
INSERT SQL Command	390	(2)
Using NULL and Default Values	392	(2)
Allowing NULLS and the Allow Nulls Option	393	(1)
Default Values	394	(1)
Getting it Wrong	394	(1)
Allowing Defaults to Populate Columns	394	(2)
Altering the Table	394	(2)
Inserting Data Using Default Values	396	(4)
Using Enterprise Manager	396	(6)
DBCC CHECKIDENT	399	(1)
Using Query Analyzer	400	(1)
Dragging and Dropping Column Names	401	(1)
Column Constraints	402	(6)
Add Constraint	403	(5)
Inserting Images	408	(4)
Where to Store Images	409	(2)
WRITETEXT and TEXTPTR	411	(1)
Dealing with Several Records at Once	412	(3)
Inserting Several Records in a Query Batch	413	(2)
No Permissions for INSERT	415	(2)
Summary	417	(2)
Chapter 13: Retrieving Data	419	(38)
Using Enterprise Manager	420	(3)
The SELECT Statement	423	(2)
Naming the Columns	425	(1)
The First Searches	426	(2)
Displaying the Output Differently	428	(3)
Limiting the Search	431	(4)
String Functions	435	(2)
LTRIM/RTRIM	435	(1)
LEFT/RIGHT	436	(1)
Order! Order!	437	(2)
Other Limiting Methods	439	(4)
SET ROWCOUNT n	440	(1)
TOP n	441	(1)
TOP n PERCENT	442	(1)
The LIKE Operator	443	(4)
Retrieving Pictures	447	(2)
Creating Data - SELECT INTO	449	(2)
Using More Than One Table	451	(4)
Summary	455	(2)
Chapter 14: Updating Data and Transactions	457	(20)
The UPDATE Command	458	(1)
Updating Data - Query Analyzer	458	(5)
Transactions	463	(2)
Atomicity	464	(1)
Consistency	464	(1)
Isolation	464	(1)
Durability	464	(1)
BEGIN TRAN	464	(1)
COMMIT IRAN	465	(1)
ROLLBACK IRAN	465	(1)
Locking Data	465	(1)
Updating Data - Using Transactions	466	(3)
Updating Image Data Types	469	(2)
Building Your Own Template	471	(4)
Summary	475	(2)
Chapter 15: Deleting Data	477	(12)
DELETE Syntax	478	(1)
The DELETE Statement	478	(3)
Transaction Log Activity	481	(1)
TOP Clause in DELETE Statements	482	(1)
Using Enterprise Manager to Delete Rows	482	(2)
Truncate Table	484	(1)
Dropping the Table	485	(1)
Summary	486	(3)
Chapter 16: Building a View	489	(32)
What Is a View?	490	(1)
Using Views as Security	491	(1)
Encrypting Views	492	(1)
Creating a View - Enterprise Manager	492	(6)
Creating a View - Using a Wizard	498	(10)
CREATE VIEW Syntax	508	(1)
Creating a View - Query Analyzer	509	(1)
Creating a View - Using a Template	510	(4)
Setting Permissions on a View	514	(1)
Indexing a View	515	(3)
Summary	518	(3)
Chapter 17: Stored Procedures	521	(34)
What is a Stored Procedure?	521	(1)
Why Not Choose a View?	522	(1)
CREATE PROCEDURE Syntax	523	(2)
Returning a Set of Records	525	(1)
Creating a Stored Proc - Enterprise Manager	525	(4)
Creating a Stored Proc - Using a Wizard	529	(9)
Returning an Error using RETURN	538	(1)
Use of the SET Statement	539	(1)
Creating a Stored Proc - Using a Template	539	(5)
Controlling the Flow	544	(2)
IF ELSE	544	(1)
BEGIN. .. END	545	(1)
CASE Statement	545	(1)
Validating a Parameter - Using Query Analyzer	546	(3)
Using the CASE Statement	549	(3)
Summary	552	(3)
Chapter 18: Advanced Stored Procedures	555	(24)
System Variables	555	(2)
@@ERROR	556	(1)
@@IDENTITY	556	(1)
Setting Multiple Values	557	(1)
Altering a Stored Procedure	557	(7)
RAISERROR	564	(8)
System Stored Procedures	572	(5)
sp_addmessage	572	(1)
sp_help	573	(1)
sp_password	574	(1)
sp_who	574	(1)
System Catalog Stored Procedures	575	(1)
Extended Stored Procedures	576	(1)
Summary	577	(2)
Chapter 19: XML and Data Retrieval	579	(52)
HTML/XML background	580	(2)
How does XML work?	582	(3)
Installing IIS 5 on Windows 2000	585	(7)
Testing Your IIS 5 Installation	592	(2)
SQL Server and IIS	594	(13)
Retrieving Data Through XML	607	(6)
Web Assistant Wizard	613	(12)
Retrieving images	625	(2)
XPath Queries	627	(1)
Summary	628	(3)
Chapter 20: Triggers	631	(24)
What Is a Trigger?	632	(1)
CREATE TRIGGER Syntax	633	(1)
Why Not Use a Constraint?	634	(1)
Deleted and Inserted Logical Tables	635	(1)
Creating a Trigger	635	(4)
Dealing with Table Updates	639	(6)
Using the UPDATE() Function	645	(7)
What About Image Data Types?	652	(1)
Summary	653	(2)
Appendix A: Further References	655	(20)
SQL Server Resources	655	(5)
Books Online	655	(5)
Internet Resources	660	(9)
Microsoft Sites	660	(6)
SQL Server	661	(2)
Technet	663	(1)
MSDN	664	(2)
Microsoft Knowledge Base	666	(1)
Non-Microsoft Sites	666	(78)
Online Magazines	667	(1)
User Groups	668	(1)
Books	669	(6)
Appendix B: System Functions	675	(14)
Appendix C: Function Listing	689	(22)
String Functions	689	(7)
Data Type Conversions	696	(3)
Date and Time Functions	699	(4)
Mathematical Functions	703	(8)
Appendix D: Access Upsizing	711	(8)
Access Upsizing Wizard	711	(2)
SQL Server DTS	713	(6)
Appendix E: Security	719	(8)
Users	719	(1)
Logins	719	(1)
Roles	720	(1)
Initial Permissions	720	(2)
Giving Other Users Access Rights	722	(5)
Appendix F: Glossary of Terms	727	(16)
Appendix G: Support, Errata, and forums.apress.com	743	(8)
Support and Errata on www.apress.com	744	(1)
Finding Errata	744	(1)
Adding an Erratum to the Web Site	744	(1)
Customer Support	744	(1)
What We Can't Answer	745	(1)
How to Tell Us Exactly What You Think	745	(1)
The Peer-to-Peer Forums at forums.apress.com	745	(6)
Index	751