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