CREATE INDEX
Creates an index on a given table or view.
Only the table or view owner can create indexes on that table. The owner of a table or view can create an index at any time, whether or not there is data in the table. Indexes can be created on tables or views in another database by specifying a qualified database name.
Syntax
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]
< index_option > :: =
{ PAD_INDEX |
FILLFACTOR = fillfactor |
IGNORE_DUP_KEY |
DROP_EXISTING |
STATISTICS_NORECOMPUTE |
SORT_IN_TEMPDB
}
Arguments
UNIQUE
Creates a unique index (one in which no two rows are permitted to have the same index value) on a table or view. A clustered index on a view must be UNIQUE.
Microsoft® SQL Server™ checks for duplicate values when the index is created (if data already exists) and checks each time data is added with an INSERT or UPDATE statement. If duplicate key values exist, the CREATE INDEX statement is canceled and an error message giving the first duplicate is returned. Multiple NULL values are considered duplicates when UNIQUE index is created.
When a unique index exists, UPDATE or INSERT statements that would generate duplicate key values are rolled back, and SQL Server displays an error message. This is true even if the UPDATE or INSERT statement changes many rows but causes only one duplicate. If an attempt is made to enter data for which there is a unique index and the IGNORE_DUP_KEY clause is specified, only the rows violating the UNIQUE index fail. When processing an UPDATE statement, IGNORE_DUP_KEY has no effect.
SQL Server does not allow the creation of a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set. If attempted, SQL Server displays an error message; duplicates must be eliminated before a unique index can be created on the column(s).
CLUSTERED
Creates an object where the physical order of rows is the same as the indexed order of the rows, and the bottom (leaf) level of the clustered index contains the actual data rows. A table or view is allowed one clustered index at a time.
A view with a clustered index is called an indexed view. A unique clustered index must be created on a view before any other indexes can be defined on the same view.
Create the clustered index before creating any nonclustered indexes. Existing nonclustered indexes on tables are rebuilt when a clustered index is created.
If CLUSTERED is not specified, a nonclustered index is created.
Note Because the leaf level of a clustered index and its data pages are the same by definition, creating a clustered index and using the ON filegroup clause effectively moves a table from the file on which the table was created to the new filegroup. Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index. It is important that the filegroup have at least 1.2 times the space required for the entire table.
NONCLUSTERED
Creates an object that specifies the logical ordering of a table. With a nonclustered index, the physical order of the rows is independent of their indexed order. The leaf level of a nonclustered index contains index rows. Each index row contains the nonclustered key value and one or more row locators that point to the row that contains the value. If the table does not have a clustered index, the row locator is the row's disk address. If the table does have a clustered index, the row locator is the clustered index key for the row.
Each table can have as many as 249 nonclustered indexes (regardless of how they are created: implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX). Each index can provide access to the data in a different sort order.
For indexed views, nonclustered indexes can be created only on a view with a clustered index already defined. Thus, the row locator of a nonclustered index on an indexed view is always the clustered key of the row.
index_name
Is the name of the index. Index names must be unique within a table or view but do not need to be unique within a database. Index names must follow the rules of identifiers.
table
Is the table that contains the column or columns to be indexed. Specifying the database and table owner names is optional.
view
Is the name of the view to be indexed. The view must be defined with SCHEMABINDING in order to create an index on it. The view definition also must be deterministic. A view is deterministic if all expressions in the select list, and the WHERE and GROUP BY clauses are deterministic. Also, all key columns must be precise. Only nonkey columns of the view may contain float expressions (expressions that use float data type), and float expressions cannot be used anywhere else in the view definition.
To find a column in the view that is deterministic, use the COLUMNPROPERTY function (IsDeterministic property). The IsPrecise property of the function can be used to determine that the key columns are precise.
A unique clustered index must be created on a view before any nonclustered index is created.
Indexed views may be used by the query optimizer in SQL Server Enterprise or Developer edition to speed up the query execution. The view does not need to be referenced in the query for the optimizer to consider that view for a substitution.
When creating indexed views or manipulating rows in tables participating in an indexed view, seven SET options must be assigned specific values. The SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNING must be ON. The SET option NUMERIC_ROUNDABORT must be OFF.
If any of these settings is different, data modification statements (INSERT, UPDATE, DELETE) on any table referenced by an indexed view fail and SQL Server raises an error listing all SET options that violate setting requirements. In addition, for a SELECT statement that involves an indexed view, if the values of any of the SET options are not the required values, SQL Server processes the SELECT without considering the indexed view substitution. This ensures correctness of query result in cases where it can be affected by the above SET options.
If the application uses a DB-Library connection, all seven SET options on the server must be assigned the required values. (By default, OLE DB and ODBC connections have set all of the required SET options correctly, except for ARITHABORT.)
Some operations, like BCP, replication, or distributed queries may fail to execute their updates against tables participating in indexed views if not all of the listed SET options have the required value. In the majority of cases, this issue can be prevented by setting ARITHABORT to ON (through user options in the server configuration option).
It is strongly recommended that the ARITHABORT user option be set server-wide to ON as soon as the first indexed view or index on a computed column is created in any database on the server.
See the Remarks section for more information on considerations and restrictions on indexed views.
column
Is the column or columns to which the index applies. Specify two or more column names to create a composite index on the combined values in the specified columns. List the columns to be included in the composite index (in sort-priority order) inside the parentheses after table.
Note Columns consisting of the ntext, text, or image data types cannot be specified as columns for an index. In addition, a view cannot include any text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement.
Composite indexes are used when two or more columns are best searched as a unit or if many queries reference only the columns specified in the index. As many as 16 columns can be combined into a single composite index. All the columns in a composite index must be in the same table. The maximum allowable size of the combined index values is 900 bytes. That is, the sum of the lengths of the fixed-size columns that make up the composite index cannot exceed 900 bytes. For more information about variable type columns in composite indexes, see the Remarks section.
[ASC | DESC]
Determines the ascending or descending sort direction for the particular index column. The default is ASC.
n
Is a placeholder indicating that multiple columns can be specified for any particular index.
PAD_INDEX
Specifies the space to leave open on each page (node) in the intermediate levels of the index. The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. By default, SQL Server ensures that each index page has enough empty space to accommodate at least one row of the maximum size the index can have, given the set of keys on the intermediate pages. If the percentage specified for FILLFACTOR is not large enough to accommodate one row, SQL Server internally overrides the percentage to allow the minimum.
Note The number of rows on an intermediate index page is never less than two, regardless of how low the value of FILLFACTOR.
FILLFACTOR = fillfactor
Specifies a percentage that indicates how full SQL Server should make the leaf level of each index page during index creation. When an index page fills up, SQL Server must take time to split the index page to make room for new rows, which is quite expensive. For update-intensive tables, a properly chosen FILLFACTOR value yields better update performance than an improper FILLFACTOR value. The value of the original FILLFACTOR is stored with the index in sysindexes.
When FILLFACTOR is specified, SQL Server rounds up the number of rows to be placed on each page. For example, issuing CREATE CLUSTERED INDEX ... FILLFACTOR = 33 creates a clustered index with a FILLFACTOR of 33 percent. Assume that SQL Server calculates that 5.2 rows is 33 percent of the space on a page. SQL Server rounds so that six rows are placed on each page.
Note An explicit FILLFACTOR setting applies only when the index is first created. SQL Server does not dynamically keep the specified percentage of empty space in the pages.
User-specified FILLFACTOR values can be from 1 through 100. If no value is specified, the default is 0. When FILLFACTOR is set to 0, only the leaf pages are filled. You can change the default FILLFACTOR setting by executing sp_configure.
Use a FILLFACTOR of 100 only if no INSERT or UPDATE statements will occur, such as with a read-only table. If FILLFACTOR is 100, SQL Server creates indexes with leaf pages 100 percent full. An INSERT or UPDATE made after the creation of an index with a 100 percent FILLFACTOR causes page splits for each INSERT and possibly each UPDATE.
Smaller FILLFACTOR values, except 0, cause SQL Server to create new indexes with leaf pages that are not completely full. For example, a FILLFACTOR of 10 can be a reasonable choice when creating an index on a table known to contain a small portion of the data that it will eventually hold. Smaller FILLFACTOR values also cause each index to take more storage space.
The following table illustrates how the pages of an index are filled up if FILLFACTOR is specified.
| FILLFACTOR | Intermediate page | Leaf page |
|---|---|---|
| 0 percent | One free entry | 100 percent full |
| 1 - 99 percent | One free entry | <= FILLFACTOR percent full |
| 100 percent | One free entry | 100 percent full |
One free entry is the space on the page that can accommodate another index entry.
Important Creating a clustered index with a FILLFACTOR affects the amount of storage space the data occupies because SQL Server redistributes the data when it creates the clustered index.
IGNORE_DUP_KEY
Controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index. If IGNORE_DUP_KEY was specified for the index and an INSERT statement that creates a duplicate key is executed, SQL Server issues a warning and ignores the duplicate row.
If IGNORE_DUP_KEY was not specified for the index, SQL Server issues an error message and rolls back the entire INSERT statement.
The table shows when IGNORE_DUP_KEY can be used.
| Index type | Options |
|---|---|
| Clustered | Not allowed |
| Unique clustered | IGNORE_DUP_KEY allowed |
| Nonclustered | Not allowed |
| Unique nonclustered | IGNORE_DUP_KEY allowed |
DROP_EXISTING
Specifies that the named, preexisting clustered or nonclustered index should be dropped and rebuilt. The index name specified must be the same as a currently existing index. Because nonclustered indexes contain the clustering keys, the nonclustered indexes must be rebuilt when a clustered index is dropped. If a clustered index is recreated, the nonclustered indexes must be rebuilt to take the new set of keys into account.
The DROP_EXISTING clause enhances performance when re-creating a clustered index (with either the same or a different set of keys) on a table that also has nonclustered indexes. The DROP_EXISTING clause replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. The nonclustered indexes are rebuilt once, and only if the keys are different.
If the keys do not change (the same index name and columns as the original index are provided), the DROP_EXISTING clause does not sort the data again. This can be useful if the index must be compacted.
A clustered index cannot be converted to a nonclustered index using the DROP_EXISTING clause; however, a unique clustered index can be changed to a non-unique index, and vice versa.
Note When executing a CREATE INDEX statement with the DROP_EXISTING clause, SQL Server assumes that the index is consistent, that is, there is no corruption in the index. The rows in the specified index should be sorted by the specified key referenced in the CREATE INDEX statement.
STATISTICS_NORECOMPUTE
Specifies that out-of-date index statistics are not automatically recomputed. To restore automatic statistics updating, execute UPDATE STATISTICS without the NORECOMPUTE clause.
Important Disabling automatic recomputation of distribution statistics may prevent the SQL Server query optimizer from picking optimal execution plans for queries involving the table.
SORT_IN_TEMPDB
Specifies that the intermediate sort results used to build the index will be stored in the tempdb database. This option may reduce the time needed to create an index if tempdb is on a different set of disks than the user database, but it increases the amount of disk space used during the index build.
For more information, see tempdb and Index Creation.
ON filegroup
Creates the specified index on the given filegroup. The filegroup must have already been created by executing either CREATE DATABASE or ALTER DATABASE.
Remarks
Space is allocated to tables and indexes in increments of one extent (eight 8-kilobyte pages) at a time. Each time an extent is filled, another is allocated. Indexes on very small or empty tables will use single page allocations until eight pages have been added to the index and then will switch to extent allocations. For a report on the amount of space allocated and used by an index, use sp_spaceused.
Creating a clustered index requires space available in your database equal to approximately 1.2 times the size of the data. This is space in addition to the space used by the existing table; the data is duplicated in order to create the clustered index, and the old, nonindexed data is deleted when the index is complete. When using the DROP_EXISTING clause, the space needed for the clustered index is the amount of space equal to the space requirements of the existing index. The amount of additional space required also may be affected by the FILLFACTOR specified.
When creating an index in SQL Server 2000, you can use the SORT_IN_TEMPDB option to direct the database engine to store the intermediate index sort results in tempdb. This option may reduce the time needed to create an index if tempdb is on a different set of disks than the user database, but it increases the amount of disk space used to create an index. In addition to the space required in the user database to create the index, tempdb must have about the same amount of additional space to hold the intermediate sort results. For more information, see tempdb and Index Creation.
The CREATE INDEX statement is optimized like any other query. The SQL Server query processor may choose to scan another index instead of performing a table scan to save on I/O operations. The sort may be eliminated in some situations.
On multiprocessor computers on SQL Server Enterprise and Developer Editions, CREATE INDEX automatically uses more processors to perform the scan and sort, in the same way as other queries do. The number of processors employed to execute a single CREATE INDEX statement is determined by the configuration option max degree of parallelism as well as the current workload. If SQL Server detects that the system is busy, the degree of parallelism of the CREATE INDEX operation is automatically reduced before statement execution begins.
Entire filegroups affected by a CREATE INDEX statement since the last filegroup backup must be backed up as a unit. For more information about file and filegroup backups, see BACKUP.
Backup and CREATE INDEX operations do not block each other. If a backup is in progress, index is created in a fully logged mode, which may require extra log space.
To display a report on an object's indexes, execute sp_helpindex.
Indexes can be created on a temporary table. When the table is dropped or the session ends, all indexes and triggers are dropped.
Variable type columns in indexes
The maximum size allowed for an index key is 900 bytes, but SQL Server 2000 allows indexes to be created on columns that may have large variable type columns with a maximum size greater than 900 bytes.
During index creation, SQL Server checks the following conditions:
- The sum of all fixed data columns that participate in the index definition must be less or equal to 900 bytes. When the index to be created is composed of fixed data columns only, the total size of the fixed data columns must be less or equal to 900 bytes. Otherwise, the index will not be created and SQL Server will return an error.
- If the index definition is composed of fixed- and variable-type columns, and the fixed-data columns meet the previous condition (less or equal to 900 bytes), SQL Server still checks the total size of the variable type columns. If the maximum size of the variable-type columns plus the size of the fixed-data columns is greater than 900 bytes, SQL Server creates the index, but returns a warning to the user. The warning alerts the user that if subsequent insert or update actions on the variable-type columns result in a total size greater than 900 bytes, the action will fail and the user will get a run-time error. Likewise, if the index definition is composed of variable-type columns only, and the maximum total size of these columns is greater than 900 bytes, SQL Server will create the index, but return a warning.
For more information, see Maximum Size of Index Keys.
Considerations when indexing computed columns and views
In SQL Server 2000, indexes also can be created on computed columns and views. Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored.
The UNIQUE or PRIMARY KEY may contain a computed column as long as it satisfies all conditions for indexing. Specifically, the computed column must be deterministic, precise, and must not contain text, ntext, or image columns. For more information about determinism, see Deterministic and Nondeterministic Functions.
Creation of an index on a computed column or view may cause the failure of an INSERT or UPDATE operation that previously worked. Such a failure may take place when the computed column results in arithmetic error. For example, although computed column c in the following table will result in an arithmetic error, the INSERT statement will work:
CREATE TABLE t1 (a int, b int, c AS a/b)
GO
INSERT INTO t1 VALUES ('1', '0')
GO
If, instead, after creating the table, you create an index on computed column c, the same INSERT statement now will fail.
CREATE TABLE t1 (a int, b int, c AS a/b)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1.c
GO
INSERT INTO t1 VALUES ('1', '0')
GO
The result of a query using an index on a view defined with numeric or float expressions may be different from a similar query that does not use the index on the view. This difference may be the result of rounding errors during INSERT, DELETE, or UPDATE actions on underlying tables.
To prevent SQL Server from using indexed views, include the OPTION (EXPAND VIEWS) hint on the query. Also, setting any of the listed options incorrectly will prevent the optimizer from using the indexes on the views. For more information about the OPTION (EXPAND VIEWS) hint, see SELECT.
Restrictions on indexed views
The SELECT statement defining an indexed view must not have the TOP, DISTINCT, COMPUTE, HAVING, and UNION keywords. It cannot have a subquery.
The SELECT list may not include asterisks (*), 'table.*' wildcard lists, DISTINCT, COUNT(*), COUNT(<expression>), computed columns from the base tables, and scalar aggregates.
Nonaggregate SELECT lists cannot have expressions. Aggregate SELECT list (queries that contain GROUP BY) may include SUM and COUNT_BIG(<expression>); it must contain COUNT_BIG(*). Other aggregate functions (MIN, MAX, STDEV,...) are not allowed.
Complex aggregation using AVG cannot participate in the SELECT list of the indexed view. However, if a query uses such aggregation, the optimizer is capable of using this indexed view to substitute AVG with a combination of simple aggregates SUM and COUNT_BIG.
A column resulting from an expression that either evaluates to a float data type or uses float expressions for its evaluation cannot be a key of an index in an indexed view or on a computed column in a table. Such columns are called nonprecise. Use the COLUMNPROPERTY function to determine if a particular computed column or a column in a view is precise.
Indexed views are subject to these additional restrictions:
- The creator of the index must own the tables. All tables, the view, and the index, must be created in the same database.
- The SELECT statement defining the indexed view may not contain views, rowset functions, inline functions, or derived tables. The same physical table may occur only once in the statement.
- In any joined tables, no OUTER JOIN operations are allowed.
- No subqueries or CONTAINS or FREETEXT predicates are allowed in the search condition.
- If the view definition contains a GROUP BY clause, all grouping columns as well as the COUNT_BIG(*) expression must appear in the view's SELECT list. Also, these columns must be the only columns in the CREATE UNIQUE CLUSTERED INDEX clause.
The body of the definition of a view that can be indexed must be deterministic and precise, similar to the requirements on indexes on computed columns. See Creating Indexes on Computed Columns.
Permissions
CREATE INDEX permissions default to the sysadmin fixed server role and the db_ddladmin and db_owner fixed database roles and the table owner, and are not transferable.
Examples
A. Use a simple index
This example creates an index on the au_id column of the authors table.
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'au_id_ind')
DROP INDEX authors.au_id_ind
GO
USE pubs
CREATE INDEX au_id_ind
ON authors (au_id)
GO
B. Use a unique clustered index
This example creates an index on the employeeID column of the emp_pay table that enforces uniqueness. This index physically orders the data on disk because the CLUSTERED clause is specified.
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'emp_pay')
DROP TABLE emp_pay
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'employeeID_ind')
DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay
(
employeeID int NOT NULL,
base_pay money NOT NULL,
commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
VALUES (1, 500, .10)
INSERT emp_pay
VALUES (2, 1000, .05)
INSERT emp_pay
VALUES (3, 800, .07)
INSERT emp_pay
VALUES (5, 1500, .03)
INSERT emp_pay
VALUES (9, 750, .06)
GO
SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay (employeeID)
GO
C. Use a simple composite index
This example creates an index on the orderID and employeeID columns of the order_emp table.
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'order_emp')
DROP TABLE order_emp
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'emp_order_ind')
DROP INDEX order_emp.emp_order_ind
GO
USE pubs
GO
CREATE TABLE order_emp
(
orderID int IDENTITY(1000, 1),
employeeID int NOT NULL,
orderdate datetime NOT NULL DEFAULT GETDATE(),
orderamount money NOT NULL
)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (5, '4/12/98', 315.19)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (5, '5/30/98', 1929.04)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (1, '1/03/98', 2039.82)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (1, '1/22/98', 445.29)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (4, '4/05/98', 689.39)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (7, '3/21/98', 1598.23)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (7, '3/21/98', 445.77)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (7, '3/22/98', 2178.98)
GO
SET NOCOUNT OFF
CREATE INDEX emp_order_ind
ON order_emp (orderID, employeeID)
D. Use the FILLFACTOR option
This example uses the FILLFACTOR clause set to 100. A FILLFACTOR of 100 fills every page completely and is useful only when you know that index values in the table will never change.
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'zip_ind')
DROP INDEX authors.zip_ind
GO
USE pubs
GO
CREATE NONCLUSTERED INDEX zip_ind
ON authors (zip)
WITH FILLFACTOR = 100
E. Use the IGNORE_DUP_KEY
This example creates a unique clustered index on the emp_pay table. If a duplicate key is entered, the INSERT or UPDATE statement is ignored.
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'emp_pay')
DROP TABLE emp_pay
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'employeeID_ind')
DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay
(
employeeID int NOT NULL,
base_pay money NOT NULL,
commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
VALUES (1, 500, .10)
INSERT emp_pay
VALUES (2, 1000, .05)
INSERT emp_pay
VALUES (3, 800, .07)
INSERT emp_pay
VALUES (5, 1500, .03)
INSERT emp_pay
VALUES (9, 750, .06)
GO
SET NOCOUNT OFF
GO
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay(employeeID)
WITH IGNORE_DUP_KEY
F. Create an index with PAD_INDEX
This example creates an index on the author's identification number in the authors table. Without the PAD_INDEX clause, SQL Server creates leaf pages that are 10 percent full, but the pages above the leaf level are filled almost completely. With PAD_INDEX, the intermediate pages are also 10 percent full.
Note At least two entries appear on the index pages of unique clustered indexes when PAD_INDEX is not specified.
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'au_id_ind')
DROP INDEX authors.au_id_ind
GO
USE pubs
CREATE INDEX au_id_ind
ON authors (au_id)
WITH PAD_INDEX, FILLFACTOR = 10
G. Create an index on a view
This example will create a view and an index on that view. Then, two queries are included using the indexed view.
USE Northwind
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO
--Create view.
CREATE VIEW V1
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID
GROUP BY OrderDate, ProductID
GO
--Create index on the view.
CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (OrderDate, ProductID)
GO
--This query will use the above indexed view.
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev, OrderDate, ProductID
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID AND ProductID in (2, 4, 25, 13, 7, 89, 22, 34)
AND OrderDate >= '05/01/1998'
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC
--This query will use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID AND DATEPART(mm,OrderDate)= 3
AND DATEPART(yy,OrderDate) = 1998
GROUP BY OrderDate
ORDER BY OrderDate ASC