MyDoggieJessie (12/19/2012)

---

I don't believe there's a way within SQL to prohibit the truncation of the strings when it's assigned to the variable. How is this proc called? If it's from an application then proper form validations should be in place to check for lengths before they are passed into the procedure.

Automatically setting everything varchar(max) should be okay (maybe more of a poor programming practice than anything) but char(max) is not always a good option...so you may want to avoid that. There is storage/memory "overhead" using char, 1 byte per character, so a char(max) = 8K - i.e. a lot of potentially wasted memory being allocated.

My advice would be to test for your parameters before assigning them to the parameters being passed into your procedure

MyDoggieJessie (12/19/2012)

---

@myself - ID10T error, of course! 8000 is the limit

MyDoggieJessie (12/19/2012)

---

I don't believe there's a way within SQL to prohibit the truncation of the strings when it's assigned to the variable. How is this proc called? If it's from an application then proper form validations should be in place to check for lengths before they are passed into the procedure.

Automatically setting everything varchar(max) should be okay (maybe more of a poor programming practice than anything) but char(max) is not always a good option...so you may want to avoid that. There is storage/memory "overhead" using char, 1 byte per character, so a char(max) = 8K - i.e. a lot of potentially wasted memory being allocated.

My advice would be to test for your parameters before assigning them to the parameters being passed into your procedure

Jeff Moden (12/19/2012)

---

There's also a disadvantage to using (for example) VARCHAR(MAX). The MAX datatypes really don't like to be joined to and it will slow down queries.

"Right Sizing" is always the correct answer but, if you're unsure of what the "right size" actually is, create the variable to be 1 larger than what you think the "right size" is.

If the DATALENGTH ends up being greater than your "right size", throw an error. While this may seem to be a chore, its part of the parameter validation that I would normally do on varchars that I'm not sure won't grow. Examples of that are address columns, name columns, etc.

pdanes (12/19/2012)

---

So I've been told, but how much? A few milliseconds greater response time against days worth of me tearing my hair out is not a difficult choice.

No argument on "Right Sizing", but that's the problem - I don't always know what is going to be fed to some of these procedures, so I estimate, add what I think is a reasonable fudge factor and then discover I didn't leave enough. One example is a delimited string. I have to parse the string and search for stuff. If the string to be parsed is longer than I expected, one of the parameters can get cut in two, and I go looking for part of a parameter, which may or may not give sensible results.

How do I find out that it's too long? That's my problem - SQL Server simply chops it off, instead of telling me that I have too much. And manually putting in logic to test the length in the application leaves me with the same problem - I have no machine-reliable way of telling how long a parameter SQL Server will allow, so I'm back to manually examining the T-SQL code, and coding the application to match. If one changes, there's no way for the other to know. I can easily change one and forget to do the other, which is where I am now.

pdanes (12/19/2012)

---

I declare varchar(100) to be safe, then I change the calling logic, or add some padding or delimiters, or some such, and suddenly I'm over 100.