Finding Your Clustered Indexes - SQL Azure Team Blog - Site Home - MSDN Blogs

Finding Your Clustered Indexes

Finding Your Clustered Indexes

Rate This
  • Comments 4

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

    clip_image002

  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.

  • Can you please show the BEFORE and AFTER scripts (before being the one auto generated by SQL Management Studio and after the modification to clustered)?  And can you highlight the changes?  In looking at the above exmple script I don't understand what to modify

  • I believe the script is getting truncated in the post here on IE8

  • Craig:  Sorry about that, I narrowed the code sample and highlighted the text that needs to be changed.

  • Hey, Thx for your post. I got this error :

    Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

    But when i check tables for indexes using method you mentioned, i noticed that primary index is already clustered !

Page 1 of 1 (4 items)
Leave a Comment
  • Please add 6 and 1 and type the answer here:
  • Post