When creating a database, what choices you think when determining how large your nvarchar ought to be.

Basically ended up being to make a previous address table my stomach reaction could be for address line 1 to become nvarchar(255) as an old access database.

I've discovered by using this has me in make use of that old 'The string could be truncated'. I understand that this is often avoided by restricting the input box but when a person really includes a address line one that's over 255 this ought to be permitted.

How large must i make my nvarchar(????)

I do not use nvarchar personally :-) I usually use varchar.

However, I am inclined to use 100 for title and 1000 for comments. Trapping and with longer strings is one thing the customer can perform, say via regex, so SQL only will get the information it needs.

You are able to avoid truncation errors be parameterising the calls, for instance via saved procs. When the parameter is understood to be varchar(200), say, then truncation happens quietly should you send > 200. The truncation error is tossed just for an Place or UPDATE statement: with parameters it will not happen.

The 255 "limit" for SQL Server dates back to six.5 because vachar was restricted to 255. SQL Server 7. + transformed to 8000 and added support for unicode

Edit:

Why I do not use nvarchar: Double memory footprint, double index size, double disk size, simply have no need for it. Sometimes for any large Swiss company with offices globally so I am not parochial.

Also talked about here: varchar vs nvarchar performance

On further reflection, I'd suggest unicode attracts client designers but like a developer DBA I concentrate on performance and efficiency...

My recommendation: make sure they are just like large as you actually need them.

E.g. for any zipcode column, 10-20 chars are certainly enough. Ditto for any telephone number. E-Mails may be longer, 50-100 chars. Names - well, It's my job to manage with 50 chars, ditto for first names. You could and simply extend fields if you will need to - that's no a large undertaking whatsoever.

There is no reason for making all varchar/nvarchar fields as large as they possibly can be. In the end, a SQL Server page is bound and restricted to 8060 bytes per row. Getting 10 fields of NVARCHAR(4000) is simply requesting trouble.... (since should you really attempt to fill all of them with an excessive amount of data, SQL Server will barf to you).

If you actually need a very large area, use NVARCHAR/VARCHAR(MAX) - individuals are saved inside your page, as lengthy because they fit, and will also be delivered to "overflow" storage when they get too large.

NVARCHAR versus. VARCHAR: this really boils lower to do you want "exotic" figures, for example Japanese, Chinese, or any other non-ASCII style figures? In Europe, even a few of the eastern European figures can't be symbolized by VARCHAR fields any longer (they'll be removed of the hachek (? spelling ?). European languages (British, German, French, etc.) are ok offered by VARCHAR.

BUT: NVARCHAR does use two times just as much space - on disk as well as in your SQL Server memory - whatsoever occasions. If it ought to be, you really need it - but would you REALLY ? :-) The choice is yours.

Marc

It is dependent on which the area signifies. If I am carrying out a quick prototype I leave the defaults of 255. For anything like comments etc I'd most likely place it to 1000.

The only method I'd allow it to be more compact is really on a few things i certainly be aware of siez of, zip codes or NI amounts etc.