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