sql

Here is another intersting tweet from What Developers Need to Know About SQL Server:

What Aaron is talking about here is that SQL is a Query Language – that is what SQL and relational databases are optimized for – getting result sets.

SQL is not a data manipulation language – yes, you can do quite a lot of manipulation of string, dates and such, but the language does not lend itself to such operations.

It is far more efficient to move such operations to the client/UI code – whatever language you use to interact with the database will almost certainly have better facilities for manipulating strings and dates.

Here is one stand alone tweet from What Developers Need to Know About SQL Server, which is rather information dense and worth expanding on:

Stuart Miller compares the mental shift required for someone who is used to OOP do develop in SQL to the mental shift those used to imperative programming (c, COBOL, Basic and more) need to go through in order to truly understand and use OOP correctly.

This different programming paradigm is set based programming, taking its name from set theory, a mathematical framework from which the relational model of databases was derived by Edgar F. Codd, which in turn gave relational databases their name.

One of the major differences between set based programming and object oriented programming has to do with how they deal with groups of related items. Where in object oriented programming, one would iterate over a collection of objects, dealing with each item at a time, set based programming deals with the group as a whole – there are no iteration constructs. That’s right – there are no loops in set based programming.

As result, the way to think about and apply operators is in groups of related items (sets, or relations, in Codds’ relational model), not individual items. Operators allow filtering, aggregating, projection, joining and more – always resulting in groups of related items (where a group can be empty or consist of a single item).

Relational theory is the basis from which relational databases have been built and the relational engines that power them are optimized for this kind of thinking – set based thinking. Most relational database do offer looping constructs – but these are most often than not best avoided as they go against the grain and can be the cause of performance issues. This is not because relational databases are slow – it is because using loops instead of operating on sets goes against how they were built and what they are optimized to do.

I will be starting a series of blogs, inspired by a twitter conversion Brent Ozar had with other database folks and the highlights of which he collated as a blog post, titled What Developers Need to Know About SQL Server.

Since these started life on twitter, they are quite short and though Brent briefly expands on some of the tweets, I believe there is value is expanding on them even further, explaining the rationale and thinking behind them.

Though Brent and the conversation are ostensibly SQL Server oriented, many of the points raised are applicable to any relational database – Oracle, MySql, PostgreSQL etc. I plan to mostly deal with these shared points.

Let me know in a comment if there is anything specific you are interested in.