Software Vendor Mistakes With SQL Server: Not Strongly Typing Parameters

Month Out

---

Far and away, my most common clients are people who create or host applications that run on top of SQL Server. Over the years, I’ve noticed some common trends, but I’ve never written a cohesive set of posts that bring them all together in an easy to find reference.

All the posts in this series will be under the “Software Vendor Mistakes” tag, and I’ll have a roundup post at the end that links to them all. The topics I’m going to cover are purely from the perspective of software development, not infrastructure.

Many infrastructure decisions are made before problems with code, indexes, etc. are fixed, or to cover them up. I understand that line of thinking, but I want to reach the people who can make fundamental changes so a clear picture of what hardware is necessary comes through.

Enough preamble. Business time.

Parameter Inference

---

When working with ORMs, care has to be taken to strongly type your parameters to match the data type, length, precision, and scale of the columns those parameters will be compared to. Time and time again, I see the same patterns with string parameters:

• They’re unnecessarily typed as Unicode/nvarchar
• They’re not defined with an appropriate length
• They’re used as catch-all parameters for temporal types (dates, etc.)

These coding malfeasances cause issues with:

• Implicit conversions (index scans 🙀)
• Unnecessary later filters (predicates that can’t be used as seek operators)
• Plan cache pollution (compiling a new plan for every query)

I see this in particular with applications that attempt to be “database agnostic”, which almost seems to really mean “database atheistic”. It’s like developers don’t believe that databases really exist, and they can flaunt the rules that govern reliable performance.

Sure, the implicit conversion thing can happen outside of ORMs, but the length thing isn’t generally an issue.

Examples

---

I don’t have a handy example of the length issue causing plan cache pollution outside of this Stack Exchange Q&A: Parameterized query creating many plans

But that’s only because I recently moved computers and I’m not quite set up for that demo. But an implicit conversion demo is quite easy enough to demonstrate when you have a varchar column and you compare it to an nvarchar predicate. I do that in this video, normally part of my paid training:

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.