Gain in-depth knowledge for passing your exam with Exam-Labs DP-300: Administering Microsoft Azure SQL Solutions certification video training course. The most trusted and reliable name for studying and passing with VCE files which include Microsoft Azure Database DP-300 practice test questions and answers, study guide and exam practice test questions. Unlike any other DP-300: Administering Microsoft Azure SQL Solutions video training course for your certification exam.

Optimize Query Performance

1. 69. validate data types defined for columns

Hello. Now we've got that review of the select statements out of the way. We're going to look at this section about how to optimise query performance. So part of that is about normalising tables. We'll have a look at that in the next three videos and then have a look at the resulting execution plans. But in this video, I want to look at data types that are defined for columns. So each of these has a particular data type, and if we expand the table and go into columns, you can see the data type we've got here: int, NVAR, char, unique identifier, and date and time. So what are the data types that you can have? Well, this PDF is included as a resource in the Resource Lecture near the beginning of this course. First and foremost, we obtained precise figures. So we ended up with big int, small intimate int, and bit. So each of these is 256 times the size of the previous one. So tiny in can have 256 values, not 255, whereas bit can have only zero or one smallint, and big in can have negative numbers. As you can see, the small integer goes to 32,767, the int goes to around 2 billion, and the big integer follows. However, there is a price to be paid in additional storage costs. So additional storage costs mean that you fit less on a page of data. So you should always try to minimise storage costs as much as you can. You've also got decimal, which is the same as numeric. So we've got two values in here, the precision, which is the number of digits, and the scale, which is the number of decimal places. So decimal brackets of nine commas and five digits allow for nine digits, of which five are after the decimal place separator. So for instance, the number 123-45-6789 would be an example of a numeric. Nine plus five. There are up to nine digits, of which five are after the decimal place. Simply the number 10 is also anexample of decimal nine comma five. You don't have to use all of the digits and decimal places. You've also got these types, money and small money. And they are nothing more than bigint and int divided by 10,000. So you can go to four decimal places, and small money allows you to go to around $214,000, and money allows you to go to some big figures. So these are the exact or approximate numbers. These won't store your values precisely. You have flat, which I have stored in the seven-digit or fifteen-digit range. And then you've got reality. But real is just another name for flot with seven-digit precision. So float brackets 24 provide seven digits of precision, while float bracket 52 provides fifteen digits of precision. So you could also say float bracket one, but I wouldn't bother. It takes the same amount of storage. If you're wondering what that number is, that number is the number of bits. So there are eight bits in a bite. A bite will get you up to 256. So you can see, with 24 bits, you can get up to the $9,999,000 mark. But these are approximate numerics, so they could be better for storage space, but they will only give you the number approximately. So if you have a number 123-45-6789 and you store it as floor 24, then you might get 12345-6, and anything after that is just approximate. So it's good for storing really large or really small values when you don't need to know exactly what they are. date and time. We've got lots of different examples here. date, so it only stores the date without the time. So you only have three bytes needed for that. You've also got a similar one, which is Time, which stores up to seven decimal places. So you specify the number of decimal places you want saved. So in time brackets, one will store just one decimal place. You can see the number of items needed is variabledepending on how many decimal places you want date time,this stores up to 1300 of a second. So the third decimal place will either be a three, seven, or zero. And it starts with the adoption of the Gogarian calendar in the United Kingdom. So, that came in September 1752. So this starts in 1753, and it's only eight bytes of binary date time two that stores up to seven decimal places and is a bit more efficient than date time. Then add a date and time offset to indicate where you are in the world. For instance, maybe you're in the United States in the winter. So that is 5 hours behind GMT, or UTC. And finally, we've got small day time, which doesn't store any seconds; it just starts to the nearest minute. But again, you get some good storage requirements. There are only four bytes. We've got character strings. Char will store as many characters as you enter. Char brackets 20 will therefore store 20 characters. Not up to 20 characters; it will store 20 characters. So, if you have a chart with 20 characters and add two characters to the string, it will still take 20 characters. If you want it to be more representative of what you store, then you can use the chart, which stores the number of characters it needs plus two additional bites. Now these are character strings, so they are based on standard character sets in Europe. If you want to include characters from outside of Europe, then you'll probably need Unicorn character strings, which are prefixed with an N. So we have Nchar and NV char. And by the way, if we go back to here, if I say select high, that is a character string, but if I say select capital Nhigh, then that denotes it as a Unicorn string. So it's a capital N. Outside of the single quotation marks, we've also got text and NXT. They are deprecated and we advise you not to use them. They will be removed in a later version of SQL Server. Instead, you have varcharmax, which you could store an essay in. There are 2 billion characters and the same NvArMax when you are starting binary strings. So this could be anything; it doesn't have to be just characters; you could load a text file, or any type of file or picture file, in and store it in binary and VAR binary, and there are a few other things that have unique identifiers. So that's when you're storing Guiduid. So if you see something that's really long with Hexadecimal, then that would be a unique identifier or good.You also have XML for XML data. I don't think we'll be using much of that in this course, and you've got a few other data types. If you've taken previous SQL Server courses, you'll recognise all of these as nothing new. If, however, some of these are new, then this PDF is available as a download in the resource lecture near the beginning of this course, and I advise you to have a look at it because these terminologies will come up quite often whenever we look at columns. So now you know that NVA Charge 60 can be up to 60 unicorn characters, for instance, but I won't be commenting depending on what they are or just expecting you to be able to recognise them and understand the sort of things that they contain. So that is the data type that you can use for columns.

2. 66. identify data quality issues with duplication of data

Hello. And in this section, we're going to just go away from actually using SQL Server and think about how we can create better databases. And we can do this using a process called normalization. So we can to start off with a tablewhich is not really well designed, and then wecan improve and improve it going through all ofthese first normal forms, second normal form, third normalform, and so on until we get to anoptimal version of the table structure that we need. So I'm going to start off with a not-good table structure. And this is it. And so far, it's not too bad. We've got people's names of John,Margaret, and Albert and teachers. So you can see that both John and Albert are being taught by Mr. Y, and Margaret is being taught by Mrs. G. So now we need teacher's sage, the next column I've invented. So the teacher's age is 52 for Mr. Y and 36 for Mrs. G. Now what are they going to study? Well, John is going to study physics. Margaret is going to study chemistry, and Albert is going to study biology. So you'll notice that even though they've got the same teacher, they're actually taking different classes so far, so I'm okay with this table. But I've just heard John is going to take a second class with Mr. Y. John is going to take some computing. Okay, so I will—how am I going to add computing into this? Well, there are two possible ways because I'm teaching bad table design, and I'm going to have one possible way. I'm going to call this class one. And I'm then going to have another class, "class two," another column. and here we're going to have computing. So we've stored the data that we need. I've just heard that John is going to take a third class. Okay, well, we obviously need a third column. So this is class three, and this is going to be art. Now, the alternate way, well, we have one column to start with, so why not put everything into that one column? So we'll have computing, and we'll have art. So hopefully you can see some of the difficulties that I'm having with this poor table design. In the next video, we'll start by having a look at the first normal form and then have a look at some of the other forms and try to actually make this better. But why do we want to make this better? What is in it for us? Well, the first thing is to minimise duplicate data. So you can see that Mr. Y is 52. But we got that in twice. We don't need it twice. So maybe we also want to store other attributes of the teacher. For instance, his address I would then have to put that in several times. The second reason is to reduce data modification issues. Well, what do Nerf do I mean by that? Well, it's Mr. WY's birthday. Happy birthday, Mr. Wise. So I'm now going to go to John's entry, and I'm going to give him a teacher age now of 53. Okay, so now Mr. Y is both 53 and 52. That's inconsistent data. I have no idea which one is correct. Or maybe there's another problem. Maybe Margaret has decided not to do the course. That's no problem. I'm going to delete. Margaret. Small problem. Now, in deleting Margaret, we've also deleted Mrs. G, who has an age of 36. There is no way. Now, we can see that from this table. So it's possible that there is a teacher who is not teaching, maybe a head teacher or something. But now that Mrs. G, who did this personal favour to teach Margaret chemistry, has done so, we've now deleted any reference to her in this table. Now, the third issue is to simplify your queries. So let's say I wanted a list of everybody who was studying computing. Well, it's where class one or class two equals computing or class three. How many columns of this do I have? How many subjects are people taking? And I would have to expand and expand it if somebody decided. I know you've got eight rooms for classes on your table, but I'm going to do a ninth class. Well, I'll have to go back to my existing queries and rewrite them. Or alternatively, I have to say, Where is classlike computing? And then if there's a separate class called the History of Computing, then that would be caught by way of a class like computing, even though I just want computing and not the History of Computing. So as you can see, it's getting really complicated to just do a very simple query. I'm having to either do lots of oresor having to do a like, but thenI might get some extraneous data retrieved. So I can use normal forms to reduce or minimise all of these problems. And in the next video, we'll have a look and start off with the first normal form.

3. 67. identify normal form of database tables - Normal Forms 1 to 3

In the previous video, we had a not-optimal table structure, and we're going to use the first normal form to get it into better shape. But what is the first normal form? Well, the first normal form was proposed by Edgar F. Cott. He's the person who first proposed the relational model. Now, his requirements for the first normal form are two fold.Firstly, the values in each column must be atomic and indivisible. So what does that mean? Well, maybe we've got somebody's name. So, John Smith Well, that's not indivisible. You are able to divide it between the first name and last name, and maybe even the middle name. And each value must contain only a single value. So in our example, we've got multiple values. Not good. So in our example here, we've got multiple values for class. Yes, we divided it up into separate columns. But really, that is exactly the same as this. Why? We have them all rolled into one. We have multiple values for classes. So let's now put this into first-person normal form. So in the first normal form, we just take each of these and put them on a separate row. So if I insert new columns and rows and say that we now have a class for physics, a class for computing, and a class for art So we still have the same information. We still have John with Mr.Y and age 52 for the teacher. But now our classes are now physics, computing, and art. And it would be the same procedure if you had this information. So again, we just add in extra rows, and we divide them at the comma. So now we have art and computing on their own. Either way, we arrive at exactly the same table. So this is now in its first normal form, but there's still a fair bit more we can do. The requirements for the second normal form are that first of all, we've already done everything in the first normal form. So we have everything that is atomic and indivisible. Each value contains only a single value. The next is to reduce repeated information. So if there is any repetitive data, then we might need to create separate tables. And we tipped the tables with a foreign key. So here you can see that we have repeated information. We have John, whose teacher is Mr. Y, who's 52. The only reason we got that repeated is because he's taking three classes with Mr. Y. And in fact, in this school, all you've got is one teacher for each individual pupil. In this school, John can't take a class with Mrs. G, for instance. So in the second normal form, we can separate these classes out into another table. So in the second normal form, we would have these classes in a separate table from the pupils' names. So now it just looks like we've just duplicated everything. So this is our pupil teacher table, and this is our pupil class table. But what we can now do isget rid of the repeated information. So we only need to know that John is with Mr. Y once. So now we know that John is with Mr. Y, who's 52 years old, for physics. John is with Mr. Y, who's 52, for computing, and the same for art. So this is now a second normal form, and these are linked together using a primary key here of people names to a foreign key of people names in the table people class. So a primary key cannot repeat a foreign key. It can and often does repeat itself; it is a one-to-many relationship. So this is getting better. But what's the next form? So if this is the second normal form, what's the third? Well, the third form—firstly, everything has to be in the second normal form. But secondly, anything that is not dependent on the primary key is to be removed from that particular table. So what do we mean by that? Well, going back to our second normal form, we see that John has Mr. Y who is 52 and Albert has Mr. Y who is 52. The fact that the teachers are 52 is not dependent on the primary key pupil's name. Instead, teacher age is dependent on the teacher. So what we need to do to get this to work properly is add another table. So this table is going to be available for teachers, and we're just going to have the teachers and the teacher ages. So we can now remove all of the teacher ages from the table pupil teacher. So now we only have two teachers in our table, who happen to be 52 and 36 years old. And we know that John is going to see Mr. Y. And Mr. Y is 52. So we can connect the dots. And now we have a primary key of a teacher going to a foreign key of a teacher here. So in our teacher table, we only have one teacher for each teacher, whereas each teacher can teach more than one pupil. So now let's go through the problems that we saw in the previous video. So if we delete Margaret, yes, we delete the fact that Margaret is being told by Mrs. G, but Mrs. G remains aged 36 in the teacher table. So that's no longer a problem. If it's Mr. WY's birthday, then we just have one raw to increase. So Mr. Y is now 53, and we don't have any inconsistencies with him being 52 and 53. And if I'm looking for everybody who does chemistry or computing, then I just need to say where class equals computing. There is no need to do where class likes computing, where class one equals computing, where class two equals computing, and so on. So that makes the third normal form very useful. And I generally, when I'm using SQL Server, go to the third normal form, except when I'm using something like a data warehouse with SQL Server Analysis Services. And when that happens, I generally go to the first Normal Form. And the reason for this is because it's a lot quicker for the computer to read this than it is to do all the joins that are here. And speed is more important for the data warehouses. And in a data warehouse, I'm not actually updating the data; it's really read only, so I'm not going to be deleting data or updating data or anything like that. However, that's only the case if you're doing a data warehouse. For every other instance, I generally go to the third normal form. So these are the most important ones. Unnormalized means anything goes, including really bad design. If you have multiple items in one column, such as someone's name having first name, middle name, and last name all at once, Normal Form would tell you to separate them out into unique columns, better columns, and better rows, and then remove anything that is duplicate in the rows. So now we have the pupils' classes and the pupils' names with their teachers. So now we only have Mr. Y in one place, and then anything that is not related to the primary key but is related to another column should again be taken out into another table. So these are the most important normal forms, and I suggest you take a look at your own databases, your own table constructions, and see if there is a way to go through these three normal forms and input, improve them for consistency, improve them for updates and deletions, and improve them for your queries.

4. 67. identify normal form of database tables - Normal Forms 4 to 5

Microsoft's website says the fourth normal form and the fifth do exist, but are rarely considered impractical designs. Disregarding these rules may result in less-than-perfect design, but shouldn't affect functionality. Quite frankly, I don't use the four from the fifth, and I don't think I need to. To be honest, I find these start getting moremore and complicated designs rather than less and less. So I always go to the Third Normal Form. So let's have a look at what the Fourth Normal Form is. So the requirements are that it should already be in third-normal form. And that, for me, is good, but it should have no multi-value dependencies. So what else does that mean? So here we have an example of a table. So we have pupils 1, 3, and 5 studying math with Mr. A and two and four studying science with Mrs. B. So it could be that we should separate the instructor out, because each instructor is not based on the pupil but on the course, and that would follow the third normal form. However, there is a problem when pupilnumber two studies math with Miss C. So it's no longer Mr. A who teaches math; it's also Mr. C. So now it's no longer separate. This is because the instructor is no longer dependent on the course because, now with math, we no longer have an idea whether it's Mr. A or Miss C. So the fourth and final form would be to separate out the relationships between pupil and course and pupil and instructor. So that would result in two separate tables,one pupil and course, one pupil and instructor. Now, for me, at the moment, we have a composite primary key. So with pupil and course, because pupil is nolonger primary key by itself, because we now havenumber two, science and number two, math. So I would have another ID. So this is just a table ID, including 12345 six.And I would repeat that in both of these tables. So that will be our primary key, instead of the pupil and course composite key. That, for me, I'm okay with; I can deal with it. I'm not a great fan of this because I think pupil, course, and instructor, with themselves in total, are the primary keys, not just a pupil course. For me now it's a pupil course instructor,because it's possible that pupil two was havingmass with missed A or with Miss C. So all of those are the primary keys. In reality, I would have this extra column, but I wouldn't separate it out into these two. I'll be much happier with theall in one particular table. Of course, I wouldn't be using Mr. A and Mrs. B as my instructors; I'd probably be using Inst. two. And then I'll be relating this instructor ID to an instructor name elsewhere, so we can have title-surname, so that's Mrsabc So this is how I would actually be doing my table, rather than going down this fourth normal form route. And I'll probably also give the course an idea as well. So maybe these are the same math course, or maybe these are different math courses. I have no idea. Why would they be taught by different people if they're the same? It's possible, but it's possible that it's completely different. So this is how I would do it. Rather than using this as my result, the fifth normal form goes one stage further. And there are some people who say, "Well, if it's ordering four, it's very rare for there to be any violation of the fifth normal form." So it says I have no join dependencies. So here we have an example. Mrs. A sells fridges and freezers from Companies One and Two. Mr. B only sells refrigerators manufactured by Company Two. The fifth normal form would mean that I would be separating out the person and company, person and item, and company and item. And the advantage of this is that, at the moment, I have duplication. I've got company One in twice, company Two in three times, a fridge in three times, and a freezer in twice. So, five rows in total, whereas each of these tables is no more than four rows. So it does reduce the amount of information. However, for me, yes, you can deduce that Mr. B would be selling fridges from Company Two, and Company Two does sell fridges. However, this is going way too far for me. I would much prefer it to be in a form closer to this. Again, I would use company ID and item IDs rather than actually listing the items. But this is what the fifth form is all about. It reduces the complexity of any joins. So we have a company and an item and a person and an item and a company. So there were your free joints. Now, Mr. B Cells Products and Company Two, this could be useful for asking the question. Mr. B sells fridges. Company 2 sells fridges and freezers. Why is Mr. B not selling freezers from Company Two? And that is something that can also be deduced in this fifth normal form. So, having gone through the fourth and fifth normal forms, my advice is to ignore them. Except for the requirement that you know what the fourth and fifth normal forms are, If you are creating your databases, I would just go down to the third normal form. If you see opportunities for improvement along the lines of the fourth normal form and the fifth normal form, and you think it's a good idea, then by all means keep going. But Microsoft recommends that you stick with the Third Normal Form because it's just adding complexity without necessarily creating those massive improvements. And in fact, for me, it actually adds complexity.

5. 58. determine the appropriate type of execution plan

Now in this video we're going to go a bit behind the scenes and have a look at execution plans. So what I've got here is a cross joint. As a result, I'm joining this table to itself. So if this table is giving me 450 rows, then join it to itself. We have no idea how they actually join. I just want every single row compared against every single row, resulting in a huge number of rows, 450 squared. Now I'm not so much interested in the results. You can see it's taking a bit of a while, but I want to show you three different types of execution plans. So the first one is an estimated plan. So the computer has a look at the select query and says, "Okay, I think it should take X, Y, and Z," and here we go. If I click on this particular icon, it says "Display estimated execution plan." We don't actually see the result; we see what is happening. So we have a clustered index scan—in fact, a couple of them—with a nested loop joining them together and then a select statement at the end. That's the actual closure at the end. So it does the from first and then the close. Now execution plans run from the right to the left, and the arrow thickness represents the number of rows, and you can hover over these arrows for more details. So, if it's thicker, it has more estimated raws; more estimated raws means more memory reserved. If it's a particularly long-running query and the computer thinks that it can divide, say, a table up into two and then join them together at the end, then it can introduce what's called parallelism, and we'll be looking at that later on in other videos. Now, I should point out that if you write and click, you can also see the execution plan as an XML. You can analyse the actual execution plan, zoom in and out, and also see properties, which come in on the right hand side. So that's the estimated execution plan. It happens immediately without running the query. So the next thing I want to show you is the actual execution plan. So again, if I run a simpler version of this, then we get a third tab, and you can see we have the scan and it goes through to the select clause. So this is the actual as opposed to the estimated. So it includes additional runtime statistics and, of course, is what actually happened rather than what the computer thought. Well, I think that this number of roles, theamount of roles that are actually in each table,each view may have changed since the last query,it may not be 100% accurate. When you have the actual execution plan, that is what is actually there. Now there is an alternate way to include these actual execution plans, and that is to say "set, show plan all on," so if I do that and run, then you can see that the result is no longer the actual table, but information about each of these stages. So you've got it in table form. So I'll say author for the time being, and analternative is set to display plan text on. So now you can see it with separate queries showing execution information. So neither of these versions setshow plan, text or show plan. It will not actually execute the statement, but it will show execution information. So let's go back to our previous version, which has a separate tab for the actual execution plan. Now, that's fine when it's a short query, but what if it's a long query like this? We don't get the actual execution plan until it ends. So instead of having live query statistics, we can also have them in addition to the actual execution plan. So if I were to click on this icon as well, both the actual execution plan and live query statistics would be shown. Then here you can see the execution information as it's actually happening. So you can see the number of seconds things are taking. You can see the data flowing. Needless to say, it does take more time for the computer to process it, so I wouldn't be doing this on every single query. You can also see how far the query has progressed. When it's finished, the dashed lines will be solid lines. And as you can see, it's similar to the source of information that we have with the actual execution plan. So these are the three different types of execution plans. estimated execution plan, actual execution, and live query statistics. In the next video, we'll have a look at how these tables can be joined together.

6. Different types of loops used in execution plans, and Scan and Seek

In this video, we're going to have a look at various ways of joining tables or views together and also at the difference between a scan and a seat. Let's have a look at this query. Select Star from the address table. 450 roles. So the execution plan that I've included is a ruse. So what does that mean? Well, it's like if you took a book and started at page one and went all the way through to the end. So I'm now going to introduce the "where" clause, which refers to the city's buffalo. And let's look. And there are only four items here—four rows. So ideally, the computer knows where all four rows are where the city is bustling, and it goes to this one, that one, and these two. However, look, it's still a scan. That's not the case. And the reason is because we don't have an index for the city. So we've got an index for these things. We've got index for state province. For instance, we've got an index for addressline 1, and City is in there somewhere. But it's so far down the list, the computer can't use it. Now, suppose we added a new index. So this is a non-clustered index. A clustered index would mean that the table would get reshaped for that particular index. All the roles will be sorted in that order. The non-clustered index is just maintained separately. So I'm going to create a new nonclustered index. And if I refresh, you can see we now have this new index. So if I now do this select, we can see that the execution plan is changed from a seat to a scan. The computer knows where all of these four items are, and it's going just there. Next I'm going to add the query we were looking at in the last video. And if I execute that and wait for some 24 seconds, you'll see that it is using a nested loop. Nested loop joins are very efficient. They're used when you've got two inputs: input one and input two. Input 1 is at the top. So input one is generally small; input two is potentially large. But most importantly, it is indexed on whatever join is being used. Now, we don't have such an index here. This is a junction. But the computer has worked out that the nested loop is sufficient for a cross-join as well. Advantages of nested loops, it uses the leastIO, that's input output and the fewest comparisons. So what happens is that it takes the input one, the pinput, and takes just one row of that, and then it matches any rows in the bottom input for it. So in this case, all of the tables And then it takes the next row and matches the next row, and so on. So, that's a nested loop join. Next is a merge join.So this is used when input one and input two are not small, but also when input one and input two are sorted on their join. So in this case where the sales order IDs are the same, the advantage of merge joins is that they can be very fast, so the computer is going to look for nested loop joins when it can and merge joins when it can't. Now, what happens if that particular index, the sorting, is not there? So what I'm going to do is I'm going to take this sales order detail table and create a new one. So that's what this code does: it creates a new table called sales order detail copy. So it's a very simple statement, so it's identical to the previous one. I'll just refresh that with just one exception. Here are the indexes of sales order detail. There are no indexes for sales order detail copy, so I'll do that again, but this time I'm going to create a new header, sales Again, no indexes, indexes being the plural of index as opposed to indices in SQL Server. So now if I run exactly the same query, except this time it's on the copy, we don't have those input ones and inputs sorted on their joints, so now it's got to use a hash match. As a result, this is my least favourite join. It's used for large, unsorted, non-indexed inputs. It can also be used in the middle of complex queries as "intermediate results," often not indexed or suitably sorted. So we've got three main types of joins: nested loopjoins, merge joins, and hash joins, in that order. We also have seek and scan. Scanner is like going through a book from page one onwards, and seek is like going to an index at the back of the book and going, "I need to go to pages 515 and 25." Now, I should mention that a new batch mode adaptive join was introduced in SQL Server 2017. So you could consider this a fourth join, but it isn't. Once the first input is scanned in batch mode, it converts to a hash join or a nested loops join. So it's like a question mark, which then gets converted. But we'll be looking at the batch mode adaptive join when we have a look at intelligent query processing, or IQP. So there are three types of joins: nested loops, mergejoins, and hashtags, and then scans and seeks. So with that information, that will help us when we have to identify problem areas in execution plans. Which is the next video?

Pay a fraction of the cost to study with Exam-Labs DP-300: Administering Microsoft Azure SQL Solutions certification video training course. Passing the certification exams have never been easier. With the complete self-paced exam prep solution including DP-300: Administering Microsoft Azure SQL Solutions certification video training course, practice test questions and answers, exam practice test questions and study guide, you have nothing to worry about for your next certification exam.

Hide