Microsoft 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 Learn error handling Included on CD-ROM! Sample database files Sample script code in Transact-SQL For customers who purchase an ebook version of this title, instructions for downloading the CD files can be found in the ebook.

Table of 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 IF ELSE 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