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?")

  • This might help answer some of your questions stackoverflow.com/a/4380625/1021726 – user1021726 Mar 5 '14 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. – Marcel Popescu Mar 5 '14 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. – user1021726 Mar 5 '14 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 '14 at 16:08
  • Hmm, I think this dba.stackexchange.com/a/47941/35182 is even more compelling. – Marcel Popescu Mar 6 '14 at 10:54
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.

| improve this answer | |
  • 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 :) – Marcel Popescu Mar 5 '14 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.

| improve this answer | |
  • 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. – Marcel Popescu Mar 5 '14 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 '14 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..." – Marcel Popescu Mar 5 '14 at 15:41
  • My answer outlines why NVARCHAR(MAX) might not be a one-size-fits-all solution. – user22815 Mar 5 '14 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.

| improve this answer | |
  • 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. – Marcel Popescu Mar 5 '14 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 '14 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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