Sergiy (2/11/2016)

---

Orlando Colamatteo (2/11/2016)

---

Sergiy (2/11/2016)

---

Hugo Kornelis (2/11/2016)

---

A pretty dangerous thing to do, and an incredibly irresponsible advice to put out on forums.

Believe me, unfortunately - it's not.

Those who know what their data types mean and why they are there won't listen to it, as they don't need it anyway.

Those who don't know their data - they'll have to learn it in order to decide which length to use for VARCHAR data types.

Replacing the max datatypes with shorter versions is fine if you know for sure that you will never need a larger length.

Placing the max datatypes is fine only if you know for sure what you will need a larger length for.

But that is in most cases impossible to know without reading the code and talking to users and managers.

Yes, in most cases it's impossible for developers to explain why did they use MAX data types.

They use it just because they don't want to think about table design.

Forcing them to decide on an actual length limit for string values will make them think about it.

Which is good.

So, it's pretty good advice after all.

An interesting conclusion. You still did not address a concern I also had with your approach where you improved system performance 70 times. The one statement from Hugo you decided not to quote and refute or address, this:

Never, ever, ever shorten a data type without analyzing the code, verifying your assumptions, and setting up a good test.

You said yourself you did not read one line of code. I am curious how you landed on NVARCHAR(500) for some columns and NVARCHAR(100) for others and determined all of it was safe. Did you profile the data? What was your method?

The database was 4 years in production.

Longest string in the fields was just below 100 characters.

There was no any trend of increasing length over time.

So, I concluded 500 will be just enough.

5 years on - no complaints so far.