Window Azure SQL Clustered Index Problem Fix

By | April 9, 2014
rx online

Windows Azure SQL Database is SQL Server technology delivered as a service on the Windows Azure Platform. The cloud-based SQL Database solutions can provide many benefits, including rapid provisioning, cost-effective scalability, high availability, and reduced management overhead. SQL Database supports the same tooling and development practices used for on-premise SQL Server applications. Therefore, it should be a familiar experience for most developers.

Recently we have migrate our database from SQL 2008 server to SQL Azure however, we faced many issue.

To insert any data in azure table it is mandatory to set the clustered index on each and every table. In small project it is easy to identity however in big project it is very difficult to investigate in which table index is created or not.

To fix this issue our team has created the index on each and every table which was time consuming process and chance of error.

so we did some research on it and able to find the Single T-SQL  Query which will generate the script to create index on table for which index are missing and it work for me.

select 'create clustered index idx_'+stt.name+'_'+sc.name+' on '+stt.name+'('+sc.name+') GO ' as Query,sc.name,sc.column_id from sys.tables  stt
inner join sys.columns sc
on sc.object_id=stt.object_id
where type_desc='USER_TABLE' and type ='u' and 
stt.object_id not in (select si.object_id from sys.indexes  si 
inner join sys.tables  st
on si.object_id=st.object_id
where si.type_desc='Clustered') 
and sc.column_id=1
order by stt.name

This query will help to  list the table on which index is not present and also it will generate the T_SQL Scripts to create the same.

We are still struggling with couple of issue like:

  • we can’t create SQL Job’s,
  • Not able to send Email via SQL,
  • You can’t use select * into <table_name> (since it will not create the clustered index)
  • CLR are not supported and many more.

If i will get something on above then  sure i will share the article with you, also requested you all if you have any reference with above issue then please share article links on comments.

1,209 total views, 1 views today