Reg: SqlServer Data Type

Hi
Can you please explain the difference between Text and nText data type in
SqlServer and at what situation we have to use this.
Siva.ntext is national text and it is used for storing unicode data.
text is used for storing non-unicode data
ntext occupies twice the size for storing the same data as in text
ntext supports all characters defined in unicode standard.|||and this is from BOL.. if you had not already seen it.
Using Unicode Data
The Unicode specification defines a single encoding scheme for most
characters widely used in businesses around the world. All computers
consistently translate the bit patterns in Unicode data into characters usin
g
the single Unicode specification. This ensures that the same bit pattern is
always converted to the same character on all computers. Data can be freely
transferred from one database or computer to another without concern that th
e
receiving system will translate the bit patterns into characters incorrectly
.
One problem with data types that use 1 byte to encode each character is that
the data type can only represent 256 different characters. This forces
multiple encoding specifications (or code pages) for different alphabets suc
h
as European alphabets, which are relatively small. It is also impossible to
handle systems such as the Japanese Kanji or Korean Hangul alphabets that
have thousands of characters.
Each Microsoft? SQL Server? collation has a code page that defines what
patterns of bits represent each character in char, varchar, and text values.
Individual columns and character constants can be assigned a different code
page. Client computers use the code page associated with the operating syste
m
locale to interpret character bit patterns. There are many different code
pages. Some characters appear on some code pages, but not on others. Some
characters are defined with one bit pattern on some code pages, and with a
different bit pattern on other code pages. When you build international
systems that must handle different languages, it becomes difficult to pick
code pages for all the computers that meet the language requirements of
multiple countries. It is also difficult to ensure that every computer
performs the correct translations when interfacing with a system using a
different code page.
The Unicode specification addresses this problem by using 2 bytes to encode
each character. There are enough different patterns (65,536) in 2 bytes for
a
single specification covering the most common business languages. Because al
l
Unicode systems consistently use the same bit patterns to represent all
characters, there is no problem with characters being converted incorrectly
when moving from one system to another. You can minimize character conversio
n
issues by using Unicode data types throughout your system.
In Microsoft SQL Server, these data types support Unicode data:
nchar
nvarchar
ntext
Note The n prefix for these data types comes from the SQL-92 standard for
National (Unicode) data types.
Use of nchar, nvarchar, and ntext is the same as char, varchar, and text,
respectively, except that:
Unicode supports a wider range of characters.
More space is needed to store Unicode characters.
The maximum size of nchar and nvarchar columns is 4,000 characters, not
8,000 characters like char and varchar.
Unicode constants are specified with a leading N: N'A Unicode string'.
All Unicode data uses the same Unicode code page. Collations do not control
the code page used for Unicode columns, only attributes such as comparison
rules and case sensitivity.|||SivaprakashShanmugam a écrit :
> Hi
> Can you please explain the difference between Text and nText data type in
> SqlServer and at what situation we have to use this.
NTEXT is encoded as UNICODE wich use 2 byte per chars
TEXT is encoded as ASCII wich use 1 byte per chars
This mean :
1) the amount of data is double while using NTEXT so the perfs whyle
retrieving data will be slower
2) only NTEXT an be use simultaneously by many alphabets like latin,
gr, cyrilic, hebrew, japanese, chinese...
A +

> Siva.
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************