Tag Archives: SQL

Dipping a Toe in the NoSQL Pool

There’s a great deal of data floating around and it has to be put somewhere. In 1970, the relational database (RDBMS) was invented to help store it. Data that was *related* could be grouped together in a table. A schema was used to define the structure of the data within the database and the relationships among it. SQL was used to manipulate the database and the data it contained. There are many databases that use this model, such as MySQL, PostgresSQL, and Oracle.

For many years, relational databases were the cornerstones of applications. Organizations used them as the backend store for their thick-clients as well as being an integral part of the LAMP stack used in early web applications. Only recently has our software needed a little extra oomph.

The NoSQL movement promises to fulfill requirements of high availability, horizontal scaling, replication, schemaless design, and complex computational capabilities. It contests the notion that RDBMS are always the best place to store your data and opens the doors to greater freedom when choosing your storage mechanism.

The framework used to evaluate these systems is based on consistency, availability, and partition tolerance (CAP). The CAP theorem was developed by Eric Brewer to formally talk about tradeoffs in highly scalable systems1. Like other decisions made in the software world, you can only pick two out of the three criteria.

The NoSQL movement doesn’t subscribe to a particular data model like RDBMS do. There are three other models that are part of the crowd:

key-value: much like a map that supports put, get, and remove (Redis, Dynamo)

column-oriented: still uses tables like the relational model, but without joins (BigTable)

document-oriented: stores structured documents like JSON or XML (CouchDB)

You may be thinking, “Ok, so what is the best one?” I only wish the answer was that simple. Many different factors go into choosing and you are not limited to one mechanism per application. You can choose different stores for different types of data and functionality.2, 3

Structuring your application to take advantage of these data store capabilities requires analysis of your data requirements. You may need fast access or maybe your data is written more than it’s read. Perhaps you need to perform calculations such as map/reduce or graph manipulations. Maybe your data is of the binary variety. And of course, the availability rabbit hole – do you trust your server not to fail when you’ve just been featured on the 6 o’clock news (or Digg)?

While this is a lot to think about, the benefits of charting your way through the NoSQL forest are worth the effort in the long run. Your application will be better suited to expandability and your maintenance efforts may be decreased. However, there’s no cause to throw out your SQL books…just yet.

More info & references:

1. http://www.julianbrowne.com/article/viewer/brewers-cap-theorem

2. http://blog.nahurst.com/visual-guide-to-nosql-systems

3. http://blog.heroku.com/archives/2010/7/20/nosql/

4. http://architects.dzone.com/news/nosql-old-wine-new-bottle

SQL: The Non-standard Standard

In a previous blog titled “Supercharging Your SQL Statements,” Fred Winkler touched upon some interesting capabilities inherent to the SQL Query Language. It is an unfortunate fact that while SQL as a standard is widely accepted, the actual implementation of that standard varies considerably between vendors.

Variation from the standard may not necessarily be a bad thing, although it is quite confusing to those who aren’t expecting it. Let’s look at the TRIM example Fred used.

SELECT TRIM(FIELD) from TABLE

That should be easy enough; however, MSSQL doesn’t support the use of the TRIM function. Instead, you have to use a LTRIM (Left Trim), RTRIM (Right Trim) or a combination of both.

(MSSQL Syntax)
SELECT (LTRIM(RTRIM(FIELD))) from TABLE

Not the most pleasant expression to work with, but it gets the job done. This is a good example where deviation from the standard may make the resulting script look more confusing. Continue reading

Supercharging Your SQL Statements

Structured Query Language (SQL) is a database computer language designed for managing data within relational databases. The most common applications for SQL are actions (calls) on the database using SELECT, UPDATE, DELETE and INSERT (data) functions.  SQL can provide many other database calls that will contribute to the reduction of user programs.   SQL includes built-in functionality that can help format and calculate data during any of these database actions/calls.  Below are some examples of using SQL functions to accomplish these processes. Continue reading