Why do I need to set the maximum length of each column of text in the database?

Question

Why do all RDBMS insist that you say what the maximum length of the text field will be ... why can't he just derive this information from the data that is placed in the database?

I mainly worked with MS SQL Server, but every other database that I know also requires that you set these arbitrary restrictions in your data schema. The reality is that it is not particularly useful or friendly to work because business requirements change all the time, and almost every day some end user tries to put a lot of text in this column.

Does anyone with some in-house knowledge of RDBMS know why we just don’t conclude about the limitations of the data that is stored in the repository? I'm not talking about guessing type information, but guessing the limits of a particular text column.

I mean, there is a reason I don't use nvarchar (max) for every text column in the database.

Answer 1

Because computers (and databases) are stupid. Computers do not know very well, and if you do not tell them, they cannot say that the column will be used for a phone number or a copy of War and Peace. Obviously, the database can be designed so that each column can contain an infinite amount of data - or at least as much as disk space allows, but it will be a very inefficient design. To get efficiency, we compromise and force the designer to tell the database how much we expect to put in the column. Presumably, it can be installed by default, so if you don't specify it, it just uses it. Unfortunately, any default is likely to be inappropriate for the vast majority of people in terms of effectiveness.

Answer 2

This is related to speed. If the maximum row size is specified, you can optimize the way information is stored for faster I / O. When speed is key, the last thing you want is a sudden shuffle of all your data just because you changed the state abbreviation to the full name.

When the maximum size is set, the database can allocate maximum space for each object in this column and regardless of changes in the value, which should not change the address space.

Answer 3

This post not only answers your question about whether to use nvarchar(max) everywhere, but also gives some insight into why databases have historically not allowed this.

Answer 4

This is like saying: why can't we just indicate the database that we want to get in the table and indicate what type and how many columns we need from the data that we give.

We just know better than the database. Suppose you have one of a million chances to put 2000 characters in a database, most of the time - 100 characters. The database is likely to explode or cancel the 2k character string. He simply cannot know that you will need a length of 2k if during the first three years you entered only 100 lines of length.

In addition, character lengths are used to optimize line placement so that lines can be read / skipped faster.

Answer 5

I think this is because the RDBMS uses random access to data. To make random access to data, they need to know which address on the hard drive they must go to quickly read the data. If each row of one column has a different data length, they cannot conclude that it is the starting point of the address to which they must jump directly to get it. The only way is to download all the data and check it.

If RDBMS changes the length of the column data by a fixed number (for example, the maximum length of all rows) every time you add, update, and delete. It is extremely labor intensive.

Answer 6

What would the database justify? If business requirements change regularly, it will be as amazing as you. If there is a reason why you are not using nvarchar (max), perhaps the reason is that it is also not the default ...

Answer 7

check out this protector http://www.sqlservercentral.com/Forums/Topic295948-146-1.aspx

Answer 8

For example, I’m going to switch to a few quicksand and offer to compare it with applications that allocate memory (RAM). Why don't programmers request / allocate all the memory they need when starting the program? Because often they don’t know how much they need. This can cause applications to take up more and more memory when they are launched, and possibly also freeing up memory. You have several applications running at the same time, new applications are launched and old applications are closed. And applications always want adjacent memory blocks, they work poorly (if at all) if their memory is scattered throughout the address space. Over time, this leads to fragmented memory and all these garbage collection problems that people tear off their hair for decades.

Return to the databases. Do you want this to happen to your hard drives? (Remember, hard drive performance is very, very slow compared to memory operations ...)

Answer 9

It looks like your business rule: enter as much information as possible in any text field so that you do not get angry with the database administrator.

You do not allow users to enter 5,000 characters, as they do not fit into the envelope.

That's why Twitter has a text limit and eliminates the need to read through a bunch of meaningless chatter that just goes on and on, and never gets to the point, but only manages to annoy the reader, making them wonder why you have to give up their time, choosing an egocentric and inhuman lifestyle, focused on promoting the act of copying and pasting as much data as possible, since the gods of the memory buffer will allow ...