Okay.
So we're moving on now and it's time for a 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 and we talked about how data takes different forms and you
have text and numbers and I kind of left it at that.
We talked about var char and int are 2/1 data types, but I showed you that there are a bunch more.
Well, now it's a time that I promised we'd revisit.
We'd return to those data types and go into a bit more detail in 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 tiny differences that honestly won't make that aren't relevant
to this course.
So what we're going to do is focus on what will have the highest impact for you, what has the highest
impact in my developer life and the people that I know and have to talk to about how they use my SQL.
So basically it boils down to a couple of different data types and a couple of different broad categories.
So the first category.
It's text or string data types.
And then we'll also talk about numeric data types.
We have a lot to talk about there because we've only worked with iNts.
We need to talk about how we store decimal points or how we store non whole numbers.
I can't remember 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 I mentioned, we've already seen var char or var car, however you want to say it.
I think most people actually say var car, but.
I don't know.
I save our char.
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 char or 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 it's a var char in our existing books table,
but we could specify it's a char and give it a fixed length of five 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 five characters, if it's ten characters, it's going to be truncated, it's
going to get chopped at that five character mark.
And if it's fewer, you'll see in a moment what it does.
It 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 char three, only three characters are allowed in
this example.
So this is taken straight from the documentation, some really boring explanatory text, but it has
some important things to know.
The length of a char column is fixed to the length that you declare when you create the table.
We've already discussed that the length can be any value from 0 to 2 five five, so the longest char
or 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 ten character limit, let's say Char ten and I, let's say I insert something that is only
five characters, it's going to add five spaces after it on the right side of it, pat it with these
base spaces to make it exactly ten characters.
However, it's kind of confusing because when you go to look at that string, when you select it back
and it's retrieved, those spaces are actually removed.
As you can see here, when char values are retrieved, trailing spaces are removed and less blah, blah,
blah.
There is a way to get them to stay there, but I'm not sure why you would want that to happen.
The only reason that I want to point that out is that I think it helps understand, helps you understand,
has helped other students that 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, it's always five
or always ten characters and you can't get around that.
So if you give it something shorter, it will elongate it with spaces to make it ten characters.
If you give it something longer than ten characters, it's going to truncate it to make it ten 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 cha cha.
You can save space and time essentially by using cha cha or a yes or no flag while you're n or any sort
of like 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 were doing where a pound or a what he called an animal shelter
or keeping track of animals and their sex could use M and F.
However, if we wanted 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 fa cha.
So you could probably come up with some other situations where this would make sense, but typically
we use cha cha because it gives us a lot of flexibility.
Otherwise use water.
So there's a little there's a table here that I actually took this from the docks.
I just kind of blew it up and added it into my slides.
But it's from the documentation for char and char and what it's showing you here for a given value,
like let's take a B.
If we have a column with data type chart for.
You can see that it adds the two spaces.
So it's actually stored as a B space space.
And if we store the same thing, it string a B in a column with var char for so var char instead of
char it just stores a b.
But what I want to call your attention to is not just how it 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 four 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 these strings grow.
Now it's not a 1 to 1 thing, so it's not like if we have a ten character string here, it's going to
take up ten 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 that another way.
That difference is insignificant for most applications that I have ever worked on.
If you are working on some giant app, you work at 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, it's 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.
Okay.
So that's the difference on paper.
You can go ahead and skip to 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 in just a new database.
I made basically just a testing database.
I didn't want to muddy the waters with our inside of our book shop database.
I want to leave that 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 delete it at the end of this anyways.
So I'm going to create a table here and all I want to do is have two different columns.
One, that's a char one that's a bar char.
We'll just do dogs, let's say create table dogs and we'll have name, which will be char a fixed length
five characters and then we'll have breed, which will be bar char and let's just say ten there.
Just like that.
And we'll go ahead and hit enter.
And I'm missing a comma.
Here we go.
So we have that dogs 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.
It's been a little while since we've inserted we've been doing a lot of select, but we need to have
our values and the values that we'll pass in.
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.
Well, what is it?
Short dog breed.
Beagle?
I guess.
I don't know.
So we'll do that.
And we'll do one more here.
This time will have a name.
That's exactly five characters, let's call it Robbie.
Spelled that way.
I apologize for unoriginal names here.
Just trying to work with the constraints and breed here will be corgi.
And we'll do one more.
And this time we'll have a long name.
How about Princess?
Jane.
And Princess Jane is a man.
I'm told that a lot here with another breed.
Let's go with Retriever.
First of all, that right.
You never remember.
All right.
Forgive me if that spelled wrong.
Okay.
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 dogs.
You'll see.
We have Bob Robbie.
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 blurb 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 there 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 is five characters long.
Princess Jane, unfortunately, is cut off.
However, when we look over here, beagle, corgi and retriever works how you expect it's a variable
character length.
Let's just do one more example here with Princess Jane.
We'll just have another Princess Jane and a long retriever.
Blah, blah, blah, blah, blah.
Now when I do a select star, notice again what happened.
This is what we expect to happen.
We've already seen this how VAR works.
It still has a maximum length that we can provide.
In this case, we did ten.
I believe.
Yes.
So it can work similarly to CHA in that in that sense where it will truncate things.
So just to wrap up the end here, Char again is faster for fixed length text.
So if you know something is always going to be the same size use chart, however, if you have any doubt
use var char.
And again, the difference is relatively insignificant for most of the things.
I don't want to be presumptuous, but most of the time it's not going to be a dealbreaker.
So moving on, we're now going to discuss numbers in the next video, not this video.