Code Analyze your SQL scripts in Visual Studio and elsewhere | Data Exposed: MVP Edition

Key Takeaways

learn how you can use Visual Studio to analyze your squl scripts and tell you some best practices this week on data exposed MVP [Music] Edition hi I'm Anna Hoffman and welcome to this episode of data exposed MVP Edition today I'm joined by Eric Eric thanks so much for joining us today can you kick us off by telling us a little bit about what you do yeah I uh help people uh run applications in a AER with custom development and uh a part of that um they often use a SQL server or as a SQL database and um I am also an MVP and provide tools for helping people make the most of ss SQL database and SQL Server um as a net developer cool awesome well we will absolutely put some links in the description for folks to go learn more about some of the things you work on um but getting right into today like you wanted to talk about analyzing your SQL scripts in visual studio and I'd love to learn a little bit more about what you mean and what you want to show us yes um so I'm going to show how you can improve uh your SQL scripts and your database schema by running a something called Static code analysis as a net developer um it's something that most of net developers are quite familiar to because it's actually kind of built into visual studio and always running in the background and coming with suggestions about how you can improve your code it's not a compilation that's causing errors on disabling you from actually building your code but it's more uh kind of opinionated ideas about how you can improve your code by analyzing it in the background cool and um SQL Server database projects has a similar feature it's not exactly running in the background just like it is in Visual Studio but it's static and when you do a build with that feature enabled you will get notifications about how you can improve your SQL scripts and your database scheme cool awesome well that sounds great I love to uh Dive Right In and take a look cool um so I'm going to switch to visual studio and I have created a database project here and um I have the data um workload install in visual studio and that enable to go to file new project and create a database project which is a project that uh holds my database schema and all my code that I want to run inside my database and uh it will enable it will uh allow me to analyze all the code and ensure that everything is consistent so like if I refer from one table to another in a foreign key or in my store procedures I will get build error if I refer to something that doesn't exist so it ensures that my all my objects are consistent and I will get build errors if they're not and um as build the outcome of the build is a dpack which is a piece of um it's basically a zip file that you can take and then you can apply that against your your production database and say uh now I have this schema um this is what I want my database to look like make it happen so it's um almost magic so awesome um and part of that so we will try I have added a kind of movie database with some screening table and a theater table so we have a movies table with an ID and a title and a release date uh I have tried a little bit of a quirky uh column name here just to uh see if I can break something um and I have a also have a a poster where I'm using the image type uh we have a screening of a movie and it's pointing back to the movie table of course and a screening happens in a theater so we also have a a little theater table and then I have a single stop procedure um that allows me to get some screenings at Movies um based on a day so now I will try to build this project and you can see down here uh that this duck pack file that I mentioned earlier is H produced and that dark pack file then you can take that and deploy against your database also via uh your continuous deployment Pipeline and as aops in aops or GitHub for example awesome cool um right now we're just building the project so if for example I try try to type anything here that uh let me just see yeah so if I do something like this like call this something and we have another foreign key referring to this um ID in the screening table you can see I already start to get errors here and if I try to build the project it will most likely fail so this is the kind of consistency checking I I mentioned earlier awesome and a question for you so like I'm guessing this is using like some base set of rules like do you do we have any control if we want to use different rules or we don't care about about some rules or are there scenarios like that yes there are scenarios where you can disable and enable rules but right now I have not enabled any rules at all so let's start by doing that you I go into the properties of the project and the right down here at the bottom there's a a tab called code analysis so now I will check this Mark and enable code analysis on built and I have a number of rules enabled already I will save the project and then I will build again you can see we can check the AR error list at the moment there's no warnings so we will build the project again so now it's it's going past the phase of ensuring that our schema is a schemas is our schema Integrity is okay but then it's actually if producing a number of warnings telling us things like you should avoid using select star ah uh and um so it's more it's things you can do but maybe in many instances you shouldn't do this is what the static code analysis gives you so it's as I said before it's opinionated but it's made by Microsoft and other smart people that um know what you should or have good opinions about what you should and shouldn't do so maybe you can adopt those rules and by adopting those rules maybe as a DBA you can help your developers ensure that the their SQL scripts and their tables are designed with high quality with a high quality bar so we can take these rules reflect on them and rephrase our schema to ADH her to those rules for example in this table I have used a very quirky column name and it says don't use these special characters in columns names because it will make it painful for everybody so let's get rid of these and then it says um then it said uh you don't use deprecated text uh intext and image data types so I used an image data types here and I should probably use my binary Max in reality I probably shouldn't keep a poster image in my database at any rate but let's um let's fix it see if we can fix it so I have now tried to fix my schema I will build the project again and we will no longer see those issues that was mentioned before there's still a number of issues to fix so um so that's basically using those rules um cool this is awesome databas database project comes with a number of Microsoft created rules um and you can also add extra rules um I have published a couple of nuket packages with some additional rules um that you can apply to your Visual Studio installation and get um even even better in my opinion code analysis you can see there's many many many rules here um that you can apply and run against your database schema awesome that's great and is it pretty easy to add rules like for example you mentioned you have a new get package that you release like what if I wanted to add like a custom rule myself yeah you can do that uh you need to be able to program in CPP um and you need to have Visual Studio or at least Visual Studio code so you can build a c project right um but but other than that um it's definitely possible and I have published a blog post about how it can be done so um create a custom code analysis rule um what packages you should refer to in your library and um basically going through the motion of uh creating a file that rule file is just a dll file that you can then include uh in your project or add to visual studiio sorry so awesome oh another question for you Eric like let's say I maybe I am leading a team a Dev team and we're we're using this service and you know what instead of having warnings we actually want this to error like there are certain rules that we feel really strongly or really opinionated about like is that something that I could do easily you can see there's a column here says read warning as error so if we check this one with avoid select star and then H try to save that setting it's all these settings are saved as part of the project file and will be included in Source control so it will apply to anybody using this project um so we'll try to build again and you can see now we got one error uh from this uh select star uh break thing we did here so yeah awesome so that's how you can kind of protect your database so then you can protect your database and you can actually do it very very early in the process about uh so you as a DBA can say we I'm I'm I'm participating in code reviews of this and I want to to always have this uh this rule enforced because I don't want anybody to we don't want any select stars to sneak in anywhere yes right yeah no I love that okay I have two quick questions for you before we close out uh one is like you're usually in Visual Studio do I need to have a license and then my follow-up question was like does this work in places like Azure data Studio or vs code yes uh regarding license uh the SQL team has been uh negotiating with Visual Studio team and in the license agreement for visual studio Community Edition it says that even as an Enterprise user you are allowed to use this particular project type and some other SQL Server related project types for free uh for as many employees as you want amazing yes and what was the follow-up question s sorry the followup was uh you know if I want I mean this kind of answers it for me then it's fre you here but if I'm using vs code or Azure data Studio can I get the same capability there yes it's it's possible uh I have a blog post about how it can be done it's not it's it's it's it's not a really really nice experience at the moment I think I would imagine work is being done on it because this capability has just been enabled like very very recently but it's it's possible to do that with the database projects both in vs code and in um as data Studio which is based on V code um so yes it's technically possible and I have a blog post telling you how to do it but it's not a it's not a streamline experience but even in Visual Studio is actually not you have to put a dll file in a place where you would normally not have right access when you if you're not administrator on your machine um so even in Visual Studio is not 100% smooth um I am a I am a contributor to a project called uh sorry let me just go to this one called Ms build SDK SQL point that you can build in Visual Studio where which already includes all these rules and it's very easy to add your own rules as well uh and you don't need to go to any non uh read only folders and place stuff if you're using this SDK cool awesome well Eric thanks so much for coming on the show personally I learned a lot I think our viewers probably did too uh a lot of awesome references you referenced today we'll make sure we get all of those in the description for our viewers um if you like this episode go ahead give it a like and leave us a comment and let us know what you think of this capability and we hope to see you next time on day to exposed [Music]