What are the disadvantages of using VARCHAR?

  • Can lead to high space utilization if most of the values are close to maximum.
  • Positioning of VARCHAR column has to be done carefully as it has performance implications.
  • Relocation of rows to different pages can lead to more I/Os on retrieval.

What is the difference between SYNONYM and ALIAS?

SYNONYM: is dropped when the table or tablespace is dropped. Synonym is available only to the creator.

ALIAS: is retained even if table or tablespace is dropped. ALIAS can be created even if the table does not exist. It is used mainly in distributed environment to hide the location info from programs. Alias is a global object & is available to all.

What do you mean by NOT NULL WITH DEFAULT? When will you use it?

This column cannot have nulls and while insertion, if no value is supplied then it will have zeroes, spaces or date/time depending on whether it is numeric, character or date/time.

Use it when you do not want to have nulls but at the same time cannot give values all the time you insert this row.

What is a synonym ?

Synonym is an alternate name for a table or view used mainly to hide the leading qualifier of a table or view.. A synonym is accessible only by the creator.

What is index cardinality?

The number of distinct values a column or columns contain.