Primary Index on VARCHAR column

Database
Enthusiast

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) ?
6 REPLIES
Junior Contributor

Re: Primary Index on VARCHAR column

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
Enthusiast

Re: Primary Index on VARCHAR column

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

Enthusiast

Re: Primary Index on VARCHAR column

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

Enthusiast

Re: Primary Index on VARCHAR column

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

Teradata Employee

Re: Primary Index on VARCHAR column

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.

Enthusiast

Re: Primary Index on VARCHAR column

Thanks Adeel for the respose!

Cheers!

Nishant