Sams Teach Yourself Transact-SQL in 21 Days (2nd Edition) (Sams Teach Yourself)

✍ Scribed by Lowell Mauer

• Publisher: Sams
• Year: 2001
• Tongue: English
• Leaves: 640
• Edition: 2
• Category: Library

✦ Synopsis

Sams Teach Yourself Transact-SQL in 21 Days, 2E will teach programmers how to develop Transact-SQL queries. There will be a focus on providing methods for improving productivity without a reducing performance. Specifically, the reader will:Learn Transact-SQL syntax Learn how to add, delete, and modifying data using Transact-SQL Understand coding standards Review variations from ANSI-standard SQL Be presented with basic server operations. Recognize performance issues with queries. This book will also include:Constructs such as CUBE, ROLLUP, CASE, and JOIN. Techniques to solve complex problems How the server uses indexes Methods to write (correctly) stored procedures and triggers. Templates of procedures and triggers (reference) Advanced Topics such as: Outer and self joins Temporary tables Sub-queries.

✦ Table of Contents

What’s New in the Second Edition......Page 2
Teach Yourself Transact-SQL in 21Days, Second Edition......Page 4
Copyright © 2001 by Sams Publishing......Page 5
Contents at a Glance......Page 6
Contents......Page 7
About the Authors......Page 18
Conventions......Page 20
What’s New in SQL Server 2000......Page 21
WEEK 1 Working with Rows and Columns......Page 24
DAY 1 Introduction to SQL and the Query Analyzer......Page 26
SQL and SQL Server......Page 27
Retrieving Data with Query Analyzer......Page 28
Introducing SQL Server 2000 Enterprise Manager......Page 40
Q&A......Page 45
Workshop......Page 46
DAY 2 Filtering and Sorting Data......Page 48
Filtering Data with WHERE Clauses......Page 49
Combining Conditions with OR and AND......Page 57
Searching for Ranges of Values with Inequalities (>, <, BETWEEN)......Page 60
Getting Correct Results from Date Comparisons......Page 65
Looking for Values in a List: The IN Operator......Page 71
Wildcard Searches with LIKE......Page 74
Summary of Search Conditions......Page 78
Sorting Data (ORDER BY)......Page 79
Retrieving Unique Rows......Page 82
Summary......Page 84
Q&A......Page 85
Workshop......Page 86
DAY 3 Working with Columns......Page 88
Column Characteristics......Page 89
Changing Result Sets......Page 95
Q&A......Page 116
Workshop......Page 117
DAY 4 Working with Row Aggregates and Grouping......Page 118
Grouping Data......Page 132
Creating Pivot Tables......Page 146
Summary......Page 151
Q&A......Page 152
Workshop......Page 153
DAY 5 Joining Tables......Page 156
Understanding Joins......Page 157
Understanding More About Joins......Page 167
Writing Outer Joins......Page 181
Joining a Table to Itself......Page 186
Using Union to Merge Result Sets......Page 191
Q&A......Page 195
Workshop......Page 197
DAY 6 Using Subqueries......Page 200
Understanding Subqueries......Page 201
Matching a Value with Subqueries......Page 203
Checking for Membership......Page 206
Summary......Page 211
Workshop......Page 212
DAY 7 Adding, Changing, and Deleting Rows......Page 214
Creating Tables......Page 215
Inserting Rows......Page 216
Updating Rows......Page 222
Deleting Rows......Page 225
Q&A......Page 227
Workshop......Page 228
WEEK 1 In Review......Page 230
WEEK 2 Programming with T-SQL......Page 232
DAY 8 Defining Data......Page 234
What Is Data Definition Language (DDL)?......Page 235
Working with Tables......Page 242
Altering Tables......Page 251
Defining Constraints......Page 254
Defining Table Relationships......Page 255
Summary......Page 259
Workshop......Page 260
DAY 9 Indexes and Performance......Page 262
How SQL Server Uses Indexes......Page 263
Defining and Managing Indexes......Page 273
Performing Index Maintenance and Using Performance Tools......Page 285
Q&A......Page 291
Workshop......Page 292
DAY 10 Views and Temporary Tables......Page 294
Using Views to Access Data......Page 295
Using Temporary Tables for Storing Data......Page 317
Q&A......Page 323
Workshop......Page 324
DAY 11 T-SQL Programming Fundamentals......Page 326
Handling Variables......Page 327
Reviewing Programming Structures......Page 332
Trapping Errors and Implementing Error Handling......Page 340
Workshop......Page 349
DAY 12 Understanding Database Locking......Page 352
Lock Fundamentals......Page 353
Testing Lock Interaction......Page 361
Understanding Lock Persistence......Page 365
Summary......Page 381
Q&A......Page 382
Workshop......Page 383
DAY 13 Programming with Transactions......Page 386
Understanding Transaction Basics......Page 387
Controlling Transactions in Your Programs......Page 392
Managing Concurrency......Page 402
Handling Long-Running Transactions......Page 405
Summary......Page 407
Workshop......Page 408
DAY 14 Using Cursors......Page 410
Defining Cursors......Page 411
Working with Cursors......Page 414
Cursor Locking......Page 427
Q&A......Page 428
Workshop......Page 429
WEEK 2 In Review......Page 430
WEEK 3 Procedures and Triggers......Page 432
DAY 15 Writing and Executing Stored Procedures......Page 434
Benefits of Stored Procedures......Page 435
Writing Your First Stored Procedure......Page 436
Working with Parameters......Page 442
Returning Procedure Status......Page 448
Using Output Parameters......Page 450
Summary......Page 454
Q&A......Page 455
Workshop......Page 456
DAY 16 Optimizing Stored Procedures......Page 458
Understanding Stored Procedure Optimization......Page 459
Using Table Variables and Temporary Tables with Procedures......Page 466
Handling Transactions in Stored Procedures......Page 471
Q&A......Page 478
Workshop......Page 479
DAY 17 Debugging Stored Procedures......Page 482
Debugging Explained......Page 483
Using the T-SQL Debugger......Page 492
Summary......Page 503
Workshop......Page 504
DAY 18 Writing and Testing Triggers......Page 506
Understanding Triggers......Page 507
Executing Triggers......Page 513
Using Trigger Resources......Page 518
Determining When to Use Triggers......Page 523
Q&A......Page 524
Workshop......Page 525
DAY 19 Solving Special Trigger Problems......Page 526
Managing Referential Integrity with Triggers......Page 527
Managing Special Database Actions with Triggers......Page 538
Summary......Page 547
Workshop......Page 548
DAY 20 User-Defined Functions in SQL......Page 550
Understanding User-Defined Functions......Page 551
Reviewing Advanced UDF Topics......Page 559
Workshop......Page 563
Understanding Large Data Types......Page 564
Managing BLOB Data......Page 569
Q&A......Page 576
Workshop......Page 577
WEEK 3 In Review......Page 578
Day 1......Page 580
Day 2......Page 581
Day 3......Page 583
Day 4......Page 585
Day 5......Page 588
Day 6......Page 592
Day 7......Page 594
Day 9......Page 596
Day 10......Page 597
Day 12......Page 599
Day 13......Page 602
Day 14......Page 604
Day 15......Page 605
Day 16......Page 608
Day 17......Page 611
Day 18......Page 612
Day 19......Page 613
Day 20......Page 614
Day 21......Page 615
INDEX......Page 616