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?


more ▼

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

avatar image

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

avatar image

Pavel Pawlowski
22.7k 10 15 26

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

avatar image


(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Aug 24, 2011 at 06:46 PM

Seen: 8809 times

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

Copyright 2018 Redgate Software. Privacy Policy