NVARCHAR sizes in SQL Server 2005

Question

In SQL Server 2005 (not 7.0), is there any reason to use NVARCHAR(255) instead of 256 or some other number?
Is there any optimal size, and is there any reason to use powers of two?

(I will be storing Unicode data, so I have to use NVARCHAR)

Answer 1

The optimal size is the greatest length you will need to store your data.

Answer 2

In SQL 2005+ there is a NVarChar(max) that uses that maximum size available to you. You can store up to 2GB in that. Its the same as NText

It doesnt really matter what you store as they are all pretty much the same

Answer 3

In SQL Server 2005, no. The only reason to use 255 is if you need to consider portability with other platforms. Some other database flavors change the name of the type completely after 255 characters.

Answer 4

No, there is no advantage of choosing maximum field lengths that are powers of two.

It's a holdover from the old and crusty days of software development where strings less than 256 characters long were stored one way, and strings over 255 characters another way.

Answer 5

In SQL Server (2000 and earlier IIRC), the optimal (possibly even maximum) size of columns is dependent on the size of all columns together, excluding (n)text and image contents. I believe 8K is the 'natural' size of records in a table, so your column size added together should be something less than 8000 bytes.

From SQL Server 2005 an higher this isn't much of an issue, I believe.