I was recently attending a small meeting where I was asked if I can share few things to be considered when designing SQL Azure database. Today I am sharing the same notes over here.
Disclaimer: This is no way a complete guide – this is some notes which are compiled in short meeting. SQL Azure is evolving product. All the notes are accurate when this article was written.
- Each Table must have clustered index. Tables without a clustered index are not supported.
- Each connection can use single database. Multiple database in single transaction is not supported.
- ‘USE DATABASE’ cannot be used in Azure.
- Global Temp Tables (or Temp Objects) are not supported.
- As there is no concept of cross database connection, linked server is not the concept in Azure at this moment.
- SQL Azure is shared environment and because of the same there is no concept of Windows Login.
- Always drop TempDB objects after their need as they create pressure on TempDB.
- During buck insert use batchsize option to limit the number of rows to be inserted. This will limit the usage of Transaction log space.
- Avoid unnecessary usage of grouping or blocking ORDER by operations as they leads to high end memory usage.
Reference: Pinal Dave (http://blog.SQLAuthority.com)