11 Replies Latest reply: Jan 21, 2013 3:48 AM by Most Wanted!!!! RSS

    The Better One

    Most Wanted!!!!
      Hello Gurus,

      i have seen many tables with column having datatype as char(1) / varchar2(1) and have seen many procedures where some local variables declared with char(1) / varchar2(1) . And my question is which is better char(1) or varchar2(1) , Kindly guide me.


      Regards,
      Friend
        • 1. Re: The Better One
          sb92075
          most wanted!!!! wrote:
          Hello Gurus,

          i have seen many tables with column having datatype as char(1) / varchar2(1) and have seen many procedures where some local variables declared with char(1) / varchar2(1) . And my question is which is better char(1) or varchar2(1) , Kindly guide me.
          quantify better.
          • 2. Re: The Better One
            asahide
            Hi,

            To my understanding, char(1) is better.
            varchar2(1) is responsible for causing CHAINED ROWS.(not always..)

            Regards,
            • 3. Re: The Better One
              Most Wanted!!!!
              Is there any performance difference between varchar2(1) and char(1)?


              regards,
              friend
              • 4. Re: The Better One
                KPR
                Hi Friend,


                This is very basic question...Hope you know pretty well about char and varchar2 datatype difference.

                Anyhow I will explain once again....!!!

                varchar2(10)...I have pass the value as kris...remaining 6 bytes space free ... useful for other value
                char(10) --- I have pass the value as john... remaining 6 bytes space is not useful.

                So, you decide which is better....based on your requirement..!!

                REgards
                KPR
                • 5. Re: The Better One
                  Most Wanted!!!!
                  KPR wrote
                  
                  This is very basic question...Hope you know pretty well about char and varchar2 datatype difference. 
                  yes i do.

                  but when it comes to char(1) and varchar2(1) .both remains static so is there any performance difference between char(1) and varchar2(1).


                  regards
                  friend
                  • 6. Re: The Better One
                    KPR
                    Hi Friend.
                    varchar is variable size & char is fixed size. in large quantities this can make a difference in storage space as well as affect computation time.
                    performance wise, use char if data is going to be fixed length. if you are flexible with varying length of your data then use varchar.
                    KPR
                    • 7. Re: The Better One
                      Purvesh K
                      most wanted!!!! wrote:
                      Hello Gurus,

                      i have seen many tables with column having datatype as char(1) / varchar2(1) and have seen many procedures where some local variables declared with char(1) / varchar2(1) . And my question is which is better char(1) or varchar2(1) , Kindly guide me.


                      Regards,
                      Friend
                      At times, it not about Performance. Its about Right/Wrong.

                      CHAR(1) will not ensure, both will carry same data. CHAR(1) when fed with a NULL String (''), will still contain data with a Single SPACE, whereas VARCHAR2 will not. So, they are different datatypes.

                      And, you may always find some Idiotic character (person) who will consider NULL String ('') to be a NULL, and my ruin the database architecture. Hence, CHAR datatype is generally recommended, when you need to have data of same width, say in Reporting tools (an example).

                      See the below demonstration:
                      set serveroutput on;
                      declare
                      v_char        char(1) := '';
                      v_var         varchar2(1) := '';
                      begin
                        dbms_output.put_line('Char :: ' || replace(v_char, ' ', '~'));
                        dbms_output.put_line('Varchar :: ' || replace(v_var, ' ', '~'));
                        if v_char = v_var then
                          dbms_output.put_line('match');
                        else
                          dbms_output.put_line('not match');
                        end if;
                      end;
                      
                      anonymous block completed
                      Char :: ~
                      Varchar :: 
                      not match
                      Conclusion, IMO:- Better or not, is to be decided upon by the usage of datatypes in a situation. Since, CHAR and VARCHAR2 are both different, I would not regard the comparison to be Fair.
                      • 8. Re: The Better One
                        Karthick_Arp
                        Interesting...

                        NULL value for CHAR and VARCHAR2 in Database table
                        SQL> create table t(char_col char(1), varchar2_col varchar2(1));
                         
                        Table created.
                         
                        SQL> set null 'NULL'
                        
                        SQL> insert into t values (null, null);
                         
                        1 row created.
                         
                        SQL> select * from t;
                         
                        CHAR_COL             VARCHAR2_COL
                        -------------------- --------------------
                        NULL                 NULL
                        In PL/SQL Define CHAR and VARCHAR2 variable as NULL and the output is
                        SQL> declare
                          2    lChar char(1) := null;
                          3    lVarchar2 varchar2(1) := null;
                          4  begin
                          5    dbms_output.put_line(rpad('lChar', 20) || nvl(lChar, 'NULL'));
                          6    dbms_output.put_line(rpad('lVarchar2', 20) || nvl(lVarchar2, 'NULL'));
                          7  end;
                          8  /
                         
                        lChar               NULL
                        lVarchar2           NULL
                         
                        PL/SQL procedure successfully completed.
                        In PL/SQL Define CHAR and VARCHAR2 variable as '' and the output is
                        SQL> declare 
                          2    lChar char(1) := '';
                          3    lVarchar2 varchar2(1) := '';
                          4  begin
                          5    dbms_output.put_line(rpad('lChar', 20) || nvl(lChar, 'NULL'));
                          6    dbms_output.put_line(rpad('lVarchar2', 20) || nvl(lVarchar2, 'NULL'));
                          7  end;
                          8  /
                        lChar
                        lVarchar2           NULL
                         
                        PL/SQL procedure successfully completed.
                        CHAR variable is returned with a single space.

                        But Oracle says '' is NULL
                        SQL> select * from dual where '' is null;
                         
                        DUMMY
                        -----
                        X
                        • 9. Re: The Better One
                          Hoek
                          http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2668391900346844476
                          In addition to Karthick's remarks:
                          http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5984520277372
                          • 10. Re: The Better One
                            Purvesh K
                            I agree, that Oracle do treat Empty String as NULL, but they aren't the same.

                            NULL is Unknown, whereas Empty String is not unknown.

                            For Column with NULL values, Oracle does not store NULL, but perhaps an Internal representation designating the Column to contain an Unknown value.

                            See this:
                            select dump(1), dump(null)
                              from dual;
                            
                            DUMP(1)            DUMP(NULL) 
                            ------------------ ---------- 
                            Typ=2 Len=2: 193,2 NULL 
                            Hence, I do agree that both NULL and Empty strings are treated as NULL but it should not be implied that they are the same.
                            • 11. Re: The Better One
                              Most Wanted!!!!
                              thank you gurus.


                              regards ,
                              friend