SQL tips for application developers
this blog is for you
if you’re a developer who has to write applications that interact directly with a SQL Server database. That is to say, what you do in your day job straddles the divide between application writer and database developer. So you find yourself not only working with whatever it is you use to write applications – as if that’s not enough, you have to write database code as well. Welcome to my world.
what this is
This blog is an attempt to help you, the application writer, to use SQL effectively, with the minimum of effort. So here’s the thing – the news is good. There are a relatively small number of tips and tricks that you can easily learn, and which will help you out enormously. Each of the articles in this blog describes one of these tips, and each tip is something I really wish I had known when I first began working with apps that use SQL Server. I suffered so you don’t have to (you’re welcome).
Where possible I have provided sample SQL scripts so your suspicious disbelieving mind can satisfy itself that I’m not making this up. Where application code has been necessary I have used C# solutions, although any development language should do.
death by timeout
If you are that SQL developer (and if you’re still reading then you probably are) then you’ll be experiencing some or all of a set of common problems. Database access is invariably the slowest part of your app. Time and time again you’ll be forced back to the SQL to see if you can speed things up. Not only that, but databases don’t stay the same – tables get bigger, queries slow down. Other people will redesign objects you rely on. New and unreasonable requirements, as welcome as a fin in a swimming pool, will surface. Before you know it, your spluttered excuses will sound decreasingly convincing even to you, and you’ll be in the cross-hairs. Something Has To Be Done.
As you well know, there’s too much technology in the world to know all of it at once (there always was, but that’s another story), so you have to be disciplined in choosing what to learn. This is why many developers restrct themselves to finding out just enough about SQL Server to get hold of the data, and no more. Probably your heart sinks as you contemplate the task ahead of you, that of adding yet another skill set to your overstuffed CV (keep it to one page? how?) Nevertheless, I refer you to my previous paragraph, Something Has To Be Done.
This blog exists because once, I was you (no of course not, not really, I’m me – but just go with it for a moment). I was that unlucky coder, finally finally finally forced to go and look at the place where the data is coming from, for no other reason than to save my applications from death by timeout. I got there in the end, but the journey was far too long. This blog is your shortcut. So, are you sitting comfortably? Then we’ll begin.
oh, wait! what this is not
There is a well-travelled path (although I prefer to think of it as a kind of water-slide) from application developer to de facto dba, and this is not one of the stations along the way. If you think you are in danger of becoming a dba then my advice is to click on over to Redgate software and read Troubleshooting SQL Server: A Guide for the Accidental DBA. Or you can dive right in and start taking in-depth advice from real experts such as Brent Ozar, Pinal Dave or Grant Fritchey.
Also among the list of things this blog is not – it’s not a primer on SQL. I’m assuming that you know enough SQL to write a query or stored procedure, possibly a view, maybe a function. Or if you don’t yet know some of those things then you will as soon as you’ve had a look at the Microsoft docs pages. Finally, I’m assuming that you can handle the business of hooking up your application program to the database.