Design and implement data storage - Overview on Transact-SQL

1. Section Introduction

Hi and welcome to the section in which I just have an overview of the basic SQL commands. This is when it comes to transactSQL in my SQL Server. Now obviously, if you are well versed in SQL-based commands, then you can skip this section and move on to the next, wherein we'll be looking at SQL data warehouses. The main reason I'm having this section in place is for those students who might not be familiar with SQL-based commands. So the basic commands in SQL when it comes to select, the Where clause, the group by clause, the join clause, all are very important when you work with a SQL data warehouse when it comes to Azure Synapse. That's why I don't want to make any assumptions when it comes to what a student knows. So I said, if you're already familiar with SQL-based commands, yes, you do have the option of skipping this particular section. But then if you really want to just go through the commands, it's just a quick section, so you can take this section and then move on to Azure Synapse. Another main reason why I am going through these TSQL commands is that it is important to also understand how these commands actually work in terms of performance when it comes to Azure Synapse. So when it comes to deciding the types of tables that you create in Azure Synapses, for example, hash distribute tables, your replicatetables, your round robin tables. So designing your tables also depends on the type of queries that you're actually going to fire against your data. That's why the basis of understanding sequel commands is very important. That's why I had a section based on this. So let's move ahead.

2. The internals of a database engine

Now, in the next chapter, we are going to create a new SQL database. In the prior chapters we have seen how to create a sequel database that is hosted on a sequel database server. We'll create another SQL database on that same SQL database server. Before we go ahead, just some very important points. So, a database is nothing but a collection of tables. In the tables we have data, we have otheraspects which are also available as part of the SQL database, such as views, stored procedures, etc. So the tables in the underlying database are used for storing structured data. You can then fire queries against the SQL database. The underlying Sequel Database Engine is responsible for taking your queries and giving you the desired results.

So, for example, if you want to go ahead and fetch rows from an underlying table in the underlying database, the Database Engine will then process the operation, fetch the rows, and then return them back to you. When a database engine is used, the server itself works with the underlying database. One very important thing to remember is that, in the end, your data is stored on underlying disks and on underlying storage. For you, everything is represented as a table that has structured data. Remember that your data is stored on underlying physical disks. So when the Database Engine needs to work with data, needs to, let's say, fetch data, and then return it back to you, the Database Engine, let's say in this case, Microsoft SQL Server, needs to go onto the underlying storage device, fetch that data, and then give it to you in the format that you desire. Now, why am I bringing this up? This is very important to note when you go onto Azure Synapse.

When we look at a SQL data warehouse, understanding this basic concept that your underlying data is stored on underlying physical infrastructure is very important because, over there, a SQL data warehouse is used for storing terabytes of data or even petabytes of data. And all of the data, remember, in the end, is on disc, on underlying physical infrastructure, on underlying storage devices. So the engine, which is responsible for processing the request that you fire against the SQL data warehouse, needs to be efficient enough to ensure that it can go through all of the data on the underlying storage devices and then give you the desired results. That's actually the power of a SQL data warehouse—its ability to actually process large amounts of data. So I said to you, in the end, when it comes to structured data, when it comes to a structured SQL database, everything for you is in the form of tables, in the form of columns, in the form of rows. And that's because the underlying database engine is doing all of that work in the background to give you that sort of representation of data. That is, in the end, all of the data resides on the underlying physical disks, on the underlying physical infrastructure. Right. With this point in mind, let's move on to the next chapter.

3. Lab - Setting up a new Azure SQL database

So here we are back on the Azure Portal. Now, in all resources, I'll hit on Create. So I want to create a new SQL database. I'll go ahead with the SQL database option. I'll choose my resource group, Data GRP. Yeah, I'll give a database name now in terms of the server, so I want to choose the same server. So I have my server in the north, your application, that's part of the Data GRP group. This server was something that I created early on when I was actually practicing building this course. So we're going to choose the server which we have been using as part of this course. So I'll go ahead and choose that. Now here, when it comes to compute and storage, I'll click on Configure Database. I want to ensure that I choose a cost-effective option. So I'll choose standard again. I'll reduce the size of the underlying database. I'll hit on apply. I'll go on to Next for networking. I'll leave everything as it is. Go on to Security. Leave everything as it is.

I'll go on to additional settings. And over here, in terms of using existing data, I'll choose Sample. So this particular configuration will now create a sample database. So there's a sample database known as Adventure Works, and this database will now be installed. The data will be in place in the form of tables, and it will be part of this new database, a new DB. And then we can work in the subsequent labs. When it comes to the T SQL statements we'll be seeing when working with the tables in the underlying database, I'll go on to Next to Tags. I'll go on review and create. And finally, let me go ahead and hit Create. Now, this might take just around 3 to 4 minutes. Let's come back once we have the database in place. Now, once the database is in place, you can go ahead on the resource. So remember, this database is on our same database server, which I created earlier on. Now, in SQL Server Management Studio, I can connect to the same database server, and I should see both of my databases. I should see App DB, and I should see the new DB as well. So if I go on to databases, I can see a new DB over here. If I go ahead and expand my tables, I can see different tables already over here. So we have information about customers, information about products, information about the product model, etc. So this is part of the sample data that gets installed as part of the Adventure Works database. And if you want to play around with data, this is a good option to start with.

4. Lab - T-SQL - SELECT clause

Now in this chapter, we'll just quickly go through the select statement. Now this idea that I have is Visual Studio code, so we're not going to be running any code from here. This is to give you a good representation of what I want to show you in terms of the sequel statements. If you want to download Visual Studio Code, I'll keep the link as a reference for this chapter. Again, it's a freely available tool. So the select statement is used to protect the columns that you want to see. So, for example, if I issue this particular statement, select Star from Sales Product. So Product is the table name, and SalesLT is the schema name. If I go ahead and take the statement, if I go on to New DB, let me right click and issue a new query. I'll paste in my statement. I'll hit on execute. Here you will see all of the information that is present in the Sales Ltdot product table. Here you can see all of the columns that are being displayed to you. If you want to see only certain select columns from that particular table, you can specify the names of the columns that you want to see.

This is the production when it comes to the select statement. So I can go ahead and just highlight this and hit on Execute. And I'm only seeing information from those three rows. Next, if you just want to see the number of rows in a particular table, you can do Account Star and hit on Execute. And here you can see the number of rows in the particular table. If you want to see, there's no column name, so you can actually go ahead and give alias to the column name, so you can say Has Count and hit on Execute. And here you can see a name given in the TOTH column when it comes to a SQL data warehouse. So I'm going to be making a reference to SQL data warehouse very often when we go through these T SQL commands because it's very important. So when you're looking at the columns that you need to return as part of your query, this is also important when it comes to a SQL data warehouse. So, when SQL data warehouse stores data on the underlying disk, it does so in a column wise format. And the reason for this is that it actually increases the efficiency of the queries that are fired against the Sequel data warehouse, right? So in this chapter, you just want to go to the select clause which is available as part of your sequel statement.

5. Lab - T-SQL - WHERE clause

Let's go through our next SQL file. So this is the where clause. So the where clause is used to extract only those records that fulfil a specified condition. So over here, let's say I want to select the product ID, the name, and the product number. So only certain columns from the sales Lt dot product table where the product ID is equal to 680 will be displayed. So I only want a particular row or a set of rows wherein the product ID is equal to the value of 680. So let me go ahead and paste it over here, hit execute. So I'm only getting this particular row of data. I have another statement over here. I want now to get all of the rows where the product ID is greater than 680. So I can go ahead and just change it over here, hit execute. And now I'm getting all of the rows where the perk ID is greater than 680. Next, I want to fetch the rows where the weather pub ID is between a range of values. Over here, I'll go ahead and hit execute, and we'll get the desired result. And finally, where is the name? Now I'm looking at the name where it's actually equal to a string which contains the keyword "silver."

So over here, the person means that we can have a number of characters before silver and a number of characters after silver. So let's go ahead and also execute this so we can see all of the desired rows. So again, when we are making a reference to the SQL data warehouse, when we use the where clause, when it comes to queries against the SQL data warehouse, we should be using something known as partitions in the SQL data warehouse to ensure that we increase the efficiency of queries which are doing filtering. So when you're adding the where clause, you're basically filtering the results. You want only results based on a certain condition. If you want to increase the efficiency of the queries that are fired when you have these filtering conditions, then you have to ensure that you use something known as partitions. And obviously, this is something that we are going to look at when we look at the SQL data warehouse.

Study with ExamSnap to prepare for Microsoft Certified: Azure Data Engineer Associate Practice Test Questions and Answers, Study Guide, and a comprehensive Video Training Course. Powered by the popular VCE format, Microsoft Certified: Azure Data Engineer Associate Certification Exam Dumps compiled by the industry experts to make sure that you get verified answers. Our Product team ensures that our exams provide Microsoft Certified: Azure Data Engineer Associate Practice Test Questions & Exam Dumps that are up-to-date.