DP-300 Microsoft Azure Database – Optimize Query Performance

1. 69. validate data types defined for columns

Hello. Now we’ve got that review of the select statement out of the way. We’re going to look in this section about how to optimize query performance. So part of that is about normalizing tables. We’ll have a look at that in the next three videos and then having a look at the resulting execution plans. But in this video I want to have a look at data types which are defined for columns. So each of these have got a particular data type type and if we expand the table and go into columns, you can see the data type that we’ve got here int, NVAR, char and unique identifier and date 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. So first of all we’ve got exact numerics.

So we got big int, small int, tiny int and bit. So each of these are 256 of the size of the previous one. So tiny int can have 256 values, not 255, whereas bit can have only zero or one small int and big int can have negative numbers. So you can see small int goes to 32,767, int goes to around 2 billion and big int thereafter. However, there is a price to be paid additional storage costs. So additional storage costs means that you fit less on a page of data.

So you should always try minimize storage cost 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, that’s the number of digits and s, the scale, the number of decimal places. So decimal brackets, nine comma five allows for nine digits of which five of them are after the decimal place separator. So for instance, number 1234, dot 56789 would be an example of numeric nine comma five. There are up to nine digits of which five of them are after the decimal place. Similarly, the number 10 is also an example of decimal nine comma five.

You don’t have to use all of the digits and decimal places. You’ve also got data types, money and small money. And all they are, they are big int 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 numerics, approximate numerators. These won’t store your values precisely. You have got flot, which either stores to around seven digits position or 15 digits position. And then you’ve got real. But real is just another name of flot with seven digits precision.

So flot brackets 24 will give you seven digits precision and float bracket 52 will give you 15 digits precision. So you could also say flot 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 bike. 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 proximate numerators, 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 a floor 24, then you might get 123456 and anything after that. Nine, two, one. It’s just approximate. So it’s good for storing really large or really small values when you don’t need to know exactly what it is. Date and time. We’ve got lots of different examples here. Date, so that just stores a date without a time. So you’ve got only three bytes needed for that. And you’ve also got a similar one, which is Time, which stores up to seven decimal places. So you say how many decimal places you want stored. So time brackets one will store just one decimal place. You can see the number of bytes needed is variable, depending on how many decimal places you want date time, where this stores up to one 300th of a second.

So the third decimal place will either be a free seven or zero. And it starts with the adoption of the Gugorian calendar into the United Kingdom. So that came in September 1752. So this starts in 1753 and it’s only eight byte storage date time two that stores up to seven decimal places and is a bit more efficient than date time. And then date time offset if you want to say where in the world it is. For instance, maybe you’re in United States in winter. So that is 5 hours behind GMT or UTC. And finally we’ve got small date time, which doesn’t store any seconds, it just starts the nearest minute. But again, you get some good storage requirements. There only four bytes. We’ve got character strings. Char will store as many characters as you say.

So char brackets 20 will store 20 characters. Not if not up to 20 characters, it will store 20 characters. So if you’ve got a char 20 and you put the string high two characters, it will still take 20 characters. If you want it to be more representative of what you store, then you can use Farchar, which stores the number of characters it needs, plus two additional bytes. 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 Nchar and N VAR, 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 N hi, then that denotes it as a unicorn string.

So it’s that capital N. Outside of the single quotation marks, we’ve also got text and N text. They are deprecated advise you not to use them, they will be removed in a later version of SQL Server. Instead you have VA Char Max which you could store an essay in. There 2 billion characters and same Nvarchar Max when you are storing binary strings. So this could be anything, it couldn’t be necessarily just characters, but maybe you load a text file or in fact any sort of file, a picture file in, you can store it in binary and VAR binary and then there’s a few other things, unique identifiers, so that is when you’re storing guidance Guid. So if you see something that’s really long with Hexadecimal, then that would be unique identifier or Guid. 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. Now, if you’ve done previous SQL Server courses, you will be familiar with all of these. There’s 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 be coming up quite often whenever we look at columns. So now you know NVAR char 60 can be up to 60 unicorn characters for instance, but I won’t be commenting on what they are, I’ll just expect you to be able to recognize them and understand the sort of things that they contain. So that is the data types 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 start off with a table which is not really well designed, and then we can improve and improve it going through all of these first normal forms, forms, second normal form, third normal form, and so on until we get to an optimal 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 teachers age, 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 okay. With this table. Oh, 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 how am I going to add computing into this? Well, there’s two possible ways because I’m teaching bad table design, and I’m going to have in 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. Oh, I’ve just heard that John is going to take a third class. Okay, well, we obviously therefore need a third column. So 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 getting with with this poor table design. In the next video, we’ll start having a look at the first normal form and have a look at some of the other forms and try and actually make this better. But why do we want to make this better? What is it in it for us? Well, the first thing is to minimize duplicate data. So you can see that Mr.

Y is 52. But we’ve got that in twice. We don’t need it in twice. And maybe we also wanting to stall other attributes of the teacher, for instance, his address, and we’ll then have to put that in several times. The second reason is to reduce data modification issues. Well, what nerve do I mean by that? Well, it’s Mr. Wise 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. Wise, 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 Mrs. G, who did this personal favor to teach Margaret chemistry, 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 equals computing or class two equals computing, or class three. How many columns of this do I have? How many subjects are people taking? And I’ll have to expand and it’s expanded if somebody decided, I know you’ve got eight rooms for classes in your table, but I’m going to do a 9th class. Well, I’ll have to go back to my existing queries and rewrite them.

Or alternatively, I have to say where class like computing. And then if there’s a separate class called the History of computing, then that would be caught by where class like computing. Even though I just wanted computing and not the History of computing. So you can see it’s getting really complicated to just do a very simple query. I’m having to either do lots of ords or having to do a like, but then I might get some extraneous data retrieved. So all of these problems I can either reduce or minimize using normal forms. And in the next video, we’ll have a look and start off with the first normal form.

• Category: Uncategorized