Hjem

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