x

Why Oracle Varchar2 auto become nvarchar in SQL Server

Hi, I'm creating a linked server in SQL Server 2008 R2 and linked to Oracle DB. Then I create a view to select data from Oracle via the linked server.

As I know, Oracle VARCHAR2 should be changed to VARCHAR in Sql Server. However, after the view creation, I found they become NVARCHAR. Why? Is there any way to let them auto change to VARCHAR?

Thanks
more ▼

asked Aug 24, 2011 at 06:46 PM in Default

gracie gravatar image

gracie
1 1 1 1

Are you using an ODBC or OLEDB driver for connecting to Oracle, which driver?
Aug 24, 2011 at 10:42 PM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
It could be a correct behavior as Oracle VARCHAR2 can contain also multi-byte characters, and therefore the SQL Server chooses the NVARCHAR data type.
more ▼

answered Aug 25, 2011 at 01:11 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

This is the reason why both VARCHAR2(size) and NVARCHAR2(size) are limited to 4000 while in SQL Server varchar is limited to 8000 while nvarchar to 4000. Modern Oracle databases also allow declaring the size with specs, such as varchar2(100 byte) or varchar2(100 char), so declaring it as varchar2(100 char) allows original design to still stand after the character set has changed from single byte to double byte per character (no need to switch to nvarchar or increase the size in this case).
Aug 25, 2011 at 09:00 AM Oleg
(comments are locked)
10|1200 characters needed characters left
We are working on migration project ,from Sql server 2005 Nvarchar(10) to Oracle 11g , what is similar data type and size , our assumption is oracle 11g is varchar2(10) is it correct? Please help me
more ▼

answered Apr 15, 2013 at 04:26 AM

lokesh.k gravatar image

lokesh.k
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x595

asked: Aug 24, 2011 at 06:46 PM

Seen: 6017 times

Last Updated: Apr 15, 2013 at 04:26 AM