OK so we're moving on now and it's time for brand new section.
In this case we're spending some time revisiting something we've already discussed which is data types.
So very early on one of the first videos once we got everything set up and installed one of the first
videos was on very basic data types.
We talked about how data takes different forms and you have text and numbers and I kind of laughed at
that.
We talked about our char and our two first data types but I showed you that there are a bunch more.
Well now is it time that I promised feed revisit we'd returned to those data type and go into a bit
more detail and depth around some of the important data types so we're still not going to go over every
single one because a lot of them are just not used and some of them are very very similar with you know
tiny differences that honestly won't make that aren't relevant to the score.
So what we're going to do is focus on what will have the highest impact for you.
What has the highest impact in you know my developer life and the people that I know enough to talk
to and about how they use MY as well.
So basically it boils down to a couple of different data types and a couple different broad categories
so the first category is text or string data types.
And then we also talk about numeric data types.
We have a lot to talk about there because we've only worked with it.
We need to talk about how we store decimal points or how we store you know non whole numbers came over
the term for that.
And also we need to talk about how we store things like dates and times really important part of using
a database is typically at least in web applications or applications in general.
You're usually storing some sort of date and time information about when a transaction happens when
somebody signs up for your site.
The last time something was updated and so on.
So we'll be discussing that as well.
But for now in this video we're focusing on storing text.
So as you mentioned we've already seen var Charen of our car.
However we want to say it I think most people actually say var car but how can I save our car doesn't
really make sense I guess you say character not character.
All right.
So we've already talked about it.
It's what we've been using to store text.
But there's also Chaar our car which is an entirely different separate data type.
Now they both store text but there's one really key difference.
Well what is that difference.
Char has a fixed length.
So when we specify let's say we're working on let's say we're working with our books table and we want
to have a column called Title we could have right now of our char and our existing books table but we
could specify its char and give it a fixed length of 5 for title.
And what that means is that every title that we add in is going to be five characters.
So if it's greater than 5 characters if it's 10 characters it's going to be truncated is going to get
chopped at that 5 character Mark and if it's fewer you'll see in a moment where it does actually add
spaces to make it five characters.
So char will always allocate the same amount of space for every row or every instance of that char versus
var char can vary.
And as you can see here if we did something like chars three only three characters are allowed in this
example.
So this is taken straight from the documentation.
Some really boring explanatory text but has some important things to know the length of a char columnist's
fixed to the length that you declared when you create the table.
We've already discussed that the length can be any value from 0 to 255.
So the longest char car whatever that you can have is 255 characters long when char values are stored.
They are right padded with spaces to the specified length.
So that's kind of what I was explaining.
If we have a 10 character limit let's say char 10 and I say I insert something that has only five characters.
It's going to add five spaces after it.
On the right side of it pad it with these based spaces to make it exactly 10 characters.
However it's kind of confusing because when you go to look at that string when you select it back in
it's are treated.
Those spaces are actually removed as you can see here.
When char values are retrieved trailing spaces are removed in less blah blah blah.
There is a way to get them to stay there but I'm not sure why you'd want that to happen.
The only reason that I want to point that out is that I think it helps understand helps you understand
or has helped other students.
I've worked with understand how the fixed length works.
If you think in memory that it's actually storing something the same size every time is always five
or always 10 characters and you can't get around it.
So if you give it something shorter it will along get it with spaces to make it 10 characters.
Figure out something longer than 10 characters.
It's going to truncate it to make it 10 characters.
So why does it matter.
Well char is faster for fixed length text so there's not a ton of uses.
In my experience day to day for using char unless you know for sure that something is fixed length.
So I tried to come up with some examples like state abbreviations is one at least in the US where we
have two letter abbreviations for every state.
There's no reason to make that a variable length of our char.
You can save space and time essentially by using a chart or a yes or no flag wire and or any sort of
a binary.
It doesn't even have to be binary but something that has a fixed length.
So you could do sex in this case if you're doing you know where a pounder would be called an animal
shelter for keeping track of animals and their sex could use an F.
However if we want to do things like male and female or unknown or whatever other options those aren't
all the same length.
So it makes more sense to use far char.
So you probably come up with some other situations where this would make sense.
But typically we use far too hard because it gives us a lot of flexibility.
Otherwise you're smarter.
So there's a little there's a table here that I actually took this from the docs just kind of blew it
up and added it into my slides but it's from the documentation for char and varchar and what it's showing
you here for a given value like it.
Let's take a B if we have a column with data type char for you can see that it adds the two spaces so
it's actually stored as AB space.
Space and if we store the same thing string a B in a column with our chart for so far instead of char
it just stores a B.
But what I want to call your attention to is not just how it's stored but also the storage size how
many bytes it takes up.
So if we look at char when we declare char for every single string that we insert into that column will
always be 4 bytes and you can see over here whether it's an empty string it will convert it to be four
spaces if it's two characters it adds two spaces if it's four characters it leaves it alone if it's
more than four.
It truncates it.
So we have a fixed size here.
However with the bar chart for the same values all have different storage sizes.
So they're unchanged when they're stored.
We don't get spaces added We don't get anything truncated which is good but you can see that our storage
size does start to blossom blossom starts to grow as the length of the strings grow.
Now it's not a one to one thing.
So it's not like if we have a ten character string here it's going to take up 10 bytes.
But there is a difference.
That's all that matters.
With that said To be honest that difference won't make a big difference.
Let me put it another way.
The difference is insignificant for most applications that I have ever worked on.
If you are working on some giant app you know you work a bank of America and you've got you know tens
of millions of customers and hundreds of millions of records.
That does make a significant difference.
But if you're working on a personal project or your own hobby application you don't really need to fret
over this sort of thing.
In fact is it something sort of a common strategy that a lot of people would say is just get something
working.
And then when you get it when it comes time you can optimize things.
So if you start to realize well this one thing is becoming kind of slow this query I'm writing or whatever
it is maybe it makes sense for me to make this a fixed length and convert it to a char.
OK so that's the difference.
On paper you can go ahead skip the next video.
I'm just going to show you in practice what it looks like.
So I'm going to hop over to cloud nine and first thing I'll say I'm working on just a new database I
made basically just a testing database.
I didn't want to muddy the waters with our Instead of our book shop database I want to leave it alone.
So I just made one called new testing DB.
If you want to follow along just make a new database.
Hopefully remember how to do that.
Call it whatever you want.
I'm just going to leave it at the end of this anyway so I'm going to create a table here and all I want
to do is have two different columns one that a char one that's of our chart.
We'll just do dogs that say create table dogs and we'll have a name which will be char a fixed length
five characters and then we'll have breed which will be Vardar.
And let's just say 10 there OK.
Just like that and I'll go ahead and hit enter and I'm missing comma
area.
OK.
So we have that Doug's table.
Now let's insert some simple data.
So the first thing that I'll do is insert into dogs and remember our syntax here spend a little while
since we've started.
We've been doing a lot of selects but we need to have our values and the values that will pass and we're
going to start with something that is less than five characters.
So for a name let's say Bob and breed will also be something short let's say.
What is a short dog breed beagle.
Yes.
I don't know.
OK get so do that and we'll do one more here.
This time we'll have a name that's exactly five characters.
Let's call it Rob be spelled that way apologize for an original names here.
Just trying to work with the constraints and breed here will be a corgi.
OK.
And we'll do one more and this time we'll have a long name.
How about Princess Jane and practice Jane is a man until that last year was another breed let's go with
retriever.
Is that right.
If I remember.
All right.
Forgive me if it's spelled wrong.
OK.
So first thing first right off the bat.
Notice we got a warning on this last one.
Why do you think that happened.
Well it's because Princess Jane exceeds the limit that we've set for our char which is five characters.
So now if we do a simple select star from Dug's
you'll see we have Bob ráby.
They look good they look the same unchanged and Princess Jane unfortunately has been truncated.
So unfortunately we can't actually tell that there are those trailing spaces because they are not actually
here when the text is retrieved.
Remember with that giant plugger tech said it said that they're stored with these two extra spaces in
our case because this is three characters and we set up a five character fixed char so they're in memory
are going to be two spaces here.
But when it's pulled back out and displayed to us they're chopped off.
Robbie is unchanged because Robbie has five characters long princess Jane unfortunately is cut off.
However when we look over here beagle corgi and Retriever Vardar works how you expect it's a variable
character like let's just do one more example here with Princess Jane.
Yeah we'll just have another princess Jane.
And the long retriever blah blah blah blah blah.
Now I want to do a show like Star.
Notice again what happened.
This is what we expect to happen.
We've already seen this how our chart works.
It still has a maximum length that we can provide in this case.
We did 10 I believe yes.
So it can work similarly to char and that's it.
In that sense where it will truncate things.
So just to wrap up the entier char again is faster for fixed length text so if you know something is
always going to be the same size use char.
However if you have any doubt use of our char.
And again the difference is relatively insignificant for most things.
I don't want to be presumptuous but most of the time it's not going to be a deal breaker.
OK so moving on we're not going to discuss numbers in the next video.
This video.