Table composite Index position has performance affect?

Question

Hello, we have the following similar Index on a particular table as example:

CREATE NONCLUSTERED INDEX [Products_IDX] ON [dbo].[Products]
(
[Product_ID] ASC,
[Purchase_Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

When running like a Delete statement (on a large table) using a WHERE clause on [Purchase_Date] column will the Delete statement run faster or since the position of this column is not the first one listed it will not be used and performance will be slow?

Thanks in advance.

Answer 1

The delete statement will not be able to seek the index to find the rows, since the purchase_date isn't the first column in the index key. It might scan the index, you'd have to consult the execution plan to see what it decided.

You can execute that create index command, but it will be blocked by the delete operation since it requires a very restrictive locked - and the delete also has conflicting locks. When the delete has finished and released it locks, the create index can proceed.

Answer 2

Thank you both Tibor & Criszhan for the replies, much appreciated.

Answer 3

Hi,

>When running like a Delete statement (on a large table) using a WHERE clause on [Purchase_Date] column will the Delete statement run faster or since the position of this column is not the first one listed it will not be used and performance will be slow?

The order of columns in a multi-column nonclustered index will affect performance depending on your t-sql statement(The use of columns in the WHERE clause).

There will be a performance slowness in your case — only use the second key column of the composite index in Where clause of delete statement.. I tested this on select and delete statements in my environment, Index scan replaces seek.

Also please check the following posts for more details.
https://stackoverflow.com/questions/28475877/does-the-order-of-columns-in-an-multicolumn-non-clustered-index-matter-in-sql-se
https://stackoverflow.com/questions/2292662/how-important-is-the-order-of-columns-in-indexes

>Is it possible/safe to run this while Delete is still currently running?

I tested a table with 5 million rows in my environment. Before the end of the delete operation, the session that created the clustered index is in the executing state.
It is recommended that you run the clustered index creation after the delete operation is complete. This may also help free up space.

Answer 4

Also our large Delete is still currently running.

Was suggested to run the following adding further to this [Products] table:

ALTER TABLE [dbo].[ERSEarningsVersion] ADD CONSTRAINT [ERSEarningsVersion_PK2] PRIMARY KEY CLUSTERED
(
[Import_Seq_Nbr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Is it possible/safe to run this while Delete is still currently running?

Thanks.