GSquared (8/13/2012)
Lynn Pettis (8/13/2012)
This is how I would code it. Also note that I am using sys.indexes, not sysindexes.
IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('schema.tablename') AND NAME ='indexname')
DROP INDEX indexname ON SCHEMA.tablename;
CREATE INDEX indexname ON SCHEMA.tablename(columnlist);
Why not simply:
Create Index IndexName on schema.tablename (columnlist)with (drop_existing = on);
Because DROP_EXISTING = ON assumes that the index already exists. From BOL:
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.
Here is a test from my SandBox database:
/****** Object: Table [dbo].[test] Script Date: 08/13/2012 14:41:53 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))
DROP TABLE [dbo].[test]
GO
/****** Object: Table [dbo].[test] Script Date: 08/13/2012 14:41:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
[testid] [int] IDENTITY(1,1) NOT NULL,
[TestVal1] [int] NULL,
[TestVal2] [varchar](255) NULL,
[TestVal3] [int] NULL,
[TestVal4] [varchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE INDEX idx_test ON dbo.test(testid) WITH (drop_existing = ON );
GO
And the error message at the end:
Msg 7999, Level 16, State 9, Line 2
Could not find any index named 'idx_test' for table 'dbo.test'.