Menu
I want to know basic difference between datatype of ntext and varchar in SQL Server 2008 R2. And in what situation should I use ntext and varchar
577
Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. Storage size, in bytes, is two times the string length that is entered. The ISO synonym for ntext is national text. text.
ntext will always store its data in a separate database page, while nvarchar(max) will try to store the data within the database record itself. So nvarchar(max) is somewhat faster (if you have text that is smaller as 8 kB). I also noticed that the database size will grow slightly slower, this is also good.
Text - Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters. nText - Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.
The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar.
From ntext, text, and image (Transact-SQL) i think that the biggest difference that should concern you is
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them.
127
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
