How can I rebuild an index on table with 100 million rows without a wait time out? I have the following table with ~100 million rows:

CREATE TABLE Result (
    MetricId int NOT NULL,
    PeriodId int NOT NULL,
    Value decimal (18, 2) NOT NULL,
    CONSTRAINT PK_Result PRIMARY KEY CLUSTERED (MetricId ASC,PeriodId ASC)
)

CREATE NONCLUSTERED INDEX IX_Result_PeriodId ON Result (PeriodId ASC)


My analysis of this situation (and I'm by no means a DBA) is:


The NC index should have Value as an included column. We are
seeing queries that filter by PeriodId use the NC index but then
need a Key Lookup on the PK to get the value. I think this will give
us much better query performance.
The NC Index does not need MetricId in the key after PeriodId
because SQL Server implicitly includes any columns in the clustered
PK index.
As an aside: the main filter we use in queries is PeriodId therefore it would
be best to have the PK around the other way (ie. PeriodId,MetricId).
I also believe PeriodId is more selective (?) in that it would
make sense to filter first by PeriodId to get rid of the most
rows during a query.


My question is then - how can I create or rebuild the following index?

CREATE NONCLUSTERED INDEX IX_Result_PeriodId ON Result (PeriodId ASC) INCLUDE (Value)


I have tried editing the existing index through Management Studio which requires a rebuild, and that times out after 5-10 minutes. It's a 'Wait Timeout' I believe.

I have tried creating a brand new index through Management Studio with the same result.

I have tried putting the database in Single User mode and creating the new index. Same result.

Upgrading to Enterprise is not an option for me.

How can I create this index on such a large table?
Community Wiki answer for the solution originally provided in a question comment:


  Martin Smith: Run the script through a new query window in SSMS. Not through the visual designer.