➤ EMP DEPT Tables Queries
➤ Set Operators in Oracle
➤ CREATE command
➤ RENAME command
➤ DROP command
➤ Default Value in Column
➤ ABS() in Oracle SQL
➤ CEIL() in Oracle SQL
➤ SYSDATE in Oracle
➤ Trunc Oracle date
➤ TO_CHAR() in Oracle
➤ GROUP BY Clause
➤ Having Clause
➤ ORDER BY Clause
➤ Constraints in Oracle
➤ Rename Constraint
➤ Disable Constraint
➤ Drop Constraint
➤ NOT NULL Constraint
➤ UNIQUE Constraint
➤ PRIMARY KEY Constraint
➤ FOREIGN KEY Constraint
➤ CHECK Constraint
➤ Unlock User in SQL Plus
➤ Find SID in Oracle database
➤ Check Database Version
➤ Check Database Size
➤ Error ORA 01031
Char vs Varchar in SQL | In SQL both char and varchar/varchar2 data type is used to store alpha-numeric values. But both store these values differently from each other.
Char is used for storing the fixed lengths of alpha-numeric data in bytes. Whenever we are trying to store less number of bytes than the maximum size specified in the char data type, then the server internally automatically adds blank spaces in place of the remaining bytes until the end of the string. This is called a blank padded mechanism.
Example:- Name CHAR(10);
Now, in each row of the “Name” column 10 bytes of space will be allocated. If we store “PROGRAM” then only 7 bytes among 10 bytes will be used, the remaining 3 bytes will be blank and wasted.
P | R | O | G | R | A | M |
Varchar and Varchar2 data type is used to store the variable or dynamic length of alpha-numeric data in bytes. Whenever we are using the varchar/varchar2 data type and try to store less number of bytes than the maximum size specified in the varchar/varchar2 data type then the server doesn’t add blank space in place of the remaining bytes. Those bytes will be released and will not be wasted.
Example:- VARCHAR CHAR(10);
Now, in each row of the “Name” column 10 bytes of space will be allocated. If we store “PROGRAM” then only 7 bytes among 10 bytes will be used but the remaining 3 bytes will be released. Hence these 3 bytes will not be wasted.
P | R | O | G | R | A | M |
Also see:- Difference between VARCHAR vs VARCHAR2
When should we use CHAR vs VARCHAR/VARCHAR2?
We should use CHAR data type when we expect the data values in a column are of the same length. For example:- to store country_code, state_code, phone number, and e.t.c char data type is the best choice because each column will contain the same length. VARCHAR and VARCHAR2 data types should be used when the data values in a column are of dynamic length. For example:- to store name, mail id, address, and e.t.c.
Comparison table between CHAR vs VARCHAR in SQL
CHAR | VARCHAR/VARCHAR2 |
---|---|
It is used to store a fixed number of alpha-numeric values. | It is used to store a dynamic number of alpha-numeric values. |
CHAR understands “character”. | VARCHAR understands “variable character”. |
Whenever we are trying to store less number of bytes than the maximum size specified in the char data type, then the server internally automatically adds blank spaces in place of the remaining bytes until the end of the string. | Whenever we are using the varchar/varchar2 data type and try to store less number of bytes than the maximum size specified in the varchar/varchar2 data type then the server doesn’t add blank space in place of the remaining bytes. |
We should use char data type when we expect the data values in a column are of the same length. | VARCHAR and VARCHAR2 data types should be used when the data values in a column are of dynamic length. |
VARCHAR saves space when there is variation in the length of values, but CHAR might be performance-wise better.
CREATE TABLE TEST(NAME VARCHAR(10), LANG CHAR(10));
INSERT INTO TEST VALUES('SOPHIA', 'ENGLISH');
INSERT INTO TEST VALUES('JOHN', 'FRENCH');
SELECT LENGTH(NAME), LENGTH(LANG) FROM TEST;
Output:-
LENGTH(NAME) LENGTH(LANG)
----------- ------------
6 10
4 10
Both “Name” and “Lang” columns can store a maximum of 10 bytes of data. Here “Name” column is using the VARCHAR data type whereas the “Lang” column is using the CHAR data type. Hence the “Lang” column always takes 10 bytes of storage whereas “Name” will take the variable length between 1 to 10 based on stored data.
If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!