QUERYING MULTIPLE DATABASE OBJECTS AND MANIPULATING RESULT SETS in Font

Maker Code39 in Font QUERYING MULTIPLE DATABASE OBJECTS AND MANIPULATING RESULT SETS

Summary

This chapter focused heavily on new and advanced data access topics for those creating solutions with SQL Server Express. The two main advanced topics include Implementing joins to build your own ad hoc collections of database objects for custom queries Using subqueries to create single SELECT statements that answer multiple and complex questions The new topics include Four T-SQL functions for ranking and ordering data (ROW_NUMBER, RANK, DENSE_RANK, and NTILE) CTEs, which allow you to reuse query statements. They can drastically simplify the task of building complex queries and are especially well-suited for mapping hierarchical relationships Techniques for pivoting relational tables into cross tabs and reconstructing relational tables from data in a cross-tab format (PIVOT and UNPIVOT operators) Between focusing on advanced and new topics, the chapter presented a variety of traditional database processing techniques that most database developers and administrators will find useful at one time or another. Some of these topics include Sorting your data multiple ways with the ORDER BY clause Appending result sets to facilitate asking queries across multiple data sources Saving result sets to new tables

atabase objects that encapsulate T-SQL code make that code easier to reuse. This is an important technique because it lets you multiply the benefits of your investments in T-SQL code development. You will therefore find it more worthwhile to create solutions with T-SQL when you encapsulate your code in database objects. SQL Server Express offers four database objects for packaging T-SQL code: Views User-defined functions Stored procedures Triggers This chapter examines techniques for creating, managing, and referencing each of these four types of objects. You can leverage that is, reuse database objects by 1. Creating a database object with the code that you want to reuse 2. Referencing the database object in one or more other code blocks Database object types distinguish themselves by the roles that they can play in a database solution. One goal of this chapter is to characterize the database objects in terms of the benefits that they bring to a solution. The skills that you gain in this chapter will motivate you to create and apply database objects in your custom database solutions.

Views are virtual tables. That is, a view can represent a set of rows like a table. However, a view is not actually a table. Instead, a view is a SELECT statement that performs data access, such as many of the T-SQL code samples in s 5 and 6. The difference between a SELECT statement and a view is that a view encapsulates a SELECT statement as an object in a database. You can make a new view with a CREATE VIEW statement similarly to the way that you make a table with a CREATE TABLE statement. A CREATE VIEW statement contains a SELECT statement defining the rowset that a view represents, while a CREATE TABLE statement contains specifications for columns and constraints (see 4 for more detail on creating tables). You can drop and modify views with the DROP VIEW and ALTER VIEW statements. A SELECT statement in a database object, such as a view, is more readily reusable than a SELECT statement in a .sql file.

You can reference a view in the FROM clause of a SELECT statement because a view is a database object that acts like a virtual table. This capability to be referenced in a FROM clause dramatically enhances accessibility to the filtering, ordering, and aggregating features of a SELECT statement relative to a SELECT statement in a .sql file. You can also change a view s underlying data sources by referring to the view in the same way that you do a table in INSERT, UPDATE, and DELETE statements. If a view s concluding clause is WITH CHECK OPTION, you can force INSERT and UPDATE statements that reference the view to conform to criteria within a view s SELECT statement.