IMPORTANT in .NET

Encoder PDF 417 in .NET IMPORTANT

It is a common misconception that a clustered index causes the data to be physically ordered in a table. That is not entirely correct: A clustered index causes the rows in a table as well as the data pages in the doubly linked list that stores all the table data to be ordered according to the clustering key. However, this ordering is still logical. The table rows can be stored on the physical disk platters all over the place. If a clustered index caused a physical ordering of data on disk, it would create a prohibitive amount of disk activity.

As a general rule of thumb, every table should have a clustered index, and this clustered index should also be the primary key.

Several readers probably turned purple when they read that the clustered index should also be the primary key. General rule of thumb does not mean always. The primary key is not always the best choice for a clustered index. However, we don t have the hundreds of pages in this book to explain all the permutations and considerations for selecting the perfect clustering key. Even if we did have the space to devote to the topic, we would still end up with the same general rule of thumb. Clustering the primary key is always a better choice than not having a clustered index at all. You can read all the considerations required to make the appropriate choice for clustered index in the Inside SQL Server book series from Microsoft Press.

You use the CREATE INDEX Transact-SQL command to create a clustered index. The general syntax for this command is as follows:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON <object> ( column [ ASC | DESC ] [ ,...n ] ) [ INCLUDE ( column_name [ ,...n ] ) ] [ WITH ( <relational_index_option> [ ,...n ] ) ] [ ON { partition_scheme_name ( column_name ) | filegroup_name | default } ][ ; ]

We already covered the UNIQUE keyword in 3. All primary keys and unique constraints are implemented as unique indexes. The CLUSTERED and NONCLUSTERED options designate the type of index you are creating. We will cover the NONCLUSTERED option in Lesson 3, Creating Nonclustered Indexes, of this chapter. After you specify that you want to create a clustered index, you need to specify a name for your index. Every index must have a name that conforms to the rules for object identifiers. Next, you use the ON clause to specify the object to create the index against. You can create an index on either a table or a view (we cover indexed views in 7, Implementing Views ). After you specify the table or view to create the index against, you specify in parentheses the columns on which you will create the index. The ASC and DESC keywords specify whether the sort order should be ascending or descending.

4

You also use the ON clause to specify the physical storage on which you want to place the index. You can specify either a filegroup or a partition scheme for the index (we cover partition schemes in 6, Creating Partitions ). If you do not specify a location, and the table or view is not partitioned, SQL Server creates the index on the same filegroup as the underlying table or view. The next part of the CREATE INDEX command enables you to specify relational index options. Covering each option in detail is beyond the scope of this book, but Table 4-1 briefly describes the relational options you can set for an index.

Table 4-1