I'm trying to work out the best indexing strategy for a slightly weird pattern I'm forced to use with Azure SQL.
- Tables are dropped, re-created & loaded with new data daily from ADF
- Tables are read only once loaded.
- Tables can be 20 million+ rows & 50+ columns wide.
- Table schema may change each day, hence drop & re-create
As I don't know the full schema in advance. I'm adding a surrogate identity column to each table & creating a Clustered Primary key on this column.
However key creation takes up to 15 mins & the table is locked during creation - this is a problem...
So - maybe I leave these tables as heaps? Forget the surrogate key & create non-clustered indexes instead as creation won't lock the table.
Any nasty implications of heaps I'm missing? Can I do without a clustered index\key?
I hope this makes sense - it's a little difficult to get across without making the question 2 pages long....