MSSQL - Should I mention column max length? I am a bit confused from what I read so far regarding to this issue.
In my database I usually write the max length of a column when I create a table or add a column.
Because I need to support other languages than English, some of my columns are nvarchar type,
the problem occurs when a programmer is not aware he can insert to a nvarchar column half of the length mentioned and this is causes sometimes errors.
My question is what would happen if I will stop writing the max length of the columns, what would be the actual size of the content, any performance affect?
We are validating the content is the client and server side so if I will have a column without max length it won't be more than what we defined in the server side so I am not worry about this.
This is 100% an education problem for your developers and that is where this needs to be addressed.
Not specifying variable column length is a bad idea that will probably break almost immediately for you. Spoiler: it gives severely small lengths to the column.
Similarly, using MAX as the length by default is also a bad idea and will set you up for performance and architectural challenges down the line.
You should stick to using the smallest variable length fields that fit your business requirements and do some sessions with your developers to ensure they understand how SQL Server handles variable length data types, or check their work more closely in pull requests.
In the case where you have already tried this (which I hope is not the case) I have written a script sp_sizeoptimiser that flags occurrences of this problem to help you root out and remove them.