Solved

ntext vs. text

Posted on 2003-10-29
4
53,864 Views
Last Modified: 2012-06-27
We are developing an Intranet for our organization and are using SQL 7.0 as the database for it.  We have a content table with a field to hold the text for the various html pages.  Can somebody explain to me the difference between the text and ntext datatypes?  Which is better to use for storing web page content?  Also, what is the difference between varchar and nvarchar?  I couldn't seem to find any clear-cut explanation of the differences.

Thanks
0
Comment
Question by:jsprenk55
  • 3
4 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9642605
ntext - for internaltional applications (japanese, chinese characters). Takes 2bytes per charatcer.
text - non-unicode characters.

ntext, text, and image
Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.

ntext

Variable-length Unicode data with a maximum length of 230 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-92 synonym for ntext is national text.

text

Variable-length non-Unicode data in the code page of the server and with a maximum length of 231-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

image

Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.


HTH
0
 

Author Comment

by:jsprenk55
ID: 9642652
So, If I understand this correctly, we would be OK using the text data type because this is not an international system?  Looks like the text data type takes up less space also?  

What is the difference between nvarchar and varchar?

0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9642723
Same idea for nvarchar and varchar. Nvarchar for UNICODE and varchar for NON-UNICODE.
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 100 total points
ID: 9642740
More info

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 using 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 the 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 such 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 system 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 all 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 conversion 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.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 87
how to fix this error 14 46
Extract XML Data from using TSQL 5 31
Convert SP in a format for debugging 7 0
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now