Why you should always define column lengths

One of our projects recently had severe performance issues which were caused by database queries. Eventually we figured out that the problem was the memory allocation caused by table columns which were all using NVARCHAR(MAX).

The reason that all the columns were using this type was accommodativeness and the belief that nowadays it doesn't matter anymore if you define the column lengths, since it is dynamic and there is enough memory anyway. This was causing SQL server to request memory grants for queries for much more memory than actually was needed. It is optimizing assuming to the length of the column as you can see in the following picture.

For this example query, 42 MB were requested, but only 2MB were used. This resulted in high memory consumption, slow performance and even timeouts.

Conclusion

It always makes sense to limit the columns to the maximum expected length. It helps your SQL Server to optimize queries and optimize memory management, especially when you select a lot of string columns in a query. Other benefits are of course that you improve the data integrity and also have some validation on a lower level.