Foundations of T-SQL

I’ve start preparing for the 70-461 exam and have just started looking at the training kit guide book. The stuff in this exam is the stuff I do on a daily basis so I should be able to get through it quicker than the stuff for the DBA exam.

I assumed I could just skim through the book and pick out the bits I didn’t know that well. I started at the beginning and was quite surprised that I didn’t remember ever reading the stuff they talk about in chapter 1.

This chapter is titled “Foundations of Querying” and it briefly talks about the the origin and evolution of SQL and the T-SQL dialect.

It all stems from the mathematical principals of set theory and predicate logic.
In set theory a set is collection of objects that are considered as a whole and not as individual objects. A set has no duplicates and the set {a, a, b} is considered to be the same as the set {a, b}. There is also no order to a set and {b, a, c} is considered to be the same as {a, b, c}.

In 1969 Edgar F. Codd came up with the relational model. I’ve obviously heard of the relational model but having never read anything on te theory behind I was under the misconception that the relational bit meant that the tables are related to each other by foreign keys.

However a relation is a mathematical term and has a heading and a body. The heading is a set of attributes, each of a particular type, and the body is a set of tuples.

The standard SQL language (of which T-SQL is a dialect) stems from this relational model and tries to represent a relation with a table, attributes with columns and tuples with rows.
The relational model also has predicate logic as a basis. A predicate is an expression that when applied to an object, gives the result true or false.

The main point this chapter seems to be getting across is that if you inderstand the concepts in the relational model then you should understand how to write better T-SQL with the basic premise being that you perform actions on sets of data and not process data iteratively with things like loops and cursors. The bit about sets not containing duplicates and not being ordered tells us that we should have primary keys on our tables and that the engine is not going to care about the ordering of the data.

If you’ve never heard of the relational model or set theory and you write any SQL it’s worth looking into it so understand how best to write your queries.

The guide book recommends SQL and Relational Theory, Second Edition by C. J. Date (O’Reilly Media, 2011) and I’m going to try and get a copy of this.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s