Differences between Char and Varchar Datatypes | MSSQL Training

[Music] hi guys welcome to nourish IT this is Rocco and in this session we will discuss about the differences between character and work at data types okay so cab and we are categorized difference what exactly okay in our character data types catechol so let us see now I will go to give the list here the differences in between character and work app I can observe the two data types we have here okay one is my first one I will take your cat of size is the one thing same the same when I want to go is my second one okay for example where cat off in the last session I said either you can use size or you can use a max so these are the two data types we are using in our when you design your tables okay but what is the differences in between care and where care that is it's very important to know okay so let us see one by one we will go to discuss first one come to care of size this data type the people was called as where it is a fix to the length to date no type six for the length is nothing but whatever the files are you representing along with your CAD data type the size was fixed that with the size I cannot change at my rent I'm level otherwise at my execution of value so whenever you want to executing or inserting the values into the column internally your server will allocate in the fixed size of memory that is we called as a fixed length data type we call so simply it pixel length data type simply we can call static data type also we can prompt I think you know the information about static static means we can't change we cannot change at or execution time the kind of the nature is called a static nature so same to same you are tagged datatype is a fixed I said fix it is nothing but a static so we can't change the size of the datatype at my execution time okay this is water cat but your this tag data type whatever I am going to be using here the scatter data type simply we are going to use to store your characters okay characters otherwise you can also say characters and also alphanumeric characters alpha numeric characters the both kind of information I can store into this data type column but whatever are you storing this information or data for storing of your characters purpose your character data type will allocated the mechanism I said this is which mechanism it is followed means non Unicode mechanism will be follow because I already said to you in the last video session character data type is a non unicode data type category so same the same what are the characters it will be store the characters will be storing into non Unicode mechanism but non Unicode mechanism is nothing but simple I can say now it is nothing but for every character purpose it will allocated one byte of memory okay for every character purpose it will allocated one byte of memory so this kind offer the wave mechanism your character datatypes will be stored and this data type match area I said sizer it will take the maximum size here a thousand bytes so eight thousand bytes means for each character is equals to one bike memory will occupy that this is size representation 8,000 wide it's nothing but lengthen how much length your again 8,000 characters so 8,000 characters I will be go to send within a single expression so within the single expression at a time I can send up to 8,000 characters expression I can send it up okay so that is what it is your each character one byte it will occupy this is occupied memory this is length of the expression so this length of the expression is represent what we called here length of the expression okay so this is what it is here the nature of cap datatype okay but the same - same here observed once it is a character data type I said fix isn't static means what is the problem here memory based it the major drawback in care data type is what now to waster memory so now the memory if I want to say because memory saving is very important in real-time when you develop your application the memory management is very important role it will be play in your development by this reason I want to save my memory and sup know so to say my memory okay I don't want to waste unnecessarily my memory then in place of cab data type I can even though I can use what now where care of size R max now let us come to work here and see I will give again step by step then you can understand what is the different work at data type simply be called as a it is a variable length data type so variable length means in your expression how many variables is that how many characters are you sending based on the character of based on the variables it were allocating memory for example I send one expressions ie in this expression tree character from there eat the character will variable and how many variables are there you have three variables that means it will allocated three bytes of memory okay so now that we called a variable length data type this variable length data type is simply called as a dynamic that means this data type either can take size of max whatever the memory allocation was happen dynamically so once my memory was dynamically then I failing my memory I don't want to waste my memory simple and this data type also we are used to store characters and also alpha numeric characters alphanumeric characters also accepted and it is also same to same non Unicode mechanism so non Unicode mechanism means what I said here one cat is equals to one byte so now here after you have two things was there for example where care of sizes if anyone asked you this maximum length is 8,000 bytes but the single thing if I use where care of max if I use it then my size is 2gb that means backup sighs if I taken then user was defined maximum size I can different up to eight thousand bytes only but when I use it without the definition I use just max keyboard there are automatically 2gb memory allocation was happen but whatever the eight thousand bytes and 2gb these two the types of memory was allocated in dynamically so that's why your memory was same okay so this is what now we're cat data type and character data types now each and every point you can check then you can understand what is the difference what I said character is a fixed length that is variable and the data type it is static data type that is a dynamic data type it will store the characters in non Unicode mechanism same to same there also store the characters in on Unicode mechanism one cat is equal to one byte one care is equal to one by and there you are maximum size eight thousand bytes within the expression 8,000 character second same but this memory will be static on their marker of sizes eight thousand it means that eight thousand character second string Valkyr of max is number 2 gb but that memory was allocated in dynamically so this is the difference of your car and the market but here I said one point to remember static data type and dynamic data type I said but how we can say here memory wasted and their memory same how means now I will give one a small example on this two data type you can check okay now see ok so now I will give one small example now what is the nature of static and dynamic and how the memory was wasted okay one small example I will show you observe I will take one example to create one column here my column name is name column on this name column for example I applied care of and this is one column I'm going to design in the table in this column you can see I was taken character data type I was taken but already we said character data type is very static and here I was assigned the size is 10 okay now once you can give like this year later if I want to insert some name for example hello is a 1 Express line pattern this expression of having here the total 5 characters but this expression purpose it will take the memory how much memory will allocated means 3 4 5 six seven eight nine ten in this each character will take one byte of memory okay and remaining space we can see that is Western and this expression also accepted into my column later if you take second one hell I was taken now Halley's also accepted into my column there is nothing different but had an expression Papa fall for allocating memory so it will take only three bytes of memory four five six seven eight nine ten in the same go same pattern if I take a he he was also accepted into my column but he purpose also it was allocated ten bytes of memory but how many it is occupied to only occupied three four five six seven eight nine ten so my fingers first scenario what we understand here here file here three here to the total size you can feel 10 size here 10 size your eyes also 10 that is what I'm saying your this is not a change how much sighs I was given they're the same size is allocated by server for each and every expression whenever user infect so you're ten minus five remaining memory five bytes here ten minus three remaining seven bytes ten minus two remaining eight bytes so that means whatever it was there here this is called a wasted memory what I was called here wasted memory wasted memo okay and I said this is a static static means the five I cannot change now we can see at my execution time for a each and every expression purpose your size was 10 10 10 only that's why this data type is called as a static data type my partner of summer and memory is wasted so how much memory is left for you here my memory is file here my memory is 7 here memory is 8 so 8 + 7 15 15 plus 5 on B so 20 bytes of memory are you wasted for how many expressions three expressions on me hello hi he-hello hell he for this three expressions purpose I was wasted 20 bytes of memory is it necessary no so that means I want to overcome this problem Atwater yes so how to overcome means now I will go for where care so now we can come and see now now the same example when I was taken mind okay name as usual but here I was placing where care offer 10 I was given so same column I am taking here also and I will take few the column and now you can see now the same what kind of information what kind of information there are inserted same expressions here also I am inserting my first expression is hello so hello is accepted into my column successfully accepted hello but in this expression go to how many characters are there your memory will be allocated that many bytes only for example h-e-l-l-o so this is what now okay what it is I am saying here the expression like you second expression what I inserted their help the same expression in setter now but here also it will take here 3 it is also accepted into your column later I will go for E and here it will take a 2 now we are 2 is accepted and see also X 4 into my column but see or there is no memory Western because in your expression how many characters are there that many bytes only allocated so there is no wasted memory and later in the first execution my size is 5 3 & 2 but what I say you are given 10 but the sizes are change that's why this is called a dynamic data type your size you can change at your execution level so see evolution of a file tree to change but your execution level 10 10 10 was given so this is called static and this is called a dynamic so this is no it's a memory register it is a no memory waster so this is the differences between care and the fat cap if anyone asked you what is the differences then you can tell this difference okay so thank you thank you for watching this video [Music]

Channel: Naresh i Technologies

Views: 52,435

Keywords: MSSQL, Sudhakar Sharma, SSMS, Naresh IT, Hands on MSSQL Training, MSSQL Demo, Online MSSQL Training, MSSQL Tutorial Videos, MSSQL Overview, MSSQL Interview Questions, Control Language in SQL Server, Control Language, SQL Server Control Language, Transaction Control Language

Id: ElpoonrCsDU

Length: 16min 35sec (995 seconds)

Published: Wed Feb 08 2017