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

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.

Answer 2

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 3

I always prefer to choose nvarchar: Differences between varchar and nvarchar in SQL Server

Answer 4

• Varchar is the most common, but it is only used for ASCII text.
• NVarchar is the default for storing strings, because it works with Unicode.
• Char is fixed-length char. It is pretty rare. Almost nobody uses it any more.
• NChar is fixed-length unicode char. I've never seen it used.
• Text and NText are unlimited binary storage for huge strings. They cannot be indexed, so you might want to avoid them.