Are you sure you want to cancel your subscription?

If you cancel, your subscription will remain active through the paid term. You will be able to reactivate the subscription until that date.

Sorry to see you go

Your subscription will remain active until . If you change your mind, you may rectivate your subscription anytime before that date.

Are you sure you want to reactivate?
Welcome Back!

Your subscription has been reactivated and you will continue to be charged on .

Reactivate Subscription

Thank you for choosing to reactivate your subscription. In order to lock in your previous subscription rate, you owe: .

Your Subscription term is from - .

Questions? Call Sales.

Payment Due:

Auto-Renew Subscription

To auto-renew your subscription you need to select or enter your payment method in "Your Account" under Manage Payments.

Click continue to set up your payments.

CBT Nuggets License Agreement

Unless otherwise stated all references to “training videos” or to “videos” includes both individual videos within a series, entire series, series packages, and streaming subscription access to CBT Nuggets content. All references to CBT or CBT Nuggets shall mean CBT Nuggets LLC, a Delaware limited liability company located at 44 Country Club Road, Ste. 150, Eugene, Oregon.

A CBT Nuggets license is defined as a single user license. Accounts may purchase multiple users, and each user is assigned a single license.

  • GRANT OF LICENSE. CBT Nuggets grants you a non-transferable, non-exclusive license to use the training videos contained in this package or streaming subscription access to CBT content (the “Products”), solely for internal use by your business or for your own personal use. You may not copy, reproduce, reverse engineer, translate, port, modify or make derivative works of the Products without the express consent of CBT. You may not rent, disclose, publish, sell, assign, lease, sublicense, market, or transfer the Products or use them in any manner not expressly authorized by this Agreement without the express consent of CBT. You shall not derive or attempt to derive the source code, source files or structure of all or any portion of the Products by reverse engineering, disassembly, decompilation or any other means. You do not receive any, and CBT Nuggets retains all, ownership rights in the Products. The Products are copyrighted and may not be copied, distributed or reproduced in any form, in whole or in part even if modified or merged with other Products. You shall not alter or remove any copyright notice or proprietary legend contained in or on the Products.
  • TERMINATION OF LICENSE. Once any applicable subscription period has concluded, the license granted by this Agreement shall immediately terminate and you shall have no further right to access, review or use in any manner any CBT Nuggets content. CBT reserves the right to terminate your subscription if, at its sole discretion, CBT believes you are in violation of this Agreement. CBT reserves the right to terminate your subscription if, at its sole discretion, CBT believes you have exceeded reasonable usage. In these events no refund will be made of any amounts previously paid to CBT.
  • DISCLAIMER OF WARRANTY AND LIABILITY. The products are provided to you on an “as is” and “with all faults” basis. You assume the entire risk of loss in using the products. The products are complex and may contain some nonconformities, defects or errors. CBT Nuggets does not warrant that the products will meet your needs, “expectations or intended use,” that operations of the products will be error-free or uninterrupted, or that all nonconformities can or will be corrected. CBT Nuggets makes and user receives no warranty, whether express or implied, and all warranties of merchantability, title, and fitness for any particular purpose are expressly excluded. In no event shall CBT Nuggets be liable to you or any third party for any damages, claim or loss incurred (including, without limitation, compensatory, incidental, indirect, special, consequential or exemplary damages, lost profits, lost sales or business, expenditures, investments, or commitments in connection with any business, loss of any goodwill, or damages resulting from lost data or inability to use data) irrespective of whether CBT Nuggets has been informed of, knew of, or should have known of the likelihood of such damages. This limitation applies to all causes of action in the aggregate including without limitation breach of contract, breach of warranty, negligence, strict liability, misrepresentation, and other torts. In no event shall CBT Nuggets’ liability to you or any third party exceed $100.00.
  • REMEDIES. In the event of any breach of the terms of the Agreement CBT reserves the right to seek and recover damages for such breach, including but not limited to damages for copyright infringement and for unauthorized use of CBT content. CBT also reserves the right to seek and obtain injunctive relief in addition to all other remedies at law or in equity.
  • MISCELLANEOUS. This is the exclusive Agreement between CBT Nuggets and you regarding its subject matter. You may not assign any part of this Agreement without CBT Nuggets’ prior written consent. This Agreement shall be governed by the laws of the State of Oregon and venue of any legal proceeding shall be in Lane County, Oregon. In any proceeding to enforce or interpret this Agreement, the prevailing party shall be entitled to recover from the losing party reasonable attorney fees, costs and expenses incurred by the prevailing party before and at any trial, arbitration, bankruptcy or other proceeding and in any appeal or review. You shall pay any sales tax, use tax, excise, duty or any other form of tax relating to the Products or transactions. If any provision of this Agreement is declared invalid or unenforceable, the remaining provisions of this Agreement shall remain in effect. Any notice to CBT under this Agreement shall be delivered by U.S. certified mail, return receipt requested, or by overnight courier to CBT Nuggets at the following address: 44 Club Rd Suite 150, Eugene, OR 97401 or such other address as CBT may designate.

CBT Nuggets reserves the right, in its sole discretion, to change, modify, add, or remove all or part of the License Agreement at any time, with or without notice.

Billing Agreement

  • By entering into a Billing Agreement with CBT Nuggets, you authorize CBT Nuggets to use automatic billing and to charge your credit card on a recurring basis.
  • You agree to pay subscription charges on a monthly basis, under the following terms and conditions:
    • CBT Nuggets will periodically charge your credit card each monthly billing cycle as your subscription charges become due;
    • All payments are non-refundable and charges made to the credit card under this agreement will constitute in effect a "sales receipt" and confirmation that services were rendered and received;
    • To terminate the recurring billing process and/or arrange for an alternative method of payment, you must notify CBT Nuggets at least 24 hours prior to the end of the monthly billing cycle;
    • You will not dispute CBT Nugget’s recurring billing charges with your credit card issuer so long as the amount in question was for periods prior to the receipt and acknowledgement of a written request to cancel your account or cancel individual licenses on your account.
  • You guarantee and warrant that you are the legal cardholder for the credit card associated with the account, and that you are legally authorized to enter into this recurring billing agreement.
  • You agree to indemnify, defend and hold CBT Nuggets harmless, against any liability pursuant to this authorization.
  • You agree that CBT Nuggets is not obligated to verify or confirm the amount for the purpose of processing these types of payments. You acknowledge and agree that Recurring Payments may be variable and scheduled to occur at certain times.
  • If your payment requires a currency conversion by us, the amount of the currency conversion fee will be determined at the time of your payment. You acknowledge that the exchange rate determined at the time of each payment transaction will differ and you agree to the future execution of payments being based on fluctuating exchange rates.

CBT Nuggets reserves the right, in its sole discretion, to change, modify, add, or remove all or part of the Billing Agreement at any time, with or without notice.

Microsoft SQL Server 2012 70-461

Writing Queries in SQL Server 2012

Course Duration: 11:29:50
Introduction to 70-461: Querying Microsoft SQL Server 2012
In this introductory Nugget we take a good look at what's in store for the series. We start with the 70-461 exam objectives from Microsoft and follow that up with a look at how this series is laid out. Next, we get familiar with the SQL Server 2012 express editions and install it on our Nuggetlab virtual machine. Finally we look at how to obtain and attach the AdventureWorks2012 sample database into our instance and show you a few tips on how to manage your queries and scripts using SQL Server projects.
Creating and Altering Tables with T-SQL
You'll learn how to create, modify and delete tables using T-SQL in this Nugget. You'll also learn how to utilize context sensitive help, translate T-SQL syntax, generate and analyze scripts from tables, and learn some tips and tricks in SQL Server 2012 Management Studio along the way.
Creating and Altering Views with T-SQL
This Nugget will show you how to create, modify and delete views using T-SQL. You'll learn how to modify data using views, create and use indexed views, analyze query execution plans and generate sample data for testing purposes.
Designing Views
Understanding when and how to design views will help you create solutions to common problems and simplify data access. This Nugget will give you the know-how to do just that -- you'll start with view design 101 and learn when to use views; next we'll learn how to design views in SQL Server 2012 Management Studio as well as encrypt view definitions. Finally we'll learn how to apply security at the view level to simplify permission management and keep our base tables secure.
Creating and Modifying Constraints
In this Nugget, you'll learn how to create constraints, which gives us a way to shape the data going into our database and ensure data integrity. You'll learn how to create column and table constraints such as defaults, checks, unique indexes, primary keys and foreign keys using T-SQL. You'll also see how to create a database diagram to get a better visual of the overall database design.
Creating and Modifying DML Triggers
DML Triggers allow us to respond to data modifications on our tables in real-time and are a great tool for any DBA or developer to have in their skill set. This Nugget will show you when and how to create every type of DML trigger for every type of data modification. You'll also learn how to build nested triggers, understand potential performance implications and see how to send an email from inside a trigger using T-SQL.
Querying Data using SELECT
This Nugget will take you on a tour through the SELECT statement and how we use it to return data from the database. You'll start with the basics -- learning the major components of the SELECT statement and follow it up with how to use some extremely useful T-SQL language constructs and built-in functions. You'll also learn how to rank data, build and execute dynamic SQL, and use system metadata to query the database about itself.
Implementing Subqueries
Learning how to write subqueries gives us more ways to design a query. This Nugget will start with the subquery basics and show you where and when we can write nested queries. You'll also learn how to create Common Table Expressions (CTEs) to help us better manage T-SQL code and finish with how to build matrix style results using pivot tables.
Implementing Data Types
Choosing the right data type for storing data, whether it be in table columns or variables, is essential for the integrity, storage and performance of our database. This Nugget gives you an understanding of every data type available in SQL Server 2012 -- you'll learn how to choose the right data type for your database structures, get some common guidelines for working with data types and see first hand the data we can store in each data type.
Implementing Aggregates
This Nugget will show you how to group and aggregate data in a variety of ways. You'll start with the GROUP BY clause to get a handle on the basics of grouping data and incorporate grouping sets for subtotaling our query results. You'll see the new analytic and ranking functions in action while learning how to generate, work with and aggregate spatial data.
Querying and Managing XML Data
XML is a widely adopted format for storing data and this Nugget will show you everything you need to know about XML in SQL Server. The basics cover XML itself -- from schemas to namespaces to how we transform relational data to an XML format. You'll also learn how to load and associate XSD schemas with XML columns and import/export XML using BCP, OPENXML and even SQL Server Integration Services (SSIS).
Creating and Altering Stored Procedures
Stored Procedures allow us to create reusable components on the database server to increase managability and performance of our T-SQL code -- this Nugget will show you the basics and beyond of creating and executing Stored Procedures as well as techniques for building and designing a set of Stored Procedures for use in the data layer of an application.
Modifying Data with T-SQL
This Nugget will show you the basics of data modification with the INSERT, UPDATE and DELETE statement. We'll analyze the syntax of each statement, learn the basic usages and see how to utilize the OUTPUT clause to discover what changes were caused by each statement.
Combining Datasets
Understanding how to work with data from multiple queries will give you another skill to prepare you for anything and everything. This Nugget will show you how to combine, compare and merge results using operators such as UNION, EXCEPT, INTERSECT and MERGE.
Working with Functions
Functions give us a great alternative to reusing code over stored procedures and provide more flexibility in where we can use them. This Nugget will get you familiar with everything functions in T-SQL -- you'll learn how to create scalar and table-value functions, use built-in and system functions and see examples of how to use them in the real world.
Optimizing Queries
Performance is a top priority for databases and many performance issues come from poorly designed queries. This Nugget will show you how to find query performance issues using query execution plans, find slow performing queries in a database using DMVs and give you design guidelines needed to create blazing fast queries.
Managing Transactions
Transactions ensure our data stays in a consistent state and is protected from concurrent processes. This Nugget will show you how to create, manage and work with transactions -- you'll also learn how locking works and see how isolation levels help us when it comes to concurrent operations.
Row-based vs. Set-based Operations
Understanding how T-SQL statements process rows can help you make better design decisions when creating queries. This Nugget will get you familiar with row-based operations such as cursors and loops, when to use them and how to convert them to set-based operations for performance benefits.
Implementing Error Handling
Knowing when and how to handle errors will improve end user and developer experience with the database. This Nugget will show you where, when and how to handle errors using the TRY..CATCH statement. You'll also learn how to generate, store and view custom error messages in SQL Server.

No Bookmarks

This SQL Server video training with Garth Schulte covers the latest version of Microsoft’s much in-demand server, and includes topics such as creating and altering tables with T-SQL, implementing subqueries, managing transactions, and more.

Recommended skills:
  • At least two years experience with writing queries

Recommended equipment:
  • Microsoft SQL Server 2012

Related certifications:
  • MCSA: SQL Server 2012
  • MCSE: Data Platform
  • MCSE: Business Intelligence
  • MCSM: Data Platform

Related job functions:
  • Database administrators
  • System engineers
  • Database developers
  • Database analysts

Take your SQL Server knowledge to the next level by learning how to write queries and work with database entities behind the scenes using T-SQL in Microsoft SQL Server 2012.

This series will take you from front-end user to back-end wizard. You'll learn the ins and outs of working with SQL Server 2012 programmatically, covering in great detail both sides of the T-SQL language -- from creating database objects using DDL (Data Definition Language) to becoming a data ninja by writing queries that slice and dice the information stored in these database objects using DML (Data Manipulation Language).

This is the beginning of the road for SQL Server 2012 certifications and a prerequisite to both of the professional level certifications: Data Platform and Business Intelligence. This series will prepare you for the 70-461 exam and beyond with real world examples!

Introduction to 70-461: Querying Microsoft SQL Server 2012

00:00:00 -
00:00:01 - An introduction to 70-461, query
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 this series.
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, database developers.
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 Edition.
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 hand corner.
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 or
00:02:36 - 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 primary responsibilities
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 this series--
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 Transact SQL.
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, and functions.
00:04:49 - Otherwise known as interfaces, and what the security
00:04:52 - implications are when designing views.
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, DML 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 applications.
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 coalesce statement.
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 data output.
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 expressions,
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 of there.
00:07:07 - We'll look at how to implement data types.
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 modifications.
00:08:07 - So we'll take a look at how to modify data.
00:08:09 - 24% of the exam is on modifying data.
00:08:12 - And we'll start with stored procedures.
00:08:14 - Everybody loves stored procedures.
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 look there.
00:08:42 - We'll also go ahead on how to modify data using the three
00:08:44 - big data modification statements here.
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 data sets.
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 select statement.
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 deterministic versus
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 queries.
00:09:34 - So we'll start with a Nugget on optimizing queries here.
00:09:36 - We'll learn how to understand statistics, database
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 optimized.
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 parameterized queries.
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 manage transactions,
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 based operation.
00:10:48 - So when to use cursors, basically when
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 defined functions.
00:11:16 - We'll combine multiple DML operations,
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 throw statements
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 series overview.
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 DML triggers.
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 website.
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 or 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 happened there.
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 configuration installation.
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 the wizard.
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 that's installed
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 Advanced Services.
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 reporting services,
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 download there.
00:16:44 - So again, I already downloaded this.
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 in English.
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 to views.
00:17:30 - It's really a great sample database.
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 AdventureWorks.
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 our screen.
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 existing version
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 license term.
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 selection.
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 installation paths.
00:20:12 - And we'll hit Next.
00:20:13 -
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 this here--
00:20:21 - we're going to do a named instance.
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 there aren't.
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 requirements.
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 Server browser.
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 collation here.
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 configuration.
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 Windows authentication.
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 SQL Nugget.
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 data directory.
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 defaults there.
00:22:01 - You can configure user instances
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 Install Only.
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 reporting here.
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 rules
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 again here.
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 tools installed.
00:22:50 - This is SQL Server Management tools and some extra database
00:22:53 - tools like the profiler and stuff.
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 services packages,
00:23:10 - things of that nature.
00:23:11 - We've got the database engine services.
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 was installed.
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 data tools.
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 services projects.
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 tools here.
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 configuration, client
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 SQL Server.
00:25:23 - So those are the configuration tools.
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 anything that's
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 from online.
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 .NET development.
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 good stuff.
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 the developer
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 is complete.
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, including the
00:28:04 - database engine.
00:28:05 - And then, we have the installation
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 or keyword.
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 future Nuggets.
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 database.
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 Explorer here.
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 data warehousing,
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 support files.
00:29:57 - And there it is, AdventureWorks 2012_data.mdf.
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 Studio.
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, slick looking
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 directory.
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 AdventureWorks 2012,
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 our installation
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 data file.
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 2012 database.
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 and running.
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 solution 70-461.
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, Querying
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 documentation locally.
00:35:02 - And we even got the sample AdventureWorks database
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.
00:35:20 -

Creating and Altering Tables with T-SQL

Creating and Altering Views with T-SQL

Designing Views

Creating and Modifying Constraints

Creating and Modifying DML Triggers

Querying Data using SELECT

Implementing Subqueries

Implementing Data Types

Implementing Aggregates

Querying and Managing XML Data

Creating and Altering Stored Procedures

Modifying Data with T-SQL

Combining Datasets

Working with Functions

Optimizing Queries

Managing Transactions

Row-based vs. Set-based Operations

Implementing Error Handling

This forum is for community use – trainers will not participate in conversations. Share your thoughts on training content and engage with other members of the CBT Nuggets community. 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
Community Standards

We encourage you to share your wisdom, opinions, and questions with the CBT Nuggets community. To keep things civil, we have established the following policy.

We reserve the right not to post comments that:
contain obscene, indecent, or profane language; contain threats or defamatory statements; contain personal attacks; contain hate speech directed at race, color, sex, sexual orientation, national origin, ethnicity, age, religion, or disability; contributes to a hostile atmosphere; or promotes or endorses services or products. Non-commercial links, if relevant to the topic, are acceptable. Comments are not moderated, however, all comments will automatically be filtered for content that might violate our comment policies. If your comment is flagged by our filter, it will not be published.

We will be continually monitoring published comments and any content that violates our policies will be removed. Users who repeatedly violate our comments policy may be prohibited from commenting.
Garth Schulte

Garth Schulte

CBT Nuggets Trainer


Area of Expertise:
Visual Studio 6, Visual Studio.NET Windows/Web Programming, SQL Server 6.5-2012

Course Features

Speed Control

Play videos at a faster or slower pace.


Pick up where you left off watching a video.


Jot down information to refer back to at a later time.

Closed Captions

Follow what the trainers are saying with ease.

MP3 Downloads

Listen to videos anytime, anywhere


Files/materials that supplement the video training

Annual Course Features

Transcender Practice Exams

These practice tests help you review your knowledge and prepare you for exams.
Available only with the annual subscription.

Virtual Lab

Use a virtual environment to reinforce what you are learning and get hands-on experience.
Available only with the annual subscription.
Your browser cannot access Virtual Labs
Add training to a playlist
or create a new list
Add to current playlist
or add to an existing list
Add to new playlist