Does MS T-SQL include a data type called varchar2? and if so, what's the difference between it and between varchar?

Question

Had this question in an interview.

Answer 1

No. varchar2 is an Oracle datatype, equivalent to its (as well as TSQL's) own varchar.

CLARIFICATION

The differentiation between Oracle's varchar and varchar2 seems to have originated back when the ANSI SQL standard was still being formulated. I cannot locate any definitive references as to what exactly the implementation differences, if any, were initially (pre-Oracle 7), as this remains a source of confusion. Suffices to say that the two are practically treated as synonyms since Oracle 7. Both suffered improvements (maximum size went from 2k to 4k) in release 8. Note that TSQL's varchar can store 8k.

V.

Answer 2

I've never heard Vlad's suggestion that the VARCHAR data type used to only support 2k of data. Given that they are synonymous in every release since 8.1.7 (I haven't checked the documentation for earlier releases), that seems odd to me. Vlad, do you have a reference for that?

From the SQL Reference section on the VARCHAR data type

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. Oracle recommends that you use VARCHAR2 rather than VARCHAR. In the future, VARCHAR might be defined as a separate datatype used for variable-length character strings compared with different comparison semantics.

Since Oracle predates the SQL standard, certain comparison semantics in Oracle don't match the standard (such as comparing NULL and the empty string). In theory, Oracle has the VARCHAR data type so that in the future they can change the VARCHAR data type's comparison semantics to match the SQL standard without affecting the backwards compatibility of applications using the VARCHAR2 data type.