T-SQL Stored Procedures – who needs them?

The answer to this question may seem obvious – for a secure, well-performing application, of course we all need them!

But what about all those great legacy applications that are littered with embedded SQL? What if we are asked to bring these into the brave new world of rich interfaces and mobile apps (plus those still glued to your traditional PC)? I have seen plenty of applications with MSAccess and MySQL back-ends. When scaling these up, do we really have to create a plethora of stored procedures when all that embedded SQL blinks winningly up at us to be used as-is? This question can be equally posed for a new application, where developers with SQL experience but lacking the know-how to use stored procedures, gamely rush into creating the SQL while coding. And what about dynamic SQL? How do we deal with that?

OK, so let’s rewind… we know that stored procedures bring enormous benefits:

1. Increased performance – the SQL is pre-compiled, unlike embedded or dynamic SQL
2. More secure – execute access given to the stored procedures only – direct access to tables and views are denied.
3. Better separation – the database access is contained within one layer, rather than scattered throughout the application UI code.
4. Easier maintenance – wholesale change is easier to apply to a number of stored procedures rather than searching through reams of application code.
5. Easier performance tuning – with the separation described above, it is easier to examine the performance of a stored procedure in isolation from UI code and fine-tune it as necessary.
6. Easier testing – stored procedures expose an additional area of testing thereby catching bugs in a more timely manner.

And the drawbacks?

The main drawback is the increased development time and the increased skill required to build the queries (but is this a bad thing for your important application?).

As for dynamic SQL – there certainly are situations where we may want this, e.g. building a complex filter from the UI, which don’t fit the pattern of simple parameters for stored procedures.  But we can still ensure the database access is encapsulated within the stored procedure by parsing strings or using data (e.g. in table variables) and I would argue this is best practice.

So it looks like we should always aim to build our data access layer using stored procedures. But what about those drawbacks?

OK, it’s time to get real – stored procedures are not complicated – In fact they are just the same as your embedded SQL but embedded in a server-side procedure instead. That means there’s more code to write. In fact quite a lot more code as we can add auditing, error handling and security checking, amongst other very useful things. And we need a version for each CRUD action.

But, after having written many stored procedures it becomes apparent that there is an enormous amount of repetition. In fact stored procedures settle into a very definite and predictable pattern which is pretty irritating if you have to code them individually from scratch, so much so that you’d think you were missing a trick. And you would be, because let’s face it, you can generate the procedures directly from the underlying data model (i.e. the table definitions and the relationships).

In fact there is often code to generate dynamic SQL from the underlying schema within applications. The leap is to change this dynamic code such that it generates the stored procedures as part of the overall development process.

Future reading:

The stored procedure CRUD pattern laid bare (Coming Soon!)

Other (more) entertaining musings on generating stored procedures

Advertisement

What Happened to Database Design?

Database design seems to take a much lower priority on a system architecture than it used to, and I can understand why…

These are my rather generalised stakeholder categorisations based on personal industry experience;

Customers are not interested in the boring table schema demo or SQL scripts in Agile projects, and who can blame them, why would they be when there is a flashy UI to look at when it come round to sprint reviews?

Project managers are not interested as it is not a demonstrable feature. How far does the statement ‘ we have developed a fully normalised and performance tuned database for you’ really go with a customer. The customer generally wants a fully functional system that looks good that they can show their manager. Main interest factors are cost and time overruns.

UI developers refer to it as a repository and don’t care what it looks like, as far as they are concerned it can be a stack of XML files. In actual fact, these days they only care about the service layer as the database is abstracted from their layer.

Service layer developers are generally happy if they don’t have to join one table to another table. So completely de-normalised tables can be quite common when developed by WCF developers. In fact if tables are completely de-normalised they rarely need to address joining tables or addressing foreign keys.

The only parties actually interested in a good database design are the database developer as they realise that they can write SQL that really performs, and the DBA as they can see the benefits of a scaleable system that is unlikely to bloat over time.

One thing we’ve noticed is that with all the layers involved in even medium-sized systems these days, most developers tend to specialise in either one or two of the layers.  It’s tricky to find a SQL developer/DBA with good UI knowledge and vice versa. Most people pick the closest tier to their speciality and learn a little bit more about how it interacts with their own tier. This makes them more marketable and allows them to fully specialise with their own technology.

I’ve interviewed a lot of candidates over the last decade and there is a definite shift away from the data layer. I can see why…

• Good database architecture can be complex.
• Experience counts when normalising a database correctly.
• Understanding set theory is a must for good SQL.
• SQL itself is a fairly large language these days.
• UI work is much, much sexier !

We aren’t the only ones to have noticed this shift. Software Tools are starting to come out to address the lack of knowledge. Entity framework now has the option to ‘code first’ and generate a database layer from the service layer.

Whilst it’s great that there are tools and support out there for the lonely UI developer struggling to create a demo, and has little database knowledge. I get very disappointed about the number of these prototype systems which make it into production and are left for the DBA to sort out when performance starts to suck.  It brings back memories of little MS Access database systems that got upgraded to SQL Server and shoved into a full business use. They generally grew and grew, degraded and degraded to the point of complete failure.

So what’s the answer?  I wish I knew.

There is an excellent and diverse SQL Community out there willing to pass on knowledge; PASS, SQLBits, SQLServerCentral, SQLServerClub,  and I still only look for recruits that have some decent database skills in their portfolio. I firmly believe that a database is the most important foundation in a system architecture.

But perhaps part of the problem is that creating a good data layer is that it is time consuming and can be costly when done correctly. If we can all find ways of building standards and perhaps abstracting the table design changes using template stored procedures then we may at least get the Service Layer guys more interested!

Until then spare a thought for where the application is going to end up. Don’t throw your ‘code first’ database at your Production DBA and run, try spending some time up front talking to them and actually try to design a database first. You will be much more marketable in future, although probably a lot less interesting to the rest of the project team…!