Oracle和SQL Server中的NVARCHAR之间有区别吗? [英] Difference between NVARCHAR in Oracle and SQL Server?
问题描述
我们正在将一些数据从sql server迁移到oracle.对于在SQL Server中定义为NVARCHAR的列，我们开始在Oracle中创建NVARCHAR列，认为它们是相似的.但是看起来它们并不相似.
我已经阅读了关于stackoverflow的几篇文章，并希望确认我的发现.
如果数据库字符集为AL32UTF8(对于我们的情况如此)，Oracle VARCHAR2已经支持unicode.
SQLServer VARCHAR 不不支持unicode. SQLServer明确要求列必须为NCHAR/NVARCHAR类型，以便以unicode(特别是2字节UCS-2格式)存储数据.
因此，可以正确地说SQL Server NVARCHAR列可以/应该作为Oracle VARCHAR2列迁移吗?
是的，如果您的Oracle数据库是使用Unicode字符集创建的，则应将SQL Server中的NVARCHAR迁移到Oracle中的VARCHAR2.在Oracle中，存在NVARCHAR数据类型，以允许应用程序在数据库字符集不支持Unicode时使用Unicode字符集存储数据.
但是，迁移时要注意的一件事是字符长度语义.在SQL Server中，NVARCHAR(20)分配20个字符的空间，这在UCS-2中最多需要40个字节.在Oracle中，默认情况下，VARCHAR2(20)分配20个字节的存储空间.在AL32UTF8字符集中，虽然可能会处理更多的字符，但它可能仅可能容纳6个字符的足够空间(AL32UTF8中的单个字符需要1到3个字节.您可能希望将Oracle类型声明为VARCHAR2(20 CHAR)表示您想要为20个字符分配空间，而不管需要多少字节.与试图解释为什么允许使用20个字符串而拒绝其他10个字符串相比，沟通起来要容易得多. >
您可以在会话级别更改默认长度语义，以便您创建的任何未指定任何长度语义的表都将使用字符而不是字节语义
ALTER SESSION SET nls_length_semantics=CHAR;
这使您避免在每次定义新列时都键入CHAR.也可以在系统级别进行设置，但是NLS团队不建议这样做-显然，并非Oracle提供的所有脚本都已针对更改了NLS_LENGTH_SEMANTICS的数据库进行了全面测试.可能只有很少的第三方脚本.
We are migrating some data from sql server to oracle. For columns defined as NVARCHAR in SQL server we started creating NVARCHAR columns in Oracle thinking them to be similar..But it looks like they are not.
I have read couple of posts on stackoverflow and want to confirm my findings.
Oracle VARCHAR2 already supports unicode if the database character set is say AL32UTF8 (which is true for our case).
SQLServer VARCHAR does not support unicode. SQLServer explicitly requires columns to be in NCHAR/NVARCHAR type to store data in unicode (specifically in the 2 byte UCS-2 format)..
Hence would it be correct to say that SQL Server NVARCHAR columns can/should be migrated as Oracle VARCHAR2 columns ?
Yes, if your Oracle database is created using a Unicode character set, an NVARCHAR in SQL Server should be migrated to a VARCHAR2 in Oracle. In Oracle, the NVARCHAR data type exists to allow applications to store data using a Unicode character set when the database character set does not support Unicode.
One thing to be aware of in migrating, however, is character length semantics. In SQL Server, a NVARCHAR(20) allocates space for 20 characters which requires up to 40 bytes in UCS-2. In Oracle, by default, a VARCHAR2(20) allocates 20 bytes of storage. In the AL32UTF8 character set, that is potentially only enough space for 6 characters though most likely it will handle much more (a single character in AL32UTF8 requires between 1 and 3 bytes. You probably want to declare your Oracle types as VARCHAR2(20 CHAR) which indicates that you want to allocate space for 20 characters regardless of how many bytes that requires. That tends to be much easier to communicate than trying to explain why some 20 character strings are allowed while other 10 character strings are rejected.
You can change the default length semantics at the session level so that any tables you create without specifying any length semantics will use character rather than byte semantics
ALTER SESSION SET nls_length_semantics=CHAR;
That lets you avoid typing CHAR every time you define a new column. It is also possible to set that at a system level but doing so is discouraged by the NLS team-- apparently, not all the scripts Oracle provides have been thoroughly tested against databases where the NLS_LENGTH_SEMANTICS has been changed. And probably very few third-party scripts have been.
这篇关于Oracle和SQL Server中的NVARCHAR之间有区别吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
