What data type do I choose for storing plain text in Microsoft SQL Server 2008?

Question

For example, there is a string field Name in my C# (Linq-to-SQL); what data type would that SQL field have to be?

varchar? nchar? I'm confused.

Answer 1

SQL Server 2005+ equivalent

The best SQL type equivalent to C# String is nvarchar(max). It stores exactly the same amount of text as C# String object instance. But beware. Choosing other [n][var]char types would be more optimal on data store (meaning they could work much faster), but choosing one of these greatly depends on the data that you wish to store.

• char(n) is for fixed length strings (of length n) so if your strings don't vary in size, you would choose this type (i.e. product keys)
• varchar(n) is used for variable string lengths up to length of n (i.e. email address)
• varchar(max) is used for variable string lengths of whatever length up to 2G characters so basically the same as C# String type (i.e. blog post content)
• prepend n to these types and your data is stored in double bytes (unicode) so your strings are more versatile in multilanguage applications

Pre SQL Server 2005

Before SQL Server version 2005, [n]varchar(max) types were not supported. You had to use [n]text types instead, but they were stored differently hence were much slower compared to [n][var]char alternatives and were also more cumbersome for developers to handle and manipulate.

Answer 2

It could be any of the SQLServer types that store string data. So NVarchar, Varchar, Char, NChar.

In general, I choose to make anything text-related NVarchar, unles im 100% sure only latin charater set is needed in which case you can safely go with Varchar.