 Hi, this is Robert Gray and I'm the host of Visual Studio Toolbox. I've been doing this show for quite some time. The first episode that I have up on the screen here was May 2011, and we're on pretty close to our 150th show. I've done all of them myself with one exception, Seth Juarez once came on and co-hosted, not worked fine, and I decided recently that, yes, I'm obviously going to continue doing the show, but that I would let some others have some of the fun, and so I've decided to ask a few people to co-host the show every now and again. Dmitri Leyland, who's going to do the episode you're going to see next, who's going to do a bunch of episodes, Donovan Browns are going to do some of the ALM and DevOps stuff, and we'll see who else may show up as a co-host in the future. So just to let you know that the show goes on, I'll still be the main host, I'll still be doing most of them, but we're going to have some additional co-hosts, and I'm really looking forward to that. So today's episode will be Dmitri Leyland as the co-host, and I will see you next time on Visual Studio Toolbox. Hey, and welcome to an episode of Visual Studio Toolbox. My name is Dmitri Leyland, and as Robert announced me a second ago, I'm going to be a co-host in a future episode starting with this one, and I'm really excited to have Kevin Kona with me from the SQL Server team. Hey, Kevin. Hi. Welcome to Toolbox. Thanks very much. We've been working for a while together, and we've been talking about this video, it's awesome. We're finally here. So what are we going to talk about today? Yes, so today we're going to talk about using SQL Server data tools in Visual Studio to make it easier to develop your applications that interact with databases. Okay, and specifically SQL Server, right? I mean, that's the scope. I remember doing evangelism for these tools, you know, I'm familiar with them for a long time, and a question I'd often get is, does SQL Server data tools work with Oracle or MySQL or something else? Well, it's for our product, right? So that's the first thing to say, and it's free. A lot of people think another thing we have to get through some skew of some Visual Studio thing, right? Yeah. This thing's available to everybody, right? That's right. It's 100% free. It's built into Visual Studio ever since Visual Studio 2012, and with Visual Studio 2015, obviously we've added a lot of new improvements and features that work great there. Yeah, and you guys ship all the time, right? This is something that's really a live project. We're not talking about something we ship for developers like a long time ago or anything like that. This has been around for how many years? Yeah, it's been around a while, but it's now shipping monthly, not even every three months, but monthly updates. So our current release, I'm actually going to show you what's coming out in about a week or so. Awesome. And like that new feature that it in, I can point those out if you want, but every month come back and check and you'll see some new things. Yeah, and I had to look down. It's October 24th, so a week from October 24th, because when publishing the video, folks might already have access to what you just showed. That's right. So by the time this goes live, it should be up and ready, release 16.5. Okay, awesome. Well, let's get into it. We've got a lot to cover and why don't you kick us off? Great. Okay, so first I just wanted to give some context, so a couple of quick slides here. And I want to talk to you about what a lot of people see when they're used to developing, right? Which is you do your application in source control, everything's well managed, but often in a real environment, the database development is kept separate. Sometimes it's a different team, a DBA manages it. And when they're doing that, it's often not checked into source control. Usually they'll have a set of... That never happens. Never happens. Never not checking something to source control. Yeah, never happens. Often you end up developing directly against stage or sometimes even production. You make your changes right there, you hope and... There's like a data person on the team, like a DBA or the database guy or the staging server person. They often like, I mean, how many times have I had this in my career, right? They manage the staging environment. If somebody needs to deploy some SQL, they might deploy it there. You come in next day, your stuff isn't, your code isn't working unless you get latest or something. It's always been kind of messy, so this helps with that. Yeah, and it's hard then to track your changes, know when things are gonna happen. It gets disconnected, so it slows down your development. There's a lot of issues with this. And that's where a lot of people are there today and we wanna move them away from that. There's some clear issues there. I mean, you don't know what's gonna work against production, you might not be able to see all of the things that are in your database and see when things break. Because with application code, you compile, you get a lot of helpful information from that. Spoiled Visual Studio Developers, like myself. We wanna see all the things that build time, right? As much as possible they can be caught. And we like to have everything checked in, part of the solution, and get latest and start working in the morning as a developer, right? Those are the things that most of us take for granted for everything else except often SQL, right? SQL becomes that one thing and this is the solution for that. Yeah, that's right. And so I wanted to go through what we think would be very useful for you and what SQL Server Data Tools provides. So when you're looking for a better approach than this, what you wanna look at for key features improving productivity are being source control friendly, just like your application, being a repeatable process. So multiple times you should be able to roll this out because that's really critical and most enterprises will want to have that kind of staging verification process. Nothing like missing some column and a table or having the wrong data type just because things weren't as organized in your project, right? Yeah, and especially the deployment part is very important because it might be there in your local database, but if the script doesn't have it, then it's all missing and you're in trouble. Your staging might break or eventually even worse, your project might break. Exactly. Continuous integration and deployment is great. So whenever you check in, you should be able to run a deployment, make sure it all works, ideally run some tests against it and do all of that goodness as well, just like you would for your application. And the reason why that works is because SSDT, that this tool, right, by having it installed in the little studio brings T-SQL into your solution. It's a project in your solution is part of it and that's one of the biggest things that we're trying to help folks with. I think, again, having that disconnect, having SQL be something else has always been a pain, but this is right there. That's right, that's right. And then one of the great things SQL Server Data Tools does is makes it very easy to find out about your code, fix errors, discover what's wrong, which can be very difficult sometimes with SQL. And understanding the impact of your changes, it's another really good one. And we're gonna mention briefly alternatives to SQL Server Data Tools in the next slide. Things like understanding the impact of changes using Schema Compare, which we'll show later, they're great for anybody who's developing with SQL, even if they don't use the SQL projects that we're gonna show. Okay, awesome. So moving on, there are two main solutions. So one is the SQL projects that we're gonna talk about. That's where you have everything in T-SQL, it's source controlled and it's a project based solution. The common alternative is a migration framework like entity framework migrations. So you can use SQL Server Data Tools with entity framework. We're gonna show that today. But the migration framework, what it does is it takes a set of changes. So for every time you wanna update the data part of your application, you would write a new migration that goes from one state to the other. So that's a great technique to do similar things to SQL Server Data Tools. Clearly, I'm a little biased and think that you should try it, SQL Server Data Tools. You don't work in the EFT. Yeah. That's a DT team. But they're both valid paths, right? That's right. And complementary in many ways. Exactly. And if you're already using a migration framework approach, you may well want to stick with that, but use some of the complementary tools that we're providing. Yeah. I think, you know, as a baseline for SEDT, when I always talk to customers myself, I mean, I use SEDT for my side projects. It's the number one thing is TSQL is right there with me. It deploys with me. It's part of my build process, part of my CI process. Other things, you know, how you get from TSQL version one to version two and some environment, that's a whole different conversation. Yeah. And the other thing that we've seen with SQL projects that's great is oftentimes people start with very app-centric code, and as their applications scale up, grow bigger, they want to make it faster, more performant, they start putting a lot more logic into the database level. That's where something like migration, improving queries and everything else, take some effort, unfortunately. And that's where you get the real benefits from SQL projects because you'll start to understand a lot more and make it a lot easier and more productive to develop your store procedures, your views, all of your programmability objects there. Okay, awesome. So that's the background for why we want people to try out SQL Server Data Tools. We hope they will. And now we'd like to demo it, if that's possible. Yeah, let's jump in. We love demos and toolbox. Great, okay. So I've actually taken an application that you wrote a while back called healthclinic.biz. I can't take the full credit. I mean, I wrote like 90, no, I did not write this application. My team did though, so that is correct. We wrote it back for Connect last year, the developer fall moment that we have. It's available on GitHub, so you just pulled that from GitHub and integrated it into this. Yeah, so I pulled it down from GitHub. This is an ASP.NET application backed by Entity Framework right now. And I'm going to go and take that and start using an SSDT project to do the SQL part of that. Okay, awesome, let's go. So just briefly to show you what this does, it's got a login page over here. If you're a user, you'll get some information and the important part that we'll take note of, hopefully this works. Great, is you've got information about patients, about doctors, about all of these different concepts that are there. And one of the long scenario that we're going to do here is right now it only works for doctors, but if you can imagine this business is expanding, it becomes multidisciplinary and adds in physical therapists and other type of professionals. Trying to change that database. Trying to change that database up. And this is a great time to come back to your app and start using SQL Server Data Tools for that part of it. I think a lot of developers will admit that this is the one time in the project lifecycle where you always get a little bit nervous. You're like, changing code, that's easy, changing database. Where's my DBA, you know, what scripts or who's using EF, what project technology we're using here, so yeah, I think this is awesome. Okay, awesome. So going into Visual Studio, this is set up into a number of different projects. The important ones here are the web front end, which we'll look at. And if you look here, the one thing that we need to know from here is if we go to theappsettings.json, this is going against a local DB instance locally. So you can obviously override this, but I created this local DB instance just to keep things nice and separated and there's a database there. Yeah, that makes sense. And you know, it's worth saying for folks who are watching this, SSDT works with everything, right? Every version of SQL Server we can think of, whether it's developer edition, local DB, SQL DB in the cloud, right? You guys don't care, you work with SQL Server no matter where it's running. That's right. And local DB is built in, it's installed with Visual Studio, so it's right there. We updated it in Update 2 to support SQL Server 2016. And so we've continued upgrading that and we'll keep shipping SQL Server data tools with the latest versions whenever they're next. And how far back does SSDT work with SQL Server 2000? 2005. So for the database projects, it's SQL Server 2005. There are business intelligence projects just to pitch that we've unified the installer for SQL Server data tools so that if you do have business intelligence, just update SQL Server data tools from inside Visual Studio, you'll have the option to install those. And they are also backwards compatible 2008 and up for analysis and reporting services and 2012 and up for integration services. All right, awesome. Great. So the other part of this that's obviously interesting to us at the moment is the data project. So this- I would have guessed the office. Yeah, not quite. Not today. And I haven't explored the wonders of that so I'm sure you have it in another video. It's a cool demo, we built a lot into it but I'm glad we found the SQL piece for this demo. Yeah. So here you can see that each at table has its own repository here. So this is a doctor's repository. It's done in the entity framework way. It's lovely, plain old C-sharp objects and it's got a whole bunch of information there. But for me, it is actually kind of hard to understand exactly how this maps to a database. So what I actually wanna do is go and view the data part of this from a database and pull it into a project system. So that's what we're gonna do next. So I'm gonna stop this demo app for the moment so that I can just get a little bit more source control. And if you haven't started with SQL Server at DataTools before, one of the things is you might be using Server Explorer to connect straight to your databases. We'd recommend you use SQL Server Object Explorer here. So we power the functionality in Server Explorer. It's just a little more limited and doesn't have all the options. The SQL Server Object Explorer is more specific to SQL Server, has more functionality. You install SSDT, it's right there in your view options, might as well use it. Exactly. So going here and you can see that I've already connected to this server and the database is ready to go over here. It's been populated with some sample data as part of the app, which is all great. And I could right click here and create the new project, but since I just wanna organize it correctly, I'm gonna create a project first and pull in the schema of that database to understand what it looks like. Yeah, and this is already, we're talking about SSDT project, right? So this is a solution, does not have SSDT project in it, we're gonna add one. That's right. Yeah, and there's two ways, right? You can add it manually first on the right side or you can go from the left side to the right side. Exactly, exactly. So let's do that. And if we just go to add a new project, we go and we'll get a SQL Server one here. I'm just gonna make sure that this is under the source folder and I will call this to be consistent my health.data.sql since it's our SQL project. Okay, so this has created a blank SQL project. Things to note when you create this for the first time, if you open the properties, you set a target platform here. So this is quite important. This is how we validate that all of the syntax and code that you're using will work. This is your build validation. Exactly, so this is where build validation comes from. You can see all of the versions listed here. This is classic Azure SQL database, obviously with V12, huge new surface area and that's the default. That will eventually go away, but for now, again, for compatibility reasons, we'll keep it in. But yes, we're using SQL Server 2016 locally, so we'll leave it at that. And we're going to import the schema data in. So if we just go import, you can import a bag of scripts if you have your code like that. If in those scenarios you were running and you had everything managed, it won't pull in alter scripts, just creates and we'll go over that in a minute. And a DAC pack is just when you do a build, just like you get a DLL, out for SQL projects, you get a DAC pack, which is just the single file containing all of your schema information for you. And it's the thing you can hand over in a scenario where you don't have access to the production or some Q&A environment, you can head over to the DAC pack, that team could deploy it and you can be sure that they deploy the DAC pack, your SQL changes made it out to the environment. That's right, yes. And that gets checked into, that gets as part of the build, you can publish it, then you can pull down as part of a release flow. And we've actually got blogs and doing that for Visual Studio Online, which is great. Yeah, that's awesome. I mean, I remember even a few years ago when I was still in the field at Microsoft, I would run into customers that didn't know about that part of it or the tools. This is really powerful stuff. We could spend time just on that little thing, but let's keep moving. But for now, just pulling in the database, so we want to pull in all of the data. So I'm just going to choose the connection. I have actually connected this before, so it's in my recent history. But if I hadn't, if you go to the browse over here and all of your local servers get helpfully listed, so I'm just going to go down and pick my health from there, connect and start. Yes, there's a lot of options, right? We don't have time to talk about all the import options, all the build validation options, things you can configure based on your project needs so folks shouldn't feel like we have some special need and this thing won't work for me. Check it out, maybe it will, right? Yeah, there's a huge amount of configuration. Obviously, we won't go into all of it today, but pretty much everything is configurable or extensible. Cool, so what are we showing here? Okay, so now that this is finished, you'll notice that a folder's been added and underneath it are all of our tables. So it is a simple application. It's just a set of tables right now. You can see, for example, we showed the doctor before. If you go in here, and I'll just minimize this to give this room, yeah, you can see that since it's a table, we have a very good designer, makes it very easy to make changes up here and any changes are reflected in the SQL code. So if it's not in the designer, you can easily just type the SQL to make it all work. Which is great. I remember really kind of rediscovering this for the first time in my site project that I was working on and this was so awesome. I'm seeing this and like, oh man, I want to talk about all the school stuff in the editor that we have. So maybe you can describe some of the capabilities of this view because this view itself is worth a shot, right? Yeah, yeah, it really is. And even some people use SQL Server Management Studio and if they love the table designer there, this is that on steroids. It's a better version, it's more modern, it's great. All of the columns are listed one by one up here. You can change data types as you need. And it's highlighting down here the actual SQL as you need to be there. That's right, it's moving in. You can check a lot of the settings. Your keys, your indexes, your constraints are all shown up here. Again, clicking on it brings us right to the primary key down here, which is great. And you can bootstrap adding in new keys, adding in indexes, et cetera, right from here. One of the things people might not know is that the properties window down here also has configuration. We're not gonna really go into that in detail today, but you can alter a lot of the settings right from there. So again, if you don't know T-SQL, this is a very comfortable, safe environment to kind of learn and develop, especially with the table needs. Yeah, it works for all sorts of people's level of exposure to the SQL server. And it does the validation for you if you break some T-SQL down there and edit it. You can edit this, right? So you can take away a comma and an addition. Our great one actually to show you is what happens if I reference something that doesn't exist? Because that's a great one, and it comes up red and tells you this foreign key has an unresolved reference. So it says this thing doesn't exist, it's broken. As long as it's part of this project in the solution, this will find it, this will validate it. Yeah, exactly. And just to show what happens, if you try and build this, which as we mentioned, building before, building will do all the validation that you'd expect. And now that there's an error here, it will say builds failed, brings up the error list, with this, if I double click, it brings me straight to where the error is, which is really great. It makes it very simple to fix things as you go. And what you'll usually find is if you're renaming things, if you're changing the structure, that will happen a lot. And you'll get errors or warnings in some cases that tell you, hey, there's a problem here. And T-SQL as a language is very tolerant. So if you do that on the database side, it'll happily change the table, break the view, break the store procedure, and you'll never notice. If you work with SQL, you've been there. Yeah, that's not a missing area. So obviously we'll want to revert this back for now. And the other part to show is that since we know now, and let's just verify that this does build, since it does build, we're going to check this into source control. So as we mentioned, the great thing then is you get all of your history, all of your change log, all put in just like your application. And as you make changes, they can run in combination together. Yeah, awesome. Okay, so let's go over to Team Explorer. And I've seen people use this in a way where they don't want the SQL building as part of their project. And that's fine. You can make a solution of your own or some variant that doesn't include this thing. It's an optional, but just having somebody manage this as part of their ALM lifecycle becomes very cool. Yeah, yeah, exactly. And often what will happen is one person is still the developer one or two and the other people pull it down. And as part of their published script, they just have it publish the database for them. So they're always in sync with the database developers instead of having some special mechanism to catch up with. And by special mechanism, you mean that email you have to check in the morning with the SQL attached to it? Exactly. That has never happened. No, no. So we'll just create a new branch for this obviously and create it. And we're going to go into our changes here. And I'm just going to very quickly do initial project commit. Okay, and commit all. Obviously, you can sync this up as usual to, you know, Visual Studio Online and all the other services. Yeah, exactly. So that's great. So that's building and that's kind of step one just to show you how that works. But it's not very useful unless you actually publish it somewhere, right? So the next thing I'd like to show is what happens when you try and publish to an existing database, just like the one we've already come from, and also to a brand new one. Because we were talking about having dev, stage and production. The magic behind this is that everything is, as you saw, create scripts. But when it comes time to publish, it will actually generate the correct alter statements to make it look the way it should. So for the existing database, we should see very few changes, just maybe some settings where we had default settings and we didn't pull those in. And for a brand new database, it'll just create it straight out. So how about I show you that? Yeah, I'll do it. Great. Okay. So if I choose to publish to my existing one, we'll just go here and go publish. Just like you published, by the way, you know, your ASP.net website. It's very similar. And again, you just need to say, what's your target database? So since I already connected to it again, it's super quick. This is where I'll show you those huge amount of options that you have. This is a scary button to click whenever it says advanced. I remember the first time I went in here it was a scary moment to be told like, oh boy. Yeah, database development is complicated. The key things to look at here are we will, by default, block you if you try and do a change that would cause data loss. So that's obviously your P0 scenario. The time you would uncheck this are basically when you're just in your local environment and you're doing a huge amount of changes and just want to tear down, restart. Yeah, that's what I do in my local dev environment, right? I would never want to do it. Be careful there. Yeah. Don't keep the setting in terms of another deployment. You know, you can back up. You can do transactional deployment if you want. Again, these are great options for when you're deploying to production you don't really need them locally. One of the key ones is choosing what to drop or ignore. By default, we'll leave things in the database that aren't in your project. Just say there's a schema that somebody else is using you don't want to touch it. We'll ignore it. But if you do want a little bit more control. Or you want to punish your teammates where it didn't deploy so that's not in here. That's true. You turn this on. You turn this on. So once you're comfortable with the process everyone's on board, you'll turn this on. But what you might want to do in those cases is don't drop users logins or permissions since those are often managed by your DBA. And so we've added that. Still should be separate. Exactly. And so for people who haven't looked at this in a while that's something that we've added in the last year or two is the ability to say not drop your users, tables, et cetera, which is really powerful. But for now I'm just going to get out of this. And I always go and try and save my profile. So I'm just going to hit create profile here. And I'll generate the script to show you what would happen. So again, this is going against the existing database. You can see I was practicing beforehand. Yeah. I'll just clear this out so that it's not quite so busy. OK, so when we get this, first off, if you click the preview window, you get a very helpful thing showing what's happening. And in this case, as you'd expect, almost nothing's happening. We just pulled it back in. Nothing's changed. You're just comparing between what's in T-SQL, what's in the DevEnvironment. And it's the exact same. So here you go. And there's a little bit of bootstrap code here just to set some properties, as you can see. But really, I just always know to skip those. Once it starts with the actual database, it does nothing. So it's happy. Nothing yet. Nothing yet. It's all good. So what happens if we deploy to somewhere that doesn't exist already? So let's go publish. And this would be, for me, I often end up doing a little bit of database development before plugging it back into the application. So again, this is where I'd have my destructive, no worry about block and data loss, et cetera one. So go in here. And instead, I'm going to go to a different locally V instance. I'll just go to this Projects V13. That's built in, again, when we create a project, we have our own dedicated server just so that we don't interfere with ASP.NET applications, et cetera. That might be expecting it on usually MS-SQL local DV. Yeah, makes sense. OK, so I am going to call this My Health again. And delete that. OK, I'll create this profile as well. And it, oh, I forgot to put that. My Health, there we go. And I'll just save it. And again, I'll generate the script. And you can publish directly, and we'll just save the script for you. But oftentimes, somebody wants to verify what's happening, so we're just showing that. My behavior with this initially was that we kept generating scripts, and they kept manually running them. I get very comfortable very quickly, especially for local dev. And I just let this thing publish out. It makes sense. And then you can create the script and compare notes with somebody or send it over to show the changes you're about to push maybe to the shared environment or whatever. Exactly. Yeah, so if you look at the preview here, you'll see that there's a huge amount of actions to create everything for the very first time. So in this case, it's going to create. And once we make changes later, we'll show you what happens. And it's worth saying, I mean, again, you said this in the beginning, but I want to be clear for our audience, which is, there's just tables in this particular example. But this works with functions and users and views and whatever, right? All the database, all the database objects. There's no database object that this thing wouldn't have pulled in. It just that our example doesn't have a complex. Exactly, exactly. So I'll just run this to publish it up to that database. Okay, and by the way, what's interesting is we've got some warnings here on maximum key lengths, et cetera. So we're not going to cover those, but science sometimes maybe want to redesign your app. And that's where having control at the database level can be really good. Sure, I'm the only one from my team here. I'll take it, we screwed this one up. I guess we should have hired a DBA. There we go, no, no, no. The whole point of this tool is you get to, you don't need to have your dedicated DBA quite as much anymore. They're free to deal with the complex stuff. You can do all of your day-to-day applications. Yeah, I remember at my, one of the first companies where I used an early version of this, before Microsoft even, right, many years ago, and the DBA actually like started to love this. When I showed them the first time, they were all skeptical, they were used to SSMS. That's how they did things. They would manually check things in the source control. It was really a hectic process. Then I started showing them Visual Studio. The person never even used VS much in their life. They used to be a developer like a long time ago. And they switched to almost using this as the primary tool because it's just so powerful, even for a DBA. So it's not just a developer tool. Yeah, it really helps. And once you get used to it, it can give huge power. A lot of DBAs, yeah, they happily move over and some have to be persuaded, but that's where you can either give them a script that you've generated as the handoff point or you can give them the DAC pack and let them use our command line tools to deploy it that way, which is great. For me, it was actually interesting. It was actually the reverse scenario, like from a mentality perspective, from a developer, you have to convince them that this gives you the advantage of having T-SQL in your project and you can work as a team better, part of ALM, DevOps is awesome, right? Developers need to be sold on that. From the other side, DBAs, if you show this to them, the selling point is not just everything I just mentioned, but the fact that they can often hit a five and run the app that we developers run. And sometimes DBAs don't have that capacity, right? They just don't do dev day in day out, right? They just do T-SQL at a big organization. This allows them to actually have the local dev environment, they could test the T-SQL change, the same way we as developers could change it, they become even more part of the team. So I think this should benefit both sides quite well. That's right. But it doesn't force you, like you can have this just for one side of the fence, you could have it for nobody, nobody wants to use it, but I think it works both in both user types. So how about now we've got everything working, we know how it's going, how about we make some changes to this? Yeah, let's do it. Okay, so I think I mentioned to you, but this is currently very focused on doctors and what happens when you want to change that. So for me, I had to think about this, there's a lot of different ways to do that. I decided that I really like having the simple table storing everything for doctor here, but really it's becoming healthcare provider rather than doctor. So I don't want to break the existing app and so what I'm going to do is kind of stage it. So I'm going to change this and make it a healthcare provider and change all of that, but I'll provide a view that's filtered to just the doctors. And so if anyone's familiar with SQL, but it's just you'll view over a table, we'll just simple query to filter it down. Yeah, we have to change behind a view, therefore we don't have to change our code right away. Exactly. Yeah, that's a really good suggestion in general. We should do a whole like T-SQL, you know, how to deal with T-SQL in this tool. And being honest, I'm not the best, because as was many people who I primarily code C-Sharp, my T-SQL skills are a lot worse than the people who'll be using this tool. So if I do mess up on the T-SQL, please be considerate on that part. I'm not, that's not my expertise. So the first thing I'm going to do is just add in a new column, which is going to be called provider type. And this is just going to be the name. I could use some set of types here or an enum, but what I'm going to do instead is is I'll just use a description. So it's going to be nvarkar50, should be completely fine. I'll allow nulls because, you know, for the existing ones we're just going to assume anything that's null as a doctor. And I'm going to be using this in queries. So one of the things that I'm going to do is I'm going to add an index on it because you want it to perform pretty well. This makes it super simple. So I'll just add a regular index. I'm going to put it on this provider type column. So I'll put that in here. And once I do that, it will come in here. And obviously it's saying red because, you know, it doesn't know which column I want to put it on. So I'll just type in provider type. Okay. So this is going to be the thing that separates our doctors from physical therapists in this example. Yeah. Okay. So the other thing and something that's really great that we can show is I want to rename this. So it's no longer right to call it a doctor, but I don't want to do anything complex. I don't want to lose my data or anything like that. So I'm just going to use a rename function in here. So just like your C sharp projects, we have a couple of great refactorings. Rename is there and it will actually do the rename while preserving all of your data, avoiding data motion. You can move it to a specific schema. You can expand the wildcards which I'll show you or do fully qualified names, which is great if you've got some confusion over things. So I'll just rename here and call this healthcare provider. So this is our continuation of spoiling the SQL developer. Yes, exactly. So once we do this, what's great is you actually get to see everywhere that's affected by that change. So you can see here that we already had an index on it. It's getting renamed now. The constraint that we had is getting renamed and is there anywhere else? Just another constraint. So that's where all of the table is used. So I'm going to apply that and those changes are added. There's also, by the way, a refactor log file down here. That's where these refactorings are stored since databases are stateful. I'm also going to rename this doctor ID. But first, I do want to make sure it's like, hey, what's the impact of this going to be? So I'm going to go and one of the great things here again, spoiling the developer, is that I can just find all of the references to this. So I know whether it's safe or a crazy idea to do this. It's truly able to do that because, again, it's part of the solution and understands the context of the database in this. And again, this has just got tables at the moment. But if you've got views and stored procedures in particular, being able to see exactly where in the stored procedure it's used, maybe then verify all of your parameters are correct. It's great. This is very, very useful. So here you go and you get all the similar results just like anything else. You can see here that it's used in the clinic appointment and home appointment. And if I do look at that, I will see here that it's actually a column here called doctor ID. So if I do want to change the name on this, I'll probably end up changing it in a bunch of places, which I'm going to do. So let's change the name on this. So again, I'm just going to refactor, rename it. Yep, so refactor works at different levels. Mm-hmm, yeah. We can do at the column level, awesome. Yeah, so this is going to be a provider ID. OK, again, we get shown exactly where it's going to be affected. It does actually take effect in those things. So apply. And then because I saw those other ones were there, I'm going to refactor, rename those as well. And just go in here, provider ID, OK. And I think there was one more. So let's just find all references again, since I am very lazy sometimes. And here we go, this one. OK, perfect. And if I go in here, I'll just go. And that should be all of them. And so what's great about this is you see how quick it is to make all of these changes. Oh, yeah. And there's just a lot of fear when you're doing this against live database. Or what usually happens is you try and make the first change. It errs out because of all those other references. You get that long list of errors, or you run the app and it crashes, which is a more realistic scenario if you don't even have this part of your solution. Exactly. So the validation that you're getting wouldn't, in fact, be much greater even without using refactor. But refactor is even better. That's right. I will point out, though, the one thing that I didn't do here was update the code in the application to handle this. So when we're creating our view, we're just going to have to make sure that we're still using that same thing. So now that we've done that, I'm just going to build, because I am paranoid. And what's great is you get this very quick validation loop through that. It works, all good, happy. Developers are paranoid because code is after us. Yes. It's not paranoid when somebody's trying to break your build. That's right. So I'm going to add in the view now so that for the application it should be transparent going back to it. So again, as you mentioned, all of the items really are there. So I'll just show you a new item. Just the wealth of things. Programmability, security, all of the latest things. If we go to tables and views. And these are just templates, right? So just get you started quickly. Exactly. Basically everything in SQL that's scriptable. Everything and everything has a template. So you can see the new memory optimized tables since 2014 are there. Those are awesome. Great improvements, by the way, there. And temporal, which is proving really popular, which is it stores a record of all the changes to your date over time. So that's been super popular feature of this. I could have used that maybe five years ago. Yeah. You guys are catching up. Yeah. No, it's great. You should definitely try it out. More features. That's right. So I'm going to add this. And I'm going to call it out of view though. And I'm going to call it doctor, since that's what it is now representing. See, if you were on my team, I'd send you an email, be like, you didn't put it in a folder called views. Come on. I know, I know. But for today, we're going to be a little lazy on it. So lazy. But it's worth showing that in this aspect, like there's no enforcement of table structure, or anything like that. This is purely for like, even when you're importing, right, you can check different import options, create folders for tables, or other settings, more flat structure if you like. Yeah, we've got a couple of defaults there that tend to be what people like. But you can organize it any way you want after that. Yeah. So here, again, you've got the usual IntelliSense here. So if I just go health care provider, it comes in. As I mentioned, I want to filter this then. So I'm just going to do a where clause. So it was provider type. Again, I love IntelliSense. And I'm just going to assume it's doctor. But since all of the existing ones are there, I'll also do provider type is null. And I'm going to assume that anything null is a doctor. We're like blasting past the IntelliSense of this. But this is full IntelliSense that's smart enough to understand the structure in which your database is created. So it knows that there's a table. It knows there's a column. It'll know there's a view, a function, again, all these different things we could have had in this application. And that's how at build time it will validate. So if you try and do something where you're using the wrong data type, we will flag that we'll either give an error if it's going to break, or we'll flag it if it's not. So those kind of really semantic, intelligent advisers are great to give you all of those errors. In this case, the other thing is that we wanted to change it. So we don't want to break the app, so we need to keep that original name the same. So you can see here you get the list. I'm too lazy to type though that. So I'm going to use another refactoring, which is to expand wild cards, which is great. You can see it all comes out. And you can see provider ID is there. I'm just going to format this so it looks good. All right, folks out there in the universe. Raise your hand wherever you are if you've typed this out manually. That's me, that's me. So we've all cried. Yes, this is great. And then all I need to do here is to keep it all together. I'm just going to bring this back to be doctor ID. I will escape it, which is just a good practice. As you can see, we try to enforce that pretty heavily as well to avoid any strangeness. And there we go, that's our view. One thing I actually forgot to do before, so before I publish this, I'm going to want to validate this. So I'm just going to move some data across very quickly from the other server. So again, this can be hard, right, to do. It can be, it's tricky. Unless you have the right tool, I mean, this is a lot of manual work. Exactly, so I just want to verify that this is going to work. Before I make any changes, it's the best time because once I've changed the structure, it can be a little hard. And I mean, so there's a couple of things to say here. First of all, a view needs data, right, to be functional in this particular case. So that makes sense. We need to bring the data over. But the other thing is that all of this change that you're making here locally, you can go and see which tables you've modified, which files, C sharp files you've modified. You can see the change log. So this is still part of your change so that you could decide even to undo if this goes really badly. And I've had this happen to be one time in my career where I went down to the rabbit hole of like, you know, six hours of changes and I stopped myself and I said, what am I doing? This is the wrong approach. And I under check out and everything, including T-SQL, because we were using SSDT. And it was just so beautiful. I mean, I made a local copy of it, just in case I wanted to go back to crazy. But you have that flexibility. And if you had your SSMS window open and you were changing SQL somewhere, even if it's in the source control, it becomes this weird state. Yeah, it can be so hard to undo. You just don't know what you're doing. Whereas here, we've done all this and yet because we're offline, nothing's happened yet. We've gotten all the same validation we would have, but it's all just ready to go. Full validation. You can see where your changes are. You can decide to check it in. It's part of your work items, association, all that. Exactly. And when you are setting up and bootstrapping your test environment, you're usually going to have to pull over some data. So I'll just show you very quickly. Schema Compare will hopefully get to although we're running a little low on time. Data Compare lets you compare the two databases. This is relatively new, right? This has been, I mean, it's been around. And tell people more about this because I think this is one of the most hidden things in SSDT, it's a really cool feature. Yeah, it's an interesting story. In the older, older versions, it was there. Then briefly when we came out with SQL Server data tools as its own offering, it wasn't there, but within a few months we caught up. A certain developer named Dimitri remembers that. And he was like, come on, I actually needed it when it wasn't there. I'm sure you guys had your reasons, right? Yeah, yeah. It was just a lot of work, a lot of changes. And so it just took a, you know, we wanted to make sure that we got in for SQL Visual Studio 2012. Can't screw this up, right? You can't screw up data compare. Exactly. You don't want people's data being wrong because Microsoft did it, right? Yeah, but it does let you to compare basically all different types of records. I'm just going to choose the target which is that other database that I created in localDB here. That's the one, hopefully, unless I, oh, well the good news is I can always hit cancel which is great. And I will go and browse back to it on my localDB instance and make sure that I have the right one. As far as like demos go on Toolbox, I think you've done really well. Not too bad. I jinxed it, never mind. It's okay. This is going to go bad now. Well, we'll see. I mean, the good thing is we did change this up so that it doesn't freeze anymore, which is great. So it got to show that you can just cancel out of things very easily. Yeah, it sounds nice. So now that we have that, the thing to point out is the tables do have to be the same. So they have to have the same key for comparison purposes. And once you have that, you can list them, I'm going to compare everything but if you have large, large tables that are say 100 gigabytes in size. Yeah, this could take a while. Yeah, so do we consider of that? Be considerate if you're comparing against Azure or another cloud provider as well that this is pulling it onto your own machine. So, you know, if data ingress and I guess all that charges. So just be aware of that. But obviously locally, it's very quick to show it. And what you can see here is all of the differences. So if we click on this one, you can see that as you'd expect we didn't put any data in the new one. You've got a bunch of data in the existing ones. So this is our original database here in the source and then the target is the new code we've created and that's why it has nothing. Exactly, and so what I want for my testing is just a couple of these. So I'm going to start unchecking some of them. And what I want, and I'll just make this a little smaller since the screen is bad, is I'm just going to do doctor and I'm going to do the tenant ID which I discovered earlier is the thing that everything else refers to. Yeah, I was about to ask that question. It probably needs all the things that doctor is referencing. So the data can be inserted in a bad way. Exactly, so it does know to order the tenants first but it won't make it be checked. So that is something to be aware of. But if I now update the target, and hopefully this will work because I tried earlier, great. If I hit refresh, what you'll see now is for the doctor, all of the things are identical, which is great. And again, if you've made changes, it will just show you the changes. And it's showing zero not because there's no records, it's showing zero because there's no differences. Exactly. In the two. Exactly. And also, you could have generated a script here. So if you want to take that script based approach or just you want to hit that button and look at the script, then just hit update. It's something you can always do. Yeah, and it's great for reference data. If you want to have your scripts generated for you, once you put some of it in, it can be a great way to just script out that reference data. Huge. Okay, so we're done with data, compare it now. We've got our data across and I can actually start testing my view. So let's see what happens before I make my changes. So I'm going to make the changes to the database over here. I'll just hit refresh since we added it. Great. And now that we've got that in there, if I look at the doctor table, again viewing data is very simple. It's super easy to edit this, which is really great. And you can see all of the data there, there's about eight rows worth of data. So hopefully whatever we've done will make eight rows worth of data changes. So here is my publish for I believe local DB. You can rename those. Great. I'm going to start trusting this and I'm just going to hit the magic publish button. Magic button. Yes. So this does all the same steps. So you still have a preview to give you the high level summary. You still have the script, but it will just automatically publish it at the end and tell you, hooray, it worked. Yeah, one thing that I remember myself missing at first was these blue links on the right here. If you have especially a very large, I have one of those large 34 inch displays, a wide screen. This could be pretty far on the right. Clicking on those, those take you to the individual scripts and such. But again, they are great, especially the preview I like, because they don't have to figure out the sequel. I just get a very short summary of what's happening. Yeah, that makes sense. And again, if you look at what happened there, by the way, actually we should go into that briefly. We renamed these four things. We altered the table and we created the new things. And it did it all in the right order. If there's anything complex, it will say drop constraints, do all of the things it needs. But in this case, if I just use a script, it's pretty clean script, which is great. We're usually pretty smart about these things. So we rename the doctor, rename it doctor ID to provider ID. So bunch of just very simple renames. And that's something SQL Server supports that will just change it without any data movement or anything. It's a very verbal script file that generates. It does all the print statements. So as it's running in SSMS, let's say, let's say you give it over to your DBA or whatever your teammate, they can see what's going on. I really love how this tool does it. Yeah, and it is smart enough to just alter the table after that, create an index on that, then create the view and it does it all in the right order, which is really great. And this bit at the end is just the refactor log so that traces the steps. So if you try and apply it again, it knows that you've done all of these renames. Great, so that was awesome. And now I'm just gonna test things. So I wrote this and hopefully it will work. So we can now view if I just refresh this. I should see that my doctor has gone away and healthcare provider is here instead. So I'll just view the data on that. As you can see, it still has all of the data. What's funny is this one, hopefully this will work. If I hit refresh on this, oh, nope, it's not happy with that because it was a table and that's a view. Yeah, because it's doctor that's trying to pull out. But if I go down to the views, views are also editable the same way, which is great. And I can see here. And that was actually a good error because it doesn't assume that just because you have something named the same, that it's the same thing and it's a valid scenario. It's like, look, there's no more doctor table. Go bring a view version of this up. Exactly. And so now I'm just gonna start muddling with this data a little bit. So what I'm gonna do is I'm just gonna make sure what I would do is make one of these a doctor and make sure it still works. Once I tab off of the row, it will just automatically commit it up. So that should still work. But now if I change one of these so that they're no longer a doctor, but instead a physical therapist, what I'm hoping for is, yes, we'll see how close it is. Yeah, we can fix that up later. If I go over here and I hit refresh, you'll see that it's missing row seven. Yeah, because it doesn't meet view criteria, right? Exactly. So what's great about this then is that everything else is the same. My application will continue, but now I can add a new page that's very simple based on the other healthcare provider type and I can expand my app out. I'll check this in now and go from there. And I've just one last feature to very quickly show which is schema compare. So as with everything, you can see your history over here, all of the objects that we have. So I'll just say refactored to support against spelling is fun when you're typing live. If I was graded on spelling for my job, I wouldn't be here. There you go. We all have things we're good at. Yeah, so I'll just close you down and then there's just one last thing which we mentioned would be useful for everybody. So this has been really powerful and you can see how this all works. If you're using entity framework, you just need to not do the auto migrations and instead I have part of your script be that you when you're publishing just publish this beforehand. Yeah, but I mean, I think this tool has like various use cases, right? You can use it just to kind of do a sanity check from one environment to the next from one local database to the next. You can use it to replace migrations. You can use it in some instances where something went wrong, you know, there's all sorts of I think awesome use cases. That's it. And so the last one then is when you do want to be able to understand things and see what's changed, one really powerful thing is schema compare. So you get your history through GATAR through TFS integration, you get that great history file by file. But what you don't know is how does that compare to the real database? And here you've right clicked on your data project. Yes, on my data project. So your project now has these various SQL related options. That's right, and so schema compare will let you compare your project to any database, to databases to each other or even those DAC packs we mentioned that, you know, so if somebody gives you a random DAC pack, you can actually use this as a DBA to actually verify what's going on and understand all of the changes that are gonna happen in a very simple intuitive way. Yeah, and I've done this in real life. Like I, again, my own development, like I'm just one guy working on a solution. So I keep all my DAC packs from every deployment and I just did it that way. At the time I wasn't using EF migrations and I had an issue and I had to compare back to a previous deployment I did and I found out what my issue was like so quickly. Otherwise I'd be sitting there like, what changes do I have? Imagine it's not even source control. Oh my gosh. Yes. Or it is a file somewhere and some SharePoint site. You haven't ever had that? I have and actually I'll just show you. So I'm just gonna choose this database first and I'm gonna go up to my original database over here on ASP.net and hit okay. I'll actually show you one just quick tip and technique for how to make that handy. If you do wanna reference a couple like say, hey, here was my original or major impactful changes to look back, you can very quickly go and where is this hidden there should be snapshot. So snapshot will just create a DAC pack which is the backup at this point in time for you. You can then have that checked in and that's great if you wanna bootstrap it for these kind of comparisons. So if you're- And it's based on your project. Based on my project, yes. Because you created it here and you can also create the same snapshots of a database and some environment. And I've had that in reverse where again like, man I've worked so much with this tool. I forget how much I worked with this as DT. There was a scenario where the production environment is having an issue. I got a snapshot from the DBA, I didn't have access to the environment. I compared it and right away I saw something was missing from a deployment script. For some reason it didn't make sense but sometimes you just gotta fix the issue first and then keep going, right? Exactly, yeah. No, so it's really great. It will work against pretty much any database you have. If you just wanna quickly like that, get it out in a pretty secure way. Small, it's simple. So that's snapshots. Bonus feature for people since I hadn't planned to show that. And now when we do schema compare, the first thing I'll say is I really hope that this looks good because on my machine is the only one that sometimes doesn't show this by default but it was friendly to me and decided to be nice, which is great. So this shows all of the changes that have happened. So you can see here that this has changed the provider ID, the constraint, just a regular diff viewer here. So you can see exactly what's changed. See the darker color is where something is different on both sides. So you can see that these have been altered again here with slightly more changes and this one here again. And again, it's smart enough to know it was healthcare providers now, doctor. And finally on the view, this is a brand new view. So it's just there. The icon indicating here, like we have a plus that shows that this is new. We have a little edit, like pen. Change. One day we need to make that a computer or a keyboard, as people forget what pens are eventually. Yes, yes. The other cool thing is that update button, right? The fact you can change or fix the issue. Yeah, and so you can update it. If the target is a project or database, you can directly update it. As with everything, you'll start noticing there's always a script button there. So if you're worried, it's always there. And again, there are a huge amount of options again for this pretty much very similar options. Oh, the one part that I would like to show as well is what happens if you try and disable something. So if I try and undo this, it will get unchecked and so different ones like this and this. This is great. So I've used this when I find the change, but that's not the change I want to push. Like maybe I'm in the middle of two things. The one thing that's keeping me from moving forward in some test environment, I just uncheck the thing that I'm going to fix later or it's not breaking. So empowering the user, right? That's what we're trying to do here. Yeah, so that's it. And that's really the high level tour of most of the features. Just a couple of things that this is a small part of what this does. It's got database unit testing, the ability to add in advanced static code analysis rules, some of which is extensible. So you can pull in, if you've got your own set of business rules, you can embed them as logic into your project so that whenever you build, you'll get flagged if you've got naming issues, et cetera, among many other things here. So do check it out and also if you are still a SQL Server Management Studio user, also realize that the latest version of that is out as well. It works against monthly also works against again, older versions of SQL Server all the way back really well. And so the SQL tools are thriving, well worth a look. And this will hopefully help you in your day-to-day development. All right, well Kevin, thank you so much for being in Toolbox and thank you for everybody who watched it. I hope you find SSDT useful. We'll put some links to the tools, to the example we show, to anything that was relevant. We'll get all up there and we'll have you back on. There's a lot more to talk about. Yeah, thank you very much. Thank you.