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