收录日期:2020/10/25 06:54:07 时间:2010-09-07 02:07:48 标签:sql,sql-server

What's the best data type to be used when storing strings, like a first name? I've seen varchar and nvarchar both used. Which one is better? Does it matter?

I've also heard that the best length to use is 255, but I don't know why. Is there a specific length that is preferred for strings?

nvarchar stores unicode character data which is required if you plan to store non-English names. If it's a web application, I highly recommend using nvarchar even if you don't plan on being international. The downside is that it consumes twice as much space, 16-bits per character for nvarchar and 8-bits per character for varchar.

What's the best data type to be used when storing strings, like a first name? I've seen varchar and nvarchar both used. Which one is better? Does it matter?

See What is the difference between nchar(10) and varchar(10) in MSSQL?

If you need non-ASCII characters, you have to use nchar/nvarchar. If you don't, then you may want to use char/varchar to save space.

Note that this issue is specific to MS SQL Server, which doesn't have good support for UTF-8. In other SQL implementations that do, you can use Unicode strings with no extra space requirements (for English).

I've also heard that the best length to use is 255, but I don't know why.

See Is there a good reason I see VARCHAR(255) used so often (as opposed to another length)?

Is there a specific length that is preferred for strings?

If you data has a well-defined maximum limit (e.g., 17 characters for a VIN), then use that.

OTOH, if the limit is arbitrary, then choose a generous maximum size to avoid rejecting valid data. In SQL Server, you may want to consider the 900-byte maximum size of index keys.

nvarchar means you can save unicode character inside it. there is 2GB limit for nvarchar type. if the field length is more than 4000 characters, an overflow page is used. smaller fields means one page can hold more rows which increase the query performance.