Microsoft SQL Server 2000 Programming Step by Step

by Microsoft Corporation

• ISBN13: 9780735611429
• ISBN10: 0735611424
• Edition: 1st
• Format: Paperback
• Copyright: 2000-11-29
• Publisher: Microsoft Press

Summary

With scalability for the most demanding applications, Microsoft® SQL Server'„¢ 2000 can dramatically reduce the time needed to bring e-commerce, data warehousing, and line-of-business applications to market. Whether you're new to databases or familiar with Microsoft Access, Oracle, and other relational databases, MICROSOFT SQL SERVER 2000 PROGRAMMING STEP BY STEP is the ideal way to learn how SQL Server can work for you. Written by an experienced database developer and author and replete with examples and exercises, this book will help you quickly get up to speed with creating and maintaining databases using the interactive tools provided by SQL Server 2000 Personal, Standard, and Enterprise Editions. It's a must-have resource for anyone who wants to create and maintain databases and program with Transact-SQL. This title shows you how to: Get started with SQL Server: Use the Enterprise Manager to connect to a database; start, stop, and pause SQL Server Back up and restore a database and use the Maintenance Plan Wizard to create a monthly backup plan and manage SQL Server security functions. Create a SQL database: Create and manage databases, tables, indexes, relationships, constraints, table objects, and database diagrams. Retrieve data: Retrieve rows, use the SELECT statement, join tables and summarize data, and view data in a variety of ways. Work with data: Add, delete, and update rows, and import and export data. Use the Transact-SQL programming language: Understand operators, variables, parameters, and control of flow Use stored procedures; create and manage triggers Learn error handling Included on CD-ROM! Sample database files Sample script code in Transact-SQL

Author Biography

With twenty years experience in the field, Rebecca M. Riordan has earned an international reputation for designing and implementing computer systems that are technically sound, reliable, and effectively meet her clients' needs. Her particular area of expertise is database design and she is a five year Access MVP. Rebecca has also authored several database books and served as a senior technical support engineer for Microsoft's database products. As a Microsoft employee in Australia, Rebecca was the Senior technical support engineer for Microsoft's database products.

Table of Contents

Introduction	xvii
PART 1 Getting Started with SQL Server
The SQL Server 2000 Environment	3	(16)
Understanding the Enterprise Manager	4	(2)
Starting the Enterprise Manager	5	(1)
Controlling SQL Server	6	(7)
Registering a Server	7	(5)
Starting and Stopping a Server	12	(1)
The Enterprise Manager Console Tree	13	(1)
The System Databases	14	(2)
Connecting to and Exiting a Database	16	(3)
Database Objects	16	(1)
Exiting the Enterprise Manager	17	(2)
Administering SQL Server	19	(22)
Backing Up and Restoring Databases	20	(21)
Backing Up a Database	20	(9)
Restoring a Database	29	(1)
Using the Database Maintenance Plan Wizard	30	(11)
SQL Server 2000 Security	41	(36)
Understanding Security Modes	42	(1)
Windows Authentication	42	(1)
SQL Server Authentication	42	(1)
User Logins	43	(17)
Creating User Logins	43	(13)
Managing Logins	56	(4)
Database-Level Security	60	(17)
Database Users	60	(3)
Database Roles	63	(14)
PART 2 Creating Databases
Creating a Database	77	(14)
Creating Databases	77	(11)
Creating a New Database	78	(7)
Setting Database Properties	85	(3)
Managing Databases	88	(3)
Deleting a Database	88	(3)
Creating Tables	91	(24)
Creating Tables	92	(15)
Understanding Data Types	93	(2)
Creating a New Table	95	(2)
Adding Columns to a Table	97	(10)
Managing Tables	107	(8)
Altering Columns	107	(3)
Altering Tables	110	(5)
Creating Indexes	115	(24)
Understanding Indexes	116	(15)
Creating Indexes	116	(15)
Maintaining Indexes	131	(8)
Altering Indexes	131	(4)
Removing Indexes	135	(4)
Creating Relationships	139	(18)
Understanding Relationships	139	(7)
Creating Relationships	142	(4)
Managing Relationships	146	(11)
Altering Relationships	146	(3)
Maintaining Relationships	149	(8)
Creating Check Constraints	157	(12)
Understanding Check Constraints	157	(4)
Creating Check Constraints	158	(3)
Managing Check Constraints	161	(8)
Altering Check Constraints	161	(2)
Maintaining Check Constraints	163	(6)
Creating Table Objects	169	(18)
Understanding Defaults	170	(6)
Creating Defaults	170	(6)
Understanding Rules	176	(5)
Creating Rules	176	(5)
Understanding User-Defined Data Types	181	(6)
Creating User-Defined Data Types	182	(5)
Creating Database Diagrams	187	(20)
Understanding Database Diagrams	187	(9)
Creating a Database Diagram from an Existing Schema	188	(8)
Using Database Diagrams to Maintain the Database	196	(11)
Changing the Database Schema	196	(2)
Creating Database Objects	198	(9)
PART 3 Retrieving Data
Retrieving Rows	207	(10)
Using the Query Designer	208	(5)
Viewing Rows in a Table	208	(2)
Updating Rows in a Table	210	(3)
Understanding the SQL Server Query Designer	213	(4)
The Diagram Pane	213	(1)
The Grid Pane	214	(1)
The SQL Pane	214	(3)
The Select Statement	217	(18)
Understanding the Select Statement	218	(17)
Selecting All Columns	218	(2)
Selecting a Subset of Columns	220	(3)
Creating Column Aliases	223	(3)
Creating Calculated Columns	226	(5)
Using the Top n Clause	231	(4)
Sorting and Selecting Rows	235	(22)
The Order By Clause	236	(9)
Sorting Rows	236	(4)
Sorting by Multiple Columns	240	(5)
The Where Clause	245	(12)
The Basic Where Clause	245	(3)
Using Special Operators	248	(4)
Combining Selection Criteria	252	(5)
Joining Tables	257	(24)
Understanding the From Clause	258	(1)
Creating Joins	259	(22)
Inner Joins	260	(9)
Outer Joins	269	(8)
Unions	277	(4)
Summarizing Data	281	(18)
Understanding Select Distinct	282	(7)
Using Select Distinct	282	(7)
Understanding Group By	289	(10)
Using Group by	289	(4)
Using the Having Clause	293	(6)
Views	299	(26)
Understanding Views	299	(18)
Creating Views	300	(10)
Using Views	310	(7)
Managing Views	317	(8)
Altering Views	317	(3)
Removing Views	320	(5)
PART 4 Working with Data
Adding Rows	325	(20)
Understanding the Insert Statement	326	(1)
Using the Insert statement	326	(19)
Inserting Rows Using the Grid Pane	326	(4)
Inserting Rows Using the SQL Pane	330	(5)
Inserting Multiple Rows	335	(10)
Updating Rows	345	(14)
Understanding the Update Statement	345	(1)
Using the Update Statement	346	(13)
Updating Rows Using the Grid Pane	346	(6)
Updating Rows Using the SQL Pane	352	(3)
Updating Rows Using the From Clause	355	(4)
Deleting Rows	359	(18)
Understanding the Delete Statement	360	(1)
Using the Delete Statement	361	(11)
Deleting Rows Using the Grid and Diagram Panes	361	(4)
Deleting Rows Using the SQL Pane	365	(7)
Using the Truncate Table Statement	372	(5)
Deleting All Rows Using the Truncate Table Statement	373	(4)
Copying and Moving Data	377	(32)
The Data Transformation Services Wizards	377	(19)
Using the DTS Import Wizard	378	(10)
Using the DTS Export Wizard	388	(8)
Attaching and Detaching Databases	396	(3)
Detaching a Database	396	(1)
Attaching a Database	397	(2)
The Copy Database Wizard	399	(10)
Using the Copy Database Wizard	399	(10)
PART 5 Transact-SQL
The Query Analyzer	409	(32)
Understanding the Query Analyzer	410	(5)
Starting the Query Analyzer	410	(3)
Selecting a Database	413	(2)
Using the Query Window	415	(10)
Entering Transact-SQL Statements	416	(4)
Using SQL Scripts	420	(5)
Using the Object Browser	425	(16)
Opening Objects	427	(3)
Adding Objects to the Editor Pane	430	(4)
Scripting Objects	434	(7)
Data Definition Language	441	(30)
Understanding DDL	441	(22)
Creating Objects	442	(9)
Altering Objects	451	(8)
Dropping Objects	459	(4)
Using the Object Browser for Data Definition	463	(8)
Scripting DDL	463	(2)
Using Templates	465	(6)
Analyzing Queries	471	(20)
Using the Query Analyzer to Optimize Performance	472	(10)
Execution Plans	472	(7)
Server Traces	479	(1)
Client Statistics	480	(2)
The Index Tuning Wizard	482	(9)
Using the Index Tuning Wizard	482	(9)
Transact-SQL Language Components	491	(38)
Transact-SQL Commands	492	(4)
Data Manipulation Commands	494	(1)
Data Definition Commands	494	(1)
Database Administration Commands	495	(1)
Other Commands	496	(1)
Transact-SQL Operators	496	(13)
Operator Precedence	496	(1)
Comment Operators	497	(1)
Arithmetic Operators	498	(3)
Comparison Operators	501	(2)
Logical Operators	503	(2)
Bitwise Operators	505	(2)
Other Operators	507	(2)
Transact-SQL Functions	509	(20)
Using Functions	510	(1)
Data and Time Functions	510	(3)
Mathematical Functions	513	(3)
Aggregate Functions	516	(3)
Metadata Functions	519	(2)
Security Functions	521	(2)
String Functions	523	(3)
System Functions	526	(3)
Programming Objects	529	(24)
Temporary Tables	530	(12)
Understanding Temporary Tables	530	(1)
Using Temporary Tables	531	(11)
Variables	542	(11)
Understanding Variables	542	(4)
Using Variables	546	(7)
Controlling Execution	553	(18)
Conditional Processing	554	(10)
IF ... ELSE	554	(3)
Case	557	(5)
The Goto Command	562	(2)
Looping	564	(7)
Simple While loop	564	(2)
Complex While Loops	566	(5)
Transact-SQL Cursors	571	(26)
Understanding Cursors	572	(4)
Cursor Characteristics	573	(1)
Cursor Types	574	(2)
Using Cursors	576	(21)
Creating Cursors	576	(4)
Manipulating Rows with a Cursor	580	(10)
Updating and Deleting Rows with a Cursor	590	(2)
Monitoring Transact-SQL Cursors	592	(5)
Stored Procedures	597	(26)
Understanding Stored Procedures	598	(2)
Exchanging Data with Stored Procedures	598	(1)
System Procedures	599	(1)
User-Defined Stored Procedures	600	(1)
Using and Creating Stored Procedures	600	(23)
Using Stored Procedures	600	(9)
Creating Stored Procedures	609	(14)
Triggers	623	(16)
Understanding Triggers	623	(3)
After Triggers	624	(1)
Instead of Triggers	625	(1)
Creating Triggers	626	(13)
Using the Create Trigger Command	626	(6)
Using the Update Function	632	(2)
Using the Inserted and Deleted Tables	634	(5)
User-Defined Functions	639	(20)
Understanding User-Defined Functions	639	(2)
Scalar Functions	640	(1)
Table-Valued Functions	640	(1)
Creating User-Defined Functions	641	(7)
Creating Scalar Functions	642	(2)
Creating Table-Valued Functions	644	(4)
Using User-Defined Functions	648	(11)
Using User-Defined Functions in Transact-SQL Statements	649	(3)
Using User-Defined Functions in Table Definitions	652	(7)
Glossary	659	(6)
Index	665