1

When I developed applications Database First, I would always set limits (the likely maximum required) on varchar or nvarchar fields as this reduced SQL Server page size - and was generally seen as best practice.

I notice that Code First, MVC 4, EF5 apps seem to just set string fields to nvarchar(max) unless specifically set.

Is it Best Practice to annotate all model strings with [MaxLength(n)]? I have seen little mention of this but it seems like a bad idea to be using nvarchar(max) at the SQL Server level?!

Am I missing something? Are there any benchmarks or hard stats to back this up either way.

What is seen as best practice?

Thanks.

niico
  • 11,206
  • 23
  • 78
  • 161
  • 1
    http://stackoverflow.com/questions/4378795/sql-performance-is-there-any-performance-hit-using-nvarcharmax-instead-of-nva – ta.speot.is Jun 09 '13 at 04:19
  • so basically you're saying I *shouldn't* bother using MaxLength unless there's a specific reason to limit the length of a string? Is there accepted best practice here? – niico Jun 09 '13 at 04:33
  • 1
    Restrict it where a MaxLength makes sense (e.g. A phone number or the field needs to be indexed). Otherwise I don't see the point, it's not like MAX fields are an order of magnitude slower. – ta.speot.is Jun 09 '13 at 04:51

1 Answers1

3

I would absolutely recommend specifying maxlength. Your assumption that it's a bad idea to always use nvarchar(max) is correct (my opinion).

There are pros and cons to both approaches.

Pros for using nvarchar(max)

  • Faster to develop - less analysis of what data may be stored in a particular column
  • More flexible - you don't have to worry about modifying the schema when your name field is 100 characters and a person with a 101 character name comes along

Cons against using nvarchar(max)

  • Slightly slower
  • No restrictions on your data - why let a user store a 3000 character name, addresss, etc.?
  • Less descriptive to other developers - nvarchar(2) on a state field vs nvarchar(max) on a state field. It's more obvious what should be stored just by looking at the schema definition.
  • nvarchar(max) cannot be indexed.
Tom Studee
  • 10,316
  • 4
  • 38
  • 42
  • thx - what are your thoughts on the link ta.speot.is posted above? – niico Jun 09 '13 at 04:34
  • @niico revised my answer to include pros and cons as I believe there is some subjectivity in this topic. – Tom Studee Jun 09 '13 at 13:05
  • [A couple of other points in my answer here](http://stackoverflow.com/questions/2009694/is-there-an-advantage-to-varchar500-over-varchar8000/5654947#5654947) – Martin Smith Jun 15 '13 at 12:01