first_page -------------0x000000000000 0xA82200000100 0x981B00000100 0xD81B00000100 in Visual Basic .NET

Generate Universal Product Code version A in Visual Basic .NET first_page -------------0x000000000000 0xA82200000100 0x981B00000100 0xD81B00000100

index_id ----------1 1 1 1

partition_number ---------------1 2 1 2

You ll notice that the second partition of TransactionHistoryArchive now has exactly the same information that the rst partition of TransactionHistory had in the rst result set. It has the same number of rows (11,155), the same number of pages (209), and the same starting page (0xD81B00000100, or le 1, page 7,128). No data was moved; the only change was that the allocation unit starting at le 1, page 7,128 is not recorded as belonging to the second partition of the TransactionHistoryArchive table.

Although my partitioning script created the indexes for my partitioned tables using the same partition scheme used for the tables themselves, this is not always necessary. An index for a partitioned table can be partitioned using the same partition scheme or a different one. If you do not specify a partition scheme or legroup when you build an index on a partitioned table, the index is placed in the same partition scheme as the underlying table, using the same partitioning column. Indexes built on the same partition scheme as the base table are called aligned indexes. However, an internal storage component is associated with automatically aligned indexes. As previously mentioned, if you build an index on a partitioned table and do not specify a legroup or partitioning scheme on which to place the index, SQL Server creates the index using the same partitioning scheme that the table uses. However, if the partitioning column is not part of the index de nition, SQL Server adds the partitioning column as an extra included column in the index. If the index is clustered, adding an included column is not necessary because the clustered index already contains all the columns. Another case in which SQL Server does not add an included column automatically is when you create a unique index, either clustered or nonclustered. Because unique partitioned indexes require that the partitioning column is contained in the unique key, a unique index for which you have not explicitly included the partitioning key is not partitioned automatically.

Summary

In this chapter, we looked at how SQL Server 2008 stores data that doesn t use the normal FixedVar record format and data that doesn t t into the normal 8-KB data page. I discussed row-over ow and large object data, which is stored on its own separate pages, and lestream data, which is stored outside SQL Server, in les in the lesystem. Some of the new storage capabilities in SQL Server 2008 require that we look at row storage in a completely different way. Sparse columns allow us to have very wide tables of up to 30,000 columns, so long as most of those columns are NULL in most rows. Each row in a table containing sparse columns has a special descriptor eld that provides information about which columns are non-NULL for that particular row. I also described a completely new row storage format used with compressed data. Data can be compressed at either the row level or the page level, and the rows and pages themselves describe the data that is contained therein. This type of row format is referred to as the CD format. Finally we looked at partitioning of tables and indexes. Although partitioning doesn t really require a special format for your rows and pages, it does require accessing the metadata in a special way.

8

The Query Optimizer in Microsoft SQL Server 2008 determines the query plan to be executed for a given SQL statement. Because the Query Optimizer does not have a lot of exposed features, it is not as widely understood as some of the other components in the SQL Server Engine. This chapter describes the Query Optimizer and how it works. After reading this chapter, you should understand the high-level optimizer architecture and should be able to reason about why a particular plan was selected by the Query Optimizer. By extension, you should be able to troubleshoot problem query plans in the case when the Query Optimizer may not select the desired query plan and how to affect that selection. This chapter is split into two sections. The rst section explains the basic mechanisms of the Query Optimizer. This includes the high-level structures that are used and how this de nes the set of alternatives considered for each plan. The second section discusses speci c areas in the Query Optimizer and how they t into this framework. For example, it discusses topics like How do indexes get selected , How do statistics get used , and How do I understand update plans