Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Goalie35
Yak Posting Veteran
81 Posts |
 Posted - 2007-08-28 : 21:11:29
|
| I'm creating a table that needs to hold a large text field (up to 4000 characters) and I'm curious about the best data type to use for this.I mean I know I can use varchar(4000) or nvarchar(4000) but what's the best 1 to use? Also, what does the "max" represent when using something like nvarchar(max)? Should I use this "max" size instead?Thanks.-Goalie35 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-28 : 22:08:00
|
| Use varchar(max). check out books online for more info on it.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-28 : 22:33:10
|
| Use nvarchar for unicode data, varchar for regular data, varchar(max) for more than 8000 characters. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-29 : 01:45:09
|
Remember that there is a performance penalty with VARCHAR(MAX).It is very much handled and treated as old TEXT datatype. It is not stored in table, only a pointer to the storage area.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 06:57:57
|
| "I know I can use varchar(4000) or nvarchar(4000) but what's the best 1 to use?"Nvarchar will use TWO bytes per character, varchar will only use ONE. Nvarchar stores using unicode characters, so can store Chinese character sets etc.We use Varchar for all text UNLESS there is an EXPLICIT need for Unicode. (varchar can handle basic European accents and symbols etc provided the character set of the Display program is the same as the one used to do the Input!)The One v. Two byte sizing thingie also effects indexes on those columns, data manipulation, and so on. So I figure that Varchar is more "efficient" than Nvarchar.4,000 characters is the limit for Nvarchar; for Varchar the limit is 8,000. Beyond that you need Nvarchar(MAX) or Varchar(MAX) as the others have said.Kristen |
|
|
|
|
|
|
Data type for large text field