Indexes on target Azure Datashare

Question

Hi,

We have client sharing their data via Azure datashare and used SQL server views. We received the data and tables are getting created based on views in source datasets. The problem we are facing is when querying the data as there are no indexes has been created on these tables, it takes long time to query data.

We tried setting up indexes and PK, but they get wiped out on next incremental(becasue the table get dropped).

Is there anyway to keep them intact after incremental snapshot has finished? or Is there way that we can trigger sql job to generate those indexes once incremental process has been finished?

We prefer that datashare process created those indexes once the process is done.

Please advise

Thank you

Answer 1

Hello @Balwinder Sohi ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is how to recreate a index on the SQL table once the data load is complete , please do let us know if its not accurate.

Is there anyway to keep them intact after incremental snapshot has finished? or Is there way that we can trigger sql job to generate those indexes once incremental process has been finished?

Yes its possible , once the table is dropped ( i am assuming before the next load ) all the indexes associated with the table are also gone . Since you mentioned SQL job , I am assunming that you are in premise .

You can write a script and run it from SQL job .

Step
Check if the index exists , if it does drop it (

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='Index_Name'
AND object_id = OBJECT_ID('[SchmaName].[TableName]'))
begin
-- CREATE INDEX code
end

Please do let me if you have any queries.
Thanks
Himanshu

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators