Nvarchar in SQL works as varchar2 in oracle

Question

I heared about varchar2 in Oracle that emp_name varchar2(10) if we enter value less than 10 then remaining space is automatically deleted

In SQL how we can achieve this thing. By using nvarchar can we achieve same thing in SQL?

Answer 1

AFAIK, varchar2 is unique to Oracle, and therefore has no exact match in any other db system. The point at issue is ANSI compliance. Oracle's varchar and varchar2 treat empty strings as NULL, whereas the ANSI standard is to treat an empty string as different to NULL. The difference between varchar and varchar2 in Oracle, is that Oracle is committed never to change the behaviour of varchar2; varchar on the other hand may at some stage be made ANSI compliant. It is for this reason that you are encouraged in Oracle to use varchar2, so that there is no risk of an application breaking as a result of an eventual change in the treatment of nulls and empty strings.

nvarchar is different again, for a totally separate reason: nvarchar columns hold Unicode characters, and use two bytes per character, as opposed to one for varchars.

So we can see that nvarchar and varchar2 are not equivalent. However, in the limited sense of your question, ie what happens when you store a string whose length is less than the maximum set for the field, then for practical purposes they indeed behave the same, in that the value is not padded with white space to make up the length. Please note here that this is not the behaviour you describe: nothing is being "automatically deleted"; it is just that nothing is being automatically padded.

Edit

For SQL Server read here for an explanation of the actual storage taken by varchar (and nvarchar). Your senior is simply wrong. Using varchar or nvarchar(50) will not use 50 (or 100) bytes per record. Storage space used is only the amount that is required for each record. AFAIK all dbs behave similarly.