I was recently attending a small meeting where I was asked if I can share a 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 a short meeting. SQL Azure is an 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 a single database. Multiple database in a 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 a shared environment and because of the same there is no concept of Windows Login.
- Always drop TempDB objects after they need as they create pressure on TempDB.
- During buck insert use the batch size option to limit the number of rows to be inserted. This will limit the usage of Transaction log space.
- Avoid unnecessary use of grouping or blocking ORDER by operations as they leads to high end memory usage.
Reference: Pinal Dave (https://blog.sqlauthority.com)