Building and Optimizing a SQL Server Database (70-464)
4.1 (33 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
970 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Building and Optimizing a SQL Server Database (70-464) to your Wishlist.

Add to Wishlist

Building and Optimizing a SQL Server Database (70-464)

Learn how to build and optimize database solutions while preparing for the Microsoft SQL Server 70-464 Exam
4.1 (33 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
970 students enrolled
Created by LearnSmart LLC
Last updated 4/2016
English
English
Price: $200
30-Day Money-Back Guarantee
Includes:
  • 12 hours on-demand video
  • 379 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Gain a thorough overview of the various tools and services that are available for you to administer the SQL Server or develop applications.
  • Start writing SQL statements to define tables, insert data into tables, and update data in tables.
  • Store the data in appropriate table structure and with appropriate attributes for each column.
  • Best design & implementing your tables and views by reviewing tables, normalization, denormalization, schemas, temporary tables and computed columns.
  • Within database development be able to monitor indexes and how your query performs, and how to optimize both further.
  • Ensure data integrity and constraint considerations by learning about how to enforce data integrity at the application level, the types of data integrity, and the available options for enforcing data integrity with identity property and sequences.
  • Review the core concepts related to the use of execution plans and learn how to read SQL Server Execution Plans.
  • Go indepth into storing and querying Extensible Markup Languages (XML) data in SQL Server.
  • Understand locking mechanisms, isolation levels, and transactions to increase physical integrity of data for every transaction.
  • Learn about Transact-SQL statements, User-Defined Functions, Data Manipulation via Triggers, T-SQL coding to optimize your SQL Server database.
  • Utilize our paired demonstrations that exemplify and provide step by step real world application and use of many concepts that are discussed.
  • Prove to yourself, and others, that you are ready for Microsoft’s Developing Microsoft SQL Server Databases (70-464) certification exam with our integrated quizzes and finals.
View Curriculum
Requirements
  • There is not any requirements for this course.
  • A computer with internet.
  • It is helpful to have a basic understanding of and interest in SQL Server 2012.
  • This is the 1st (out of 2) Courses in our SQL Server 2012 Database Series, next is "Designing a SQL Server Database and Storage Solution: Microsoft 70-465" Course
  • Please review this course map to see just how we align and partner with Microsoft in providing this training: https://drive.google.com/file/d/0B-bU4vzmt_NyVUlZc2NNN0pYZGs/view?usp=sharing
Description

Welcome to Building and Optimizing a SQL Server Database: Microsoft 70-464 with LearnSmart.

The following lectures will prepare you for Microsoft SQL Server 70-464 exam, which goes toward the Microsoft Certified & Professional (MCP) and MCSE: Data Platform Solutions Expert certification. These lectures teach the principles, techniques, and tools needed to successfully prepare for and pass the “Developing Microsoft SQL Server Databases” exam.

This is the 1st Course in our 2 Course SQL Server 2012 Database Series including:

  1. Building and Optimizing a SQL Server Database: Microsoft 70-464
  2. Designing a SQL Server Database and Storage Solution: Microsoft 70-465

Course Overview:

This course is designed to prepare the student for the Microsoft SQL Server 2012 Exam 70-464 Students will like a look at how to best build and implement databases. This course covers a majority of topics that you will find on the exam, including: creating tables, maintaining indexes, implementing data types, and working with XML data. Learn the main exam objectives by learning these key concepts we cover:

  • Implement database objects
  • Implement programming objects
  • Design database objects
  • Optimize and troubleshoot queries

Our lectures are paired with a variety of demonstrations and quizzes giving visual example and real world look of the concepts that will be talked about.

Our sections listed below are can be taken in any order, as a review of a particular concept or exam domain. However, if you are just becoming familiar with SQL Server 2012 and it’s database, it is recommended that you view the sections sequentially.

Course Breakdown:

Section 1: Gain a thorough overview of the various tools and services that are available for you to administer the SQL Server or develop applications.

Section 2: Start writing SQL statements to define tables, insert data into tables, and update data in tables.

Section 3: Best design & implementing your tables and views by reviewing tables, normalization, denormalization, schemas, temporary tables and computed columns. Also be able to store the data in appropriate table structure and with appropriate attributes for each column.

Section 4: Ensure data integrity and constraint considerations by learning about how to enforce data integrity at the application level, the types of data integrity, and the available options for enforcing data integrity with identity property and sequences.

Section 5: Go over how to design and implement views in SQL Server, and will also talk about partitioned and indexed views

Section 6: Within database development be able to monitor query performance, and how to optimize both further.

Section 7: Review the core concepts related to the use of execution plans and learn how to read SQL Server Execution Plans.

Section 8: Take a look at how to determine the outcomes of the decisions that SQL Server makes. We will cover the core concepts related to the use of execution plans, the roles of the most common execution plan elements, and how to work with execution plans.

Section 9: Review stored procedures and all its potential benefits. We cover how to pass parameters to stored procedures and receive output, as well as how to control the execution context of a stored procedure.

Section 10: Compare the user-defined functions in SQL Server to functions in high-level languages and become familiar with will be functions, procedures, and reusable components. This course will be paired with several demos that offer a real world example at the concepts that we will discuss.

Section 11: Learn about the different options that SQL Server provides for manipulating data with triggers. Be able to determine when to use constraints and when to use triggers.

Section 12: Meet complex business requirements by implementing managed code in SQL server and review importing and cataloging assemblies and choosing between managed code and T-SQL for your situation.

Section 13: Understand locking mechanisms, isolation levels, and transactions to increase physical integrity of data for every transaction.

Section 14: Learn about Transact-SQL error handling, how it has traditionally been implemented, and how to implement structured exception handling in T-SQL.

Section 15: Go indepth into storing and querying Extensible Markup Languages (XML) data in SQL Server.

Section 16: Review how to work with SQL Server Spatial Data by looking into types of spatial data, spatial reference identifiers, spatial data in applications, and more

Recommendations:

Learn from others! Here are some reviews from participants (Click on reviews to see full list of reviews)

  • Great Material - This course is a great reference and way to refresh your skills for the new Net+ exam! I needed a good refresh before taking the exam and this course is clearly laid out and copiously covers the material for the exam. Great course, well worth the money! -- M.B.
  • Perfect Course - Not only do you get great lectures that are pretty much PowerPoints with an instructor teaching you the concepts, there are also Demo lectures that show you how each of the concepts works in real life. This is such valuable information to have when taking the test. I feel very confident that this course is the best Network+ course on Udemy. -- D.R. Jr.
  • A Complete Jumpstart - What has impressed me the most is the depth of the content to this coarse. Each HD video is crystal clear in both the visual and audio, even including quality closed captioning (great for reading along with the lectures). I wasn't interested in the flashcards or the crossword puzzles when I started the program, but I've found them to be very useful in staying engaged in the class when away from home. This is everything you need to get started and have a classroom quality experience from home. Dive in and stay focused! -- P.C.
Who is the target audience?
  • Looking to enhance your skills in the SQL Server 2012 database system?
  • Are you an IT professional, database administrator, support specialist, or database developer? These concepts are key for these kind of professions.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 383 Lectures Collapse All 383 Lectures 11:46:58
+
Course Orientation
1 Lecture 01:42
+
Introduction to SQL Server Toolset & Services
27 Lectures 48:04

SQL Server Components
01:51

SQL Server Instances
02:23

SQL Server Editions
02:49

SQL Server Toolset and Services
01:08

Connecting to SQL Server
04:06

Knowledge Check
00:38

Introduction to Management Studio
01:15

SHOW ME Demonstration: SQL Server Management Studio
05:01

SQL Server Data Tools
00:36

SHOW ME Demonstration: Using SQL Server Data Tools
01:53

SHOW ME Demonstration: Using Books Online
01:51

Knowledge Check
00:46

Chapter 1 & 2 Quiz
6 questions

Configuring SQL Server Service
03:04

SHOW ME Demonstration: Verify SQL Server Component Installation
01:14

SHOW ME Demonstration: Check Service Accounts of Instance
01:19

SHOW ME Demonstration: Enable Named Pipes Protocol
01:45

SHOW ME Demonstration: Create an Alias for DevNew
01:43

SHOW ME Demonstration: Connecting to the Alias
02:26

Knowledge Check
00:49

Sqlcmd Utility
01:25

Windows PowerShell
00:57

SQL Server Tools
02:34

SHOW ME Demonstration: Using SQL Server Profiler
02:09

Third-Party SQL Server Tools
01:08

Knowledge Check
00:45

Conclusion
00:59

Final: Introduction to SQL Server Toolset & Services
12 questions
+
Working with Data Types
22 Lectures 56:15

Data Types
02:34

Knowledge Check
00:41

Exact Numeric Data types
03:27

Approximate Numeric Data Types
01:18

SHOW ME Demonstration: Working with Data Types
06:14

Knowledge Check
00:34

Chapter 2 & 3 Quiz
4 questions

Datatime Data Type
04:04

SHOW ME Demonstration: Working with date and Time Data Types
03:10

Null and Not Null Columns
03:03

SHOW ME Demonstration: Working with Identity and GUID
02:02

Knowledge Check
00:31

Unicode
02:02

Character Data Types
00:55

Collations
01:15

SHOW ME Demonstration: Working with Collations
03:48

SHOW ME Demonstration: Working with Collations
02:03

Converting Data Types
02:03

SHOW ME Demonstration: Converting Data Types
06:24

Knowledge Check
00:52

Knowledge Check
00:44

Conclusion
01:43

Final: Working with Data Types
14 questions
+
Designing and Implementing Tables & Views
24 Lectures 38:52
Designing and Implementing Tables
02:43

Ensuring Data Integrity
02:02

Security at Table Level
01:04

Knowledge Check
00:21

Normalization
01:35

Normal Forms
03:37

Knowledge Check
00:46

Chapter 1 & 2 Quiz
3 questions

Keys in SQL Server
01:48

Foreign Keys in SQL Server
02:51

Knowledge Check
00:32

System Tables
00:54

Schemas
02:47

SHOW ME Demonstration: Schemas
02:24

Knowledge Check
00:36

Chapter 3 & 4 Quiz
4 questions

Creating and Drop Tables
02:03

Altering Tables
00:38

SHOW ME Demonstration: Working with Tables
02:51

SHOW ME Demonstration: Altering Tables
02:26

Knowledge Check
00:31

Temporary Tables
01:14

SHOW ME Demonstration: Temporary Tables
01:52

Computed Columns
00:49

SHOW ME Demonstration: Computed Columns
01:58

Knowledge Check
00:30

Final: Designing and Implementing Tables & Views
7 questions
+
Ensuring Data Integrity through Constraints
23 Lectures 43:17

Data Integrity
01:06

Maintaining Data Integrity
04:50

Knowledge Check
00:20

Types of Integrity
01:09

Enforcing Data Integrity
01:54

Implementing Domain Integrity
00:40

Domain Integrity: Data Types
01:47

Domain Integrity: Column Nullability
01:20

Knowledge Check
00:45

Chapter 1 & 2 Quiz
4 questions

DEFAULT Constraint
01:57

CHECK Constraint
01:20

Primary Key Constraint
02:11

SHOW ME Demonstration: Data and Domain Integrity
04:51

Referential Integrity
01:56

Cascading Referential Integrity
01:29

Considerations for Constraint Checking
01:18

SHOW ME Demonstration: Entity and Referential Integrity
04:47

IDENTITY Property
02:05

Sequences
01:04

SHOW ME Demonstration: Working with Identity & Sequences
03:41

Knowledge Check
00:39

Conclusion
01:05

Final: Ensuring Data Integrity through Constraints
10 questions
+
Designing and Implementing Views
27 Lectures 48:43
Views
03:37

Advantages of Views
02:13

Knowledge Check
00:35

Types of Views
01:42

System Views
02:57

Dynamic Management Views
01:47

SHOW ME Demonstration: Querying System and Dynamic Management Views
07:02

Knowledge Check
00:53

Chapter 2 Quiz
5 questions

Creating Views
02:13

Dropping Views
01:13

Knowledge Check
00:36

Creating Views
01:03

Dropping Views
02:48

Knowledge Check
00:52

Updatable Views
01:19

SHOW ME Demonstration: Implementing Views
03:37

Knowledge Check
00:53

Obfuscating View Definitions
00:44

Performance Considerations for Views
01:15

Partitioned Views
01:14

SHOW ME Demonstration: Investigating Views and Performance
03:16

SHOW ME Demonstration: View Cost vs. Query Cost
01:10

Knowledge Check
00:18

Creating Indexed Views
01:12

Considerations for Indexed Views
00:50

SHOW ME Demonstration: Querying Indexed Views
02:45

Knowledge Check
00:39

Final: Designing and Implementing Views
9 questions
+
Introduction to Indexing
31 Lectures 54:50

Indexing in SQL Server
02:02

Core Indexing Concepts
01:45

The Need for Indexes
00:50

Knowledge Check
00:52

Chapter 1 Quiz
4 questions

Index Structures
02:39

Index Fragmentation
01:29

SHOW ME Demonstration: Viewing Index Fragmentation
05:02

Knowledge Check
00:36

Data Types and Indexes: Numbers
01:40

Data Types and Indexes: Characters
00:53

Knowledge Check
00:36

Data Types and Indexes: Numbers
02:41

Data Types and Indexes: Characters
01:41

Knowledge Check
00:49

Index Statistics
00:53

SHOW ME Demonstration: Viewing Index Statistics
02:57

Table Structures in SQL Server
02:07

Forwarding Pointers
01:09

Knowledge Check
00:41

Clustered Index
03:02

Unique vs. Non-Unique clustered indexes
01:51

Working with Clustered Indexes
04:37

Incorporating Free Space in Indexes
02:06

Knowledge Check
00:28

Working with Non-Clustered Indexes
01:40

INCLUDE Clause, Lookups, Indexes on Views
01:26

SHOW ME Demonstration: Create Clustered/Non-clustered Indexes
03:25

SHOW ME Demonstration: Modify, Delete Clustered Indexes
01:27

Knowledge Check
00:39

Conclusion
01:52

Final: Introduction to Indexing
10 questions
+
Advanced Indexing
18 Lectures 34:11

Indexing in SQL Server
02:20

Indexed Views
01:19

SHOW ME Demonstration: Create a Indexed Views
02:11

Knowledge Check
00:32

Chapter 1 Quiz
4 questions

Filtered Indexes
03:32

SHOW ME Demonstration: Create a Filtered Index
01:56

Creating Full-Text Indexes
01:30

Full-Text Indexes
01:56

Knowledge Check
00:31

What are Columnstore Indexes?
01:09

Full-Text Indexes
02:50

Knowledge Check
01:36

What are Columnstore Indexes?
02:02

Nonclustered Columnstore Indexes
03:40

SHOW ME Demonstration: Create a Columnstore Index
04:08

Knowledge Check
00:41

Conclusion
00:50

Final: Advanced Indexing
8 questions
+
Reading SQL Server Execution Plans
19 Lectures 33:06

SQL Server Query Execution Plan Phases
03:21

Execution Plan
01:55

Types of Execution Plans
02:26

Types of Execution Plans
02:00

Execution Context
01:28

Knowledge Check
00:31

Chapter 1 Quiz
5 questions

Elements
02:14

Nested Loops and Look Ups
01:08

Joins and Matches
01:21

Elements
02:13

Nested Loops and Look Ups
00:44

Joins and Matches
01:58

Capturing Exception Plan Information
03:01

SHOW ME Demonstration: Capture Execution Plans using Activity Monitor
00:54

Re-Executing the Query Plans
01:45

SHOW ME Demonstration: Viewing Cached Plans
01:05

Knowledge Check
00:36

Conclusion
01:09

Final: Reading SQL Server Execution Plans
10 questions
+
Designing and Implementing Stored Procedures
31 Lectures 56:48

Stored Procedures and Applications
03:17

Knowledge Check
00:56

Transact-SQL and Managed Code
02:13

Knowledge Check
01:43

Benefits of Stored Procedures
04:27

Knowledge Check
01:36

System Stored Procedures
03:10

Knowledge Check
01:36

SHOW ME Demonstration: Working with System Stored Procedures
02:13

Creating Stored Procedures
04:23

Knowledge Check
01:22

SHOW ME Demonstration: Working with System Stored Procedures
01:42

Creating Stored Procedures
03:06

Knowledge Check
00:46

SHOW ME Demonstration: Drop Stored Procedure
00:40

User-Defined Stored Procedure
02:00

Knowledge Check
01:00

Chapter 1 & 2 Quiz
4 questions

Parameters in Stored Procedures
01:35

Input Parameters
01:28

Output and Return Parameters
02:00

SHOW ME Demonstration: Stored Procedures with Input and Output Parameters
02:27

Query Plans
02:25

Controlling Execution Context
01:12

Knowledge Check
00:30

Tokens
01:15

Using EXECUTE AS
02:02

SHOW ME Demonstration: Execution Context
01:22

Best Practices
01:19

Knowledge Check
00:45

Conclusion
01:35

Chapter 3 & 4 Quiz
5 questions

Final: Designing and Implementing Stored Procedures
10 questions
7 More Sections
About the Instructor
LearnSmart LLC
4.2 Average rating
8,816 Reviews
77,749 Students
68 Courses
Smarter Training. Never Open a Textbook.

LearnSmart has served the learning community with high-quality professional skills and IT certification training since 1997. In that time, the company has helped thousands earn career-related certifications from respected vendors, such as Project Management Institute (PMI)®, Cisco, Microsoft, CompTIA, Oracle, and EC-Council. LearnSmart offers over 1,000 courses covering IT, project management, administrative, HR, and workplace safety topics. With a rapidly growing clientele of individuals and corporations, LearnSmart serves a broad range of Fortune 500 companies to universities, as well as government institutions and the armed forces.

LearnSmart is able to meet diverse career and learning needs through its extensive selection of training. LearnSmart uses industry experts to give learners the most up-to-date content in a video format as well as training resources including; reference tools, pretests, study guides and labs. For more information about how we can help pinpoint and solve your individual or multi-user training challenges.

PMI is a registered trademark of the Project Management Institute, Inc.