An introduction to 70-461, query
Microsoft SQL Server 2012.
Hello, and welcome everyone.
My name's Garth Schulte.
I'll be your guide through this series.
And a little background on myself and SQL Server.
I've been working with it personally as a DBA and a
developer since SQL Server 6.5, which
kind of blows my mind.
Because that was 15 years ago.
And SQL Server's come a long way since then.
So this exam specifically from Microsoft is really focused on
getting you familiar with how to work with database objects
and the data stored inside these objects using
Transact SQL, TSQL.
So we're going to get very familiar with Transact SQL
both from a DDL perspective, data definition language
statements, things like create, alter, and drop.
And we'll get very familiar with DML statements, data
manipulation statements, those statements such as select,
insert, update, and delete, and all the fun stuff we can
do with those.
And so we'll cover all the exam objectives that
Microsoft's going to be expecting from us, and go way
above and beyond that.
I'll show you a lot of the tricks that I've learned over
the years working on SQL Server, some time saving
features, and just learning how to work with SQL Server
and work with Transact SQL in a way that'll make you a very
efficient programmer.
Or as I like to call us, DBDs, database developers.
Hey, DBAs get their own acronym, so why not the
developers, too?
So we'll take a good look at it from both perspectives,
from the DBA and the DBD perspective on how to query
and work with SQL Server 2012.
So with all that said, let's head over to our white
boarding and take a look at the outline for this Nugget.
We're going to start with an exam overview.
Are here, I just want to take you over to Microsoft's
website and get you familiar with what is going to be
required, or what Microsoft is going to expect from us as far
as the objectives go on the exam.
So we'll look at the 70-461 exam, check out the
objectives, and see what this Nugget is going to cover.
Then, we'll take a look at the Nugget series overview.
Just want to go over all the different Nuggets in this
series, talk about the videos.
And specifically here, find out how you can relate these
two together.
So if there's something that you're weak on or something
that you just want to learn specifically inside of the
exam objectives, you'll easily be able to find it and know
exactly what video to go to inside of the series.
And finally, we're going to go take a look at how to install
SQL Server 2012 Express Edition.
And there are actually five editions out there of SQL
Server 2012 Express.
And I'll briefly go over each one of them.
And then, we're going to head over to Nugget Lab, which is a
new initiative here from Nuggets that allows you to
work on the same exact server that we're going to be doing
these Nuggets on.
So just to show you, you've probably seen this little icon
in the upper right hand corner.
This is a virtual instance that, again, you can
also gain access to.
Just head over to cbtnuggets.com or
nuggetlab.com to find out more about how you can access
Nugget Lab.
But it's a fresh install of Windows Server 2008 right now.
So there's nothing on it.
It's brand new.
So we're going to head over.
We're going to install SQL Server 2012 Express on
it, get it set up.
I'll show you how to set it up, the documentation, because
that's changed a little bit in this version.
And then, we will do all of our demos on this Nugget.
And we'll build it up.
And I'll show you how we can access all the demos from
using Nugget Lab.
And we'll generally have a good time over
there in Nugget Lab.
And learn a lot, of course.
All right, let's head over to Microsoft's website and take a
look at the 70-461 example.
So if we head down to the Start button and fire up
Internet Explorer here, we can just Google 70-461.
And we'll find out that it equals negative 391.
But actually, what we really want here is the very first
link that comes up, exam 70-461 from Microsoft.
If we click on that and head on over here, I'm just going
to zoom out a little bit here.
There we go.
And here's the overview.
So it just gives you a brief overview.
It's intended for SQL Server DBAs, implementers, system
engineers, and developers with a few years experience looking
to prove their skills and knowledge in writing queries.
So here's a list of some of the primary responsibilities
and some of the things that we're going to need to know in
order to pass the exam.
What I like to do is come over to the skills measured tab and
take a look at really the details here of what they're
going to be expecting.
So you can see 24% of the exam is going to be focused on
creating database objects.
So this is where we're going to start.
And again, I modeled this series--
pretty much each one of these main bullet
points here is a Nugget.
So the very first Nugget that's going to follow after
this introduction is going to be how to create and work with
tables using Transact SQL.
So some simple statements here, just get familiar with
the syntax.
We're going to learn how to create tables without using
the built in tools using things like the alter drop,
the alter column, and obviously here the create
table statement.
So we'll take a good look how to create tables
programmatically using Transact SQL.
Then, we'll do the same thing with views, how to create and
alter views using create, alter, and drop.
And then, we'll move into learning how to design views.
So this is going to-- we're going to ensure code
non-regression by keeping a consistent signature for
procedures, views, and functions.
Otherwise known as interfaces, and what the security
implications are when designing views.
And if that is confusing, we'll make sure to clear it up
here over there in the design views.
It's actually not that bad.
And it's pretty cool, too.
It's a great way to learn how to not only design good views
for use, but it's also cool.
I like this because it's a way that you can ensure backwards
compatibility for many applications in your database.
So we'll take a good look at how to properly
design views there.
We'll take a look how to create and modify constraints.
We'll look at how to create and alter data manipulation
statement triggers, DML triggers.
And that's really going to cover how to
create database objects.
So the big objects here are going to be tables, views,
constraints, and triggers.
Then, we'll head on down to working with data.
We'll start this segment off with a big look
at the select statement.
So select is our main statement here that we use to
work with data, slice and dice data, pull data out of our
tables to pass into client applications or that our views
are going to use to do the same thing, pull the data out
of the tables, pass it back to the applications.
Or ourselves, if we just want to write some statements
against tables to analyze data and such.
So we'll take a big look at the select statement here,
everything from how to work with ranking functions, which
I personally like and I use quite a bit inside of the .NET
world when I build applications.
I'm always using ranking functions, really cool stuff.
We'll learn how to write and perform queries efficiently.
We'll use some of the new code items here, synonyms and joins
using the except and intersect keywords here, implement logic
that uses dynamic SQL and system metadata.
Learn how to write efficient, technically complex SQL
queries including all types of joins versus the
use of drive tables.
So you can see, this is going to be a big Nugget.
This will be a long one, I guarantee you.
And here at the end here, we'll take a look at the case
versus isNull versus the coalesce statement.
I love the coalesce statement.
I'll do some good real world examples of how this is useful
so you can get the hang of this.
Believe me, you'll be using it everywhere.
It's really neat to format data output.
We'll also take a look at how to implement sub-queries.
So we'll look at, how do I identify problematic elements
in query plans?
That's cool stuff.
So we'll get familiar with how to work with query plans here,
pivot and un-pivot, the Apply operator, CTE statements,
which are common table expressions,
and the with statement.
So we'll take a good look here at sub-queries and some of the
different things inside of there.
We'll look at how to implement data types.
We'll look at a lot of different data types here, how
to understand and use the right data
type for the column.
We'll look at the GUID, the global unique identifier using
new ID and the new sequential ID and how that affects
database performance.
And again here, when to use which data type
for specific columns.
We'll then also look at aggregate queries and how to
work with some of the new functions inside
of SQL Server 2012.
We've got some new analytic functions, grouping sets,
spatial aggregates a lot of people are excited about.
So we'll look at what those are all about.
And then, how to apply a ranking function.
So we'll take a good look here at how to work with
aggregates.
And then, the last part of working with data here, we'll
look at how to query and manage XML data.
We'll get familiar with some of the XML data types, their
limitations, restrictions, implement XML schemas, handle
XML data, and just generally work with XML through
importing, exporting, and also how to index XML columns.
All right, scrolling down here, the next section is
dealing with data modifications.
So we'll take a look at how to modify data.
24% of the exam is on modifying data.
And we'll start with stored procedures.
Everybody loves stored procedures.
They're awesome.
They're a great way to speed up application performance.
They're just queries that sit on the server side, optimized
for the server side.
And so we'll take a good look at how to build stored
procedures.
I've built thousands in my day.
Still build them every day, and I love them.
They're great.
They're great, especially for slow performing applications.
Great way to speed things up and keep the logic on the
database side.
A lot easier to change logic that's stored in the database
than it is compiled inside of an application.
So we'll take a good look there.
We'll also go ahead on how to modify data using the three
big data modification statements here.
Insert, used to add new data.
Update, change existing data.
Delete, remove data out of tables.
So we'll take a good look at these.
We'll look how to combine data sets.
We'll take a look at the union versus union all.
Microsoft has this in two places, right, case versus
null coalesce.
That's already up in the select statement.
So we'll probably look at that up there.
And here, we'll look how to modify data using the new
merge statement that also came out in SQL Server 2008.
Then, we'll look at how to work with functions.
Here, we'll take a look at deterministic versus
non-deterministic functions, scalar and table valued
functions, how to apply built in scalar functions, and then
just generally create UDFs, user defined functions.
And finally, the last 25% of the exam is going to deal with
troubleshooting and optimizing queries.
So we'll start with a Nugget on optimizing queries here.
We'll learn how to understand statistics, database
statistics, and the object that contains all the
statistics for the columns inside of the tables.
We'll learn how to read query plans, look at plan guides,
look at DMVs--
and no, that's not the Department of Motor Vehicles,
although that's funny that that's in the optimize area
because nobody wants to stand in line all day, right?
That could be highly optimized.
But this is more for the dynamic management views that
we can use to check out the status of a server.
We'll look at some hints here, table hints, statistics,
input, output, dynamic versus parameterized queries.
We'll look at the different join types inside of the query
plans and how SQL Server internally fetches this data.
So we'll look at the hash merge and join types and
describe the scenarios in which they would be used.
So we'll take a good look here at optimized queries.
We'll also look at how to manage transactions,
everything from marking a transactions to getting
familiar with the begin, commit, and roll back trans,
implicit versus explicit transaction, look at isolation
levels, and scope and the type of locks, trancount.
So just pretty much everything there is to do with
transactions.
And we'll look at how to evaluate the use of row based
operations versus set based operation.
So when to use cursors, basically when
to go row by row.
It's kind of like programming where row based operations is
looping through the table one row at a time, performing
logic on every row.
Set based is working with an entire result set, so the
entire table at once, or a chunk of data at once.
So evaluate the use of both of those here.
See when to use cursors here, which is pretty much never
these days.
The impact of scalar user defined functions.
We'll combine multiple DML operations,
all that good stuff.
And finally here, we'll take a look at how to
implement error handling.
Look at the try catch throw statements
inside of SQL Server.
We'll use set based rather than row based logic and deal
with transaction management.
So a small Nugget there on how to work with error handling
inside of Transact SQL.
So that's what this series is going to cover.
That's pretty much querying Microsoft SQL Server 2012.
And again, we're going to go far above and beyond that.
I'll show you a lot of cool things along the way here and
get as much extra stuff as I can squeeze into this series.
But we'll definitely cover all of this stuff and more.
All right, let's close out of this.
And now, what I want to show you here going back to the
white board is our Nugget series overview.
So if we head over there, this is going
to look really familiar.
See this?
I broke it out by each of those areas.
So pretty much, we got close to 25% in each segment here,
give or take a little bit more in certain areas.
But you can see we're creating database objects starting with
Nugget number one here, creating and altering tables
with TSQL, then views with TSQL, designing views,
modifying constraints, modifying DML triggers.
So you can see the relation here between the series and
the objectives.
I tried to marry them as closely as possible.
That way, it'll be very easy for you to find out what
objectives are taught inside of which Nugget.
All right, and I won't go through all these because we
just did over on Microsoft's website.
I just wanted to show you how to relate the
two together there.
Now, let's minimize the white board.
I want to head over to Nugget Lab and get installation of
SQL Server 2012 up and running here, Express Edition.
Now again, Nugget Lab, it's a virtual instance out there on
the internet that we're going to be doing all
of our demos on.
By the time we're done with this series, there's going to
be a nice collection of all the demos
broken out by Nugget.
And again, you can have access to this exact image here, the
exact instance of SQL Server Express Server we're going to
be working on.
It'll all be preloaded for you, and all the demos will be
there for you to work with.
So you can follow along.
So again, visit cbtnuggets.com or nuggetlab.com to find out
more information about how you can access
this Nugget Lab instance.
So let's head over to Nugget Lab.
I'll double click on the icon here.
This will get us a remote desktop connection over to our
Nugget Lab instance.
And again, this is a fresh install of
Windows Server 2008 R2.
I haven't done anything at all to this server.
So the first thing we do need to do is get SQL Server 2012
Express Edition installed.
Now, I already have it downloaded on this machine.
But I still want to show you where you can find it.
So let's head down to Start.
Hit up Internet Explorer here.
And let's just go over to Google.
And we're just going to type in SQL Server 2012 Express.
We'll search for this.
Why wait when we can click on that now?
And oh, something happened there.
Must have clicked too fast.
Here we go.
So the first link that comes up, it takes us right to the
Microsoft Express page for SQL, the latest version here.
So if we click on that, it'll get us right there.
You can click on the link right here.
That will take you to the download page.
There's also a link over here to the right that says, Get
SQL Server 2012 Express.
So let's click on that guy.
And this will take you right to the download page.
So you can download these products in 32-bit or 64-bit.
We're in a 64-bit machine here, so let's drop this down.
Here are the five editions that we can download.
Local DB is brand new to the Express Suite.
This is really targeted for developers because it's a very
lightweight version of the database engine that they can
quickly get installed and up and running.
It's actually a zero configuration installation.
You don't have to go through all these wizards and
configure everything.
It's just launch it, and it's done.
It installs.
And what's neat about this is a developer can install it on
their laptop.
They can actually access it through their development
environment, things like Visual Studio, .NET.
They can hook right into using some tools and designers
inside of Visual Studio that'll allow them to work
with the instances.
So that's brand new, and it's very lightweight, easy for
developers to use.
And that's what developer should use to design against a
SQL Server Express database.
Then, we have Express with the database only.
And very similar to Local DB, only of course you have to go
through the installation, choose your instance, set
everything up through the wizard.
So it's your typical Express Install, only it doesn't come
with tools.
So the only way you'll be able to access this is if you
already have the tools installed on the server that
you're installing Express, or remotely using SQL Server
Management Studio that's installed
on a different machine.
The third version we have here is Express with tools.
So this is both Express, the engine itself, with SQL Server
Management Studio and the tools that come with it.
And this fourth one is SQL Server
Management Studio Express.
This is just simply the tools by
themselves, no database engine.
So if you need just to install the tools on a remote machine
that needs access to a SQL Server Express Edition, you
can just download and install the tools.
And finally, what we're going to use and what I've
downloaded is Express with Advanced Services.
In the training world and when you're dealing with wanting to
learn this stuff, I always like to download the biggest
and the best because it has everything.
And that's what this has.
It has everything--
reporting services, integration services, all what
they call the Advanced Service Suite inside
of SQL Server 2012.
So this is what I downloaded.
I'd like to show you how to set it up here in case you
want to get adventurous and go outside of this Nugget and get
familiar with things like reporting services,
integration services, and that kind of stuff here.
So you would click on this, hit the Download button, and
that'll start your download there.
So again, I already downloaded this.
So let's close out of the browser here.
Yeah, we want to close all tabs.
And I'm going to go to Start.
I'm just going to hit Run here.
And let's just go right to the C drive.
And I made a folder in the C drive, the root called 70-461
Support Files.
So if we go in here, I downloaded SQL Server Express
Advanced Tools 64-bit in English.
So this is what we're going to install.
I've also here-- what I want to show you after we get this
up and running is I downloaded the
AdventureWorks 2012 database.
This is the sample database that comes separate with SQL
Server that you can download and attach to it.
And it's a great database to get familiar with things
because it's got everything from simple structures in it
to complex structures to views.
It's really a great sample database.
So if you want to play around with it,
it'll be on the server.
We'll definitely use it in some demos here to get
familiar with things.
We're also going to do our own thing, as well.
But it's nice that we have a sample database filled with
data for us to work with, all sorts of different data.
So that's why I like to install it.
And any time I want to test out something new, rather than
create a new database, create a new table structure and all
that, I just like to pop over to AdventureWorks, run some
tests out, just maybe get familiar with the new features
or new statements in TSQL, that kind of stuff.
So I definitely love using AdventureWorks.
I use it all the time to do that kind of stuff.
Now, I'll also show you where we can download
this when we get there.
But first, let's install SQL Server 2012 Express Edition.
I'm just going to double click on this.
It's going to unpack the files for us here, and then
hopefully launch the installation manager here.
So we'll just give this a second.
I'm actually just going to pause this
until it's done unpacking.
So I'll be right back.
All right, we're all done here unpacking the SQL Server
installation.
So I'm just going to maximize our screen.
And on our installation screen here, we can install a
standalone edition.
We can upgrade from an existing version
of SQL Server here.
We're just going to install the brand new installation.
It's going to unpack everything once again, just
prepare SQL Server 2012 for install.
And shortly after this here, it'll fire up our
installation wizard.
So here it is.
And the first thing it needs to do is just go through the
set up, install the set up files here.
I'm just going to minimize this screen for now so it's
not in the way.
And it's also going to check for updates.
So actually, the first thing it did there is just get the
support files going.
Now, it's going to check for product updates, which it did.
Nothing out there, brand new.
We'd hope not.
So let's hit Next.
Now, it's just going to install the set up files.
So I'll just give this a second while it installs the
setup files and be right back.
All right, so it's all set here with the support files.
It went through all that and skipped to this screen, which
just goes through and checks out all the rules against it.
So we pass all the rules here except one, Windows Firewall
is enabled.
So it's just going to let you know to make sure the
appropriate ports are open to enable remote access.
So once this is done here, we can hit Next.
We'll go through the license term.
We can accept the terms.
You can optionally here send usage data to Microsoft.
Hit Next.
And now comes the feature selection.
And here it is.
Here, we're going to install the instance features here,
the database engine, replication, full text,
reporting services, native mode, the data tools,
documentation components, management tools, the SDK here
for client connectivity, and here's the Local
DB option as well.
So we're going to leave all these checked.
This is what the default is, which is pretty much
everything.
And here's our default installation paths.
And we'll hit Next.
Now, the next area here is going to be our instance
configuration.
And let's just call this here--
we're going to do a named instance.
We'll call this SQL 2012 XP.
So that'll be the instance we work with here.
When we tab off that field, that's what it'll give our
instance ID.
And then, it'll also scan here if there's any previous
installations, which there aren't.
If you do have previous installations, it'll tell you
what the name is, what the features are, the edition, the
version, all that good stuff.
We're good here.
So we'll hit Next once again.
And now, the disk space requirements.
We should have plenty of that on this machine, as we do.
So it just gives us a little overview here of the disk
usage summary and where things are going.
So if we hit Next, we'll go to Server Configuration.
We can set up our service accounts for the database
engine, reporting services for the full text launcher here,
and then for SQL Server browser.
So we can find this on the network.
So we'll leave all the defaults on those.
You can also set up your collation here.
We're just going to hit Next.
You can pretty much leave all those at the default here for
our test environment.
And then comes time to configure the
database engine itself.
So we can choose a server configuration.
What type of authentication do we want to do?
We can do Windows authentication or mixed mode,
where we do both SQL Server and Windows authentication.
We'll just keep this simple for now and stick with Windows
authentication.
And here is the SQL Server administrator, which is me, my
account here.
And the name of this box, by the way, on our Nugget Lab
server, is actually SQL Nugget.
I changed the name to SQL Nugget.
So that's going to be the name of the server here.
And then, you can do some other things here.
You can specify your data directory.
So here are all the data directories here for the root,
data root directory.
We've got the database directory, the log directory,
the temp database directory, temp log, and
then our backup directory.
We'll leave all the defaults there.
You can configure user instances
and also files streams.
So we'll leave all that the way it is here.
Everything looks good.
Let's hit Next.
And now, for reporting services, since really we
don't have anything to do with reporting services in this
Nugget, I'd just like to get it installed again in case you
want to venture outside of this Nugget and play with it.
We will just choose Install Only.
We will not configure it here in the set up.
We'll choose Next.
Then, you have some error reporting here.
If you want to send errors to Microsoft, feel free.
And finally here, it's just going to go through the
installation configuration rules
followed by the progress.
So this is going to take a little bit to install SQL
Server 2012.
I'm going to let it go.
I'll be back when it's done.
And we're back once again here.
The last screen of the set up just gives us an overview of
everything that was installed.
So we've got the management tools installed.
This is SQL Server Management tools and some extra database
tools like the profiler and stuff.
SQL Server data tools is actually Business Intelligence
Development Studio, otherwise known as BIDS.
It's really just Visual Studio for SQL Server development.
So you can build things like reports and reporting
services, integration services packages,
things of that nature.
We've got the database engine services.
This is just the core SQL Server engine service.
Full text was installed.
Replication, reporting services in native mode.
Scroll down a little bit, here we have SQL browser
documentation components.
So one thing I do want to show you about documentation is
it's not installed by default.
It actually is the help software that points to the
online version.
So I'm going to show you how you can install help because
it'll speed things up here, especially because we're going
to be using it.
Show you some cool things you can do so you can learn any
statement on your own, or learn the
syntax of the statement.
So it's a good idea to get books online.
There's documentation for SQL Server installed locally.
We've got SQL Writer connectivity components and
the SDK also installed.
So we're good to go on this.
Let's go ahead and close out of this.
Let's take a look at what the installation did.
So let's head down to the Start
menu, up to All Programs.
Here we have a SQL Server 2012 program area here.
So here's all the components that were installed.
And we have a Visual Studio 2010 area, where you can see
Visual Studio 2010 was installed.
So going through some of the SQL Server stuff, you've got
the Import and Export Wizard for 32 and 64-bit.
We've got SQL Server data tools.
This is Business Intelligence Development Studio renamed,
and thankfully they did it because that's
a mouthful to say.
And so I'm happy.
It makes a lot more sense.
SQL Server Data Tools, it sounds a lot better, looks a
lot better.
And you'll notice that the icons are the same here.
That's because really what it is is it's Visual Studio
geared towards SQL Server and the DBAs and DBDs of the
world, and has all the projects that we can work with
to do things like create reporting services, report
projects, or create integration services projects.
So that's that.
We also have SQL Server Management Studio, which is
what we'll be spending most of our time in.
We'll come in there shortly to configure the sample
AdventureWorks database.
We have configuration tools here.
If you want to configure reporting services, this is
where you would do it.
If you want to configure SQL Servers as far as service
configuration, network configuration, client
connectivity, all that kind of stuff is inside of the
Configuration Manager.
Error and usage reporting, which we touched on inside of
the installation, and then the installation center, which we
were just in, to install SQL Server.
So those are the configuration tools.
We have the documentation and community here, which we're
going to come back to in a second to get books online,
SQL Server's help
documentation, installed locally.
And finally, you have a bunch of tools for integration
services in here like the data profile viewer deployment
wizard, execute package utility, and the project
conversion wizard.
So let's install the
documentation here, books online.
And what we can do is hit this Manage Help
Settings menu item here.
And this is going to take us to the help library manager.
This is Microsoft's Help Viewer 1.0.
This is really cool stuff.
I actually really like this because it's kind of a
simplified, centralized way to get help installed not just
for SQL Server, but anything that's
installed on your system.
So I'm a big fan of this.
And here's how it works.
Let's install content from online.
And it's just going to scan, see what we have installed on
our system, compare it with what's available online for
the Help Viewer 1.0 and give us everything.
So not only will SQL Server documentation be in here, but
a lot of things like .NET development.
If you want to do any .NET development, read the
documentation on that, you can hit Add here, update pending,
and it shows just the size.
So we're not going to do that.
But you can if you want to.
You've got BizTalk Server 2010 for Office, patterns and
practices, all very good stuff.
Highly recommended if you're ever going to get into
development.
SharePoint information, here's SQL Server 2012.
So we want books online.
And again here, I just like to install everything so it's all
on here in case you want to play and get out of your
comfort zone or out of this Nugget series.
So I'm going to add the developer
reference because of that.
And also, the installation.
So we've got all the documentation for SQL Server
2012 locally.
Here are the sizes over on the right.
And then, you've got some other things in here.
So all the Visual Studio documentation you can install,
Web Developer, Windows is here, the development driver
kit phone development, and the XNA Game Studio.
So we're good, though.
We've got everything in SQL Server 2012 checked and ready.
Update pending.
So let's hit Update.
I'm going to put you on hold one more time while this
downloads from the internet and installs it locally.
Be right back.
All right, installation is complete.
Once you hit Finish, it'll take you back to Help Library
Manager screen here.
And you can just double check through choosing online or
local help that we're using local help.
And we are.
So we'll cancel out of this.
Let's exit out of the library manager and just take a quick
look at the documentation.
So go to Start, All Programs, SQL Server 2012, Documentation
and Community.
And the bottom one inside of here, SQL Server
Documentation, will launch the help viewer.
And check it out.
All three that we chose to install are here.
We have the books online, which is the main instruction
manual for all things SQL Server 2012.
We have the developer reference for all the
different tools and components, including the
database engine.
And then, we have the installation
guide as well here.
So all three areas are here.
We're not going to be specifically coming into books
online like I am here.
We're going to use it in a context sensitive manner.
And what that basically means is when we're in Transact SQL
and there's a statement, pretty much anything that's
highlighted in blue or pink, or anything that's a SQL
Server keyword, if you highlight it are double click
on it and hit Shift F1 on your keyboard, it'll take you right
to that area inside of the documentation and give us help
for that specific statement or keyword.
Things like syntax, what all the different parameters are,
describe them, and give us an example of how to use it.
So it's awesome.
I use it all the time.
I love it.
And it'll save you a ton of time.
And it's a great way to learn a lot of the different
language constructs inside of Transact SQL.
So we'll get there in future Nuggets.
We'll use it extensively and hopefully learn a lot.
So let's close out of help.
The last thing I want to show you here is how to install the
AdventureWorks sample database.
But first, I need to show you where to find it.
So let's hit the Start button one more time here.
And boy, this Start button's getting a work
out tonight, huh?
And let's hit the Internet Explorer here.
Go to Google.
And once we're at the Google, let's type in
AdventureWorks 2012.
All right, and the first link that comes up is good.
This will take us right to the database product
samples page here.
And there's going to be a lot to choose from down here.
There's things for data warehousing,
multidimensional models.
There's online transaction processing scripts down below.
But really what we're concerned with here is the
recommended download, the big one here,
AdventureWorks 2012 data file.
This will give us the .mdf, the primary data file, which
is all we need.
All right, so I already have this downloaded.
So I'm going to close out of the browser here, go back to
the file system.
And again, it's in the C drive here.
It's in our 70-461 support files.
And there it is, AdventureWorks 2012_data.mdf.
So that's what we're going to attach to.
Let's minimize this.
And let's launch SQL Server 2012 Management Studio.
So let's go to Start, All Programs, SQL Server 2012.
Let's launch the Management Studio.
And you'll notice that it's going to look a little
different than it used to.
They used better colors in my opinion here.
And the whole point to this was to make it less
distracting.
So just muted colors.
And I like it.
I've been working with Visual Studio 2010 for a while now,
for a few years.
And it's got the same kind of interface
with the same colors.
And it's awesome.
It's really cool, professional, slick looking
and all that.
So kind of neat.
So let's connect to the instance we installed here,
SQL Nugget, backslash SQL 2012 XP.
There it is.
And if we expand the databases node, we have nothing in there
but system databases.
So let's right click on that databases node, hit the attach
button, hit the Add button here to
specify an MDF file location.
So if we hit Add, we're going to point right to our MDF file
in our support files directory.
And let's just close out of this.
You'll of course get prompted to make the experience better.
And we'll just hit OK here.
And this will just tell us where it is, what the database
name is, how we're going to attach it as
the database name.
And again, you have full control over this stuff.
So if you don't like AdventureWorks 2012,
you can change it.
Who's the owner, and just some extra stats on it there.
And then, it's telling us there's no log file found.
And it defaults to what our installation
log directory was.
So what you want to do here, you'll get an error if you
leave it as it is because it won't be able to
find the log file.
So here's what I do--
remove it.
Get it out of there.
Now, it'll create a log file on the fly in the same
location as our primary data file.
So that's the only trick to doing this.
So once we hit OK, it's going to bring it in.
And there it is.
Here is the AdventureWorks 2012 database.
All the tables that we'll be working with, here is stored
procedures, in here as well, views, you name it, we'll work
with it inside of here.
And of course, we'll make our own databases and work a lot
on our own stuff, as well.
But for some of the advanced things, there's already some
good data in here, some good examples that we're
going to use here.
So we've got it up and running.
SQL Server is also installed and up and running.
So everything looks good.
There's just one more thing I want to show you, always one
more thing.
And this might be the developer coming out in me,
but this'll definitely help you stay organized within
Management Studio.
This is really cool stuff.
I love doing this.
If you go to the File menu, New, Project.
And you may already do this.
But if not, this'll definitely save you some
time in the long run.
And you can also kind of build up your library
of queries and projects.
And everything that you work, eventually you can just keep
evolving over time.
You have a nice spot for all your scripts, everything.
Right, so let's name this solution 70-461.
We'll just call this Querying SQL 2012.
And then, the name here is going to be the project.
So basically what you have here, you have a solution
which is just a container for all the projects.
And for now, we'll just go ahead and call this
01-Creating_Tables, which is going to be the first Nugget
in the series.
And we may come back and change this.
But that's what I'll end up doing just to show you the
structure and the naming convention I'm using.
If we hit OK here, that's going to create a
solution for us.
And over here on the right hand side, if it doesn't show
up, what you can do is go to View, Solution Explorer.
And this is what we want to see.
So in here, we have the name of our solution.
Again, just a container here for all of the project files.
And then, what we'll do is every Nugget when we create a
demo, we will create a new project.
And we'll put all our queries in here.
We can have connections inside of here,
which is kind of neat.
Because if you have lots of databases you're connect to
and running samples scripts against, whether it's a dev
database or a production database, you can point it to
a connection.
So not only can you manage your connections, you can
manage your queries.
And then, you can put scripts inside of miscellaneous.
So just a cool little project to help you stay organized.
And this is really nice in this Nugget series because
again, we'll have a project for every one.
So eventually when we're done, then, the solution will be
filled with all of our projects, all the demos.
So you'll have a nice, centralized place for
everything, Querying SQL Server 2012.
So we are officially ready to rock.
All right, so in this CBT Nugget, we took a look at an
introduction to 70-461, Querying
Microsoft SQL Server 2012.
We started off with a look at the exam objectives, went over
to Microsoft's website, took a look at what's to be expected
on the exam, all the objectives we should be
familiar with.
And then, we came back here and looked at the series
outline for this Nugget series and saw that it's very close
to what the objectives are.
And we did that by design here so you could easily find what
you're looking for if you need to freshen up
on a specific topic.
And then, we jumped in and learned how to install SQL
Server 2012 Express.
We looked at all the editions, we got an installation going,
we installed the help documentation locally.
And we even got the sample AdventureWorks database
downloaded, attached, and installed inside of our SQL
Server instance.
And at the very end here, I just showed you how you could
create a solution and add project files so you can
manage your queries.
I hope this has been informative for you, and I
thank you for viewing.