Beginning SQL Server 2008 for Developers (eBook)

Robin Dewson has been hooked on programming ever since he bought his first computer, a Sinclair ZX80, in 1980. He has been working with SQL Server since version 6.5 and Visual Basic since version 5. Robin is a consultant mainly in the city of London, where he has been for nearly eight years. He also has been developing a rugby-related website as well as maintaining his own site at Fat-Belly.com.

Contents at a Glance 5
Contents 6
About the Author 15
About the Technical Reviewer 16
Acknowledgments 17
Introduction 18
Who This Book Is For 18
How This Book Is Structured 18
Prerequisites 18
Downloading the Code 19
Contacting the Author 19
SQL Server 2008 Overview and Installation 20
Why SQL Server 2008? 21
Evolution of SQL Server 22
Hardware Requirements 23
The Example 24
Installation 24
Security 36
Summary 42
SQL Server Management Studio 43
A Quick Overview of SSMS 43
Examining SSMS’s Options 51
Query Editor 66
Summary 68
Database Design and Creation 69
Defining a Database 70
Prebuilt Databases Within SQL Server 71
Choosing the Database System Type 74
Gathering the Data 75
Determining the Information to Store in the Database 77
External and Ignored Information 79
Building Relationships 80
Normalization 85
Creating the Sample Database 89
Summary 107
Security and Compliance 108
Logins 108
Server Logins and Database Users 118
Roles 118
Schemas 124
Before You Can Proceed with Your Solution 126
Declarative Management Framework 130
Summary 134
Defining Tables 136
What Is a Table? 136
SQL Server Data Types 137
Columns Are More Than Simple Data Repositories 143
Image and Large Text Storage in SQL Server 145
Creating a Table in SQL Server Management Studio 145
Creating a Table Through the Query Editor 151
Creating a Table: Using a Template 153
Creating and Altering a Template 156
The ALTER TABLE Statement 157
Defining the Remaining Tables 158
Setting a Primary Key 159
Creating a Relationship 160
Building a Relationship via T-SQL 165
Summary 167
Creating Indexes and Database Diagramming 168
What Is an Index? 168
Determining What Makes a Good Index 171
Determining What Makes a Bad Index 173
Reviewing Your Indexes for Performance 174
Creating an Index 175
Dropping an Index 187
Altering an Index in Query Editor 188
When an Index Does Not Exist 189
Diagramming the Database 189
Summary 196
Database Backups, Recovery, and Maintenance 197
Transaction Logs 198
Backup Strategies 199
When Problems May Occur 201
Taking a Database Offline 201
Backing Up the Data 203
Restoring a Database 216
Detaching and Attaching a Database 223
Producing SQL Script for the Database 231
Maintaining Your Database 236
Creating a Database Maintenance Plan 237
Setting Up Database Mail 250
Modifying a Maintenance Plan 259
Summary 263
Working with the Data 264
The T- SQL INSERT Command Syntax 264
INSERT SQL Command 265
DBCC CHECKIDENT 272
Column Constraints 273
Inserting Several Records at Once 278
Retrieving Data 279
Using SQL Server Management Studio to Retrieve Data 280
The SELECT Statement 281
Naming the Columns 283
The First Searches 283
Varying the Output Display 285
Limiting the Search: the Use of WHERE 287
String Functions 293
Order! Order! 294
The LIKE Operator 296
Creating Data: SELECT INTO 298
Who Can Add, Delete, and Select Data 299
Updating Data 304
Transactions 309
Deleting Data 315
Truncating a Table 318
Dropping a Table 319
Summary 319
Building a View 321
Why a View? 321
Using Views for Security 322
Encrypting View Definitions 323
Creating a View: SQL Server Management Studio 323
Creating a View Using a View 329
CREATE VIEW Syntax 335
Creating a View: a Query Editor Pane 336
Creating a View: SCHEMABINDING 337
Indexing a View 339
Summary 341
Stored Procedures and Functions 343
What Is a Stored Procedure? 344
CREATE PROCEDURE Syntax 344
Returning a Set of Records 346
Creating a Stored Procedure: Management Studio 347
Different Methods of Executing 351
Using RETURN 351
Controlling the Flow 355
Bringing It All Together 361
User- Defined Functions 363
Summary 367
T-SQL Essentials 368
Using More Than One Table 368
Variables 373
Temporary Tables 375
Aggregations 377
Grouping Data 380
HAVING 382
Distinct Values 383
Functions 383
RAISERROR 397
Error Handling 400
@@ ERROR 401
TRY. . . CATCH 402
Summary 406
Advanced T-SQL 407
Subqueries 407
The APPLY Operator 411
Common Table Expressions 414
Pivoting Data 417
Ranking Functions 419
PowerShell Within SQL Server 424
Summary 428
Triggers 429
What Is a Trigger? 429
The DML Trigger 430
CREATE TRIGGER Syntax for DML Triggers 431
Why Not Use a Constraint? 432
Deleted and Inserted Logical Tables 433
Creating a DML FOR Trigger 433
Checking Specific Columns 437
DDL Triggers 444
Summary 450
SQL Server 2008 Reporting Services 451
Reporting Services Architecture 451
Configuring Reporting Services 453
Building Your First Report Using Report Designer 460
Summary 469
Index 470