CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008indexing
usr
usr

Reputation: 171246

How to create a disabled index in SQL Server 2008?

How to create a disabled index in SQL Server 2008? I could first create the index and then disable it, but that is a waste of time if the table is big. How can I create an index in a disabled state directly?

Background information: I want to have two databases to have identical schema objects but I only need the index in one of the two.

Upvotes: 3

Views: 2186

Answers (2)

Martin Smith
Martin Smith

Reputation: 453897

You can create the index as a hypothetical index. 

CREATE INDEX ix ON T(Col)
WITH STATISTICS_ONLY

Not sure if that meets your needs?

Upvotes: 3

JNK
JNK

Reputation: 65217

You really can't. I'm not aware of a way to create an index without having it actually be, you know, CREATED when you run the create script.

As a workaround, you could potentially create your desired index with a filter that you know will evaluate false, for instance:

CREATE INDEX ix_myindex ON MyTable(myfields)
WHERE PrimaryKeyWhichIsNeverNULL IS NULL

THEN disable it, THEN alter it to remove the filter. I'm not 100% sure this won't rebuild it though.

Upvotes: 2

Related Questions