change-clustered-index.sql

begin transaction
go

-- http://msdn.microsoft.com/en-us/library/windowsazure/ee336275.aspx
-- This is our source table and data
CREATE TABLE [dbo].[Foo](
  [Id] [int] IDENTITY(1,1) NOT NULL,
	[When] [datetime] NOT NULL,
	CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO

INSERT INTO [dbo].[Foo] ([When]) VALUES (GETDATE());
INSERT INTO [dbo].[Foo] ([When]) VALUES (GETDATE());
INSERT INTO [dbo].[Foo] ([When]) VALUES (GETDATE());
INSERT INTO [dbo].[Foo] ([When]) VALUES (GETDATE());

-- Now let's create the new table with a non-clustered primary key
CREATE TABLE [dbo].[Foo_New](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[When] [datetime] NOT NULL,
	CONSTRAINT [PK_Foo_New] PRIMARY KEY NONCLUSTERED ([Id] ASC)
)
GO

-- Add the clustered index
CREATE CLUSTERED INDEX IX_Foo_New_When ON [dbo].[Foo_New] ([When])
GO

-- Copy the data over a row at a time
DECLARE @id int
SELECT TOP 1 @id = [Id] FROM [dbo].[Foo] ORDER BY [Id]
WHILE @@ROWCOUNT > 0 BEGIN
    INSERT [dbo].[Foo_New] SELECT [When] FROM [dbo].[Foo] WHERE [Id] = @id  -- one row
    SELECT TOP 1 @id = [Id] from [dbo].[Foo] WHERE [Id] > @id ORDER BY [Id]
END

-- Drop the old table
DROP TABLE [dbo].[Foo]
GO

-- Now rename the table
EXEC sp_rename 'Foo_New', 'Foo'

commit transaction
go