T-SQL in One Hour a Day, Sams Teach Yourself

Alison Balter is the president of InfoTech Services Group, Inc., a computer consulting firm based in Venice Beach, California. Alison is a highly experienced independent trainer and consultant specializing in Windows applications training and development. During her 30 years in the computer industry, she has trained and consulted with many corporations and government agencies. Since Alison founded InfoTech Services Group, Inc. (formerly InfoTechnology Partners) in 1990, its client base has expanded to include major corporations and government agencies such as Cisco, Shell Oil, Accenture, AIG Insurance, Northrop, the Drug Enforcement Administration, Prudential Insurance, Transamerica Insurance, Fox Broadcasting, the United States Navy, the United States Marines, the University of Southern California (USC), Massachusetts Institute of Technology (MIT), and others.   Alison is the author of more than 300 internationally marketed computer training videos, including 18 Access 2000 videos, 35 Access 2002 videos, 15 Access 2003 videos, a complete series of both user and developer videos on Access 2007, and Access 2010 and Access 2013 user videos. Alison travels throughout North America giving training seminars in Microsoft Access and Microsoft SQL Server.   Alison is also author of 14 books published by Sams Publishing including: Alison Balter’s Mastering Access 95 Development, Alison Balter’s Mastering Access 97 Development, Alison Balter’s Mastering Access 2000 Development, Alison Balter’s Mastering Access 2002 Desktop Development, Alison Balter’s Mastering Access 2002 Enterprise Development, Alison Balter’s Mastering Microsoft Access Office 2003, Teach Yourself Microsoft Office Access 2003 in 24 Hours, Access Office 2003 in a Snap, Alison Balter’s Mastering Access 2007 Development, a power user book on Microsoft Access 2007, Using Access 2010, Access 2013 Absolute Beginner’s Guide, and Teach Yourself SQL Express 2005 in 24 Hours.   An active participant in many user groups and other organizations, Alison is a past president of the Independent Computer Consultants Association of Los Angeles and of the Los Angeles Clipper Users’ Group. She is also past president of the Ventura County Professional Women’s Network. Alison is a Microsoft Access MVP and was selected as Ventura County Woman Business Owner of the Year for 2012/2013.   On a personal note, Alison keeps herself busy skiing, taking yoga classes, running, walking, lifting weights, hiking, and traveling. She most enjoys spending time with her husband, Dan, their daughter Alexis, and their son Brendan.   Contact Alison via Alison@techismything.com or visit InfoTech Services Group’s website at www.TechIsMyThing.com.

Introduction 1

1: Database Basics 5

What Is a Database? 5

What Is a Table? 5

What Is a Database Diagram? 6

What Is a View? 7

What Is a Stored Procedure? 8

What Is a User-Defined Function? 9

What Is a Trigger? 10

2: SQL Server Basics 13

Versions of SQL Server 2014 Available 13

SQL Server Components 16

Introduction to Microsoft SQL Server Management Studio 19

Connecting to a Database Server 25

Installing the Sample Files 27

3: Creating a SQL Server Database 33

Creating the Database 33

Defining Database Options 36

The Transaction Log 39

Attaching to an Existing Database 40

4: Working with SQL Server Tables 45

Creating SQL Server Tables 45

Adding Fields to the Tables You Create 46

Working with Constraints 50

Creating an Identity Specification 56

Adding Computed Columns 57

Working with User-Defined Data Types 58

Adding and Modifying Indexes 60

Saving Your Table 64

5: Working with Table Relationships 67

An Introduction to Relationships 67

Creating and Working with Database Diagrams 70

Working with Table Relationships 77

Designating Table and Column Specifications 79

Adding a Relationship Name and Description 81

Determining When Foreign Key Relationships Constrain the

Data Entered in a Column 81

Designating Insert and Update Specifications 84

6: Getting to Know the SELECT Statement 89

Introducing T-SQL 89

Working with the SELECT Statement 90

Adding on the FROM Clause 92

Including the WHERE Clause 93

Using the ORDER BY Clause 101

7: Taking the SELECT Statement to the Next Level 105

Adding the DISTINCT Keyword 105

Working with the FOR XML Clause 107

Working with the GROUP BY Clause 109

Including Aggregate Functions in Your SQL Statements 110

Taking Advantage of the HAVING Clause 117

Creating Top Values Queries 118

8: Building SQL Statements Based on Multiple Tables 121

Working with Join Types 121

9: Powerful Join Techniques 129

Utilizing Full Joins 129

Taking Advantage of Self-Joins 130

Exploring the Power of Union Queries 133

Working with Subqueries 136

Using the INTERSECT Operator 137

Working with the EXCEPT Operator 138

10: Modifying Data with Action Queries 141

The UPDATE Statement 141

The INSERT Statement    143

The SELECT INTO Statement 145

The DELETE Statement 146

The TRUNCATE Statement 147

11: Getting to Know the T-SQL Functions 149

Working with Numeric Functions 149

Taking Advantage of String Functions 151

Exploring the Date/Time Functions 163

Working with Nulls 170

12: Working with SQL Server Views 177

An Introduction to Views 177

Using T-SQL to Create or Modify a View 185

13: Using T-SQL to Design SQL Server Stored Procedures 191

The Basics of Working with Stored Procedures 192

Declaring and Working with Variables 198

Controlling the Flow 200

14: Stored Procedure Techniques Every Developer Should Know 215

The SET NOCOUNT Statement 215

Using the @@ Functions 216

Working with Parameters 221

Errors and Error Handling 227

15: Power Stored Procedure Techniques 233

Modifying Data with Stored Procedures 233

Stored Procedures and Transactions 237

16: Stored Procedure Special Topics 243

Stored Procedures and Temporary Tables 243

Stored Procedures and Cursors 245

Stored Procedures and Security 250

17: Building and Working with User-Defined Functions 253

Scalar Functions 253

18: Creating and Working with Triggers 263

Creating Triggers 263

Creating an Insert Trigger 266

Creating an Update Trigger 269

Creating a Delete Trigger 272

Downsides of Triggers 274

19: Authentication 277

The Basics of Security 277

Types of Authentication 278

Creating Logins 280

Creating Roles 285

20: SQL Server Permissions Validation 299

Types of Permissions 299

Getting to Know Table Permissions 309

Getting to Know View Permissions 312

Getting to Know Stored Procedure Permissions 314

Getting to Know Function Permissions 315

Implementing Column-Level Security 315

21: Configuring, Maintaining, and Tuning SQL Server 321

Selecting and Tuning Hardware 321

Configuring and Tuning SQL Server 324

22: Maintaining the Databases You Build 335

Backing Up Your Databases 335

Restoring a Database 338

The Database Engine Tuning Advisor 341

Creating and Working with Database Maintenance Plans 344

23: Performance Monitoring 355

Executing Queries in SQL Server Management Studio 355

Displaying and Analyzing the Estimated Execution Plan 358

Adding Indexes to Allow Queries to Execute More Efficiently 362

Setting Query Options 364

SQL Server Profiler 367

24: Installing and Upgrading SQL Server 377

Installing SQL Server 2014 Enterprise Edition 377

Installing SQL Server Management Studio 385

Index 389