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

For example, there is a string fie开发者_开发百科ld Name in my C# (Linq-to-SQL); what data type would that SQL field have to be?

varchar? nchar? I'm confused.

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.

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.

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

• 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.