programming4us
programming4us
DATABASE

SQL Server 2008 R2 : Creating and Managing Indexes - Creating Indexes (part 1) - Creating Indexes with T-SQL

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
9/1/2012 3:52:04 AM
The following sections examine the most common means for creating indexes in SQL Server. Microsoft provides several different methods for creating indexes, each of which has advantages. The method used is often a matter of personal preference, but there are situations in which a given method has distinct advantages.

Creating Indexes with T-SQL

Transact-SQL (T-SQL) is the most fundamental means for creating an index. This method was available in all previous versions of SQL Server. It is a very powerful option for creating indexes because the T-SQL statements that create indexes can be stored in a file and run as part of a database installation or upgrade. In addition, T-SQL scripts that were used in prior SQL Server versions to create indexes can be reused with very little change.

You can create indexes by using the T-SQL CREATE INDEX command. Listing 1 shows the basic CREATE INDEX syntax.

Listing 1. CREATE INDEX Syntax
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]

Table 1 lists the CREATE INDEX arguments.

Table 1. Arguments for CREATE INDEX
ArgumentExplanation
UNIQUEIndicates that no two rows in the index can have the same index key values. Inserts into a table with a UNIQUE index will fail if a row with the same value already exists in the table.
CLUSTERED | NON-CLUSTEREDDefines the index as clustered or nonclustered. NON-CLUSTERED is the default. Only one clustered index is allowed per table.
index_nameSpecifies the name of the index to be created.
objectSpecifies the name of the table or view to be indexed.
column_nameSpecifies the column or columns that are to be indexed.
ASC | DESCSpecifies the sort direction for the particular index column. ASC creates an ascending sort order and is the default. The DESC option causes the index to be created in descending order.
INCLUDE (column [ ,... n ] )Allows a column to be added to the leaf level of an index without being part of the index key. This is a new argument.
WHERE <filter_predicate>This argument, new to SQL Server 2008, is used to create a filtered index. The filter_predicate contains a WHERE clause that limits the number of rows in the table that are included in the index.
relational_index_optionSpecifies the index option to use when creating the index.

Following is a simple example using the basic syntax of the CREATE INDEX command:

CREATE NONCLUSTERED INDEX [NC_Person_LastName]
ON [Person].[Person]
(
[LastName] ASC
)

This example creates a nonclustered index on the person.person table, based on the LastName column. The NONCLUSTERED and ASC keywords are not necessary because they are the defaults. Because the UNIQUE keyword is not specified, duplicates are allowed in the index (that is, multiple rows in the table can have the same LastName).

Unique indexes are more involved because they serve two roles: they provide fast access to the data via the index’s columns, but they also serve as a constraint by allowing only one row to exist on a table for the combination of column values in the index. They can be clustered or nonclustered. Unique indexes are also defined on a table whenever you define a unique or primary key constraint on a table. The following example shows the creation of a nonclustered unique index:

CREATE UNIQUE NONCLUSTERED INDEX [AK_CreditCard_CardNumber]

ON [Sales].[CreditCard]
(
        [CardNumber] ASC
)

This example creates a nonclustered index named AK_CreditCard_CardNumber on the Sales.CreditCard table. This index is based on a single column in the table. When it is created, this index prevents credit card rows with the same credit card number from being inserted into the CreditCard table.

The relational index options listed in Table 2 allow you to define more sophisticated indexes or specify how an index is to be created.

Table 2. Relational Index Options for CREATE INDEX
ArgumentExplanation
PAD_INDEX = {ON | OFF}Determines whether free space is allocated to the non-leaf-level pages of an index. The percentage of free space is determined by FILLFACTOR.
FILLFACTOR = fillfactorDetermines the amount of free space left in the leaf level of each index page. The fillfactor values represent a percentage, from 0 to 100. The default value is 0. If fillfactor is 0 or 100, the index leaf-level pages are filled to capacity, leaving only enough space for at least one more row to be inserted.
SORT_IN_TEMPDB = {ON | OFF}Specifies whether intermediate sort results that are used to create the index are stored in tempdb. Using them can speed up the creation of the index (if tempdb is on a separate disk), but it requires more disk space.
IGNORE_DUP_KEY = {ON | OFF}Determines whether multirow inserts will fail when duplicate rows in the insert violate a unique index. When this option is set to ON, duplicate key values are ignored, and the rest of the multirow insert succeeds. When it is OFF (the default), the entire multirow insertfails if a duplicate is encountered.
STATISTICS_NO_RECOMPUTE = {ON | OFF}Determines whether distribution statistics used by the Query Optimizer are recomputed. When ON, the statistics are not automatically recomputed.
DROP_EXISTING = {ON | OFF}Determines whether an index with the same name is dropped prior to re-creation. This can provide some performance benefits over dropping the existing index first and then creating. Clustered indexes see the most benefit.
ONLINE = {ON | OFF}Determines whether the index is built such that the underlying table is still available for queries and data modification during the index creation. 
ALLOW_ROW_LOCKS = {ON | OFF}Determines whether row locks are allowed when accessing the index. The default for this new feature is ON.
ALLOW_PAGE_LOCKS = {ON | OFF}Determines whether page locks are allowed when accessing the index. The default for this new feature is ON.
MAXDOP = number of processorsDetermines the number of processors that can be used during index operations. The default for this new feature is 0, which causes an index operation to use the actual number of processors or fewer, depending on the workload on the system. This can be a useful option for index operations on large tables that may impact performance during the operation. For example, if you have four processors, you can specify MAXDOP = 2 to limit the index operation to use only two of the four processors.
DATA_COMPRESSION = { NONE | ROW | PAGE} [ ON PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] )Determines whether data compression is used on the specified index. The compression can be done on the row or page level and specific index partitions can be compressed if the index uses partitioning.

The following example creates a more complex index that utilizes several of the index options described in Table 25.2:

CREATE NONCLUSTERED INDEX [
IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person]
(
   [LastName] ASC,
   [FirstName] ASC,
   [MiddleName] ASC
  )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, FILLFACTOR=80)


					  

This example creates a nonclustered composite index on the person’s last name (LastName), first name (FirstName), and middle name (MiddleName). It utilizes some of the commonly used options and demonstrates how multiple options can be used in a single CREATE statement.

Tip

SQL Server Management Studio (SSMS) has several methods for generating the T-SQL code that creates indexes. You therefore rarely need to type index CREATE statements from scratch. Instead, you can use the friendly GUI screens that enable you to specify the common index options, and then you can generate the T-SQL script that can be executed to create the index.


Additional syntax options (not listed here) relate to backward compatibility and the creation of indexes on XML columns. 

Other  
  •  SQL Server 2008 R2 : Creating and Managing Indexes - Types of Indexes
  •  SQL Server 2005 : Basic OLAP - Building Your First Cube (part 5) - Using the Dimension Designer
  •  SQL Server 2005 : Basic OLAP - Building Your First Cube (part 4) - Using the Dimension Wizard
  •  SQL Server 2005 : Basic OLAP - Building Your First Cube (part 3) - Creating a Cube with the Cube Wizard, Using the Cube Designer
  •  SQL Server 2005 : Basic OLAP - Building Your First Cube (part 2) - Adding a Data Source View
  •  SQL Server 2005 : Basic OLAP - Building Your First Cube (part 1) - Creating the Project
  •  SQL Server 2005 : Basic OLAP - OLAP 101
  •  SQL Server 2005 : Report Server Architecture
  •  SQL Server 2005 : Report Management - Publishing, SQL Server Management Studio
  •  SQL Server 2005 : Report Access and Delivery (part 2) - Presentation Formats, Programming: Rendering
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us
    programming4us
     
     
    programming4us