 Hello, everyone. Welcome back to Azure SQL Mastery, and today I will show you how to install Azure Data Studio in your machine. Before that, let's first understand what is Azure Data Studio. Azure Data Studio is a cross-platform database tool for any data professional, like a developer DBA, Power BI developer, data engineer, or any developer who works directly or indirectly on a SQL server. Azure Data Studio has many features like code snippet, rich intelligence, rich query editors, as well as you can create your own server level as well as the database level dashboard. So let's first download the Azure Data Studio and install step-by-step. Okay, so first open the Chrome browser and type Azure Data Studio download and go to the Microsoft website and download the user installer. Yes, so download completed. So right click on the file, show in a folder, right click, and click on a run as administrator, accept the license agreement, click on next, click on next, click on next, create a desktop icon. If you want to register the Azure Data Studio editor for support of file, you can register it or skip it, click on next, and click on install button, and click on finish. So congratulations. Now you have successfully installed the Azure Data Studio. Now, first let's connect your SQL server inside the Azure Data Studio. So for that, go to the connection window, go to the server and click on a new connection and type your server name. So my server name is SQL Brain Box and click on a connect button. So once you click a connect button, by default it will open a server level dashboard. So you can customize this dashboard as well. Okay, so let's first connect and click on a new query. So this is a query window like a SSMS, SQL Server Management Studio, and here you can select your database. So I'm going to select the database adventure work and type the query select star from sales dot sales territory and click on a run button. Okay, so now you can run any query using this query editor. So this is a query editor window where you can write the query. This is a server group. The purpose of the server group, basically you can divide your different different environment using this group. So for example, right now, this SQL Brain Box is my development server. Okay. And I have many servers, right as a developer or a DBA, you can connect every day you connect many instances. Okay, so to easily identify either it's a development server or a production server, you can use this server group option. So let me create my group. So first I'm going to create a development group and I can give a color. So development group, it means I'm okay with the green color because you can run any query. Now I'm going to move this server to the development group. So just drag and drop my this SQL brain box instance as a tag as a inside the development. Similarly, you can create any number of groups like say production group and give the color. And you can you can map the production server. So you can create a n number of groups using this using the server group. Now let's go to the query window again and then this query. So the best part of Azure Data Studio is you can get a quick insight of your data using this menu. Let's say you want to save this result as a CSV, then click on this button. Your data will be stored as a CSV. If you want to save this data as Excel, you click on Excel. If you want to save a data as a JSON, so click on this menu. It will automatically create the JSON file similarly to the XML. And the best part is that you can base you can understand your data based on your chart. So you can quickly get all the insight of the data using this menus. Now let's understand some more intelligence. So if you see the intelligence are so good compared to SSMS. Now there are inbuilt code snippets are there which are also very useful in this Azure Data Studio. So for example, you want to create a table, right? So you have to just type SQL create table create database. So SQL create database SQL create index SQL stored procedures. And here is a table so SQL create table. Once you tab it, the entire syntax will be available for you. And now you have to just give the name. So let's say my table name is product and the schema is DBO. So using this code snippet, you can avoid the syntax error and it will definitely help to speed up your development work. Similarly, you can create a store procedure. So for example, create a table. Now let's create a store procedure. Okay, so using this template, you can definitely speed up your development work and avoid the syntax error. Okay, so I hope you like this video. In the next video, we will see how to backup and restore using the Azure Data Studio. So thank you very much. See you in the next video. Hello everyone, welcome back to Azure SQL Mastery and today I will show you how to do database backup and restore in Azure Data Studio. So open the Azure Data Studio and create a connection or connect your local instance. So once you connect the local instance, go to the database and here I'm going to take the database backup of Adventure Work. So right click on Adventure Work database and click on a backup. You can change the path. So here I'm going to take the database backup on a database backup folder. Give the name. Also here you can set the compression. So here I'm going to do a compressed backup. Also you can set the encryption if you want or you can also change the media setting. So here I'm going to go with the overwrite all existing backup set and you also check the reliability. So I go with the verify the backup and finish. Also you can set the retention or retention of your database backup. So I'm not going to set here. Okay, and click on a backup. So here you can see all activities detail. Okay, just now created the database backup. Now let's restore the database backups. So go to the instance and I click on a manage. Once you click on manage, the database dashboard will be available and here there is a restore button. So you click on a restore button and here I'm going to restore from the file. So select the backup file, give the path name, give the database name. So here I'm going to give the database name new today and click on restore button. So here you can see the restore database activities in progress. Now restore database activities succeed. Let's refresh it. So now the adventure work database 2017 today is available. So I hope you like this video. See you in the next video. Hello everyone. Welcome back to Azure SQL Mastery. And in this video, I will show you how to create a dashboard inside the Azure Data Studio. By default Azure Data Studio provide a two types of dashboard or database level dashboard and a server level dashboard to monitor the servers as well as database. So to check the server level dashboard, you have to connect your instance and right click and click on a manage. It will open the server level dashboard where you will get a database size level information as well as the backup status. Okay. And if you want to check the database level dashboard, you have to select any of the database right click and click on a manage. So where you will get a database level dashboard. Now the server level dashboard is the information about the servers. And the base part of this dashboard is that you can customize this dashboard based on your requirement. So for example, let's say every time when you connect the instance and if you want to check the inside of your server based on your requirement. For example, you want a number of connection inside the server CPU utilization by each database or a number of logins inside the server or a database size inside the server. Total number of database inside the server. So you can configure each and every information in your dashboard. So every time when you connect the your instance, you will get a quick insight of your server without doing any query. And similarly for the database level dashboard, you will configure a database level information. For example, top 10 consuming CPU consuming query or top 10 memory consuming query or a number of blockings inside the query or missing index detail or unused index details or types of index fragmentation level. Right. So you can configure the similar information in a database level dashboard. So whenever you connect the database and check the dashboard, you will get a database level information very quickly without typing any query. Okay, so let's first customize the server level dashboard. So for that, you have to type control shift P and open the user setting. Okay, so before that to save a time, I have created a two queries. Let me quickly show you. So open the file server level. So I have here I have created two queries number of logins in my server. So when I run this query, I will get a total number of logins inside my server. And the second query is number of CPU utilization by database. So when I run this query, I will get information about the data, how much CPU utilized by each database. Okay, so now let's customize our server level dashboard. So go to the, so click a control shift P and open the user setting. And in this search text box type a dashboard. Okay, so now when you type a dashboard, you will get a dashboard database property dashboard database tab. So first three tabs are database level and second three are a server level. So here we are customized a server level dashboard. So go to the dashboard server widgets, click a edit setting JSON file. Now you are in the JSON file. So let's customize it. So, so let's go with the first name. So I want to give a name number of logins. Okay, then grid item config and set the X and Y. So I'm going to set here one, let's go two by two and size Y two. Okay, after that, let's configure the widget. And inside the widget, I'm going to select the inside widget. And here I will give my file name. Okay, so let's first format this JSON file. So right click and click on a format documents. Okay, so before give a query file name, let's first select the type of the information you want to show. So here I want to show the information in a table format like this. This is not table format, this is a graph format, but I want to show the information like a table format. So select a type and type a table. Okay, and I will give my query file location. So save the file. Again, click on instance name, right click and click on manage. So you will get a number of logins information. So here now you customize your server level dashboard. So the default setting will not be available. So now you want a default setting as well as. So let's go to the JSON file again and copy this code and type, let's say backup history. Right, so backup history and here I'm going to remove this code and what I want, I want a backup. So backup history server inside. Let's format it. Okay, save the file. I think some missing. Let's say format. Okay, so now let's open it again a dashboard. Let's close the dashboard and right click and click on manage. So now you will get a backup history. Okay, similarly, you will get a size of the database. So let's go with select the code, copy the code, sorry, paste it. And here I want a database level size. So all the database size server inside. So let's select it. Close the dashboard. Right click click on manage. So here you will get a. Okay, let's change the name first. So here the database database size. And this is a backup history. This is a number of logins. Right click on manage. You will see the database size. Now, so similarly, you can customize a dashboard based on your requirement. So right now the number of logins backup backup history. Database size. Now there is one more. Let's do it the CPU utilization by each database. So I'm again copy the code. And here I will give the CPU utilize by database and give the database file name. So my open the file. Give the name. Okay, save this JSON file. Refresh the dashboard. Okay, let's close it and connect again. So you will get a CPU utilize by database. So here you will get other database name, CPU time and the CPU percentage. Okay, so now let's say if you want any query. Let's say for example, number of logins. Now I want a query inside this table or so click on three dots. Click on a runner query. You will get a query used by this dashboard. Okay, this is a server level dashboard. Let's configure the database level dashboard. So for that, go to the any database and click on manage. So this is a database level dashboard. So by default you are getting a facility. You can object or you can search your object using this search window. So for example, department, so you can search it. Okay, but now I want more details in this dashboard. Let's say for example, top 10 queries, CPU consuming query or top 10 memory consuming query or missing index detail or unused index detail or total tables count or a number of object details. Let's say for example, number of tables or let's say active connection detail. You will configure a database level dashboard. Okay, so for that, again, type a control shift P open the user setting and go to this time you have to select the database visits, edit a JSON file. Okay, so by default, there is an object search dashboard is there. Okay, now you want to configure your own customers dashboard. For example, top 10 memory utilize query or top 10 CPU can expensive query. Okay. So for that, let's do it. So let's say expensive queries by CPU. Okay, I'm not going to change this code. But here I'm going to select the inside visit and here I'm going to go with the type. So here I'm selecting to type a table. Again, I'm going to type a query file and my query file is available in this location. So let's select the location. So, so I have already created a query to save the time. So let's give the path, give the file name dot SQL. Okay, save the JSON file, right click any database, click on a manage. So here you will see now I will get an expensive query by CPU. Okay, or you can say CPU utilize query. So you will get this information whenever you select the database and click on a manage. So using this dashboard, you will quickly get inside of your database. You do not need to write any query. You will easily get information whenever you want and you will get you will get this information quickly. So no need to remember the queries. You will whatever you want, you can customize in your dashboard to save the time. Okay, so let's now create one more tab for the CPU utilize query. This is sorry memory utilize query, save the JSON file, click a database click on a manage. So now here right now I don't have a query, which is taking a more memory. So right now there is no result to show. Okay, so similarly you can customize your dashboard based on your requirement. My intention to show this demo. So you will customize your dashboard and you will quickly get inside of your database as well as a server based on your requirement. I hope you like this video. See you in the next video. Thank you. Hello, everyone. Welcome back to Azure SQL Mastery. And in this video, I will show you how you can create a code snippet to generate a quick SQL code without any syntax error as well as how you can create your own custom code snippet. So let's get started. Open the Azure data studio. So here we are going to perform of five steps using the code snippet. First, we are going to create the database. Second, we are going to create the table. Third, we are going to add a column in an existing table. Fourth, we are going to insert a data. And fifth, we are going to create our own custom code snippet. Okay, so let's take an example. You are a new in a SQL server and you want to create a database using the SQL code. So for that, you can use a code snippet. Now in Azure data studio, all the code snippet start with the SQL. So first step, I want to create a database type SQL and create database. So now the ready made code is available for you. You have to just replace a database name with the actual name. So I want to create the database code snippet. So type code snippet. Okay, and select the code and click a run button. Okay, so the command completed successfully. Let's refresh the database folder. Now so the database is now available. Now I want to create a table inside this database. So right click new query and for creating a new table, the ready made code snippet is SQL create table. Okay, so this is the M table. SQL create table. Okay, so again, you have to just replace a table name with actual table name. So here I want to create a table name employee. So type employee and now give the actual column name. So first column is name and the second column is address. Select the code and click on a run button. So commands completed successfully. Let's refresh the table folder. So employee table got created. Now I want to add a column in an existing table means in employee table. So for that, the course snippet is already available. So type SQL add column. Now replace this new column name with actual column name. So I want to add a mobile and a table name. So table name is employee. Select the code and run. Let's quickly check. Select start from employee. So now the mobile column is exist. Now let's say I want to insert a data inside this employee table. So for that type SQL insert table. Okay, now just replace the table name with actual name. So my employee and column name. So you have to give actual column name. So ID, name, address, mobile and actual data. So 1, India, mobile number. And here I want to insert only one row. So I am going to remove this. Select the code and run. So the one row affected. Let's quickly check. Okay, so now we have successfully insert the data in employee table. So we have completed four steps using the SQL course snippet. And till now all the course snippets are available in the Azure Data Studio. Now you have to create your own course snippet. So in the SQL Server 2016, we have a facility that we can drop any object using this if-exist statement. It means that if the table is exist, then it will drop. Otherwise, it will not throw any error. Because let's say I just give you a small example. In my course snippet database, salary table is not exist. Now I want to drop table salary. So when I run this command, it will give the error. Cannot drop the salary because it does not exist. Or you do not have a permission. But if I use a new syntax, it will not throw the error. Okay, now I want to create my own code snippet using this syntax. So for that, you have to type control shift P and type user snippet. So open the user, configure the user snippet. Click and type SQL. Okay, now here you want to write the code. So let's do it. So here again, I am going to start with the SQL. So SQL drop if exist. Let me copy the code. And here there are three parameters prefix, description and body. So let me first set the prefix. So SQL drop if exist. Then I want to set the body. Inside the body, I want to set my code. So what is my code? My code is this. Okay, so let me add it first. Okay, so first drop table. Okay, so here I want to drop any object, any object type. It might it might be a table or view or function or a database. So here my first parameter is object type. So dollar one. So dollar one is my first parameter and type object type. And if exist, here you have to give the actual object name. So this one is my second parameter. So let's do it. So dollar two and object name. Okay, now here you want to replace the actual name with the parameter. The first parameter is dollar one and second parameter is dollar two. Give that. Okay, sorry, this is a body and this is a description. Okay, so inside the description, just give the name. You can drop any database object with new SQL 2016 syntax. Save the file and format it. Close the file and now let's quickly check. So control N and here first select the database. So database is code snippet and type the code snippet SQL drop if exist. Great. Now here you have to just replace the name. So my object type. So here I want to drop the table employee. So my object type is table and my object name is employee. Select the code and run. So command completed successfully. Let's refresh the folder. So now no more table is exist. Similarly, let's say I want to drop my database or I want to drop a view. So you can just replace the object type with actual object type and object name with actual table or view name. Okay, similarly, let's say if you want to drop a database. So in object type is my database and object name is actual database name. So my database name is code snippet. Okay, now you have to add one more syntax while drop the database use master. Let me close all the query first. Run. Command completed successfully. Let's refresh the database folder. Now the code snippet database is no more exist in my server. So I hope you like this video. See you in the next video. Hello, everyone. Welcome back to Azure SQL mastery. And in this video, I will show you how you can customize your workspace based on your requirement. For example, you want to change the color of this workspace or you want to change the font size or you want to change the keyboard shortcut or you want to work in a Zen mode. Then you can customize entire workspace based on your preferences. So let's get started. First, I want to change the color of this my workspace for that you have to type control shift P. And type color theme. So you can change the color. So let's say right now I'm in the light azure data studio. You can go with the any theme based on your likes. Okay, so I always work in a light azure data studio. I have seen most of the developer prefers a dark azure data studio. Now I want to change the font size. How you can do it? So for that, again, type the control shift P and type open user settings here. There are a lot of options are there. Okay, but I especially am looking for a font. So let's type font. Let me slit the window. Okay, great. Now, let me change the font. Let's say right now the font size is 35. I want to go with 50 and see here. Okay, no, let's say I want to go with the 25. So based on your preference again, you can change the font size. Similarly, you can change the cursor style. So right now by default, it's a line. Okay, but if you want to change your cursor style, then you go here and change based on your preference. So block or maybe block outline. So I always go with the default setting, but it's again, it's up to you. Okay, now I want to change the keyboard setting for that, you have to type control shift P and type keyboard settings. So open keyboard shortcuts. Okay, now here for the cancel query, right now the shortcut is shift A. So I want to change with the shift C, then you can just double click on the line and type whatever the shortcut you want. So I want to shift C and click on enter. So now any running query, I want to cancel it with a keyboard shortcut. I have to type shift C. So let's do it. So here there is one query and I want to run this query 1000 times. So I am starting it and now I want to cancel. I am going to type shift C. So now it's cancel. Okay, so now this keyboard shortcuts again, you can change based on your preference. So I hope you like this video. See you in the next video. Hello everyone. Welcome back to Azure SQL Mastery. And in this video, I will show you how to use extension to add more functionality in Azure Data Studio. So let's take an example. You are a SQL Server DBA and you want to create a jobs in Azure Data Studio. Or you want to troubleshoot a SQL problem using the SQL Profiler. Or you want to generate a script for your database object. Or you want to create a deckback and backpack file for your database. Or you want to import a text file or a CSV file to database. Now there is no direct facility is available in Azure Data Studio. So if you go here and right click, sorry, right click. So only we have a few options compared to SSMS. Okay, so if you compare, if you check the in a SSMS, we have a multiple folders, database, security, server object, applications, management, integration service, catalog, SQL Server agent. Right, but in Azure Data Studio, we have only three options, database, security and server object. Okay, so in SSMS, using the SQL Server agent, you can schedule a job. Okay, using the database option, you can generate a script for your database. Or you can create a deckback file or backpack file, or you can import and export files. But in Azure Data Studio, only you have a limited features. Okay, now as a DBA, you want to achieve the same functionality in Azure Data Studio. Because the SSMS is available only for the windows, but the Azure Data Studio is available for macro user, windows user, as well as the Linux user, because it's a cross platform tool. So now you can achieve through marketplace and you can search extension based on your requirement. So for example, the first recommended tool, so there are total 56 extension is there, but it will keep growing every day. Okay, so let's take an example, the admin pack for SQL Server. So using this extension, you can achieve a full requirement for server agent, where you can schedule a job, SQL Server deckback file where you can create a deckback backpack file, or SQL Server import where you can import a CSV and text file, SQL Server profiler where you can troubleshoot as your SQL problem. So let's install this extension. So to install our extension, just click on an install button. So now this extension is installed successfully. Let's quickly check. So go to the connection, right click and click on manage. So when you click on manage, it will open a server level dashboard. And here you can see in the administration section, there is a SQL agent where you can schedule a job through Azure Data Studio. Similarly, let's check the other functionality. Select the database folder. Click on any of these database and right click. You see now we have two more features are added. Import wizard and a data tier applications. So data tier applications on use for the deckback and backpack file and import wizard is useful when you want to import your CSV and text file. Now, okay, here also you see there is one more feature is added called a launch profiler where you can troubleshoot your SQL Server problem. So launch profile is in SSMS we have a SQL Server Profiler. It's the same. Now let's install one more extension called a database administration tool extension for Windows user. This extension is basically used to generate a script. To generate a script of your database object. Okay, so now the extension is installed. Let's quickly check right click, select a database and right click and generate a script. So here now using this feature, you can select your database object and you can generate a script. This is a similar to the SSMS generate script feature. Okay, now there are many more extension that total 56 extension are there based on your requirement you can install it. For example, manage instance dashboard where you can manage your Azure database. PowerShell command. So if you want to run your PowerShell command through Azure Data Studio, you can add this extension. Thank you very much and see you in the next video. I hope you will add more and more features in Azure Data Studio using this marketplace. See you in the next video. Hello everyone. Welcome back to Azure SQL Mastery. And in this video, I will show you how you can use Zupiter notebook. Zupiter SQL notebook is an open source database application that allows you to create and share the document containing the code, equation, visualization and narrative text. Also, you can create a single file which contains a code as well as the result set and that file you can share to your team member for the reference. So let's get started. Open the Azure Data Studio and click on a create notebook. And in the inside this notebook, I am going to perform some DML operation. Here there are two sections. First is a text cell and second is a code cell. So in the text cell, I am going to add the text. So how to run select query? Add a paragraph. And here I am going to add a code. Let me complete the connection. Select start from Cells.Cells territory and I am going to run this select statement. Because I want this result set inside my document. Let me add one more text. How to delete the data from table? Let me add the code text here again. Code cell and delete from table where territory ID equal to 1. Again I want result set inside this document. Now let me add one more text. How to create a copy of that table? Copy of existing table. Let me add a code cell. Select start from Cells.Cells territory. And let me create a similar copy. And I want result inside this document. Isn't it? So result is available. So similarly you can create a entire document, useful document or you can create a document based on your requirement. For example, you have a specific business logic. So you can create an entire document for your business logic and you can share to a team member for future reference. So now I am going to save this file. Once you save the file, you can open the existing notebook from here. So inside the notebooks, open the folder and here inside this folder I have three notebooks. So just now we have created this notebook. So anytime you can open the notebook and you can run it. So I hope you now understand the concept of notebook. And this notebook is very useful where you can define your entire business logic. You can explain entire your database structure. Also it's a very useful when you have to share some knowledge to your team member. So I hope you like this video. See you in the next video. Thank you.