Introduction 1
Who Is This Book For?..................................................................................................... 2
Companion Website............................................................................................................. 2
Conventions Used in This Book................................................................................. 3
1 Understanding SQL 5
Database Basics....................................................................................................................... 5
What Is SQL?......................................................................................................................... 11
Try It Yourself...................................................................................................................... 12
Summary.................................................................................................................................... 12
2 Introducing SQL Server 13
What Is SQL Server?......................................................................................................... 13
SQL Server Tools................................................................................................................ 16
Summary.................................................................................................................................... 18
3 Working with SQL Server 19
Making the Connection................................................................................................... 19
Selecting a Database........................................................................................................... 20
Learning About Databases and Tables................................................................ 21
Summary.................................................................................................................................... 25
4 Retrieving Data 27
The SELECT Statement................................................................................................. 27
Retrieving Individual Columns.................................................................................. 27
Retrieving Multiple Columns..................................................................................... 29
Retrieving All Columns................................................................................................... 31
Retrieving Distinct Rows.............................................................................................. 32
Limiting Results.................................................................................................................... 33
Using Fully Qualified Table Names...................................................................... 36
Summary.................................................................................................................................... 36
5 Sorting Retrieved Data 37
Sorting Data............................................................................................................................. 37
Sorting by Multiple Columns.................................................................................... 39
Specifying Sort Direction.............................................................................................. 40
Summary.................................................................................................................................... 43
6 Filtering Data 45
Using the WHERE Clause............................................................................................ 45
The WHERE Clause Operators................................................................................ 46
Summary.................................................................................................................................... 52
7 Advanced Data Filtering 53
Combining WHERE Clauses....................................................................................... 53
Using the IN Operator..................................................................................................... 57
Using the NOT Operator.............................................................................................. 59
Summary.................................................................................................................................... 60
8 Using Wildcard Filtering 61
Using the LIKE Operator.............................................................................................. 61
Tips for Using Wildcards.............................................................................................. 67
Summary.................................................................................................................................... 67
9 Creating Calculated Fields 69
Understanding Calculated Fields.............................................................................. 69
Concatenating Fields......................................................................................................... 70
Performing Mathematical Calculations............................................................... 75
Summary.................................................................................................................................... 77
10 Using Data Manipulation Functions 79
Understanding Functions............................................................................................... 79
Using Functions.................................................................................................................... 80
Summary.................................................................................................................................... 89
11 Summarizing Data 91
Using Aggregate Functions........................................................................................... 91
Aggregates on Distinct Values................................................................................... 98
Combining Aggregate Functions............................................................................ 100
Summary................................................................................................................................. 100
12 Grouping Data 101
Understanding Data Grouping................................................................................ 101
Creating Groups................................................................................................................ 102
Filtering Groups................................................................................................................ 103
Grouping and Sorting..................................................................................................... 106
SELECT Clause Ordering........................................................................................... 108
Summary................................................................................................................................. 109
13 Working with Subqueries 111
Understanding Subqueries.......................................................................................... 111
Filtering by Subquery.................................................................................................... 111
Using Subqueries as Calculated Fields.............................................................. 116
Checking for Existence with Subqueries.......................................................... 119
Summary................................................................................................................................. 121
14 Joining Tables 123
Understanding Joins....................................................................................................... 123
Creating a Join..................................................................................................................... 126
Summary................................................................................................................................. 135
15 Creating Advanced Joins 137
Using Table Aliases........................................................................................................ 137
Using Different Join Types...................................................................................... 138
Using Joins with Aggregate Functions............................................................. 145
Using Joins and Join Conditions........................................................................... 147
Summary................................................................................................................................. 147
16 Combining Queries 149
Understanding Combined Queries....................................................................... 149
Creating Combined Queries...................................................................................... 150
Summary................................................................................................................................. 155
17 Full-Text Searching 157
Understanding Full-Text Searching..................................................................... 157
Setting Up Full-Text Searching.............................................................................. 158
Performing Full-Text Searches................................................................................ 162
Summary................................................................................................................................. 170
18 Inserting Data 171
Understanding Data Insertion................................................................................. 171
Inserting Complete Rows........................................................................................... 172
Inserting Multiple Rows............................................................................................. 176
Inserting Retrieved Data.............................................................................................. 177
Summary................................................................................................................................. 179
19 Updating and Deleting Data 181
Updating Data..................................................................................................................... 181
Deleting Data....................................................................................................................... 183
Guidelines for Updating and Deleting Data.................................................. 184
Summary................................................................................................................................. 185
20 Creating and Manipulating Tables 187
Creating Tables................................................................................................................... 187
Updating Tables................................................................................................................ 195
Deleting Tables................................................................................................................... 197
Renaming Tables............................................................................................................... 198
Summary................................................................................................................................. 198
21 Using Views 199
Understanding Views..................................................................................................... 199
Using Views.......................................................................................................................... 201
Summary................................................................................................................................. 208
22 Programming with T-SQL 209
Understanding T-SQL Programming.................................................................. 209
Using Variables................................................................................................................... 210
Using Conditional Processing.................................................................................. 217
Grouping Statements..................................................................................................... 219
Using Looping..................................................................................................................... 221
Summary................................................................................................................................. 222
23 Working with Stored Procedures 223
Understanding Stored Procedures........................................................................ 223
Why Use Stored Procedures.................................................................................... 224
Using Stored Procedures............................................................................................. 225
Summary................................................................................................................................. 234
24 Using Cursors 235
Understanding Cursors................................................................................................. 235
Working with Cursors................................................................................................... 235
Summary................................................................................................................................. 242
25 Using Triggers 243
Understanding Triggers................................................................................................ 243
Using Triggers..................................................................................................................... 247
Summary................................................................................................................................. 250
26 Managing Transaction Processing 251
Understanding Transaction Processing............................................................ 251
Controlling Transactions............................................................................................. 253
Summary................................................................................................................................. 258
27 Working with XML 259
Understanding SQL Server XML Support................................................... 259
Retrieving Data as XML............................................................................................. 260
Storing XML Data........................................................................................................... 264
Searching for XML Data............................................................................................. 267
Summary................................................................................................................................. 268
28 Globalization and Localization 269
Understanding Character Sets and Collation Sequences...................... 269
Working with Collation Sequences...................................................................... 270
Managing Case Sensitivity........................................................................................ 273
Working with Unicode.................................................................................................. 275
Summary................................................................................................................................. 277
29 Managing Security 279
Understanding Access Control............................................................................... 279
Managing Users................................................................................................................. 281
Managing Access Rights............................................................................................. 283
Summary................................................................................................................................. 285
30 Improving Performance 287
Improving Performance................................................................................................ 287
Summary................................................................................................................................. 289
Appendixes
A Getting Started with SQL Server and T-SQL 291
B The Example Tables 295
C T-SQL Statement Syntax 303
D T-SQL Datatypes 309
E T-SQL Reserved Words 315
TOC, 0672328674, 7/11/2007
