//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Other Articles :: What datatype should I use for my character-based database columns?


What datatype should I use for my character-based database columns?

Often I see the phrase "I have an NVARCHAR column..." and sometimes have to keep myself from asking "why did you choose that datatype?" Most times, this datatype isn't chosen intentionally; when you upsize from Access to SQL Server, or transfer from other database products such as Sybase SQL Anywhere, this is the default datatype applied to character-based columns (possibly to ensure that any Unicode data stored in such columns would not be lost / corrupted). 
 
When designing your database, you should really try to understand your data, and the datatype that suits it best. Here is an outline of the datatypes available for character-based data: 
 
CHARCHAR supports fixed width strings, up to 8,000 characters. Best used when data length is constant, e.g. social security numbers or ISBN numbers.
NCHARSimilar to CHAR, with the support of Unicode characters. You should only use this datatype if you need Unicode support - due to storage overhead (2 bytes per character means the maximum length of your string is 4,000 characters).
VARCHARVARCHAR supports variable length strings, up to 8,000 characters. Best used when data length is variable, e.g. last names or product SKU codes.
NVARCHARSimilar to VARCHAR, with the support of Unicode characters. You should only use this datatype if you need Unicode support - due to storage overhead (2 bytes per character means the maximum length of your string is 4,000 characters).
TEXTTEXT supports variable length strings, up to 2 GB, stored off row with a 16-byte pointer in the record itself. Should be used whenever your data will exceed the 8,000 character limit of CHAR/VARCHAR columns (though if you need only 12,000 or even 24,000 characters, you might consider overflowing into multiple VARCHAR columns, rather than use the inflexible and less efficient TEXT datatype).
NTEXTSimilar to TEXT, with the support of Unicode characters. You should only use this datatype if you need Unicode support - due to storage overhead (2 bytes per character means the maximum length of your string is only 1 GB).
VARCHAR(MAX) 
SQL 2005 ONLY
Similar to TEXT, however this 2GB capacity is employed much more appropriately... you can now use regular string functions (CHARINDEX, REPLACE, LEFT), and you no longer need WRITETEXT/UPDATETEXT.
NVARCHAR(MAX) 
SQL 2005 ONLY
Identical to VARCHAR(MAX) except with half the capacity (due to support of Unicode characters).
 
Of course, choosing TEXT/NTEXT in MSDE limits your options somewhat - if you plan to store 1 or 2 GB in these columns, you can only store a couple at most before exceeding the capacity of the entire database (see Article #2345 for more information). 
 
So, if you inherited nchar/nvarchar/ntext columns from an upsize or import, consider changing those that do not need to support Unicode characters to their non-Unicode datatype equivalents. If you do need to support Unicode strings, make sure you use an N prefix (see KB #239530 for more information): 
 
INSERT fakeTable(nCharColumn) VALUES(N'foo')
 
Access 
 
Access only supports TEXT (255) and MEMO (64 KB when entered through the GUI, and 1 GB when entered programmatically), so the choice here is much easier - use TEXT unless you need more than 255 characters. In addition, if you only need 10 or 20 characters, don't accept the default size (50) as this will be a considerable waste of space.

Related Articles

How do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing <text>?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?

 

 


Created: 8/11/2002 | Last Updated: 2/7/2006 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (147)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...