Author: Itzik Ben-Gan
Publisher: Microsoft Press
Pages: 448
ISBN: 9780735658141
Print: 0735658145
Kindle: B00JDMPI0I
Audience: Beginner T-SQL developers
Rating: 5
Reviewer: Ian Stirk

A well-known SQL Server expert explains the fundamentals of T-SQL, how does he fare?

The author is well-known for explaining advanced SQL concepts in a clear manner, often providing useful tips along the way. His aim here is to explain the basics of T-SQL while incorporating good programming practices. Although the code relates specifically to SQL Server 2012, much of it is applicable to earlier and later versions, helpfully Itzik Ben-Gan typically identifies the version association with a T-SQL feature. There are some advanced areas included but they are separated out and can be read later.  

Chapter 1 Background to T-SQL Querying and Programming

The chapter opens with a look at the theoretical background of SQL, introducing SQL in the context of the relational database. SQL is a declarative language (i.e. you say what you want to do, not how you want to do it), and T-SQL is Microsoft’s version of SQL. The section continues with a brief look at Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL).

The basics of set theory are introduced, together with predicate logic (true/false). The relational model is explained in terms of propositions, predicates, and relations (i.e. tables). The importance of missing values (nulls), constraints (e.g. foreign keys) and normalization (first, second and third normal forms), are all briefly discussed. The section ends with a quick look at the life cycle of data.

The chapter proceeds with a look at SQL Server Architecture. The different flavours of SQL Server are discussed, in terms of where they run i.e. appliance, box or cloud. The concept of SQL Server instances (having multiple independent SQL Server instance on the same server/box) is discussed, with an instance being the container for one or more databases. The various system databases (master, resource, model, tempdb, and msdb) are briefly explained. The physical set up of a database (filegroups, primary, secondary and log files) are briefly examined.

The final section looks at the tables within the database that contain the actual data. Scripts are provided to create tables, and their content and structure is explained. Coding style is briefly mentioned (e.g. use white space, follow conventions). The chapter ends with a look at data integrity, examining primary keys, foreign keys, check constraints and defaults.

This chapter provides a good introduction to SQL/T-SQL, database theory, sets, normalization, constraints, SQL Server architecture etc. There’s a useful point about not relying of the use of default values. This chapter covers a lot of ground, all of it is important, however I don’t know if it should be introduced to a beginner at the start of the book. In many ways I feel chapter 2 is the real start of the book, I wonder how many absolute beginners will give up after reading this first chapter.

Chapter 2 Single-Table Queries

The chapter starts with a look at the logical order in which the various clauses of a query are processed i.e. FROM, WHERE, GROUP BY, HAVING, SELECT, and then ORDER BY. Each clause is examined in detail, and plenty of useful example code is provided to illustrate the concepts. The section ends with a look at the use of the TOP and OFFSET-FETCH filters.

The chapter continues with a look at predicates (expressions that evaluate to true/false/unknown), these include IN, BETWEEN and LIKE. They can be expanded using logical operators (e.g. AND, OR). Useful example code is provided. Next, the CASE expression (both simple and searched) is examined, with it returning a value based on conditional logic.

The NULL mark is then discussed, indicating a missing or unknown value, and is used in 3-value logic (true/false/unknown). Useful example code is provided to show how NULLs are evaluated in T-SQL.

There’s a useful section showing example string manipulations, including: CONCAT, SUBSTRING, LEFT, LEN, CHARINDEX, REPLACE, STUFF, and LIKE. This is followed by a similar section looking at date and date time manipulation.

The chapter ends with a look at the querying of system data (metadata), inspecting catalog views, information schema views, and system stored procedures/functions (e.g. get a list of tables from the sys.tables catalog view).

This chapter provided a good overview of the SELECT statement in relation to a single table. The chapter (and book as a whole) is full of useful example code and discussions to illustrate the underlying concepts. I liked the explanation of NULLS, which is often a difficult area for beginners. The string manipulation and date/time examples should also prove useful.

The author mentions that an ordered table is not a relation - while this is true, I do wonder if this level of fastidiousness is warranted in a beginner’s book – perhaps it is better explained later, away from the main body of text.

Chapter 3 Joins

A normalised table contains information about one thing (e.g. customer data), but to do something useful we typically need to join tables together. This chapter explains how tables can be joined.

The chapter opens with a look at the simplest type of join, a CROSS JOIN, where each row in a table is matched with each row in another table, resulting in a Cartesian product. The author provides both ANSI SQL-92 and ANSI SQL-89 SQL to accomplish this. Useful example data (downloadable) and results are provided. Included is some practical CROSS JOIN code, which generates an incrementing list of numbers.

The next section discusses INNER JOINs, these are the workhorse of SQL, here rows in one table are joined with rows in another table, based on matching columns. It is recommended to join using the ON syntax instead of the earlier WHERE syntax, since the former has safety advantages. Variations on the inner join are explained with examples, including: composite and non-equi joins.

The chapter ends with a look at OUTER JOINs, these can be used to identify rows that are in one table but not the other, based on their matching columns. You can have a LEFT OUTER JOIN or a RIGHT OUTER JOIN, the left or right indicates which table will be preserved. The author notes that outer joins can lead to some interesting code, often bugs, including missing values.

This chapter covers the various ways of joining tables together, with useful example code and helpful discussions.