Foundations Of Querying

Exam objectives in this chapter:

■■ Work with Data

■■ Query data by using SELECT statements.

Transact-SQL (T-SQL) is the main language used to manage

and manipulate data in Microsoft SQL Server. This chapter

lays the foundations for querying data by using T-SQL. The

chapter describes the roots of this language, terminology, and

the mindset you need to adopt when writing T-SQL code. It

then moves on to describe one of the most important concepts

you need to know about the language—logical query

processing.

Although this chapter doesn’t directly target specific exam

objectives other than discussing the design of the SELECT

statement, which is the main T-SQL statement used to query data, the rest of the chapters in

this Training Kit do. However, the information in this chapter is critical in order to correctly

understand the rest of the book.

Lessons in this chapter:

■■ Lesson 1: Understanding the Foundations of T-SQL

■■ Lesson 2: Understanding Logical Query Processing

Before You Begin

To complete the lessons in this chapter, you must have:

■■ An understanding of basic database concepts.

■■ Experience working with SQL Server Management Studio (SSMS).

■■ Some experience writing T-SQL code.

■■ Access to a SQL Server 2012 instance with the sample database TSQL2012 installed.

(Please see the book’s introduction for details on how to create the sample database.)

i m p o r t a n t

Have you read

page xxx?

It contains valuable

information regarding

the skills you need to

pass the exam.

2 Chapter 1 Foundations of Querying

Lesson 1: Understanding the Foundations of T-SQL

Many aspects of computing, like programming languages, evolve based on intuition and the

current trend. Without strong foundations, their lifespan can be very short, and if they do

survive, often the changes are very rapid due to changes in trends. T-SQL is different, mainly

because it has strong foundations—mathematics. You don’t need to be a mathematician to

write good SQL (though it certainly doesn’t hurt), but as long as you understand what those

foundations are, and some of their key principles, you will better understand the language

you are dealing with. Without those foundations, you can still write T-SQL code—even code

that runs successfully—but it will be like eating soup with a fork!

After this lesson, you will be able to:

■■ Describe the foundations that T-SQL is based on.

■■ Describe the importance of using T-SQL in a relational way.

■■ Use correct terminology when describing T-SQL–related elements.

Estimated lesson time: 40 minutes

Evolution of T-SQL

As mentioned, unlike many other aspects of computing, T-SQL is based on strong mathematical

foundations. Understanding some of the key principals from those foundations can help

you better understand the language you are dealing with. Then you will think in T-SQL terms

when coding in T-SQL, as opposed to coding with T-SQL while thinking in procedural terms.

Figure 1-1 illustrates the evolution of T-SQL from its core mathematical foundations.

T-SQL

SQL

Relational Model

Set Theory Predicate Logic

Figure 1-1 Evolution of T-SQL.

Lesson 1: Understanding the Foundations of T-SQL Chapter 1 3

T-SQL is the main language used to manage and manipulate data in Microsoft’s main relational

database management system (RDBMS), SQL Server—whether on premises or in the

cloud (Microsoft Windows Azure SQL Database). SQL Server also supports other languages,

like Microsoft Visual C# and Microsoft Visual Basic, but T-SQL is usually the preferred language

for data management and manipulation.

T-SQL is a dialect of standard SQL. SQL is a standard of both the International Organization

for Standards (ISO) and the American National Standards Institute (ANSI). The two standards

for SQL are basically the same. The SQL standard keeps evolving with time. Following is a list

of the major revisions of the standard so far:

■■ SQL-86

■■ SQL-89

■■ SQL-92

■■ SQL:1999

■■ SQL:2003

■■ SQL:2006

■■ SQL:2008

■■ SQL:2011

All leading database vendors, including Microsoft, implement