SQL Server Fundamentals for the Accidental DBA LiveLessons (Video Training)

Eric S. Johnson, is the co-founder of Consortio Services and the primary Database Technologies Consultant; he is also a Microsoft SQL Server MVP. He has been recognized by Microsoft as an MVP for his expertise in SQL Server. His background in information technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he also acquired his MBA in 2004. All in all, he has more than 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. In addition, he is active in the local SQL Server community, serving as the president of the Colorado Springs SQL Server Users Group. He has published works from technical magazine articles for the likes of Redmond Magazine to authoring books such as A Developer’s Guide to Data Modeling for SQL Server, published by Addison-Wesley.

Preface viii

Lesson 1 SQL Server Overview 1

What You Will Learn................................................................................1

We Will Cover................................................................................1

Notes from the Lesson............................................................................1

Lesson 2 Common Uses of SQL Server 3

What You Will Learn................................................................................3

We Will Cover................................................................................3

Notes from the Lesson............................................................................3

Lesson 3 SQL Server Management Studio 5

What You Will Learn................................................................................5

We Will Cover................................................................................6

Notes from the Lesson............................................................................6

Lesson 4 SQL Server Configuration Manager 7

What You Will Learn................................................................................7

We Will Cover................................................................................7

Notes from the Lesson............................................................................8

Lesson 5 SQL Server Profiler 9

What You Will Learn................................................................................9

We Will Cover..............................................................................10

Notes from the Lesson..........................................................................10

Lesson 6 Business Intelligence Development Studio 11

What You Will Learn..............................................................................11

We Will Cover..............................................................................12

Notes from the Lesson..........................................................................12

Lesson 7 Understanding Tables 13

What You Will Learn..............................................................................13

We Will Cover..............................................................................13

Notes from the Lesson..........................................................................13

Lesson 8 Understanding Relationships 15

What You Will Learn..............................................................................15

We Will Cover..............................................................................15

Notes from the Lesson..........................................................................15

Lesson 9 Database Files 17

What You Will Learn..............................................................................17

We Will Cover..............................................................................18

Notes from the Lesson..........................................................................18

Lesson 10 The Transaction Log 19

What You Will Learn..............................................................................19

We Will Cover..............................................................................19

Notes from the Lesson..........................................................................19

Lesson 11 Understanding System Databases 21

What You Will Learn..............................................................................21

We Will Cover..............................................................................21

Notes from the Lesson..........................................................................21

Lesson 12 Processor Settings 23

What You Will Learn..............................................................................23

We Will Cover..............................................................................24

Notes from the Lesson..........................................................................24

Lesson 13 Memory Settings 25

What You Will Learn..............................................................................25

We Will Cover..............................................................................26

Notes from the Lesson..........................................................................26

Lesson 14 Backing Up Databases 27

What You Will Learn..............................................................................27

We Will Cover..............................................................................28

Notes from the Lesson..........................................................................28

Lesson 15 Scheduling Backups 29

What You Will Learn..............................................................................29

We Will Cover..............................................................................29

Notes from the Lesson..........................................................................29

Lesson 16 Restoring Databases 31

What You Will Learn..............................................................................31

We Will Cover..............................................................................31

Notes from the Lesson..........................................................................31

Lesson 17 Restoring System Databases 33

What You Will Learn..............................................................................33

We Will Cover..............................................................................33

Notes from the Lesson..........................................................................33

Lesson 18 Managing Logins 35

What You Will Learn..............................................................................35

We Will Cover..............................................................................35

Notes from the Lesson..........................................................................35

Lesson 19 Managing Users 37

What You Will Learn..............................................................................37

We Will Cover..............................................................................37

Notes from the Lesson..........................................................................37

Lesson 20 Database Roles 39

What You Will Learn..............................................................................39

We Will Cover..............................................................................39

Notes from the Lesson..........................................................................39

Lesson 21 Ownership Chaining 41

What You Will Learn..............................................................................41

We Will Cover..............................................................................41

Notes from the Lesson..........................................................................41

Lesson 22 Data Manipulation Language 43

What You Will Learn..............................................................................43

We Will Cover..............................................................................43

Notes from the Lesson..........................................................................43

Lesson 23 Data Definition Language 45

What You Will Learn..............................................................................45

We Will Cover..............................................................................45

Notes from the Lesson..........................................................................45

Lesson 24 Stored Procedures 47

What You Will Learn..............................................................................47

We Will Cover..............................................................................47

Notes from the Lesson..........................................................................47

Lesson 25 Functions 49

What You Will Learn..............................................................................49

We Will Cover..............................................................................49

Notes from the Lesson..........................................................................49

Lesson 26 Views 51

What You Will Learn..............................................................................51

We Will Cover..............................................................................51

Notes from the Lesson..........................................................................51

Lesson 27 Triggers 53

What You Will Learn..............................................................................53

We Will Cover..............................................................................53

Notes from the Lesson..........................................................................53

Lesson 28 CLR Integration 55

What You Will Learn..............................................................................55

We Will Cover..............................................................................55

Notes from the Lesson..........................................................................55

Lesson 29 Indexing Overview 57

What You Will Learn..............................................................................57

We Will Cover..............................................................................57

Notes from the Lesson..........................................................................57

Lesson 30 Working with Indexes 59

What You Will Learn..............................................................................59

We Will Cover..............................................................................59

Notes from the Lesson..........................................................................59

Lesson 31 Included Columns 61

What You Will Learn..............................................................................61

We Will Cover..............................................................................61

Notes from the Lesson..........................................................................61

Lesson 32 Application Security 63

What You Will Learn..............................................................................63

We Will Cover..............................................................................63

Lesson 33 Abstraction Layers 65

What You Will Learn..............................................................................65

We Will Cover..............................................................................65

Notes from the Lesson..........................................................................