0

I have been advocating lately that all string fields should be ntext / nvarchar(max) - we're using MS SQL Server. The objections seem to be either "it's not a good idea" (without any reason) or "that would allow the customer to send a lot of crap". Since I believe in filtering out the crap before it hits the database, is there a good reason why my idea is bad?

My main argument for not limiting the string fields is that I have discovered that we have about six different types of addresses (customer, reseller and so on), with about 20 markets (each having its own database) and environments. I have spotted at least ten different lengths for say Address1 and the same thing happens for other fields. Instead of "standardizing" them and then discovering that we missed something, I suggested that we go ahead and limit the inputs and let the database handle anything.

Does anyone have a pro/con I don't see?

(I have seen Rule of thumb for field sizes but my focus is "is making everything max sized wrong?")

5
  • This might help answer some of your questions stackoverflow.com/a/4380625/1021726 Mar 5, 2014 at 13:19
  • Thanks; I wasn't worried about the performance issues but the points about the indices being created OFFLINE is a good one. Mar 5, 2014 at 13:36
  • Another small comment is that if you set it to nvarchar(max) and then apply a filter arbitrarily in your business layer, it might be hard to track down any future address-related bugs/issues. It's better to keep data restrictions in the data layer, and not mix layer-logic with each other. Mar 5, 2014 at 13:41
  • 1
    Martin Smith's answer provides a very good reason for why not to do this stackoverflow.com/questions/2009694/…
    – billinkc
    Mar 5, 2014 at 16:08
  • Hmm, I think this dba.stackexchange.com/a/47941/35182 is even more compelling. Mar 6, 2014 at 10:54

3 Answers 3

2

I suggest you go to https://dba.stackexchange.com/ or the MSDN SQL forums and ask that question. You might receive physical threats by suggesting such a thing.

From a programming point of view, it makes sense. Why should you not just let the database hold whatever, and then limit it in the application? From a data modeling point of view this is like blasphemy. You should find out what the actual maximum allowed is, and use that, or go through and structure the database so each market has its own specific address tables. Aside from the performance issue, it is bad database design. It is bad database design because an Address is an actual thing, and every country has very specific rules about addresses. In the United States, the documentation is... daunting. But it exists.

You might also consider that just because the database is not intended to be populated from anywhere except this application, it is possible that it might end up that way later.

1
  • Thanks for the pointer to the dba site, I wasn't aware of it. There are dozens of applications, written by hundreds of people, writing to this database - hence the many incompatible field sizes. Also, there are at least 20 countries so far - I've already spent a couple of days just researching their alphabets, I am not going to research address rules :) Mar 5, 2014 at 15:40
1

While it is tempting to swing for the fence and provide more than enough storage (ever interfaced with a COBOL system with tiny, fixed-width records?), it is not necessarily a good idea.

There is a performance penalty for huge text fields as user1021726 pointed out. You will also likely not be able to create indexes on those fields.

My rule of thumb is I look at some sample data, find the longest reasonable string that could be entered, and add 50%. If an address can be at most 40 characters, make the field 60. This does not apply to fixed-width strings such as an SSN or federal tax ID.

I would also think twice about using Unicode ("N" for "National" character) on every field. While it is certainly a very good idea for something like a name or address which the end user will see, I would avoid it for any internal field that might be used by an index or join and the user cannot directly enter data into it. I know that with at least SQL Server, Unicode makes indexes and joins perform slower. Again in SQL Server, indexes can only be so many bytes too, and NVARCHAR is twice as wide. For these internal fields using shorter VARCHAR fields will definitely help performance.

4
  • The problem with that rule of thumb is that other people will have other rules, or other estimates for "the longest reasonable string", and we still end up with incompatible lengths. Mar 5, 2014 at 15:38
  • That is what I do: in a team environment, the team needs to agree on what to do. For core data types it helps to have a data dictionary that specifies common field names, types, and lengths. I am just talking about how I have gotten to that point in previous experiences.
    – user22815
    Mar 5, 2014 at 15:40
  • I'm on your side with the "the team needs to agree" part. However, I think it's easier to get the team to agree with "make everything nvarchar(max)" than with "make every address1 200 chars long, every address2 180 chars long, every suburb 50 chars long..." Mar 5, 2014 at 15:41
  • My answer outlines why NVARCHAR(MAX) might not be a one-size-fits-all solution.
    – user22815
    Mar 5, 2014 at 15:50
0

You could make everything max, but then you have to test against it. Performance when every field maxes out at 2 GB of storage? Probably not good.

Therefore, its always a good idea to put limits on things so you will now how the system will perform and to limit the data the system can accept and transmit.

2
  • I strongly doubt that the customers will enter 2GB of data in each field, especially with me restricting the field sizes on the web layer. Mar 5, 2014 at 15:38
  • Your designing the system to handle the edge case. Proper datasizing is critical for indexing. There is overhead for using this type of field. dba.stackexchange.com/questions/47910/…
    – Jon Raynor
    Mar 5, 2014 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.