Quantcast

Start with 7 free days of training.

Gain instant access to our entire IT training library, free for your first week.
Train anytime on your desktop, tablet, or mobile devices.

Developing SQL Server Databases

This Microsoft SQL Server video training course with Garth Schulte covers Microsoft’s in-demand server, including how to build, implement, optimize and troubleshoot SQL Server database, and programming objects....
This Microsoft SQL Server video training course with Garth Schulte covers Microsoft’s in-demand server, including how to build, implement, optimize and troubleshoot SQL Server database, and programming objects.

Database developers are the construction workers of database implementations. They love to build, are well paid, and constantly in high demand. This Nugget course aims to sharpen your development skills so when it comes time to build, you are equipped use the right tools and techniques for the job!

70-464 is the first exam for the SQL Server MCSE: Data Platform certification (retiring March 31, 2017, being replaced by the MCSE: Data Management and Analytics certification). This course helps you prepare for the 70-464 exam and beyond with real-world examples and live demonstrations.

Note: The exam associated with this course is scheduled to retire on March 31, 2017. Learners should plan accordingly to sit for the exam before its official retirement.

Recommended Experience
  • 2 years of experience working with SQL Server
  • Familiarity with writing T-SQL statements
Recommended Equipment
  • Microsoft SQL Server 2012
Related Certifications
  • MCSE: SQL Server 2014 Data Platform (retiring March 31, 2017)
  • MCSE: Data Management and Analytics (replacing MCSE: Data Platform)
Related Job Functions
  • Database designers
  • Database developers
  • Database administrators
Garth Schulte has been a CBT Nuggets trainer since 2001 and holds a variety of certifications spanning databases, development, and administration across vendors including Microsoft and Google.
 show less
1. Introduction to 70-464: Developing SQL Server 2012 Databases (28 min)
2. Creating and Altering Tables (36 min)
3. Designing and Implementing Security (22 min)
4. Designing Lock Granularity (30 min)
5. Maintaining Indexes (31 min)
6. Implementing Data Types (28 min)
7. Creating and Modifying Constraints (22 min)
8. Working with XML Data (28 min)
9. Writing Automation Scripts (35 min)
10. Designing and Implementing Stored Procedures (28 min)
11. Designing Table-valued and Scalar Functions (18 min)
12. Creating, Using & Altering User-defined Functions (21 min)
13. Creating & Altering Views (18 min)
14. Designing Tables (27 min)
15. Designing for Concurrency (27 min)
16. Creating and Altering Indexes (26 min)
17. Designing Data Integrity (21 min)
18. Designing Transactions (18 min)
19. Optimizing and Tuning Queries (28 min)
20. Troubleshooting Performance Problems (27 min)
21. Optimizing Index Strategies (18 min)
22. Capturing and Analyzing Execution Plans (25 min)
23. Collecting Performance and System Information (15 min)

Introduction to 70-464: Developing SQL Server 2012 Databases

00:00:00

Introduction to 70-464-- Developing Microsoft SQL Server 2012 databases. Hey, everyone, Garth Schulte from CBT Nuggets. First and foremost I just want to welcome you. And secondly, I want to thank you. Thank you for letting me be your guide and trainer through the world of SQL Server 2012.

00:00:16

This course introduction aims to get you familiar with what 70-464 is all about. From a certification perspective, where does 70-464 fit in the big picture? From an exam perspective, what do we need to know? What are some the exam objectives? And even from a real world perspective, what is a database developer, and what do they do?

00:00:33

So one more time, welcome to 70-464. It's a pleasure having you in class. And you're all so quiet. Nobody ever raises their hand or plays stump the chump. So, thank you. From here, we're going to jump into another white board and take a look at what this Nugget course is all about.

00:00:48

We'll start right off the bat by defining what a database developer is. And that's a tough one to do, a very tough one to do in the world of SQL Server and the world of IT, really, because a lot of the times, your application developer may be your database developer.

00:01:01

Or maybe your DBA is your database developer. Or maybe your database developer is your DBA and application developer. So those lines always get blurred, but we'll do our best to define what a database developer is. We'll also just get you familiar what the Nuggets in this course consist of, and I'll also get you familiar with the SQL Server certifications and where 464 fits.

00:01:22

Then we will take a look at the exam objectives. We'll head over to Microsoft's site so you can get familiar with what you need to know when it comes to 70-464. I'll also get you up to speed on what the Virtual Nugget Lab is all about. We're going to be spending a lot of time in the Virtual Nugget Lab.

00:01:36

It's where we're going to do all of our demonstrations and get our hands-on experience with SQL Server 2012 and the 70-464 objectives. And at the end here, I want to get you familiar with the Nugget Demo Database, both logically and physically. Now if you were with me through 70-461, 462, 463, I took it pretty easy on you as far as the database goes because we really needed to focus on learning Transact-SQL and learning a lot of the SQL Server objects and how to work with them.

00:02:03

But the thing is, 464, it is the beginning of the MCSE Data Platform Certification, so that means we need to up our game a little bit. So I did the same thing with our demo database. I upped our game. And I've got a really fun one, something that should be familiar to everybody, Nuggets.

00:02:21

And we're going to reverse engineer the Nugget website, turn it into a database, and make it a little more complex. So instead of working with just a few tables, we'll work with quite a few interrelated tables that will still allow us to cover all the concepts that we need to learn in 70-464.

00:02:36

All right, SQL Server professionals, start your engines. Let's do this. So what is a Database Developer or, as I like to say, DBD, because DBAs get their own acronyms? Why can't database developers? Why do we always have to have our title spelled out?

00:02:52

So database developers, our roles and responsibilities include designing and building the physical structures. We're the ones that take the company's data model, transform it into something that application developers can put their data into. And we're not just talking schemas here.

00:03:10

We're talking to data access layer around the schema, stored procedures, functions views. All of those physical objects that you see in SQL Server, the database developer is going to be responsible for building. And because of this, the database developer is often the BFF of many.

00:03:25

Now if you're not familiar with what a BFF is, you're not hip with what the kids say these days, we've got you covered here at Nuggets. It stands for Best Friend Forever. And I'm serious. If you get a job as a database developer, the first person you're going to meet is the database administrator, who's going to ask you, in not so many words, want to be my BFF?

00:03:43

And you're going to be like, well, aren't you going to take me to lunch first? But seriously, database developer is a crucial piece of the puzzle. Database administrators are going to rely heavily upon them because most of the time database administrators aren't going to have that deep level of Transact-SQL knowledge and the ability to create and design databases and their internal structures.

00:04:02

And application developers are going to rely heavily on database developers for their application needs, to ensure once again that they can get data in and take data out of these structures. And if I had to use an analogy for what a database developer is, I would say that they are the construction workers of SQL servers.

00:04:19

We're the ones that are physically building these structures from the ground up, usually based on blueprints that we receive from architects that describe the data models, the data model based on the business requirements. So we take these data models, and we transform them into structures that application developers can use to store their information.

00:04:39

Moving on to the certifications, there are four tiers of certifications here in a world of SQL Server 2012. The first one I left out. That is just pass any single exam, and you'll be an MCP, a Microsoft Certified Professional. The second tier is an MCSA, a Microsoft Certified Solutions Associate.

00:04:56

And that's going to require that you pass all three of these exams, 461, 462, and 463. And of course, we have you covered here at Nuggets with all three of those available. Once you get an MCSA, you can move on to one of the two MCSE or both, if you really want to go crazy, of the MSCE certifications.

00:05:13

MCSE stands for Microsoft Certified Solutions Expert. And the MCSA is a prerequisite either of these. Now the Data Platform-- by far, the most popular one of the two, simply because it's a wider audience. It's more general SQL Server knowledge. Business Intelligence is more geared towards SQL Server Reporting Services, Analysis Services, Integration Services, the whole business intelligence side of SQL Server.

00:05:38

There's one more tier of certification out there-- or I should say was one more tier of certification-- called the MCM, the Microsoft Certified Master. And in SQL Server it was the Data Platform version, so you needed to be an MCSE DP in order to even attempt the exam and the lab associated with this.

00:05:54

But it's all irrelevant because just last week Microsoft announced that they were retiring all the MCSM certs across all of their products. The reasoning was it only targeted a small percentage of the community. Literally only a few hundred people have the certification.

00:06:10

And if you read into that, it basically means they weren't making enough money off of it. So they said they will be replacing this with a new top tier certification that is more generalized, so it's more attainable to everyone. So stay tuned for that.

00:06:24

We'll see how that plays out here in the near future. Moving on up, here's what you can expect out of this Nugget course. 23 Nuggets-- the one introductory Nugget you're watching now and 22 objective Nuggets. I call them objective Nuggets because I like to line up the Nuggets with Microsoft's exam objectives.

00:06:39

We're going to head over their site right after this. We'll get into the details of what those objectives are. But essentially, they lay out a bullet point with a bunch of objectives, and that is going to be a Nugget in this course. And that way, you have a nice way to relate.

00:06:51

If you're looking through the objectives for the exam and you say, I have no idea what that is, then you can just use that title, come over to Nuggets, find that Nugget and start watching. And I'd love to cover the objectives the best I can. But I'll be honest, sometimes trying to decipher what they mean in some of those objectives is like trying to decipher some ancient Egyptian writing on the wall.

00:07:13

So we'll cover as best we can-- and we always go above and beyond-- do real world stuff, do demos. And we'll have a lot of fun here. So 23 Nuggets total, broken down like so-- and this is how it's broken out on Microsoft's website. Implementing database objects-- 31% of the exam.

00:07:29

We have seven Nuggets on that. Implementing programming objects-- 21% of the exam, and we have five Nuggets on that. Designing database objects-- 24%, five Nuggets. And then optimizing and troubleshooting queries-- 24%, and we have five Nuggets on that as well.

00:07:43

Let's head over to Microsoft's website and check out those objectives in detail here for the 70-464 exam. So I've got Google Chrome fired up. I'm already on the site. If you want to get here yourself, just Google 70-464. It will be the first link that pops up.

00:07:55

I'll also give you the cbt.gg URL for it. But here it is, 70-464. If we scroll down here, here are those objectives, the objective categories. If we expand it, here they are all broken out. Now here's the funny thing. You notice I have the 70-461 exam over here.

00:08:10

I like to call 464 70-461++. And you can see that just by the first objective. So check this out. Let's go over to 70-464. No thanks, we don't need help there, sir. And scroll down here to create Database Objects, Create and Alter Tables, Simple Statements.

00:08:27

Ready for this? 70-464-- Create and Alter Tables, Complex Statements. So you can see 461 is just all about getting your feet wet with T-SQL and doing all the basics-- Create, Alter, Drop, that kind of stuff. 464, now we're going to get into more advanced sides.

00:08:42

And the first thing we're going to learn about is temporary objects-- temporary tables, variables. We'll talk about alternatives to triggers, data version control, and a lot of, again, the complex side of tables, some of the more advanced features. Then we'll get into security here.

00:08:58

We'll talk about locking granularity level, how to design that, how to maintain indexes. We've got an Index Nugget over in 70-461, so this will be more the advanced side of indexes. We'll look at data types. Again, we've got a Data Types Nugget in 461, but this will be more of the advanced side of using data types and especially here developing a CLR data type, the Common Language Runtime.

00:09:19

So we'll use .NET to build our own data type. Create and Modify Constraints-- again, complex statements. We've got a simple statement version over in 70-461. So I'll show you some cool, real world stuff here with constraints, like using a user-defined function to reference a field in another table-- very, very useful real world thing to do, so I'll show you how to do that in Modify Constraints.

00:09:41

And then working with XML data-- XML data, of course, is a big thing, great data format for interoperability, not a great format for performance. But it's all over the place. We've got to deal with it. So we'll look at XML top to bottom. Then we'll look at how to implement programming objects, writing automation scripts.

00:10:01

That will be a fun one. We'll look at many of the common things that we can do in SQL. I'll show you a couple of different ways to automate it. We'll use Agent. We'll use SSIS. We'll look at the whole big picture there as far as automation goes. Design and Implement Stored Procedures-- everybody loves stored procedures.

00:10:15

Well, we looked at the very simple way to work with stored procedures and 461. Here we'll look at the advanced side. Back in 70-461, we just really looked at the simple side of stored procedures, how to create, alter, and execute them. and we did create a simple data access layer.

00:10:30

But this time, we're going up our game a little bit more and design and implement. We'll look at techniques for developing stored procedures. We'll even look at how to program stored procedures with the T-SQL and CLR. And we'll look at a more complex data access layer, since we'll be hitting a more complex database schema.

00:10:47

We also have some UDF Nuggets here, one on designing table and scalar valued functions, one on creating, using, and altering UDFs. You'll notice again the CLR in here. That's going to be something that you see in this course. And that's because I love using it.

00:11:04

If you have C# and VB skills, why not leverage them from SQL Server? Something that could be extremely difficult to do in a stored procedure using Transact-SQL can be really easy to do in a programming language. And I've had that happen a lot in the real world, where I'm like, oh, I can't do that in T-SQL.

00:11:20

Or I can, but it's going to take me three days. Do you want me to do it in three days, or do you want me to go whip up something in VB in an hour or C# in an hour and bring it into SQL? So I love CLR stuff, and I think that it will be lot of fun to teach this stuff and show you how to use it.

00:11:34

Moving on here, we have Creating and Altering Views, Complex Statements. And if you remember back in 461, we also Creating and Altering Views, Simple Statements. So this time, rather than, again, just looking at creating and altering and dropping and getting familiar with syntax, we're going to be looking at best practices for design, how to work with partitioned tables and partition views.

00:11:53

And this is actually a really good because generally you don't want things hitting your tables directly. We want to control how things and where things are coming from that hit our tables. So this is why stored procedures and views are going to our avenue for controlling that access.

00:12:07

So we'll look at exactly how to work with that. Moving on here, let's head down to Designing Database Objects. Designing tables will be a good one. Everybody needs to know that's going to do this stuff proper design. So we'll look at data design patterns.

00:12:21

We'll talk about all the common things you hear about-- normalized versus denormalization, using GUIDs as cluster index, or "gwids," the Unique Identifier Data type, understanding performance implications between temporary tables and temporary variables, set and row-based logic, encryption, table partitioning.

00:12:37

Filestream and filetable is actually really, really good for our Nugget Demo Database because if we're modeling the CBT Nuggets data model, well guess what they do? Videos-- videos, unstructured data. You never want to store that kind of information, at least that volume of information, inside of a table as binary data.

00:12:55

That's just a big no-no. Generally what you'll do is store it on the file system and then store the path to it in a database or a URL to it in the database. In our case here, we can use some of the new features in SQL, filestream and filetable, which allows you to take your unstructured data, leave it in the file system, and then link to it and create all that metadata it, all that structured metadata around that unstructured data, store that in the database.

00:13:20

And the two are linked together. So if something happens to one in the file system-- happens to it in the database. Something happens to that record in the database-- happens to it in the file system. Really cool stuff, so we'll look at a good demo there.

00:13:30

We'll also see how to design for concurrency. Locks are always a big headache for many applications out there. So we'll look at proper design for locking. Creating and altering indexes-- indexes are always our first go-to feature for increasing performance.

00:13:48

So it's always good to know your options there when it comes to indexes. We've got a few indexing Nuggets in this course. Data integrity-- we'll look at all the different ways to design data integrity. And we'll also look at implicit and explicit transaction design and how to work with transactions, nested transactions, outer transactions, all that fun stuff.

00:14:07

And at the end here, we're going to look at optimizing and troubleshooting queries. So we'll start with that Optimize and Tune Queries. We'll identify those long running queries. We'll tune a poorly performing or poorly written query. And I tell you, if you've ever been in charge of a database where you have hundreds of concurrent users across multiple applications and all of the sudden it comes to a grinding halt and you know it's not hardware-related or server-related or network-related and you know that it's just somebody submitted a query that is hogging up all the resources and creating locks and creating chaos for everyone else, the first thing you need to do is identify it.

00:14:43

And then you need to make sure that it doesn't happen again. So that's what we'll look at here. And sometimes you may not have control over that if that query is wrapped inside of an application that you didn't develop. But in our case here, let's say that we built the query, and now we need to optimize it.

00:14:56

That's what we'll look at. And we'll actually look at some cool tools along the way here, too, like the Database Tuning Advisor. That's a really good one. That can really not only identify potential problems inside of our query design, in the workload hitting our tables, but it can also write code for us, which is always good.

00:15:11

It's always good when things can write code for you. So we'll look at how to optimize and tune queries. We'll also look at how to troubleshoot and resolve performance issues. This is mainly server and database-level stuff. We'll look at how to optimized indexing strategies, how to choose the right indexing strategy for the job.

00:15:26

If we scroll down here, we've got how to capture and analyze execution plans. That's a good one. I like to think of execution plans as the end result of one of those mazes. You ever go out to a restaurant, and you know how sometimes they give you the kids' menus?

00:15:41

And they have those big mazes. You give them crayons, and they draw their maze? That's what I like to think of execution plans are. Your query is at the start. When you hit Execute, it goes to the finish. And the query plan is the end result. How did it get there?

00:15:53

Did it take the path of least resistance? Did it feel like taking the scenic route that day? Or where they like my kids, and they just drew a line from start to finish, which would be awesome if SQL Server could do that. But I like to think of SQL Server's data as that.

00:16:08

It's a big maze. And a query has to navigate that maze to get the data, what route did it take. So understanding execution plans and being able to read them can do so many things for your troubleshooting skills. So we'll take a good look at how to do that.

00:16:21

And at the end, we'll look at how to collect performance and system information. We've got a lot of nice tools in 2012 to help us collect performance and system information. Data or dynamic management views give us insight to the state of the system and lots of different data points here for both performance and system info.

00:16:38

SQL Profile, that's been one of my go-to tools forever. It was SQL Trace back in SQL 6.5 in the mid '90s. SQL Server 7 introduced SQL Server Profiler. And it's just an awesome tool because it gives you insight into everything that's hitting your server or database or anything you can think of.

00:16:55

Any event that you can think of that happens in SQL you can trap for, and you can create a trace and just use that information. And then you can also use that to generate a workload. So you can let it run for a while. It'll give you a workload that hit the database.

00:17:08

You can throw that into the Database Engine Tuning Advisor, which will tell you, based on that workload, advise us on what to do-- add more indexes, add statistics, add all kinds of stuff. We'll also look at Extended Events as well, one of the new features in 2012, which is a lot like SQL Server Profiler, just built into SQL Server.

00:17:27

So there's a taste of what we're going to be talking about here in this course, the objectives we're going to be covering. Let's switch gears and talk about the sample database we're going to be working with. I like to call it the Nugget Demo DB. But again, we're going to reverse engineer Nuggets's website and use that is our data model for our sample database.

00:17:44

Let's head over to CBT Nuggets website and see if we can figure out their data model. So we're going to start here with the Video Training link. Let's drop this down. And you'll notice, right off the bat, we have categories. We have top-level categories-- IT Training, Project Management, Office Productivity.

00:17:58

We have subcategories. And if we click on a category, it'll take us into there, and we have more categories. So here's the cool thing. Rather than have a bunch of category and subcategories table, we're going to create one categories table called Course Categories.

00:18:11

Inside of there, we'll have a column called Parent Category ID. And that will allow us in our data access layer to create a Common Table Expression, maybe inside of a stored procedure or viewer, however we do it, that will recursively look at itself to represent this hierarchy.

00:18:27

So this will be a great, real world implementation on how to do recursion using CTEs and also good design because we're only going to have one category table. Now let's scroll down a little bit here, and we have courses-- courses, formerly known as series.

00:18:42

We're going to need a Courses table to keep track of all the courses at CBT Nuggets. And if we drill into a course-- let's look at James' 70-687 here-- we're going to need a couple of things to track inside of a course. We're going to need to track the course title, description, trainer associated with it.

00:18:58

And you can tell by looking at this we're going to need a Trainer's table, so we can link trainers here with courses. And we could also use the Features table. I didn't add one into our schema. Maybe we will. If we do, we're going to need a Features table to hold all the features.

00:19:10

And then we're going to need an intermediate table between Courses and Features so we can tie multiple features to a course. That could be a good one. Maybe we will do that. And then we're obviously going to need a Nuggets table to keep track of all the Nuggets and tie those into the courses, so we know what Nuggets are associated with what courses.

00:19:25

And in the Nuggets table, we're going to need to track the Nugget title. We're going to need to track the duration, the description. And then if we zoom into a Nugget, now, this is where we're going to need a learner because I'm logged in right now. So any time I watch a video, I can track notes.

00:19:42

These notes are only associated with me. I can create bookmarks. These bookmarks are only associated with me. So we're going to need a Learners table. So so far we need a Courses table, Categories table, Nuggets tables, Trainers table, Learners table.

00:19:54

We're also going to then need a Learner Nugget Info table, so we can keep track of notes and bookmarks, which is going to be associated with a Nugget and a learner. One other really neat table that we're going to create is going to be called Learner Nugget Activity.

00:20:08

And it's there to track everything that a learner does inside of this video player-- every time you play, every time you pause, every time you skip ahead, go back, mute, or turn you volume up and down or even control playback speed. All of that is going to tracked every time it happens.

00:20:22

Why? Because I am fresh off of big data Hadoop course. And on top of that, SQL Server 2014, one of the highly touted features is really simple Hadoop connectivity. Microsoft has fully embraced the big data movement. So we'll prepare for the future by incorporating some big data thinking into our database design.

00:20:41

And what's kind of funny is that-- I talk about this in the Hadoop course-- but Netflix does exactly that. They check everything you do inside of this video player. Play, Pause, Rewind, Fast Forward, it's all tracked. And then the decisions and the answers they can dig out of that data is what's hot, what's not, what are the pain points in the viewing process?

00:20:57

And then they can refine their product, which will, in the end, enhance their viewers' experience. So really good stuff. Now let's take all that we just talked about here and put it on paper, so we can see exactly what we need when it comes to our design.

00:21:12

So I've just got our three pages that we looked at up here. We've got our Categories page. We've got our Course page, and then we have our Nugget page. And remember, so we're going to need the categories. This is going to be our Category table. Categories are going to be linked to courses.

00:21:25

Here's our Courses table. Courses are going to be linked to trainers. So here's our Trainers table. We're going to need a Nuggets table, which is going to be linked to Courses as well. We're going to have a Learner table, which is going to contain all of us.

00:21:37

Customers, learners, anybody that has a CBT Nuggets account that can watch videos will be inside of this table. And then we're going to also need a Learner Info table that's going to be linked here to our Nugget table, so we can keep track, basically, what our learners are doing with their Nuggets, where their progress are, what their favorites and their notes, all that stuff.

00:21:59

And then we're also going to have our Learner Nugget Activity table where we can track Plays, Pause, Skips, Go Back, all that fun stuff. So there it is. There's the bird's eye conceptual view of our design-- Categories, Courses, Nuggets, Trainers, Learners, Learner Nugget Info, and Learner Nuggets Activity.

00:22:15

Let's head over to the Virtual NuggetLab and see how this looks from a physical design standpoint. And then we'll also talk about the Virtual NuggetLab and what it's all about. So we're in the Virtual NuggetLab. I have SQL Server Management Studio pre-launched and ready to go.

00:22:26

Let's bring it up. We have a connection here to a SQL NuggetLab instance. This will be the local default SQL Server instance we'll be working with throughout this course. We have three databases here. AdventureWorks 2012, that's the OLTP AdventureWorks sample database.

00:22:38

AdventureWorksDW, that's going to be our OLAP sample database, and then the Nugget Demo DB, which is where we're going to try to do virtually everything. If we do need a concept that we can't find or can't figure out how to do here, then we'll definitely use AdventureWorks if we need to.

00:22:51

But I'm going to try my best just to stick in Nugget Demo DB the whole time because this is a pretty cool and fun database. So I've got our bird's eye view. You can see all of our tables are created. I created a database diagram that will give us a bird's eye view of the relationships and the tables.

00:23:04

And we can see we have all seven of our tables-- Course Category, Course, Trainer, Nugget, Learner, Learner Nugget Activity, and Learner Nugget Info. Let's zoom in here to each table. Let's start here with Course Category, and we'll just rip through these pretty quick.

00:23:16

But Course Category is going to have the Category ID, the Parent Category ID. That way we can create a recursive function that's going to go and give us that hierarchy, so we can represent the same hierarchy we saw on Nuggets's site there. So we'll have to do all that in our data access layer.

00:23:30

Here's our Course table. We're going to track the Course ID, the category associated with it, the trainer associated with it, Title, Description, Completion Date. And then we'll have a couple of computed columns. These will just happen automatically because we have all that data.

00:23:43

We'll have all the total Nuggets associated with the course, the total duration associated with the course. So we'll be able to dynamically generate those values in a computed column. And this will be a good case for a user-defined function as well because we can create a function.

00:23:55

We can make that function end up being the computed part of the column, the formula for the column that will go and calculate all that up for us. So that will be a good one. Trainer table-- just basic trainer stuff. First Name, Last Name, Photo, Certs, Expertise, Hire Date-- all the stuff you see in a Trainer page on the Nuggets website.

00:24:12

And then our center table here, our Nugget table-- so we've got Nugget ID, Course ID, Name, Description, Duration, the Video Path. And this will be a good one for filestream because, again, just storing a path in here and then storing the video in the path, well there's no link between the two.

00:24:27

The video could get deleted, in which case the front end, when it goes to look for that video because someone clicked on it, would break. So that's the whole point of the file stream is we can link the two together. We can store the path and any metadata associated with video in the database.

00:24:38

We could store the actual file in the file system. And if anything were to happen to either end, the other end would be affected. So if someone deleted the video, the record would get deleted. If someone deleted the record, the video would get deleted.

00:24:47

So we would never have those problems. It would be a nice link between our structured and our unstructured data. This is Final Uploaded Date, so just good video information there. If we scroll over, here is our learner table, all the basic stuff about a learner-- First Name, Last Name, Address, Total Time Watched, Total Nuggets Watched, Total Courses Watched.

00:25:03

Again, these can all be computed columns. Since we'll have all that data tracked, we can figure out the values have them computed automatically. Here's our fun Learner Nugget Activity table where we're just going to track what the learner did on the Nugget.

00:25:15

Here's the action that they did-- Play, Pause, Fast Forward-- Action Start and Action End. So that will allow us to track all the actions across all the learners across all the Nuggets that they're performing. And at the top-- Learner Nugget Info, where we can track their notes, bookmarks, and progress by learner, by Nugget.

00:25:31

So we will recreate this structure when we get into creating and altering tables. And we'll fill it up with some data, too. We're going to need a lot of data to work with, so that way we can create our data access layer. And we'll go back to Nuggets' website, and we'll see all the data they pull back.

00:25:45

They pull back a list of courses. They pull back all the information when you click on a course, Nuggets associated with a trainer. So we'll recreate all of that in our data access layer through views and stored procedures. And I've got to send a shout out to the Nugget team because they're fantastic and awesome in getting me real, live data that we can work with inside of the sample database.

00:26:03

So we'll get all the courses, all the Nuggets, all the trainers. We'll load it all into here. And we'll have plenty of data to work with. Another thing I want to point here is notice we have Solution Explorer open here. Let me pin this down. Now I like to create a solution for each course.

00:26:16

And then for each Nugget in the course, we will create a project inside of this solution. That project will contain all the sample files and scripts and code that we do in our demonstrations. So it's empty now, but we'll build it up as we head throughout this course.

00:26:29

And that'll give you a nice, centralized spot and an easy way to reference what code is associated with what Nugget. The last thing I want to show you here is the Virtual NuggetLab. And we are on the Virtual NuggetLab. It's really, in this case, for this course, just simply a virtual machine.

00:26:43

It's a virtual machine that contains all the tools that we're going to use-- SQL Server, everything-- pre-installed and ready to go. And it also contains all the files that we're going to be working with. So I have one directory in here called Nuggetlab Files in the root of the C drive.

00:26:56

This contains the solution we just saw on SQL Server Management Studio. All the databases, MDFs and LDFs are going to be inside of this data directory. We'll definitely be in there. And then every Nugget will have its own directory, which is where the projects that sit inside of the solution will go.

00:27:09

All the scripts, all the files, everything associated with that Nugget will be inside it. So think of the Virtual NuggetLab as, number one, just a virtual sandbox that contains all the tools pre-configured that we use throughout the course. So you can just jump right in and start learning without having to worry about setting everything up yourself.

00:27:25

Number two, it's an area that you can use to follow along with whatever course you're watching. So you can hit Pause in the Nugget and go try to attempt to do what you just saw and learned about. And number three, we're going to start providing Lab Manuals.

00:27:36

A Lab Manual is just simply a guided walk-through. Think of it like a real lab where you can walk through some of the most common things that you've done or learned about throughout the course. And it contains fun things there, like fun questions and challenges and tips and such.

00:27:50

So stay tuned to the Course page for this course at CBT Nuggets to find out more information on how you can access the Virtual NuggetLab when it becomes available. In this CBT Nugget, we took an introduction to 70-464, Developing Microsoft SQL Server 2012 databases.

00:28:04

We started off with a course overview. We defined what a database developer was. We saw that 70-464 is the first exam in the NCSE Data Platform Certification. And we also saw what this course consists of. We then went over to Microsoft's exam page for 70-464 and looked at the objectives in greater detail.

00:28:24

From there, we looked at the design of our sample database, our Nugget Demo DB. We looked at it from a logical perspective, and then we went into the Virtual NuggetLab and looked at it from a physical perspective. At the end, we just talked about what the Virtual Nugget Lab is all about, your virtual sandbox for this course.

00:28:40

I hope this has been informative for you, and I'd like to thank you for viewing.

Creating and Altering Tables

Designing and Implementing Security

Designing Lock Granularity

Maintaining Indexes

Implementing Data Types

Creating and Modifying Constraints

Working with XML Data

Writing Automation Scripts

Designing and Implementing Stored Procedures

Designing Table-valued and Scalar Functions

Creating, Using & Altering User-defined Functions

Creating & Altering Views

Designing Tables

Designing for Concurrency

Creating and Altering Indexes

Designing Data Integrity

Designing Transactions

Optimizing and Tuning Queries

Troubleshooting Performance Problems

Optimizing Index Strategies

Capturing and Analyzing Execution Plans

Collecting Performance and System Information

Please help us improve by sharing your feedback on training courses and videos. For customer service questions, please contact our support team. The views expressed in comments reflect those of the author and not of CBT Nuggets. We reserve the right to remove comments that do not adhere to our community standards.

comments powered by Disqus
Advanced 11 hrs 23 videos

COURSE RATING

Training Features


Practice Exams
These practice tests help you review your knowledge and prepare you for exams.

Virtual Lab
Use a virtual environment to reinforce what you are learning and get hands-on experience.

Offline Training
Our iOS and Android mobile apps offer the ability to download videos and train anytime, anywhere offline.

Accountability Coaching
Develop and maintain a study plan with one-to-one assistance from coaches.

Supplemental Files
Files/materials that supplement the video training.

Speed Control
Play videos at a faster or slower pace.

Bookmarks
Included in this course
Pick up where you left off watching a video.

Notes
Included in this course
Jot down information to refer back to at a later time.

Closed Captions
Follow what the trainers are saying with ease.
Garth Schulte
Nugget trainer since 2002