Finding Your Clustered Indexes

[This article was contributed by the SQL Azure team.]

Before migrating your SQL Server schema to SQL Azure, you need to create a clustered index on every table. You can read about why in this blog post. In this post, we will show how to find all the tables that don’t have a clustered index.

One of the techniques for migration is to update your SQL Server database so that its schema is compatible with SQL Azure, then transfer that same schema to SQL Azure using the Generate Scripts Wizard as we wrote about in this blog post. This is different from migrating between two different schemas, one on SQL Server and one on SQL Azure.

If you are using the first technique, you can use this script on your SQL Server to find all the tables without clustered indexes:

 SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE INDEX_ID = 0 AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE]

Once you have the list, you can target those tables that need a clustered index. Depending on the data type and the type of data stored, one way to quickly get a clustered index is to covert a nonclustered index into a clustered index. You can do this with SQL Server Management Studio. Here is how:

1. Expand the table, then the table’s indexes.

2. Choose a nonclustered index that you want to convert to a clustered index.

3. Right click on that index and choose Script Index as, then DROP AND CREATE To, then choose New Query Editor Window

   

4. SQL Server will generate a Transact-SQL script that will DROP and recreate your index.

5. Find the NONCLUSTERED reference in the CREATE INDEX statement and convert it to CLUSTERED.

6) Execute the script.

Example of the script created by SQL Server Management Studio:

 /****** Object:  Index [PK_GroupFeature]  
  Script Date: 06/01/2010 17:00:47 ******/
IF  EXISTS (SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[GroupFeature]') 
    AND name = N'PK_GroupFeature')
ALTER TABLE [dbo].[GroupFeature]
 DROP CONSTRAINT [PK_GroupFeature]
GO

USE [Kulshan]
GO

/****** Object:  Index [PK_GroupFeature] 
   Script Date: 06/01/2010 17:00:48 ******/
ALTER TABLE [dbo].[GroupFeature] ADD
  CONSTRAINT [PK_GroupFeature] 
  PRIMARY KEY CLUSTERED 
(
    [GroupFeature_Id] 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

Once you have created clustered indexes for every table that you want to migrate from SQL Server to SQL Azure you are one-step closer to deploying to SQL Azure. Do you have questions, concerns, comments? Post them below and we will try to address them.