How to store the index separately from the data on Azure?

Question

I've read that a lot of websites store the index separably from the data.

Specifically on Azure, the index will be stored in Azure SQL and the data stored in Azure Table Storage.

This supposedly increases the performance and allows you to store a lot more data and query it efficiently.

I'm not sure how to architect a system to do this. Can someone explain the process and how having an external index actually works?

What happens when you want to have secondary indexes on different columns, how does that work?

What about when you update/remove/add data? How does the index + actual data get updated?

Answer 1

The same is true for regular clustered and non clustered indexes. The clustered index holds all the data from the table, the non clustered index holds just the columns of the index and either a row locator back to the table if it is a heap (meaning no clustered index) or the key from the clustered index.

The more indexes you have the more expensive your update, delete and inserts will be since all those indexes have to be maintained when you insert into the table

So you can have your data on drive D and your indexes in another filegroup on drive E

Also a SQL Azure Database does not support heap tables, a table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table.