T-SQL in One Hour a Day, Sams Teach Yourself
Published on 5. November 2015
Book
Paperback/Softback
432 pages
978-0-672-33743-7 (ISBN)
Description
Master T-SQL database design, development, and administration the easy way-hands-on!
In just one hour a day, you'll build all the skills you need to create effective database applications with T-SQL and SQL Server. With this complete tutorial, you'll quickly master the basics and then move on to more advanced features and concepts:
Learn the fundamentals of T-SQL from the ground up, one step at a time
Succeed with the newest versions of T-SQL, SQL Server, and SQL Server Management Studio
Use T-SQL effectively as both an application developer and DBA
Master powerful stored procedures, triggers, transactions, and user-defined functions (UDFs)
Systematically optimize and secure your SQL Server databases
Learn on your own time, at your own pace
No previous T-SQL or database programming experience required
Learn how to design efficient, reliable SQL Server databases
Define efficient tables, table relationships, fields, and constraints
Make the most of T-SQL's SELECT and UPDATE statements
Work effectively with simple and complex views and joins
Master stored procedure techniques every developer should know
Build and use powerful User-Defined Functions (UDFs)
Secure databases with authentication, roles, permissions, and principals
Configure, maintain, and tune SQL Server for maximum reliability, performance, and value
Back up, restore, and audit databases
Optimize databases with the SQL Server Profiler, System Monitor, and Index Tuning Wizard
Leverage valuable insight and time saving techniques from a world renowned database expert
Register your book at informit.com/register for access to source code, example files, updates, and corrections as they become available.
In just one hour a day, you'll build all the skills you need to create effective database applications with T-SQL and SQL Server. With this complete tutorial, you'll quickly master the basics and then move on to more advanced features and concepts:
Learn the fundamentals of T-SQL from the ground up, one step at a time
Succeed with the newest versions of T-SQL, SQL Server, and SQL Server Management Studio
Use T-SQL effectively as both an application developer and DBA
Master powerful stored procedures, triggers, transactions, and user-defined functions (UDFs)
Systematically optimize and secure your SQL Server databases
Learn on your own time, at your own pace
No previous T-SQL or database programming experience required
Learn how to design efficient, reliable SQL Server databases
Define efficient tables, table relationships, fields, and constraints
Make the most of T-SQL's SELECT and UPDATE statements
Work effectively with simple and complex views and joins
Master stored procedure techniques every developer should know
Build and use powerful User-Defined Functions (UDFs)
Secure databases with authentication, roles, permissions, and principals
Configure, maintain, and tune SQL Server for maximum reliability, performance, and value
Back up, restore, and audit databases
Optimize databases with the SQL Server Profiler, System Monitor, and Index Tuning Wizard
Leverage valuable insight and time saving techniques from a world renowned database expert
Register your book at informit.com/register for access to source code, example files, updates, and corrections as they become available.
More details
Other editions
Additional editions
Alison Balter
T-SQL in One Hour a Day, Sams Teach Yourself
E-Book
10/2015
1st Edition
Pearson
€23.99
Available for download
Person
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.
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.
Content
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
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