1 00:00:00,000 --> 00:00:01,160 2 00:00:01,160 --> 00:00:03,890 An introduction to 70-461, query 3 00:00:03,890 --> 00:00:07,180 Microsoft SQL Server 2012. 4 00:00:07,180 --> 00:00:08,170 Hello, and welcome everyone. 5 00:00:08,170 --> 00:00:09,070 My name's Garth Schulte. 6 00:00:09,070 --> 00:00:10,720 I'll be your guide through this series. 7 00:00:10,720 --> 00:00:12,660 And a little background on myself and SQL Server. 8 00:00:12,660 --> 00:00:14,660 I've been working with it personally as a DBA and a 9 00:00:14,660 --> 00:00:18,100 developer since SQL Server 6.5, which 10 00:00:18,100 --> 00:00:18,870 kind of blows my mind. 11 00:00:18,870 --> 00:00:20,510 Because that was 15 years ago. 12 00:00:20,510 --> 00:00:23,070 And SQL Server's come a long way since then. 13 00:00:23,070 --> 00:00:27,820 So this exam specifically from Microsoft is really focused on 14 00:00:27,820 --> 00:00:30,410 getting you familiar with how to work with database objects 15 00:00:30,410 --> 00:00:32,940 and the data stored inside these objects using 16 00:00:32,940 --> 00:00:35,130 Transact SQL, TSQL. 17 00:00:35,130 --> 00:00:36,910 So we're going to get very familiar with Transact SQL 18 00:00:36,910 --> 00:00:40,170 both from a DDL perspective, data definition language 19 00:00:40,170 --> 00:00:43,130 statements, things like create, alter, and drop. 20 00:00:43,130 --> 00:00:46,110 And we'll get very familiar with DML statements, data 21 00:00:46,110 --> 00:00:48,750 manipulation statements, those statements such as select, 22 00:00:48,750 --> 00:00:50,900 insert, update, and delete, and all the fun stuff we can 23 00:00:50,900 --> 00:00:51,760 do with those. 24 00:00:51,760 --> 00:00:53,990 And so we'll cover all the exam objectives that 25 00:00:53,990 --> 00:00:56,310 Microsoft's going to be expecting from us, and go way 26 00:00:56,310 --> 00:00:57,220 above and beyond that. 27 00:00:57,220 --> 00:00:59,650 I'll show you a lot of the tricks that I've learned over 28 00:00:59,650 --> 00:01:01,780 the years working on SQL Server, some time saving 29 00:01:01,780 --> 00:01:05,570 features, and just learning how to work with SQL Server 30 00:01:05,570 --> 00:01:08,560 and work with Transact SQL in a way that'll make you a very 31 00:01:08,560 --> 00:01:10,782 efficient programmer. 32 00:01:10,782 --> 00:01:14,750 Or as I like to call us, DBDs, database developers. 33 00:01:14,750 --> 00:01:16,960 Hey, DBAs get their own acronym, so why not the 34 00:01:16,960 --> 00:01:18,240 developers, too? 35 00:01:18,240 --> 00:01:20,050 So we'll take a good look at it from both perspectives, 36 00:01:20,050 --> 00:01:22,460 from the DBA and the DBD perspective on how to query 37 00:01:22,460 --> 00:01:25,730 and work with SQL Server 2012. 38 00:01:25,730 --> 00:01:27,810 So with all that said, let's head over to our white 39 00:01:27,810 --> 00:01:30,280 boarding and take a look at the outline for this Nugget. 40 00:01:30,280 --> 00:01:31,790 We're going to start with an exam overview. 41 00:01:31,790 --> 00:01:33,450 Are here, I just want to take you over to Microsoft's 42 00:01:33,450 --> 00:01:36,850 website and get you familiar with what is going to be 43 00:01:36,850 --> 00:01:39,990 required, or what Microsoft is going to expect from us as far 44 00:01:39,990 --> 00:01:41,580 as the objectives go on the exam. 45 00:01:41,580 --> 00:01:44,330 So we'll look at the 70-461 exam, check out the 46 00:01:44,330 --> 00:01:47,000 objectives, and see what this Nugget is going to cover. 47 00:01:47,000 --> 00:01:48,700 Then, we'll take a look at the Nugget series overview. 48 00:01:48,700 --> 00:01:50,940 Just want to go over all the different Nuggets in this 49 00:01:50,940 --> 00:01:52,520 series, talk about the videos. 50 00:01:52,520 --> 00:01:56,000 And specifically here, find out how you can relate these 51 00:01:56,000 --> 00:01:56,630 two together. 52 00:01:56,630 --> 00:01:59,190 So if there's something that you're weak on or something 53 00:01:59,190 --> 00:02:01,290 that you just want to learn specifically inside of the 54 00:02:01,290 --> 00:02:04,190 exam objectives, you'll easily be able to find it and know 55 00:02:04,190 --> 00:02:07,960 exactly what video to go to inside of the series. 56 00:02:07,960 --> 00:02:09,669 And finally, we're going to go take a look at how to install 57 00:02:09,669 --> 00:02:12,380 SQL Server 2012 Express Edition. 58 00:02:12,380 --> 00:02:14,590 And there are actually five editions out there of SQL 59 00:02:14,590 --> 00:02:16,270 Server 2012 Express. 60 00:02:16,270 --> 00:02:18,870 And I'll briefly go over each one of them. 61 00:02:18,870 --> 00:02:20,980 And then, we're going to head over to Nugget Lab, which is a 62 00:02:20,980 --> 00:02:23,950 new initiative here from Nuggets that allows you to 63 00:02:23,950 --> 00:02:27,080 work on the same exact server that we're going to be doing 64 00:02:27,080 --> 00:02:28,140 these Nuggets on. 65 00:02:28,140 --> 00:02:30,300 So just to show you, you've probably seen this little icon 66 00:02:30,300 --> 00:02:31,390 in the upper right hand corner. 67 00:02:31,390 --> 00:02:33,920 This is a virtual instance that, again, you can 68 00:02:33,920 --> 00:02:34,940 also gain access to. 69 00:02:34,940 --> 00:02:36,980 Just head over to cbtnuggets.com or 70 00:02:36,980 --> 00:02:40,050 nuggetlab.com to find out more about how you can access 71 00:02:40,050 --> 00:02:40,850 Nugget Lab. 72 00:02:40,850 --> 00:02:44,280 But it's a fresh install of Windows Server 2008 right now. 73 00:02:44,280 --> 00:02:45,200 So there's nothing on it. 74 00:02:45,200 --> 00:02:45,760 It's brand new. 75 00:02:45,760 --> 00:02:46,595 So we're going to head over. 76 00:02:46,595 --> 00:02:49,380 We're going to install SQL Server 2012 Express on 77 00:02:49,380 --> 00:02:50,110 it, get it set up. 78 00:02:50,110 --> 00:02:52,550 I'll show you how to set it up, the documentation, because 79 00:02:52,550 --> 00:02:54,630 that's changed a little bit in this version. 80 00:02:54,630 --> 00:02:59,190 And then, we will do all of our demos on this Nugget. 81 00:02:59,190 --> 00:02:59,820 And we'll build it up. 82 00:02:59,820 --> 00:03:03,390 And I'll show you how we can access all the demos from 83 00:03:03,390 --> 00:03:04,470 using Nugget Lab. 84 00:03:04,470 --> 00:03:06,000 And we'll generally have a good time over 85 00:03:06,000 --> 00:03:07,730 there in Nugget Lab. 86 00:03:07,730 --> 00:03:09,630 And learn a lot, of course. 87 00:03:09,630 --> 00:03:12,210 All right, let's head over to Microsoft's website and take a 88 00:03:12,210 --> 00:03:14,110 look at the 70-461 example. 89 00:03:14,110 --> 00:03:15,650 So if we head down to the Start button and fire up 90 00:03:15,650 --> 00:03:19,520 Internet Explorer here, we can just Google 70-461. 91 00:03:19,520 --> 00:03:22,830 And we'll find out that it equals negative 391. 92 00:03:22,830 --> 00:03:25,340 But actually, what we really want here is the very first 93 00:03:25,340 --> 00:03:29,250 link that comes up, exam 70-461 from Microsoft. 94 00:03:29,250 --> 00:03:32,042 If we click on that and head on over here, I'm just going 95 00:03:32,042 --> 00:03:34,120 to zoom out a little bit here. 96 00:03:34,120 --> 00:03:35,060 There we go. 97 00:03:35,060 --> 00:03:37,170 And here's the overview. 98 00:03:37,170 --> 00:03:38,520 So it just gives you a brief overview. 99 00:03:38,520 --> 00:03:41,000 It's intended for SQL Server DBAs, implementers, system 100 00:03:41,000 --> 00:03:43,610 engineers, and developers with a few years experience looking 101 00:03:43,610 --> 00:03:46,580 to prove their skills and knowledge in writing queries. 102 00:03:46,580 --> 00:03:49,020 So here's a list of some of the primary responsibilities 103 00:03:49,020 --> 00:03:51,040 and some of the things that we're going to need to know in 104 00:03:51,040 --> 00:03:52,340 order to pass the exam. 105 00:03:52,340 --> 00:03:55,340 What I like to do is come over to the skills measured tab and 106 00:03:55,340 --> 00:03:57,920 take a look at really the details here of what they're 107 00:03:57,920 --> 00:03:58,780 going to be expecting. 108 00:03:58,780 --> 00:04:02,220 So you can see 24% of the exam is going to be focused on 109 00:04:02,220 --> 00:04:04,330 creating database objects. 110 00:04:04,330 --> 00:04:05,450 So this is where we're going to start. 111 00:04:05,450 --> 00:04:08,540 And again, I modeled this series-- 112 00:04:08,540 --> 00:04:10,000 pretty much each one of these main bullet 113 00:04:10,000 --> 00:04:11,780 points here is a Nugget. 114 00:04:11,780 --> 00:04:13,760 So the very first Nugget that's going to follow after 115 00:04:13,760 --> 00:04:17,000 this introduction is going to be how to create and work with 116 00:04:17,000 --> 00:04:19,100 tables using Transact SQL. 117 00:04:19,100 --> 00:04:21,120 So some simple statements here, just get familiar with 118 00:04:21,120 --> 00:04:22,089 the syntax. 119 00:04:22,089 --> 00:04:24,570 We're going to learn how to create tables without using 120 00:04:24,570 --> 00:04:27,665 the built in tools using things like the alter drop, 121 00:04:27,665 --> 00:04:29,750 the alter column, and obviously here the create 122 00:04:29,750 --> 00:04:30,430 table statement. 123 00:04:30,430 --> 00:04:32,140 So we'll take a good look how to create tables 124 00:04:32,140 --> 00:04:34,070 programmatically using Transact SQL. 125 00:04:34,070 --> 00:04:36,230 Then, we'll do the same thing with views, how to create and 126 00:04:36,230 --> 00:04:40,160 alter views using create, alter, and drop. 127 00:04:40,160 --> 00:04:43,550 And then, we'll move into learning how to design views. 128 00:04:43,550 --> 00:04:45,870 So this is going to-- we're going to ensure code 129 00:04:45,870 --> 00:04:48,470 non-regression by keeping a consistent signature for 130 00:04:48,470 --> 00:04:49,940 procedures, views, and functions. 131 00:04:49,940 --> 00:04:52,180 Otherwise known as interfaces, and what the security 132 00:04:52,180 --> 00:04:54,490 implications are when designing views. 133 00:04:54,490 --> 00:04:57,440 And if that is confusing, we'll make sure to clear it up 134 00:04:57,440 --> 00:04:59,130 here over there in the design views. 135 00:04:59,130 --> 00:05:00,980 It's actually not that bad. 136 00:05:00,980 --> 00:05:01,840 And it's pretty cool, too. 137 00:05:01,840 --> 00:05:06,750 It's a great way to learn how to not only design good views 138 00:05:06,750 --> 00:05:08,860 for use, but it's also cool. 139 00:05:08,860 --> 00:05:11,080 I like this because it's a way that you can ensure backwards 140 00:05:11,080 --> 00:05:14,040 compatibility for many applications in your database. 141 00:05:14,040 --> 00:05:16,930 So we'll take a good look at how to properly 142 00:05:16,930 --> 00:05:18,480 design views there. 143 00:05:18,480 --> 00:05:21,060 We'll take a look how to create and modify constraints. 144 00:05:21,060 --> 00:05:23,250 We'll look at how to create and alter data manipulation 145 00:05:23,250 --> 00:05:25,630 statement triggers, DML triggers. 146 00:05:25,630 --> 00:05:26,690 And that's really going to cover how to 147 00:05:26,690 --> 00:05:27,630 create database objects. 148 00:05:27,630 --> 00:05:29,870 So the big objects here are going to be tables, views, 149 00:05:29,870 --> 00:05:31,860 constraints, and triggers. 150 00:05:31,860 --> 00:05:35,300 Then, we'll head on down to working with data. 151 00:05:35,300 --> 00:05:37,580 We'll start this segment off with a big look 152 00:05:37,580 --> 00:05:39,750 at the select statement. 153 00:05:39,750 --> 00:05:42,205 So select is our main statement here that we use to 154 00:05:42,205 --> 00:05:44,640 work with data, slice and dice data, pull data out of our 155 00:05:44,640 --> 00:05:48,450 tables to pass into client applications or that our views 156 00:05:48,450 --> 00:05:51,830 are going to use to do the same thing, pull the data out 157 00:05:51,830 --> 00:05:53,370 of the tables, pass it back to the applications. 158 00:05:53,370 --> 00:05:56,750 Or ourselves, if we just want to write some statements 159 00:05:56,750 --> 00:05:59,320 against tables to analyze data and such. 160 00:05:59,320 --> 00:06:00,750 So we'll take a big look at the select statement here, 161 00:06:00,750 --> 00:06:03,245 everything from how to work with ranking functions, which 162 00:06:03,245 --> 00:06:06,540 I personally like and I use quite a bit inside of the .NET 163 00:06:06,540 --> 00:06:08,190 world when I build applications. 164 00:06:08,190 --> 00:06:11,220 I'm always using ranking functions, really cool stuff. 165 00:06:11,220 --> 00:06:14,520 We'll learn how to write and perform queries efficiently. 166 00:06:14,520 --> 00:06:17,370 We'll use some of the new code items here, synonyms and joins 167 00:06:17,370 --> 00:06:20,470 using the except and intersect keywords here, implement logic 168 00:06:20,470 --> 00:06:23,075 that uses dynamic SQL and system metadata. 169 00:06:23,075 --> 00:06:25,490 Learn how to write efficient, technically complex SQL 170 00:06:25,490 --> 00:06:29,040 queries including all types of joins versus the 171 00:06:29,040 --> 00:06:30,300 use of drive tables. 172 00:06:30,300 --> 00:06:31,950 So you can see, this is going to be a big Nugget. 173 00:06:31,950 --> 00:06:33,540 This will be a long one, I guarantee you. 174 00:06:33,540 --> 00:06:35,950 And here at the end here, we'll take a look at the case 175 00:06:35,950 --> 00:06:39,150 versus isNull versus the coalesce statement. 176 00:06:39,150 --> 00:06:40,340 I love the coalesce statement. 177 00:06:40,340 --> 00:06:43,630 I'll do some good real world examples of how this is useful 178 00:06:43,630 --> 00:06:44,500 so you can get the hang of this. 179 00:06:44,500 --> 00:06:45,720 Believe me, you'll be using it everywhere. 180 00:06:45,720 --> 00:06:47,950 It's really neat to format data output. 181 00:06:47,950 --> 00:06:50,970 We'll also take a look at how to implement sub-queries. 182 00:06:50,970 --> 00:06:54,130 So we'll look at, how do I identify problematic elements 183 00:06:54,130 --> 00:06:54,940 in query plans? 184 00:06:54,940 --> 00:06:55,650 That's cool stuff. 185 00:06:55,650 --> 00:06:58,170 So we'll get familiar with how to work with query plans here, 186 00:06:58,170 --> 00:07:01,300 pivot and un-pivot, the Apply operator, CTE statements, 187 00:07:01,300 --> 00:07:03,230 which are common table expressions, 188 00:07:03,230 --> 00:07:04,060 and the with statement. 189 00:07:04,060 --> 00:07:06,280 So we'll take a good look here at sub-queries and some of the 190 00:07:06,280 --> 00:07:07,860 different things inside of there. 191 00:07:07,860 --> 00:07:10,160 We'll look at how to implement data types. 192 00:07:10,160 --> 00:07:12,100 We'll look at a lot of different data types here, how 193 00:07:12,100 --> 00:07:15,420 to understand and use the right data 194 00:07:15,420 --> 00:07:16,800 type for the column. 195 00:07:16,800 --> 00:07:19,390 We'll look at the GUID, the global unique identifier using 196 00:07:19,390 --> 00:07:22,000 new ID and the new sequential ID and how that affects 197 00:07:22,000 --> 00:07:23,330 database performance. 198 00:07:23,330 --> 00:07:25,120 And again here, when to use which data type 199 00:07:25,120 --> 00:07:26,660 for specific columns. 200 00:07:26,660 --> 00:07:29,420 We'll then also look at aggregate queries and how to 201 00:07:29,420 --> 00:07:31,530 work with some of the new functions inside 202 00:07:31,530 --> 00:07:33,070 of SQL Server 2012. 203 00:07:33,070 --> 00:07:35,740 We've got some new analytic functions, grouping sets, 204 00:07:35,740 --> 00:07:39,140 spatial aggregates a lot of people are excited about. 205 00:07:39,140 --> 00:07:40,410 So we'll look at what those are all about. 206 00:07:40,410 --> 00:07:41,950 And then, how to apply a ranking function. 207 00:07:41,950 --> 00:07:43,200 So we'll take a good look here at how to work with 208 00:07:43,200 --> 00:07:44,280 aggregates. 209 00:07:44,280 --> 00:07:46,140 And then, the last part of working with data here, we'll 210 00:07:46,140 --> 00:07:48,580 look at how to query and manage XML data. 211 00:07:48,580 --> 00:07:51,470 We'll get familiar with some of the XML data types, their 212 00:07:51,470 --> 00:07:54,680 limitations, restrictions, implement XML schemas, handle 213 00:07:54,680 --> 00:07:58,780 XML data, and just generally work with XML through 214 00:07:58,780 --> 00:08:03,730 importing, exporting, and also how to index XML columns. 215 00:08:03,730 --> 00:08:05,630 All right, scrolling down here, the next section is 216 00:08:05,630 --> 00:08:07,810 dealing with data modifications. 217 00:08:07,810 --> 00:08:09,370 So we'll take a look at how to modify data. 218 00:08:09,370 --> 00:08:12,520 24% of the exam is on modifying data. 219 00:08:12,520 --> 00:08:14,210 And we'll start with stored procedures. 220 00:08:14,210 --> 00:08:15,530 Everybody loves stored procedures. 221 00:08:15,530 --> 00:08:16,030 They're awesome. 222 00:08:16,030 --> 00:08:18,800 They're a great way to speed up application performance. 223 00:08:18,800 --> 00:08:21,810 They're just queries that sit on the server side, optimized 224 00:08:21,810 --> 00:08:22,950 for the server side. 225 00:08:22,950 --> 00:08:24,440 And so we'll take a good look at how to build stored 226 00:08:24,440 --> 00:08:24,870 procedures. 227 00:08:24,870 --> 00:08:26,250 I've built thousands in my day. 228 00:08:26,250 --> 00:08:28,410 Still build them every day, and I love them. 229 00:08:28,410 --> 00:08:29,180 They're great. 230 00:08:29,180 --> 00:08:32,179 They're great, especially for slow performing applications. 231 00:08:32,179 --> 00:08:34,610 Great way to speed things up and keep the logic on the 232 00:08:34,610 --> 00:08:35,270 database side. 233 00:08:35,270 --> 00:08:38,419 A lot easier to change logic that's stored in the database 234 00:08:38,419 --> 00:08:40,929 than it is compiled inside of an application. 235 00:08:40,929 --> 00:08:42,059 So we'll take a good look there. 236 00:08:42,059 --> 00:08:44,500 We'll also go ahead on how to modify data using the three 237 00:08:44,500 --> 00:08:47,200 big data modification statements here. 238 00:08:47,200 --> 00:08:49,390 Insert, used to add new data. 239 00:08:49,390 --> 00:08:50,790 Update, change existing data. 240 00:08:50,790 --> 00:08:52,970 Delete, remove data out of tables. 241 00:08:52,970 --> 00:08:54,460 So we'll take a good look at these. 242 00:08:54,460 --> 00:08:56,490 We'll look how to combine data sets. 243 00:08:56,490 --> 00:09:00,030 We'll take a look at the union versus union all. 244 00:09:00,030 --> 00:09:02,570 Microsoft has this in two places, right, case versus 245 00:09:02,570 --> 00:09:04,560 null coalesce. 246 00:09:04,560 --> 00:09:05,560 That's already up in the select statement. 247 00:09:05,560 --> 00:09:07,620 So we'll probably look at that up there. 248 00:09:07,620 --> 00:09:10,190 And here, we'll look how to modify data using the new 249 00:09:10,190 --> 00:09:14,070 merge statement that also came out in SQL Server 2008. 250 00:09:14,070 --> 00:09:16,070 Then, we'll look at how to work with functions. 251 00:09:16,070 --> 00:09:17,980 Here, we'll take a look at deterministic versus 252 00:09:17,980 --> 00:09:20,940 non-deterministic functions, scalar and table valued 253 00:09:20,940 --> 00:09:24,180 functions, how to apply built in scalar functions, and then 254 00:09:24,180 --> 00:09:28,350 just generally create UDFs, user defined functions. 255 00:09:28,350 --> 00:09:31,210 And finally, the last 25% of the exam is going to deal with 256 00:09:31,210 --> 00:09:34,030 troubleshooting and optimizing queries. 257 00:09:34,030 --> 00:09:36,630 So we'll start with a Nugget on optimizing queries here. 258 00:09:36,630 --> 00:09:39,370 We'll learn how to understand statistics, database 259 00:09:39,370 --> 00:09:41,910 statistics, and the object that contains all the 260 00:09:41,910 --> 00:09:44,600 statistics for the columns inside of the tables. 261 00:09:44,600 --> 00:09:47,360 We'll learn how to read query plans, look at plan guides, 262 00:09:47,360 --> 00:09:48,090 look at DMVs-- 263 00:09:48,090 --> 00:09:51,820 and no, that's not the Department of Motor Vehicles, 264 00:09:51,820 --> 00:09:54,880 although that's funny that that's in the optimize area 265 00:09:54,880 --> 00:09:56,920 because nobody wants to stand in line all day, right? 266 00:09:56,920 --> 00:09:59,880 That could be highly optimized. 267 00:09:59,880 --> 00:10:02,760 But this is more for the dynamic management views that 268 00:10:02,760 --> 00:10:06,070 we can use to check out the status of a server. 269 00:10:06,070 --> 00:10:09,100 We'll look at some hints here, table hints, statistics, 270 00:10:09,100 --> 00:10:11,500 input, output, dynamic versus parameterized queries. 271 00:10:11,500 --> 00:10:14,330 We'll look at the different join types inside of the query 272 00:10:14,330 --> 00:10:17,940 plans and how SQL Server internally fetches this data. 273 00:10:17,940 --> 00:10:21,000 So we'll look at the hash merge and join types and 274 00:10:21,000 --> 00:10:22,570 describe the scenarios in which they would be used. 275 00:10:22,570 --> 00:10:25,030 So we'll take a good look here at optimized queries. 276 00:10:25,030 --> 00:10:27,060 We'll also look at how to manage transactions, 277 00:10:27,060 --> 00:10:29,800 everything from marking a transactions to getting 278 00:10:29,800 --> 00:10:33,140 familiar with the begin, commit, and roll back trans, 279 00:10:33,140 --> 00:10:35,880 implicit versus explicit transaction, look at isolation 280 00:10:35,880 --> 00:10:38,650 levels, and scope and the type of locks, trancount. 281 00:10:38,650 --> 00:10:41,340 So just pretty much everything there is to do with 282 00:10:41,340 --> 00:10:42,670 transactions. 283 00:10:42,670 --> 00:10:45,350 And we'll look at how to evaluate the use of row based 284 00:10:45,350 --> 00:10:48,370 operations versus set based operation. 285 00:10:48,370 --> 00:10:51,470 So when to use cursors, basically when 286 00:10:51,470 --> 00:10:53,100 to go row by row. 287 00:10:53,100 --> 00:10:56,180 It's kind of like programming where row based operations is 288 00:10:56,180 --> 00:10:58,800 looping through the table one row at a time, performing 289 00:10:58,800 --> 00:10:59,820 logic on every row. 290 00:10:59,820 --> 00:11:04,000 Set based is working with an entire result set, so the 291 00:11:04,000 --> 00:11:06,990 entire table at once, or a chunk of data at once. 292 00:11:06,990 --> 00:11:10,030 So evaluate the use of both of those here. 293 00:11:10,030 --> 00:11:13,010 See when to use cursors here, which is pretty much never 294 00:11:13,010 --> 00:11:14,260 these days. 295 00:11:14,260 --> 00:11:16,420 The impact of scalar user defined functions. 296 00:11:16,420 --> 00:11:19,170 We'll combine multiple DML operations, 297 00:11:19,170 --> 00:11:20,190 all that good stuff. 298 00:11:20,190 --> 00:11:21,370 And finally here, we'll take a look at how to 299 00:11:21,370 --> 00:11:22,750 implement error handling. 300 00:11:22,750 --> 00:11:25,540 Look at the try catch throw statements 301 00:11:25,540 --> 00:11:27,120 inside of SQL Server. 302 00:11:27,120 --> 00:11:30,045 We'll use set based rather than row based logic and deal 303 00:11:30,045 --> 00:11:31,250 with transaction management. 304 00:11:31,250 --> 00:11:33,970 So a small Nugget there on how to work with error handling 305 00:11:33,970 --> 00:11:35,730 inside of Transact SQL. 306 00:11:35,730 --> 00:11:37,330 So that's what this series is going to cover. 307 00:11:37,330 --> 00:11:41,130 That's pretty much querying Microsoft SQL Server 2012. 308 00:11:41,130 --> 00:11:42,790 And again, we're going to go far above and beyond that. 309 00:11:42,790 --> 00:11:45,110 I'll show you a lot of cool things along the way here and 310 00:11:45,110 --> 00:11:48,110 get as much extra stuff as I can squeeze into this series. 311 00:11:48,110 --> 00:11:51,105 But we'll definitely cover all of this stuff and more. 312 00:11:51,105 --> 00:11:53,240 All right, let's close out of this. 313 00:11:53,240 --> 00:11:54,890 And now, what I want to show you here going back to the 314 00:11:54,890 --> 00:11:57,230 white board is our Nugget series overview. 315 00:11:57,230 --> 00:11:58,300 So if we head over there, this is going 316 00:11:58,300 --> 00:11:59,520 to look really familiar. 317 00:11:59,520 --> 00:12:00,040 See this? 318 00:12:00,040 --> 00:12:03,630 I broke it out by each of those areas. 319 00:12:03,630 --> 00:12:08,460 So pretty much, we got close to 25% in each segment here, 320 00:12:08,460 --> 00:12:10,420 give or take a little bit more in certain areas. 321 00:12:10,420 --> 00:12:12,740 But you can see we're creating database objects starting with 322 00:12:12,740 --> 00:12:14,750 Nugget number one here, creating and altering tables 323 00:12:14,750 --> 00:12:18,400 with TSQL, then views with TSQL, designing views, 324 00:12:18,400 --> 00:12:20,580 modifying constraints, modifying DML triggers. 325 00:12:20,580 --> 00:12:25,370 So you can see the relation here between the series and 326 00:12:25,370 --> 00:12:26,030 the objectives. 327 00:12:26,030 --> 00:12:27,910 I tried to marry them as closely as possible. 328 00:12:27,910 --> 00:12:30,760 That way, it'll be very easy for you to find out what 329 00:12:30,760 --> 00:12:34,870 objectives are taught inside of which Nugget. 330 00:12:34,870 --> 00:12:36,320 All right, and I won't go through all these because we 331 00:12:36,320 --> 00:12:38,300 just did over on Microsoft's website. 332 00:12:38,300 --> 00:12:39,120 I just wanted to show you how to relate the 333 00:12:39,120 --> 00:12:40,530 two together there. 334 00:12:40,530 --> 00:12:41,500 Now, let's minimize the white board. 335 00:12:41,500 --> 00:12:44,980 I want to head over to Nugget Lab and get installation of 336 00:12:44,980 --> 00:12:48,360 SQL Server 2012 up and running here, Express Edition. 337 00:12:48,360 --> 00:12:51,040 Now again, Nugget Lab, it's a virtual instance out there on 338 00:12:51,040 --> 00:12:52,350 the internet that we're going to be doing all 339 00:12:52,350 --> 00:12:53,770 of our demos on. 340 00:12:53,770 --> 00:12:55,160 By the time we're done with this series, there's going to 341 00:12:55,160 --> 00:12:56,870 be a nice collection of all the demos 342 00:12:56,870 --> 00:12:58,610 broken out by Nugget. 343 00:12:58,610 --> 00:13:02,820 And again, you can have access to this exact image here, the 344 00:13:02,820 --> 00:13:05,270 exact instance of SQL Server Express Server we're going to 345 00:13:05,270 --> 00:13:05,980 be working on. 346 00:13:05,980 --> 00:13:09,020 It'll all be preloaded for you, and all the demos will be 347 00:13:09,020 --> 00:13:09,680 there for you to work with. 348 00:13:09,680 --> 00:13:10,970 So you can follow along. 349 00:13:10,970 --> 00:13:14,040 So again, visit cbtnuggets.com or nuggetlab.com to find out 350 00:13:14,040 --> 00:13:16,020 more information about how you can access 351 00:13:16,020 --> 00:13:18,670 this Nugget Lab instance. 352 00:13:18,670 --> 00:13:20,040 So let's head over to Nugget Lab. 353 00:13:20,040 --> 00:13:20,970 I'll double click on the icon here. 354 00:13:20,970 --> 00:13:24,100 This will get us a remote desktop connection over to our 355 00:13:24,100 --> 00:13:25,700 Nugget Lab instance. 356 00:13:25,700 --> 00:13:27,930 And again, this is a fresh install of 357 00:13:27,930 --> 00:13:29,760 Windows Server 2008 R2. 358 00:13:29,760 --> 00:13:31,860 I haven't done anything at all to this server. 359 00:13:31,860 --> 00:13:36,040 So the first thing we do need to do is get SQL Server 2012 360 00:13:36,040 --> 00:13:37,100 Express Edition installed. 361 00:13:37,100 --> 00:13:39,160 Now, I already have it downloaded on this machine. 362 00:13:39,160 --> 00:13:41,880 But I still want to show you where you can find it. 363 00:13:41,880 --> 00:13:44,570 So let's head down to Start. 364 00:13:44,570 --> 00:13:47,960 Hit up Internet Explorer here. 365 00:13:47,960 --> 00:13:50,840 And let's just go over to Google. 366 00:13:50,840 --> 00:13:56,155 And we're just going to type in SQL Server 2012 Express. 367 00:13:56,155 --> 00:13:58,570 We'll search for this. 368 00:13:58,570 --> 00:14:01,060 Why wait when we can click on that now? 369 00:14:01,060 --> 00:14:03,270 And oh, something happened there. 370 00:14:03,270 --> 00:14:04,660 Must have clicked too fast. 371 00:14:04,660 --> 00:14:05,120 Here we go. 372 00:14:05,120 --> 00:14:08,100 So the first link that comes up, it takes us right to the 373 00:14:08,100 --> 00:14:11,400 Microsoft Express page for SQL, the latest version here. 374 00:14:11,400 --> 00:14:13,560 So if we click on that, it'll get us right there. 375 00:14:13,560 --> 00:14:14,830 You can click on the link right here. 376 00:14:14,830 --> 00:14:16,800 That will take you to the download page. 377 00:14:16,800 --> 00:14:19,100 There's also a link over here to the right that says, Get 378 00:14:19,100 --> 00:14:21,530 SQL Server 2012 Express. 379 00:14:21,530 --> 00:14:23,010 So let's click on that guy. 380 00:14:23,010 --> 00:14:25,250 And this will take you right to the download page. 381 00:14:25,250 --> 00:14:29,280 So you can download these products in 32-bit or 64-bit. 382 00:14:29,280 --> 00:14:33,150 We're in a 64-bit machine here, so let's drop this down. 383 00:14:33,150 --> 00:14:35,550 Here are the five editions that we can download. 384 00:14:35,550 --> 00:14:38,070 Local DB is brand new to the Express Suite. 385 00:14:38,070 --> 00:14:41,220 This is really targeted for developers because it's a very 386 00:14:41,220 --> 00:14:44,180 lightweight version of the database engine that they can 387 00:14:44,180 --> 00:14:46,860 quickly get installed and up and running. 388 00:14:46,860 --> 00:14:50,490 It's actually a zero configuration installation. 389 00:14:50,490 --> 00:14:52,040 You don't have to go through all these wizards and 390 00:14:52,040 --> 00:14:52,860 configure everything. 391 00:14:52,860 --> 00:14:54,850 It's just launch it, and it's done. 392 00:14:54,850 --> 00:14:55,880 It installs. 393 00:14:55,880 --> 00:14:58,856 And what's neat about this is a developer can install it on 394 00:14:58,856 --> 00:14:59,880 their laptop. 395 00:14:59,880 --> 00:15:02,350 They can actually access it through their development 396 00:15:02,350 --> 00:15:05,160 environment, things like Visual Studio, .NET. 397 00:15:05,160 --> 00:15:08,020 They can hook right into using some tools and designers 398 00:15:08,020 --> 00:15:10,480 inside of Visual Studio that'll allow them to work 399 00:15:10,480 --> 00:15:11,640 with the instances. 400 00:15:11,640 --> 00:15:13,970 So that's brand new, and it's very lightweight, easy for 401 00:15:13,970 --> 00:15:14,680 developers to use. 402 00:15:14,680 --> 00:15:17,460 And that's what developer should use to design against a 403 00:15:17,460 --> 00:15:19,480 SQL Server Express database. 404 00:15:19,480 --> 00:15:22,990 Then, we have Express with the database only. 405 00:15:22,990 --> 00:15:26,640 And very similar to Local DB, only of course you have to go 406 00:15:26,640 --> 00:15:29,220 through the installation, choose your instance, set 407 00:15:29,220 --> 00:15:31,420 everything up through the wizard. 408 00:15:31,420 --> 00:15:33,940 So it's your typical Express Install, only it doesn't come 409 00:15:33,940 --> 00:15:34,430 with tools. 410 00:15:34,430 --> 00:15:36,660 So the only way you'll be able to access this is if you 411 00:15:36,660 --> 00:15:38,325 already have the tools installed on the server that 412 00:15:38,325 --> 00:15:40,960 you're installing Express, or remotely using SQL Server 413 00:15:40,960 --> 00:15:42,510 Management Studio that's installed 414 00:15:42,510 --> 00:15:44,170 on a different machine. 415 00:15:44,170 --> 00:15:47,470 The third version we have here is Express with tools. 416 00:15:47,470 --> 00:15:51,300 So this is both Express, the engine itself, with SQL Server 417 00:15:51,300 --> 00:15:53,940 Management Studio and the tools that come with it. 418 00:15:53,940 --> 00:15:55,370 And this fourth one is SQL Server 419 00:15:55,370 --> 00:15:56,550 Management Studio Express. 420 00:15:56,550 --> 00:15:58,030 This is just simply the tools by 421 00:15:58,030 --> 00:15:59,430 themselves, no database engine. 422 00:15:59,430 --> 00:16:03,190 So if you need just to install the tools on a remote machine 423 00:16:03,190 --> 00:16:06,670 that needs access to a SQL Server Express Edition, you 424 00:16:06,670 --> 00:16:09,060 can just download and install the tools. 425 00:16:09,060 --> 00:16:10,500 And finally, what we're going to use and what I've 426 00:16:10,500 --> 00:16:14,320 downloaded is Express with Advanced Services. 427 00:16:14,320 --> 00:16:17,430 In the training world and when you're dealing with wanting to 428 00:16:17,430 --> 00:16:19,460 learn this stuff, I always like to download the biggest 429 00:16:19,460 --> 00:16:20,890 and the best because it has everything. 430 00:16:20,890 --> 00:16:21,750 And that's what this has. 431 00:16:21,750 --> 00:16:22,530 It has everything-- 432 00:16:22,530 --> 00:16:24,980 reporting services, integration services, all what 433 00:16:24,980 --> 00:16:26,940 they call the Advanced Service Suite inside 434 00:16:26,940 --> 00:16:28,860 of SQL Server 2012. 435 00:16:28,860 --> 00:16:30,180 So this is what I downloaded. 436 00:16:30,180 --> 00:16:31,950 I'd like to show you how to set it up here in case you 437 00:16:31,950 --> 00:16:35,790 want to get adventurous and go outside of this Nugget and get 438 00:16:35,790 --> 00:16:36,980 familiar with things like reporting services, 439 00:16:36,980 --> 00:16:39,760 integration services, and that kind of stuff here. 440 00:16:39,760 --> 00:16:42,770 So you would click on this, hit the Download button, and 441 00:16:42,770 --> 00:16:44,980 that'll start your download there. 442 00:16:44,980 --> 00:16:46,380 So again, I already downloaded this. 443 00:16:46,380 --> 00:16:49,480 So let's close out of the browser here. 444 00:16:49,480 --> 00:16:51,230 Yeah, we want to close all tabs. 445 00:16:51,230 --> 00:16:53,331 And I'm going to go to Start. 446 00:16:53,331 --> 00:16:54,680 I'm just going to hit Run here. 447 00:16:54,680 --> 00:16:57,740 And let's just go right to the C drive. 448 00:16:57,740 --> 00:17:01,710 And I made a folder in the C drive, the root called 70-461 449 00:17:01,710 --> 00:17:02,470 Support Files. 450 00:17:02,470 --> 00:17:06,170 So if we go in here, I downloaded SQL Server Express 451 00:17:06,170 --> 00:17:08,930 Advanced Tools 64-bit in English. 452 00:17:08,930 --> 00:17:10,510 So this is what we're going to install. 453 00:17:10,510 --> 00:17:12,460 I've also here-- what I want to show you after we get this 454 00:17:12,460 --> 00:17:14,290 up and running is I downloaded the 455 00:17:14,290 --> 00:17:17,210 AdventureWorks 2012 database. 456 00:17:17,210 --> 00:17:20,660 This is the sample database that comes separate with SQL 457 00:17:20,660 --> 00:17:23,089 Server that you can download and attach to it. 458 00:17:23,089 --> 00:17:25,740 And it's a great database to get familiar with things 459 00:17:25,740 --> 00:17:28,490 because it's got everything from simple structures in it 460 00:17:28,490 --> 00:17:30,370 to complex structures to views. 461 00:17:30,370 --> 00:17:31,880 It's really a great sample database. 462 00:17:31,880 --> 00:17:33,000 So if you want to play around with it, 463 00:17:33,000 --> 00:17:34,410 it'll be on the server. 464 00:17:34,410 --> 00:17:36,680 We'll definitely use it in some demos here to get 465 00:17:36,680 --> 00:17:37,470 familiar with things. 466 00:17:37,470 --> 00:17:39,670 We're also going to do our own thing, as well. 467 00:17:39,670 --> 00:17:42,450 But it's nice that we have a sample database filled with 468 00:17:42,450 --> 00:17:45,370 data for us to work with, all sorts of different data. 469 00:17:45,370 --> 00:17:47,220 So that's why I like to install it. 470 00:17:47,220 --> 00:17:50,220 And any time I want to test out something new, rather than 471 00:17:50,220 --> 00:17:52,360 create a new database, create a new table structure and all 472 00:17:52,360 --> 00:17:54,600 that, I just like to pop over to AdventureWorks, run some 473 00:17:54,600 --> 00:17:57,350 tests out, just maybe get familiar with the new features 474 00:17:57,350 --> 00:18:00,960 or new statements in TSQL, that kind of stuff. 475 00:18:00,960 --> 00:18:03,430 So I definitely love using AdventureWorks. 476 00:18:03,430 --> 00:18:05,760 I use it all the time to do that kind of stuff. 477 00:18:05,760 --> 00:18:07,150 Now, I'll also show you where we can download 478 00:18:07,150 --> 00:18:07,920 this when we get there. 479 00:18:07,920 --> 00:18:11,240 But first, let's install SQL Server 2012 Express Edition. 480 00:18:11,240 --> 00:18:13,100 I'm just going to double click on this. 481 00:18:13,100 --> 00:18:16,980 It's going to unpack the files for us here, and then 482 00:18:16,980 --> 00:18:20,770 hopefully launch the installation manager here. 483 00:18:20,770 --> 00:18:21,880 So we'll just give this a second. 484 00:18:21,880 --> 00:18:22,920 I'm actually just going to pause this 485 00:18:22,920 --> 00:18:23,930 until it's done unpacking. 486 00:18:23,930 --> 00:18:25,580 So I'll be right back. 487 00:18:25,580 --> 00:18:28,660 All right, we're all done here unpacking the SQL Server 488 00:18:28,660 --> 00:18:29,360 installation. 489 00:18:29,360 --> 00:18:31,450 So I'm just going to maximize our screen. 490 00:18:31,450 --> 00:18:33,840 And on our installation screen here, we can install a 491 00:18:33,840 --> 00:18:34,770 standalone edition. 492 00:18:34,770 --> 00:18:36,900 We can upgrade from an existing version 493 00:18:36,900 --> 00:18:37,710 of SQL Server here. 494 00:18:37,710 --> 00:18:40,660 We're just going to install the brand new installation. 495 00:18:40,660 --> 00:18:42,640 It's going to unpack everything once again, just 496 00:18:42,640 --> 00:18:45,590 prepare SQL Server 2012 for install. 497 00:18:45,590 --> 00:18:48,650 And shortly after this here, it'll fire up our 498 00:18:48,650 --> 00:18:50,260 installation wizard. 499 00:18:50,260 --> 00:18:50,790 So here it is. 500 00:18:50,790 --> 00:18:52,040 And the first thing it needs to do is just go through the 501 00:18:52,040 --> 00:18:54,610 set up, install the set up files here. 502 00:18:54,610 --> 00:18:56,490 I'm just going to minimize this screen for now so it's 503 00:18:56,490 --> 00:18:58,670 not in the way. 504 00:18:58,670 --> 00:18:59,940 And it's also going to check for updates. 505 00:18:59,940 --> 00:19:02,640 So actually, the first thing it did there is just get the 506 00:19:02,640 --> 00:19:04,240 support files going. 507 00:19:04,240 --> 00:19:07,280 Now, it's going to check for product updates, which it did. 508 00:19:07,280 --> 00:19:09,060 Nothing out there, brand new. 509 00:19:09,060 --> 00:19:09,910 We'd hope not. 510 00:19:09,910 --> 00:19:13,070 So let's hit Next. 511 00:19:13,070 --> 00:19:14,780 Now, it's just going to install the set up files. 512 00:19:14,780 --> 00:19:16,380 So I'll just give this a second while it installs the 513 00:19:16,380 --> 00:19:18,110 setup files and be right back. 514 00:19:18,110 --> 00:19:20,950 All right, so it's all set here with the support files. 515 00:19:20,950 --> 00:19:23,670 It went through all that and skipped to this screen, which 516 00:19:23,670 --> 00:19:27,330 just goes through and checks out all the rules against it. 517 00:19:27,330 --> 00:19:30,150 So we pass all the rules here except one, Windows Firewall 518 00:19:30,150 --> 00:19:30,860 is enabled. 519 00:19:30,860 --> 00:19:32,130 So it's just going to let you know to make sure the 520 00:19:32,130 --> 00:19:35,570 appropriate ports are open to enable remote access. 521 00:19:35,570 --> 00:19:38,310 So once this is done here, we can hit Next. 522 00:19:38,310 --> 00:19:39,710 We'll go through the license term. 523 00:19:39,710 --> 00:19:40,840 We can accept the terms. 524 00:19:40,840 --> 00:19:45,470 You can optionally here send usage data to Microsoft. 525 00:19:45,470 --> 00:19:47,000 Hit Next. 526 00:19:47,000 --> 00:19:49,560 And now comes the feature selection. 527 00:19:49,560 --> 00:19:50,200 And here it is. 528 00:19:50,200 --> 00:19:52,600 Here, we're going to install the instance features here, 529 00:19:52,600 --> 00:19:55,460 the database engine, replication, full text, 530 00:19:55,460 --> 00:19:58,500 reporting services, native mode, the data tools, 531 00:19:58,500 --> 00:20:02,400 documentation components, management tools, the SDK here 532 00:20:02,400 --> 00:20:04,770 for client connectivity, and here's the Local 533 00:20:04,770 --> 00:20:06,060 DB option as well. 534 00:20:06,060 --> 00:20:07,310 So we're going to leave all these checked. 535 00:20:07,310 --> 00:20:09,000 This is what the default is, which is pretty much 536 00:20:09,000 --> 00:20:10,130 everything. 537 00:20:10,130 --> 00:20:12,130 And here's our default installation paths. 538 00:20:12,130 --> 00:20:13,380 And we'll hit Next. 539 00:20:13,380 --> 00:20:16,500 540 00:20:16,500 --> 00:20:18,690 Now, the next area here is going to be our instance 541 00:20:18,690 --> 00:20:20,300 configuration. 542 00:20:20,300 --> 00:20:21,720 And let's just call this here-- 543 00:20:21,720 --> 00:20:23,130 we're going to do a named instance. 544 00:20:23,130 --> 00:20:27,590 We'll call this SQL 2012 XP. 545 00:20:27,590 --> 00:20:28,770 So that'll be the instance we work with here. 546 00:20:28,770 --> 00:20:31,260 When we tab off that field, that's what it'll give our 547 00:20:31,260 --> 00:20:32,550 instance ID. 548 00:20:32,550 --> 00:20:35,020 And then, it'll also scan here if there's any previous 549 00:20:35,020 --> 00:20:36,200 installations, which there aren't. 550 00:20:36,200 --> 00:20:38,700 If you do have previous installations, it'll tell you 551 00:20:38,700 --> 00:20:40,850 what the name is, what the features are, the edition, the 552 00:20:40,850 --> 00:20:42,480 version, all that good stuff. 553 00:20:42,480 --> 00:20:43,050 We're good here. 554 00:20:43,050 --> 00:20:45,510 So we'll hit Next once again. 555 00:20:45,510 --> 00:20:47,410 And now, the disk space requirements. 556 00:20:47,410 --> 00:20:50,830 We should have plenty of that on this machine, as we do. 557 00:20:50,830 --> 00:20:52,800 So it just gives us a little overview here of the disk 558 00:20:52,800 --> 00:20:54,910 usage summary and where things are going. 559 00:20:54,910 --> 00:20:57,400 So if we hit Next, we'll go to Server Configuration. 560 00:20:57,400 --> 00:20:59,660 We can set up our service accounts for the database 561 00:20:59,660 --> 00:21:03,790 engine, reporting services for the full text launcher here, 562 00:21:03,790 --> 00:21:05,670 and then for SQL Server browser. 563 00:21:05,670 --> 00:21:07,020 So we can find this on the network. 564 00:21:07,020 --> 00:21:09,120 So we'll leave all the defaults on those. 565 00:21:09,120 --> 00:21:10,985 You can also set up your collation here. 566 00:21:10,985 --> 00:21:12,515 We're just going to hit Next. 567 00:21:12,515 --> 00:21:14,600 You can pretty much leave all those at the default here for 568 00:21:14,600 --> 00:21:16,270 our test environment. 569 00:21:16,270 --> 00:21:18,070 And then comes time to configure the 570 00:21:18,070 --> 00:21:20,490 database engine itself. 571 00:21:20,490 --> 00:21:22,270 So we can choose a server configuration. 572 00:21:22,270 --> 00:21:23,850 What type of authentication do we want to do? 573 00:21:23,850 --> 00:21:26,800 We can do Windows authentication or mixed mode, 574 00:21:26,800 --> 00:21:29,580 where we do both SQL Server and Windows authentication. 575 00:21:29,580 --> 00:21:31,580 We'll just keep this simple for now and stick with Windows 576 00:21:31,580 --> 00:21:32,850 authentication. 577 00:21:32,850 --> 00:21:36,410 And here is the SQL Server administrator, which is me, my 578 00:21:36,410 --> 00:21:37,420 account here. 579 00:21:37,420 --> 00:21:40,130 And the name of this box, by the way, on our Nugget Lab 580 00:21:40,130 --> 00:21:42,280 server, is actually SQL Nugget. 581 00:21:42,280 --> 00:21:43,280 I changed the name to SQL Nugget. 582 00:21:43,280 --> 00:21:45,550 So that's going to be the name of the server here. 583 00:21:45,550 --> 00:21:46,720 And then, you can do some other things here. 584 00:21:46,720 --> 00:21:49,100 You can specify your data directory. 585 00:21:49,100 --> 00:21:52,680 So here are all the data directories here for the root, 586 00:21:52,680 --> 00:21:53,620 data root directory. 587 00:21:53,620 --> 00:21:55,780 We've got the database directory, the log directory, 588 00:21:55,780 --> 00:21:58,000 the temp database directory, temp log, and 589 00:21:58,000 --> 00:21:58,800 then our backup directory. 590 00:21:58,800 --> 00:22:01,240 We'll leave all the defaults there. 591 00:22:01,240 --> 00:22:03,630 You can configure user instances 592 00:22:03,630 --> 00:22:05,230 and also files streams. 593 00:22:05,230 --> 00:22:07,100 So we'll leave all that the way it is here. 594 00:22:07,100 --> 00:22:07,920 Everything looks good. 595 00:22:07,920 --> 00:22:09,420 Let's hit Next. 596 00:22:09,420 --> 00:22:12,336 And now, for reporting services, since really we 597 00:22:12,336 --> 00:22:14,890 don't have anything to do with reporting services in this 598 00:22:14,890 --> 00:22:16,830 Nugget, I'd just like to get it installed again in case you 599 00:22:16,830 --> 00:22:19,980 want to venture outside of this Nugget and play with it. 600 00:22:19,980 --> 00:22:21,440 We will just choose Install Only. 601 00:22:21,440 --> 00:22:23,140 We will not configure it here in the set up. 602 00:22:23,140 --> 00:22:25,470 We'll choose Next. 603 00:22:25,470 --> 00:22:27,020 Then, you have some error reporting here. 604 00:22:27,020 --> 00:22:31,270 If you want to send errors to Microsoft, feel free. 605 00:22:31,270 --> 00:22:33,910 And finally here, it's just going to go through the 606 00:22:33,910 --> 00:22:35,730 installation configuration rules 607 00:22:35,730 --> 00:22:36,760 followed by the progress. 608 00:22:36,760 --> 00:22:38,440 So this is going to take a little bit to install SQL 609 00:22:38,440 --> 00:22:40,200 Server 2012. 610 00:22:40,200 --> 00:22:41,380 I'm going to let it go. 611 00:22:41,380 --> 00:22:43,130 I'll be back when it's done. 612 00:22:43,130 --> 00:22:45,450 And we're back once again here. 613 00:22:45,450 --> 00:22:47,620 The last screen of the set up just gives us an overview of 614 00:22:47,620 --> 00:22:48,630 everything that was installed. 615 00:22:48,630 --> 00:22:50,230 So we've got the management tools installed. 616 00:22:50,230 --> 00:22:53,000 This is SQL Server Management tools and some extra database 617 00:22:53,000 --> 00:22:55,110 tools like the profiler and stuff. 618 00:22:55,110 --> 00:22:58,740 SQL Server data tools is actually Business Intelligence 619 00:22:58,740 --> 00:23:01,070 Development Studio, otherwise known as BIDS. 620 00:23:01,070 --> 00:23:04,070 It's really just Visual Studio for SQL Server development. 621 00:23:04,070 --> 00:23:07,230 So you can build things like reports and reporting 622 00:23:07,230 --> 00:23:10,410 services, integration services packages, 623 00:23:10,410 --> 00:23:11,380 things of that nature. 624 00:23:11,380 --> 00:23:13,140 We've got the database engine services. 625 00:23:13,140 --> 00:23:16,190 This is just the core SQL Server engine service. 626 00:23:16,190 --> 00:23:17,590 Full text was installed. 627 00:23:17,590 --> 00:23:20,290 Replication, reporting services in native mode. 628 00:23:20,290 --> 00:23:23,810 Scroll down a little bit, here we have SQL browser 629 00:23:23,810 --> 00:23:25,100 documentation components. 630 00:23:25,100 --> 00:23:28,550 So one thing I do want to show you about documentation is 631 00:23:28,550 --> 00:23:30,260 it's not installed by default. 632 00:23:30,260 --> 00:23:34,530 It actually is the help software that points to the 633 00:23:34,530 --> 00:23:35,560 online version. 634 00:23:35,560 --> 00:23:38,390 So I'm going to show you how you can install help because 635 00:23:38,390 --> 00:23:40,750 it'll speed things up here, especially because we're going 636 00:23:40,750 --> 00:23:41,760 to be using it. 637 00:23:41,760 --> 00:23:44,000 Show you some cool things you can do so you can learn any 638 00:23:44,000 --> 00:23:45,870 statement on your own, or learn the 639 00:23:45,870 --> 00:23:46,890 syntax of the statement. 640 00:23:46,890 --> 00:23:48,960 So it's a good idea to get books online. 641 00:23:48,960 --> 00:23:52,320 There's documentation for SQL Server installed locally. 642 00:23:52,320 --> 00:23:54,270 We've got SQL Writer connectivity components and 643 00:23:54,270 --> 00:23:55,480 the SDK also installed. 644 00:23:55,480 --> 00:23:56,870 So we're good to go on this. 645 00:23:56,870 --> 00:23:59,630 Let's go ahead and close out of this. 646 00:23:59,630 --> 00:24:01,340 Let's take a look at what the installation did. 647 00:24:01,340 --> 00:24:02,280 So let's head down to the Start 648 00:24:02,280 --> 00:24:04,090 menu, up to All Programs. 649 00:24:04,090 --> 00:24:08,670 Here we have a SQL Server 2012 program area here. 650 00:24:08,670 --> 00:24:10,450 So here's all the components that were installed. 651 00:24:10,450 --> 00:24:13,310 And we have a Visual Studio 2010 area, where you can see 652 00:24:13,310 --> 00:24:15,570 Visual Studio 2010 was installed. 653 00:24:15,570 --> 00:24:17,370 So going through some of the SQL Server stuff, you've got 654 00:24:17,370 --> 00:24:21,230 the Import and Export Wizard for 32 and 64-bit. 655 00:24:21,230 --> 00:24:23,770 We've got SQL Server data tools. 656 00:24:23,770 --> 00:24:27,500 This is Business Intelligence Development Studio renamed, 657 00:24:27,500 --> 00:24:28,980 and thankfully they did it because that's 658 00:24:28,980 --> 00:24:30,370 a mouthful to say. 659 00:24:30,370 --> 00:24:31,380 And so I'm happy. 660 00:24:31,380 --> 00:24:32,300 It makes a lot more sense. 661 00:24:32,300 --> 00:24:35,140 SQL Server Data Tools, it sounds a lot better, looks a 662 00:24:35,140 --> 00:24:35,890 lot better. 663 00:24:35,890 --> 00:24:37,650 And you'll notice that the icons are the same here. 664 00:24:37,650 --> 00:24:41,120 That's because really what it is is it's Visual Studio 665 00:24:41,120 --> 00:24:44,230 geared towards SQL Server and the DBAs and DBDs of the 666 00:24:44,230 --> 00:24:47,090 world, and has all the projects that we can work with 667 00:24:47,090 --> 00:24:50,250 to do things like create reporting services, report 668 00:24:50,250 --> 00:24:53,220 projects, or create integration services projects. 669 00:24:53,220 --> 00:24:54,780 So that's that. 670 00:24:54,780 --> 00:24:56,820 We also have SQL Server Management Studio, which is 671 00:24:56,820 --> 00:24:58,430 what we'll be spending most of our time in. 672 00:24:58,430 --> 00:25:01,540 We'll come in there shortly to configure the sample 673 00:25:01,540 --> 00:25:02,850 AdventureWorks database. 674 00:25:02,850 --> 00:25:04,380 We have configuration tools here. 675 00:25:04,380 --> 00:25:06,240 If you want to configure reporting services, this is 676 00:25:06,240 --> 00:25:07,200 where you would do it. 677 00:25:07,200 --> 00:25:10,260 If you want to configure SQL Servers as far as service 678 00:25:10,260 --> 00:25:12,480 configuration, network configuration, client 679 00:25:12,480 --> 00:25:15,360 connectivity, all that kind of stuff is inside of the 680 00:25:15,360 --> 00:25:16,840 Configuration Manager. 681 00:25:16,840 --> 00:25:18,830 Error and usage reporting, which we touched on inside of 682 00:25:18,830 --> 00:25:21,280 the installation, and then the installation center, which we 683 00:25:21,280 --> 00:25:23,380 were just in, to install SQL Server. 684 00:25:23,380 --> 00:25:25,480 So those are the configuration tools. 685 00:25:25,480 --> 00:25:28,150 We have the documentation and community here, which we're 686 00:25:28,150 --> 00:25:31,050 going to come back to in a second to get books online, 687 00:25:31,050 --> 00:25:31,850 SQL Server's help 688 00:25:31,850 --> 00:25:34,370 documentation, installed locally. 689 00:25:34,370 --> 00:25:36,210 And finally, you have a bunch of tools for integration 690 00:25:36,210 --> 00:25:38,265 services in here like the data profile viewer deployment 691 00:25:38,265 --> 00:25:41,010 wizard, execute package utility, and the project 692 00:25:41,010 --> 00:25:42,290 conversion wizard. 693 00:25:42,290 --> 00:25:43,320 So let's install the 694 00:25:43,320 --> 00:25:44,780 documentation here, books online. 695 00:25:44,780 --> 00:25:47,210 And what we can do is hit this Manage Help 696 00:25:47,210 --> 00:25:49,980 Settings menu item here. 697 00:25:49,980 --> 00:25:51,960 And this is going to take us to the help library manager. 698 00:25:51,960 --> 00:25:53,920 This is Microsoft's Help Viewer 1.0. 699 00:25:53,920 --> 00:25:55,290 This is really cool stuff. 700 00:25:55,290 --> 00:25:56,960 I actually really like this because it's kind of a 701 00:25:56,960 --> 00:26:01,310 simplified, centralized way to get help installed not just 702 00:26:01,310 --> 00:26:02,720 for SQL Server, but anything that's 703 00:26:02,720 --> 00:26:04,330 installed on your system. 704 00:26:04,330 --> 00:26:05,730 So I'm a big fan of this. 705 00:26:05,730 --> 00:26:07,370 And here's how it works. 706 00:26:07,370 --> 00:26:09,480 Let's install content from online. 707 00:26:09,480 --> 00:26:12,045 And it's just going to scan, see what we have installed on 708 00:26:12,045 --> 00:26:14,780 our system, compare it with what's available online for 709 00:26:14,780 --> 00:26:18,400 the Help Viewer 1.0 and give us everything. 710 00:26:18,400 --> 00:26:21,780 So not only will SQL Server documentation be in here, but 711 00:26:21,780 --> 00:26:23,400 a lot of things like .NET development. 712 00:26:23,400 --> 00:26:25,790 If you want to do any .NET development, read the 713 00:26:25,790 --> 00:26:28,950 documentation on that, you can hit Add here, update pending, 714 00:26:28,950 --> 00:26:30,720 and it shows just the size. 715 00:26:30,720 --> 00:26:31,650 So we're not going to do that. 716 00:26:31,650 --> 00:26:33,840 But you can if you want to. 717 00:26:33,840 --> 00:26:37,230 You've got BizTalk Server 2010 for Office, patterns and 718 00:26:37,230 --> 00:26:38,710 practices, all very good stuff. 719 00:26:38,710 --> 00:26:41,000 Highly recommended if you're ever going to get into 720 00:26:41,000 --> 00:26:42,060 development. 721 00:26:42,060 --> 00:26:44,980 SharePoint information, here's SQL Server 2012. 722 00:26:44,980 --> 00:26:46,440 So we want books online. 723 00:26:46,440 --> 00:26:49,020 And again here, I just like to install everything so it's all 724 00:26:49,020 --> 00:26:52,950 on here in case you want to play and get out of your 725 00:26:52,950 --> 00:26:54,970 comfort zone or out of this Nugget series. 726 00:26:54,970 --> 00:26:56,090 So I'm going to add the developer 727 00:26:56,090 --> 00:26:57,630 reference because of that. 728 00:26:57,630 --> 00:26:58,860 And also, the installation. 729 00:26:58,860 --> 00:27:01,410 So we've got all the documentation for SQL Server 730 00:27:01,410 --> 00:27:03,050 2012 locally. 731 00:27:03,050 --> 00:27:04,760 Here are the sizes over on the right. 732 00:27:04,760 --> 00:27:07,220 And then, you've got some other things in here. 733 00:27:07,220 --> 00:27:10,390 So all the Visual Studio documentation you can install, 734 00:27:10,390 --> 00:27:13,460 Web Developer, Windows is here, the development driver 735 00:27:13,460 --> 00:27:15,710 kit phone development, and the XNA Game Studio. 736 00:27:15,710 --> 00:27:17,190 So we're good, though. 737 00:27:17,190 --> 00:27:20,840 We've got everything in SQL Server 2012 checked and ready. 738 00:27:20,840 --> 00:27:21,460 Update pending. 739 00:27:21,460 --> 00:27:22,870 So let's hit Update. 740 00:27:22,870 --> 00:27:24,390 I'm going to put you on hold one more time while this 741 00:27:24,390 --> 00:27:26,335 downloads from the internet and installs it locally. 742 00:27:26,335 --> 00:27:27,740 Be right back. 743 00:27:27,740 --> 00:27:29,500 All right, installation is complete. 744 00:27:29,500 --> 00:27:31,890 Once you hit Finish, it'll take you back to Help Library 745 00:27:31,890 --> 00:27:33,120 Manager screen here. 746 00:27:33,120 --> 00:27:35,710 And you can just double check through choosing online or 747 00:27:35,710 --> 00:27:37,230 local help that we're using local help. 748 00:27:37,230 --> 00:27:37,810 And we are. 749 00:27:37,810 --> 00:27:39,450 So we'll cancel out of this. 750 00:27:39,450 --> 00:27:41,290 Let's exit out of the library manager and just take a quick 751 00:27:41,290 --> 00:27:42,720 look at the documentation. 752 00:27:42,720 --> 00:27:46,785 So go to Start, All Programs, SQL Server 2012, Documentation 753 00:27:46,785 --> 00:27:47,900 and Community. 754 00:27:47,900 --> 00:27:49,670 And the bottom one inside of here, SQL Server 755 00:27:49,670 --> 00:27:52,390 Documentation, will launch the help viewer. 756 00:27:52,390 --> 00:27:53,100 And check it out. 757 00:27:53,100 --> 00:27:55,330 All three that we chose to install are here. 758 00:27:55,330 --> 00:27:57,650 We have the books online, which is the main instruction 759 00:27:57,650 --> 00:28:00,260 manual for all things SQL Server 2012. 760 00:28:00,260 --> 00:28:02,100 We have the developer reference for all the 761 00:28:02,100 --> 00:28:04,850 different tools and components, including the 762 00:28:04,850 --> 00:28:05,930 database engine. 763 00:28:05,930 --> 00:28:08,060 And then, we have the installation 764 00:28:08,060 --> 00:28:08,970 guide as well here. 765 00:28:08,970 --> 00:28:10,570 So all three areas are here. 766 00:28:10,570 --> 00:28:12,540 We're not going to be specifically coming into books 767 00:28:12,540 --> 00:28:13,660 online like I am here. 768 00:28:13,660 --> 00:28:16,290 We're going to use it in a context sensitive manner. 769 00:28:16,290 --> 00:28:19,460 And what that basically means is when we're in Transact SQL 770 00:28:19,460 --> 00:28:21,610 and there's a statement, pretty much anything that's 771 00:28:21,610 --> 00:28:24,190 highlighted in blue or pink, or anything that's a SQL 772 00:28:24,190 --> 00:28:26,670 Server keyword, if you highlight it are double click 773 00:28:26,670 --> 00:28:30,430 on it and hit Shift F1 on your keyboard, it'll take you right 774 00:28:30,430 --> 00:28:34,760 to that area inside of the documentation and give us help 775 00:28:34,760 --> 00:28:36,500 for that specific statement or keyword. 776 00:28:36,500 --> 00:28:39,100 Things like syntax, what all the different parameters are, 777 00:28:39,100 --> 00:28:41,000 describe them, and give us an example of how to use it. 778 00:28:41,000 --> 00:28:42,020 So it's awesome. 779 00:28:42,020 --> 00:28:42,830 I use it all the time. 780 00:28:42,830 --> 00:28:43,560 I love it. 781 00:28:43,560 --> 00:28:44,910 And it'll save you a ton of time. 782 00:28:44,910 --> 00:28:47,430 And it's a great way to learn a lot of the different 783 00:28:47,430 --> 00:28:49,590 language constructs inside of Transact SQL. 784 00:28:49,590 --> 00:28:50,790 So we'll get there in future Nuggets. 785 00:28:50,790 --> 00:28:54,460 We'll use it extensively and hopefully learn a lot. 786 00:28:54,460 --> 00:28:57,230 So let's close out of help. 787 00:28:57,230 --> 00:28:59,990 The last thing I want to show you here is how to install the 788 00:28:59,990 --> 00:29:02,940 AdventureWorks sample database. 789 00:29:02,940 --> 00:29:05,280 But first, I need to show you where to find it. 790 00:29:05,280 --> 00:29:07,480 So let's hit the Start button one more time here. 791 00:29:07,480 --> 00:29:08,800 And boy, this Start button's getting a work 792 00:29:08,800 --> 00:29:09,600 out tonight, huh? 793 00:29:09,600 --> 00:29:12,670 And let's hit the Internet Explorer here. 794 00:29:12,670 --> 00:29:14,250 Go to Google. 795 00:29:14,250 --> 00:29:16,690 And once we're at the Google, let's type in 796 00:29:16,690 --> 00:29:20,400 AdventureWorks 2012. 797 00:29:20,400 --> 00:29:23,010 All right, and the first link that comes up is good. 798 00:29:23,010 --> 00:29:25,090 This will take us right to the database product 799 00:29:25,090 --> 00:29:27,120 samples page here. 800 00:29:27,120 --> 00:29:28,840 And there's going to be a lot to choose from down here. 801 00:29:28,840 --> 00:29:30,330 There's things for data warehousing, 802 00:29:30,330 --> 00:29:31,640 multidimensional models. 803 00:29:31,640 --> 00:29:34,680 There's online transaction processing scripts down below. 804 00:29:34,680 --> 00:29:36,370 But really what we're concerned with here is the 805 00:29:36,370 --> 00:29:38,450 recommended download, the big one here, 806 00:29:38,450 --> 00:29:40,960 AdventureWorks 2012 data file. 807 00:29:40,960 --> 00:29:45,550 This will give us the .mdf, the primary data file, which 808 00:29:45,550 --> 00:29:46,590 is all we need. 809 00:29:46,590 --> 00:29:47,970 All right, so I already have this downloaded. 810 00:29:47,970 --> 00:29:50,030 So I'm going to close out of the browser here, go back to 811 00:29:50,030 --> 00:29:51,310 the file system. 812 00:29:51,310 --> 00:29:53,650 And again, it's in the C drive here. 813 00:29:53,650 --> 00:29:57,560 It's in our 70-461 support files. 814 00:29:57,560 --> 00:30:00,120 And there it is, AdventureWorks 2012_data.mdf. 815 00:30:00,120 --> 00:30:02,595 So that's what we're going to attach to. 816 00:30:02,595 --> 00:30:04,090 Let's minimize this. 817 00:30:04,090 --> 00:30:06,960 And let's launch SQL Server 2012 Management Studio. 818 00:30:06,960 --> 00:30:10,750 So let's go to Start, All Programs, SQL Server 2012. 819 00:30:10,750 --> 00:30:14,400 Let's launch the Management Studio. 820 00:30:14,400 --> 00:30:16,130 And you'll notice that it's going to look a little 821 00:30:16,130 --> 00:30:18,110 different than it used to. 822 00:30:18,110 --> 00:30:20,810 They used better colors in my opinion here. 823 00:30:20,810 --> 00:30:24,080 And the whole point to this was to make it less 824 00:30:24,080 --> 00:30:25,320 distracting. 825 00:30:25,320 --> 00:30:27,530 So just muted colors. 826 00:30:27,530 --> 00:30:28,320 And I like it. 827 00:30:28,320 --> 00:30:31,550 I've been working with Visual Studio 2010 for a while now, 828 00:30:31,550 --> 00:30:32,080 for a few years. 829 00:30:32,080 --> 00:30:34,800 And it's got the same kind of interface 830 00:30:34,800 --> 00:30:35,690 with the same colors. 831 00:30:35,690 --> 00:30:36,210 And it's awesome. 832 00:30:36,210 --> 00:30:38,030 It's really cool, professional, slick looking 833 00:30:38,030 --> 00:30:38,650 and all that. 834 00:30:38,650 --> 00:30:40,090 So kind of neat. 835 00:30:40,090 --> 00:30:41,740 So let's connect to the instance we installed here, 836 00:30:41,740 --> 00:30:45,570 SQL Nugget, backslash SQL 2012 XP. 837 00:30:45,570 --> 00:30:47,080 There it is. 838 00:30:47,080 --> 00:30:49,250 And if we expand the databases node, we have nothing in there 839 00:30:49,250 --> 00:30:50,330 but system databases. 840 00:30:50,330 --> 00:30:53,560 So let's right click on that databases node, hit the attach 841 00:30:53,560 --> 00:30:56,680 button, hit the Add button here to 842 00:30:56,680 --> 00:30:59,040 specify an MDF file location. 843 00:30:59,040 --> 00:31:02,940 So if we hit Add, we're going to point right to our MDF file 844 00:31:02,940 --> 00:31:05,410 in our support files directory. 845 00:31:05,410 --> 00:31:07,510 And let's just close out of this. 846 00:31:07,510 --> 00:31:10,050 You'll of course get prompted to make the experience better. 847 00:31:10,050 --> 00:31:11,110 And we'll just hit OK here. 848 00:31:11,110 --> 00:31:14,140 And this will just tell us where it is, what the database 849 00:31:14,140 --> 00:31:16,170 name is, how we're going to attach it as 850 00:31:16,170 --> 00:31:17,440 the database name. 851 00:31:17,440 --> 00:31:19,200 And again, you have full control over this stuff. 852 00:31:19,200 --> 00:31:21,180 So if you don't like AdventureWorks 2012, 853 00:31:21,180 --> 00:31:22,450 you can change it. 854 00:31:22,450 --> 00:31:24,800 Who's the owner, and just some extra stats on it there. 855 00:31:24,800 --> 00:31:27,480 And then, it's telling us there's no log file found. 856 00:31:27,480 --> 00:31:31,370 And it defaults to what our installation 857 00:31:31,370 --> 00:31:32,830 log directory was. 858 00:31:32,830 --> 00:31:34,410 So what you want to do here, you'll get an error if you 859 00:31:34,410 --> 00:31:36,970 leave it as it is because it won't be able to 860 00:31:36,970 --> 00:31:38,100 find the log file. 861 00:31:38,100 --> 00:31:39,000 So here's what I do-- 862 00:31:39,000 --> 00:31:39,440 remove it. 863 00:31:39,440 --> 00:31:40,840 Get it out of there. 864 00:31:40,840 --> 00:31:43,100 Now, it'll create a log file on the fly in the same 865 00:31:43,100 --> 00:31:45,050 location as our primary data file. 866 00:31:45,050 --> 00:31:46,920 So that's the only trick to doing this. 867 00:31:46,920 --> 00:31:50,310 So once we hit OK, it's going to bring it in. 868 00:31:50,310 --> 00:31:50,760 And there it is. 869 00:31:50,760 --> 00:31:53,480 Here is the AdventureWorks 2012 database. 870 00:31:53,480 --> 00:31:55,970 All the tables that we'll be working with, here is stored 871 00:31:55,970 --> 00:32:00,690 procedures, in here as well, views, you name it, we'll work 872 00:32:00,690 --> 00:32:02,450 with it inside of here. 873 00:32:02,450 --> 00:32:04,890 And of course, we'll make our own databases and work a lot 874 00:32:04,890 --> 00:32:06,400 on our own stuff, as well. 875 00:32:06,400 --> 00:32:08,790 But for some of the advanced things, there's already some 876 00:32:08,790 --> 00:32:11,470 good data in here, some good examples that we're 877 00:32:11,470 --> 00:32:12,080 going to use here. 878 00:32:12,080 --> 00:32:14,430 So we've got it up and running. 879 00:32:14,430 --> 00:32:16,360 SQL Server is also installed and up and running. 880 00:32:16,360 --> 00:32:17,800 So everything looks good. 881 00:32:17,800 --> 00:32:19,540 There's just one more thing I want to show you, always one 882 00:32:19,540 --> 00:32:20,210 more thing. 883 00:32:20,210 --> 00:32:22,680 And this might be the developer coming out in me, 884 00:32:22,680 --> 00:32:25,740 but this'll definitely help you stay organized within 885 00:32:25,740 --> 00:32:26,700 Management Studio. 886 00:32:26,700 --> 00:32:27,720 This is really cool stuff. 887 00:32:27,720 --> 00:32:28,780 I love doing this. 888 00:32:28,780 --> 00:32:31,690 If you go to the File menu, New, Project. 889 00:32:31,690 --> 00:32:32,540 And you may already do this. 890 00:32:32,540 --> 00:32:34,840 But if not, this'll definitely save you some 891 00:32:34,840 --> 00:32:35,800 time in the long run. 892 00:32:35,800 --> 00:32:38,450 And you can also kind of build up your library 893 00:32:38,450 --> 00:32:40,410 of queries and projects. 894 00:32:40,410 --> 00:32:43,000 And everything that you work, eventually you can just keep 895 00:32:43,000 --> 00:32:44,340 evolving over time. 896 00:32:44,340 --> 00:32:48,080 You have a nice spot for all your scripts, everything. 897 00:32:48,080 --> 00:32:53,290 Right, so let's name this solution 70-461. 898 00:32:53,290 --> 00:32:58,780 We'll just call this Querying SQL 2012. 899 00:32:58,780 --> 00:33:00,610 And then, the name here is going to be the project. 900 00:33:00,610 --> 00:33:03,610 So basically what you have here, you have a solution 901 00:33:03,610 --> 00:33:06,530 which is just a container for all the projects. 902 00:33:06,530 --> 00:33:09,710 And for now, we'll just go ahead and call this 903 00:33:09,710 --> 00:33:16,470 01-Creating_Tables, which is going to be the first Nugget 904 00:33:16,470 --> 00:33:17,140 in the series. 905 00:33:17,140 --> 00:33:19,210 And we may come back and change this. 906 00:33:19,210 --> 00:33:21,500 But that's what I'll end up doing just to show you the 907 00:33:21,500 --> 00:33:23,210 structure and the naming convention I'm using. 908 00:33:23,210 --> 00:33:25,570 If we hit OK here, that's going to create a 909 00:33:25,570 --> 00:33:27,030 solution for us. 910 00:33:27,030 --> 00:33:29,200 And over here on the right hand side, if it doesn't show 911 00:33:29,200 --> 00:33:32,970 up, what you can do is go to View, Solution Explorer. 912 00:33:32,970 --> 00:33:35,120 And this is what we want to see. 913 00:33:35,120 --> 00:33:37,740 So in here, we have the name of our solution. 914 00:33:37,740 --> 00:33:40,660 Again, just a container here for all of the project files. 915 00:33:40,660 --> 00:33:43,467 And then, what we'll do is every Nugget when we create a 916 00:33:43,467 --> 00:33:45,990 demo, we will create a new project. 917 00:33:45,990 --> 00:33:47,570 And we'll put all our queries in here. 918 00:33:47,570 --> 00:33:49,490 We can have connections inside of here, 919 00:33:49,490 --> 00:33:50,110 which is kind of neat. 920 00:33:50,110 --> 00:33:52,090 Because if you have lots of databases you're connect to 921 00:33:52,090 --> 00:33:54,740 and running samples scripts against, whether it's a dev 922 00:33:54,740 --> 00:33:56,680 database or a production database, you can point it to 923 00:33:56,680 --> 00:33:57,450 a connection. 924 00:33:57,450 --> 00:33:59,670 So not only can you manage your connections, you can 925 00:33:59,670 --> 00:34:01,010 manage your queries. 926 00:34:01,010 --> 00:34:02,980 And then, you can put scripts inside of miscellaneous. 927 00:34:02,980 --> 00:34:05,430 So just a cool little project to help you stay organized. 928 00:34:05,430 --> 00:34:07,830 And this is really nice in this Nugget series because 929 00:34:07,830 --> 00:34:10,860 again, we'll have a project for every one. 930 00:34:10,860 --> 00:34:13,320 So eventually when we're done, then, the solution will be 931 00:34:13,320 --> 00:34:15,639 filled with all of our projects, all the demos. 932 00:34:15,639 --> 00:34:18,000 So you'll have a nice, centralized place for 933 00:34:18,000 --> 00:34:22,150 everything, Querying SQL Server 2012. 934 00:34:22,150 --> 00:34:24,830 So we are officially ready to rock. 935 00:34:24,830 --> 00:34:26,550 All right, so in this CBT Nugget, we took a look at an 936 00:34:26,550 --> 00:34:29,570 introduction to 70-461, Querying 937 00:34:29,570 --> 00:34:31,949 Microsoft SQL Server 2012. 938 00:34:31,949 --> 00:34:34,009 We started off with a look at the exam objectives, went over 939 00:34:34,009 --> 00:34:37,239 to Microsoft's website, took a look at what's to be expected 940 00:34:37,239 --> 00:34:39,699 on the exam, all the objectives we should be 941 00:34:39,699 --> 00:34:40,850 familiar with. 942 00:34:40,850 --> 00:34:42,739 And then, we came back here and looked at the series 943 00:34:42,739 --> 00:34:46,980 outline for this Nugget series and saw that it's very close 944 00:34:46,980 --> 00:34:48,070 to what the objectives are. 945 00:34:48,070 --> 00:34:51,320 And we did that by design here so you could easily find what 946 00:34:51,320 --> 00:34:52,793 you're looking for if you need to freshen up 947 00:34:52,793 --> 00:34:53,960 on a specific topic. 948 00:34:53,960 --> 00:34:56,120 And then, we jumped in and learned how to install SQL 949 00:34:56,120 --> 00:34:57,530 Server 2012 Express. 950 00:34:57,530 --> 00:35:00,040 We looked at all the editions, we got an installation going, 951 00:35:00,040 --> 00:35:02,800 we installed the help documentation locally. 952 00:35:02,800 --> 00:35:06,230 And we even got the sample AdventureWorks database 953 00:35:06,230 --> 00:35:10,580 downloaded, attached, and installed inside of our SQL 954 00:35:10,580 --> 00:35:11,610 Server instance. 955 00:35:11,610 --> 00:35:13,340 And at the very end here, I just showed you how you could 956 00:35:13,340 --> 00:35:16,010 create a solution and add project files so you can 957 00:35:16,010 --> 00:35:17,960 manage your queries. 958 00:35:17,960 --> 00:35:19,250 I hope this has been informative for you, and I 959 00:35:19,250 --> 00:35:20,500 thank you for viewing. 960 00:35:20,500 --> 00:35:21,180