CODEWITHSUNDEEP
sqloracle-database
Ram
Ram

Reputation: 1054

Oracle SQL difference between varchar2(n) and varchar2(n char)

Scripts here at work always declare varchar2 columns as varchar2(n char). I do not see any difference and just curious. Thanks!

Upvotes: 4

Views: 21448

Answers (2)

Wayan Wiprayoga
Wayan Wiprayoga

Reputation: 4562

Based on this resource

Oracle9i and above allow Varchar2 columns to be defined as a number of bytes VARCHAR2(50 BYTE) or a number of characters VARCHAR2(50 CHAR), the latter is useful if the database is ever converted to run a double-byte character set (such as Japanese), you won't have to edit the column sizes. The default measure, normally BYTE, is set with nls_length_semantics.

If you create a column as Varchar2 (50) but only store 10 bytes, then Oracle will only save 10 bytes to disc. This does not mean that you should just create Varchar2 (4000) columns 'just in case the space is needed', that is a really bad idea which will reduce the performance and maintainability of your application.

Upvotes: 6

jim mcnamara
jim mcnamara

Reputation: 16379

The syntax is VARCHAR2(n) and VARCHAR2(n BYTE|CHAR)

The default for (n) and (n BYTE) are usually the same. (n CHAR) may not be equivalent to (n|BYTE) or (n) unless you set NLS_LENGTH_SEMANTICS parameter to what you want, CHAR or BYTE. This setting is for character sets that use multibyte characters. Generally not for UTF8, for example.

DO NOT change it, since there is existing code that works.

I would guess that (n) == (n CHAR) == (n BYTE) or your system.

Upvotes: 3

Related Questions