Hi,

I threw a query thats was taking time to delete records at the DB tunning
advisor (SQL 2005 standard).

It suggested the creation of the following non-clustered index:

------------------------------------------------------------------------------------------------------------------------------------

CREATE NONCLUSTERED INDEX [_dta_index_TABLE1_100_343__K4] ON [dbo].[TABLE1]

(

[DID] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF,
ONLINE = OFF) ON [PRIMARY]

------------------------------------------------------------------------------------------------------------------------------------


Just trying to figure out what the component parts of the create index
statement mean before running ?


WITH (SORT_IN_TEMPDB = OFF, : what does this mean ?

DROP_EXISTING = OFF, what does this mean ?

IGNORE_DUP_KEY = OFF, what does this mean ?

ONLINE = OFF) what does this mean ?

ON [PRIMARY] what does this mean ?



Thanks for any .

These are all documented in Books Online (under CREATE INDEX). I suggest you read the descriptions
there first and then ask here for further clarifications if needed.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Scott" <scott_lotus@yahoo.co.uk> wrote in message news:%23liKu2oEJHA.4420@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> I threw a query thats was taking time to delete records at the DB tunning advisor (SQL 2005
> standard).
>
> It suggested the creation of the following non-clustered index:
>
> ------------------------------------------------------------------------------------------------------------------------------------
>
> CREATE NONCLUSTERED INDEX [_dta_index_TABLE1_100_343__K4] ON [dbo].[TABLE1]
>
> (
>
> [DID] ASC
>
> )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
>
> ------------------------------------------------------------------------------------------------------------------------------------
>
>
> Just trying to figure out what the component parts of the create index statement mean before
> running ?
>
>
> WITH (SORT_IN_TEMPDB = OFF, : what does this mean ?
>
> DROP_EXISTING = OFF, what does this mean ?
>
> IGNORE_DUP_KEY = OFF, what does this mean ?
>
> ONLINE = OFF) what does this mean ?
>
> ON [PRIMARY] what does this mean ?
>
>
>
> Thanks for any .
>
>

On Tue, 9 Sep 2008 16:06:47 +0100, "Scott" <scott_lotus@yahoo.co.uk>
wrote:

>CREATE NONCLUSTERED INDEX [_dta_index_TABLE1_100_343__K4] ON [dbo].[TABLE1]
>(
>[DID] ASC
>)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF,
>ONLINE = OFF) ON [PRIMARY]
>
>WITH (SORT_IN_TEMPDB = OFF, : what does this mean ?
>DROP_EXISTING = OFF, what does this mean ?
>IGNORE_DUP_KEY = OFF, what does this mean ?
>ONLINE = OFF) what does this mean ?
>ON [PRIMARY] what does this mean ?

Books Online is the documentation installed with SQL Server. It
really isn't possible to use SQL Server effectively without using
Books Online. It is normally installed along with the tools. If you
don't have a copy, it can be downloaded from the Microsoft web site
and installed on your local machine. Microsoft updates it
occasionally so even if you have a copy you might want to make sure it
is current.

Some of the information that can be found below.

Roy Harvey
Beacon Falls, CT

SORT_IN_TEMPDB = { ON | OFF }
Specifies whether to store temporary sort results in tempdb. The
default is OFF.

ON
The intermediate sort results that are used to build the index are
stored in tempdb. This may reduce the time required to create an index
if tempdb is on a different set of disks than the user database.
However, this increases the amount of disk space that is used during
the index build.

OFF
The intermediate sort results are stored in the same database as the
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.

In backward compatible syntax, WITH SORT_IN_TEMPDB is equivalent to
WITH SORT_IN_TEMPDB = ON.

IGNORE_DUP_KEY = { ON | OFF }
Specifies the error response to duplicate key values in a multiple-row
insert operation on a unique clustered or unique nonclustered index.
The default is OFF.

ON
A warning message is issued and only the rows violating the unique
index fail.

OFF
An error message is issued and the entire INSERT transaction is rolled
back.

The IGNORE_DUP_KEY setting applies only to insert operations that
occur after the index is created or rebuilt. The setting has no effect
during the index creation operation.

IGNORE_DUP_KEY cannot be set to ON for XML indexes and indexes created
on a view.

In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to
WITH IGNORE_DUP_KEY = ON.

DROP_EXISTING = { ON | OFF }
Specifies that the named, preexisting clustered, nonclustered, or XML
index is dropped and rebuilt. The default is OFF.

ON
The existing index is dropped and rebuilt. The index name specified
must be the same as a currently existing index; however, the index
definition can be modified. For example, you can specify different
columns, sort order, partition scheme, or index options.

OFF
An error is displayed if the specified index name already exists.

The index type, relational or XML, cannot be changed by using
DROP_EXISTING. Also, a primary XML index cannot be redefined as a
secondary XML index, or vice versa.

In backward compatible syntax, WITH DROP_EXISTING is equivalent to
WITH DROP_EXISTING = ON.

ONLINE = { ON | OFF }
Specifies whether underlying tables and associated indexes are
available for queries and data modification during the index
operation. The default is OFF.

Note:
Online index operations are available only in SQL Server 2005
Enterprise Edition.



ON
Long-term table locks are not held for the duration of the index
operation. During the main phase of the index operation, only an
Intent Share (IS) lock is held on the source table. This enables
queries or updates to the underlying table and indexes to proceed. At
the start of the operation, a Shared (S) lock is held on the source
object for a very short period of time. At the end of the operation,
for a short period of time, an S (Shared) lock is acquired on the
source if a nonclustered index is being created; or an SCH-M (Schema
Modification) lock is acquired when a clustered index is created or
dropped online and when a clustered or nonclustered index is being
rebuilt. ONLINE cannot be set to ON when an index is being created on
a local temporary table.

OFF
Table locks are applied for the duration of the index operation. An
offline index operation that creates, rebuilds, or drops a clustered
index, or rebuilds or drops a nonclustered index, acquires a Schema
modification (Sch-M) lock on the table. This prevents all user access
to the underlying table for the duration of the operation. An offline
index operation that creates a nonclustered index acquires a Shared
(S) lock on the table. This prevents updates to the underlying table
but allows read operations, such as SELECT statements.

thank you for the post.