All Forums Database
tdforumuser 15 posts Joined 05/11
17 May 2011
Primary Index on VARCHAR column

Is it a good practice to define a Primary Index on VARCHAR column ? Although I dont see any strange (data type conversions) in explain plans between Join operations on VARCHAR (Primary Index) to CHAR column (Primary Index) since they are from same domain, I just want to understand if this is as efficient as Joins between CHAR to CHAR columns. How effectively does the optimizer use STATISTICS on VARCHAR column when compared to CHAR columns (assuming the length of VARCHAR column to be less than 16 bytes) ?

dnoeth 4628 posts Joined 11/04
18 May 2011

There should be no difference in CHAR vs. VARCHAR for PI, both hash the same, as trailing blanks are ignored during hashing.
The values stored within a statistic are always fixed length, so there's no difference regarding this, too.

But of course, if you join a CHAR to a VARCHAR column then you choose the wrong datatypes for one of them ;-)

Dieter

Dieter

chand308 17 posts Joined 01/11
09 Oct 2012

Will there be any performance degradation when we join  CHAR to a VARCHAR column?

Qaisar Kiani 337 posts Joined 11/05
10 Oct 2012

No there won't be any difference as both the data types belong to text domain.

19 Apr 2014

Hi Experts,
I have a doubt  just want to clear it.  Generally we tend to make some integer/decimal/numeric column as a primary index & have a tendency to avoid  string columns as primary index.
Not sure abt the specific reason   .. Please correct me if i am wrong.
 
Thanks in advance.
Cheers!
Nishant
 

Adeel Chaudhry 773 posts Joined 04/08
19 Apr 2014

Integer literals take less space as compared to charater literals.
 
For example, INT takes 4 bytes and CHAR(10) would take 10 bytes .... which effectively means more data to store, retrieve and move around .... hence would have an impact on performance.

-- If you are stuck at something .... consider it an opportunity to think anew.

19 Apr 2014

Thanks Adeel for the respose!
Cheers!
Nishant

You must sign in to leave a comment.