Chapter 7
Adding More to Your Queries
WHAT YOU WILL LEARN IN THIS CHAPTER:
- How to create and use nested and correlated subqueries
- Syntax and purpose of derived tables
- Several ways to use Common Table Expressions (CTEs)
- How to make use of the EXISTS operator
- Data manipulation with MERGE
- Ranking output rows with windowing functions
- Ad hoc paging with OFFSET and FETCH
- Tips on optimizing query performance
There are a number of topics in SQL Server that depend on other knowledge, which is a challenge in organizing a book like this. For example, there are things you’re about to learn how to do in queries that would be easier if you knew about user-defined functions, or scripting, or variables, but at the same time it’s difficult to teach about functions without knowing a few T-SQL statements to put in there. Besides, some of these statements are things that might help a beginning user see some real possibilities, so I’m going to start in on some more interesting things here in an effort to whet your appetite.
Some of the concepts in this chapter are going to challenge you with a new way of thinking. You already had a taste of this just dealing with joins, but you haven’t had to deal with the kind of depth that I want to challenge you with in this chapter. Even if you don’t have that much procedural programming experience, the fact is that your brain has a natural tendency to break complex problems down into their smaller subparts (sub-procedures, logical steps) as opposed to solving ...
