Software Vendor Mistakes With SQL Server: Not Enforcing Code Consistency

Practice Makes Perfect

---

Often when working with clients, the coding practices read like geological stratum. You can tell when and who wrote something based on the practices they used.

This is bad news, though, because it speaks to lone-wolf style development. That might be okay if all your SQL developers are well-trained, etc. But that’s mostly not the case.

It’s especially important for junior developers to have code reviewed by others so they don’t introduce bad/worst practices into production code, and for legacy code to get reviewed to make sure it’s up to current best practices.

Otherwise, you’re always going to have problems. Here’s where I see folks making mistakes, mostly because they don’t have any internal “style guide” for queries.

I don’t mean formatting — we’ll talk about that tomorrow — I mean the choices you make while you’re writing queries.

The Style Council

---

Consider just some of the options you have when writing queries:

• Temp Table or Table Variable
• Common Table Expression or Derived Table
• SELECT INTO or INSERT SELECT
• Inline Table Valued Function or View
• ISNULL or COALESCE
• Cursor or Set-Based Code

For all of these options, there are good use cases, and bad ones.

• Most of the time you want to use #temp tables, but code executed at high frequency could benefit from @table variables
• Common Table Expressions can be re-used, but the entire query inside them is executed every time they’re referenced
• SELECT INTO is convenient and may get a fully parallel insert without a tablock hint, but may also incorrectly interpret certain attributes like data type or length
• Views may be more intuitive to write, but inline table valued functions can be more useful for parameterized queries
• ISNULL is easier to spell, but it only takes two arguments and there are differences in how data types are interpreted between the two
• Often, set-based code will be more efficient, but there are many valid uses for cursors

Futuristic

---

If your application is using newer versions of SQL Server:

• Are you using STRING_SPLIT?
• Are you using STRING_AGG?

Or are you still using older methods to split strings or aggregate strings?

You could extend this to a few other things, too, like the additional programmability features added to SQL Server 2016 starting with SP1.

There’s a lot to consider and keep up with in SQL Server. Having internal documentation about this stuff is the key to making sure your code:

• Aligns with current best practices
• Comments thoroughly on deviations
• Takes advantage of new paradigms and patterns in SQL Server

If that’s the kind of thing you need help with, hit the link below to set up a free sales call to discuss your needs.

Thanks for reading!

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.