Data Loading from Staging to Development -

Question

Data Loading from Staging to Development -

patla4u

SSCrazy

Points: 2103
More actions
April 8, 2014 at 3:00 pm

#396562

Hello,
I want to load data from Staging to Development environment.
In Dev. environment , I have index that is use for stored procedure and Staging environment, I don't have index.
Now My Question is ,
Do I need to disable Index while loading data from Dev to staging ?
Which index, Clustered or Nor clustered ?
If I have 5 Non-clustered Index and 1 clustered Index in a table, Can i disable 5 Non clustered Index by common Script. This Non-clustered Index might change in future So I don't want to hardcore their name while disabling.
How can I Rebuild 5 Non clustered Index for Table ?
Thanks
Bhavesh

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

patla4u (4/8/2014)
Hello,
I want to load data from Staging to Development environment.
In Dev. environment , I have index that is use for stored procedure and Staging environment, I don't have index.
Now My Question is ,
Do I need to disable Index while loading data from Dev to staging ?
Which index, Clustered or Nor clustered ?
If I have 5 Non-clustered Index and 1 clustered Index in a table, Can i disable 5 Non clustered Index by common Script. This Non-clustered Index might change in future So I don't want to hardcore their name while disabling.
How can I Rebuild 5 Non clustered Index for Table ?
Thanks
Bhavesh

I'm not sure which direction you are trying to go.

As per the question regarding whether to disable or not, it depends on your environment. You should test it with both dropping and with not dropping the index.

As per the rebuild question - the syntax is

Alter Index index_name on table_name REBUILD

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

patla4u (4/8/2014)
Hello,
I want to load data from Staging to Development environment.
In Dev. environment , I have index that is use for stored procedure and Staging environment, I don't have index.
Now My Question is ,
Do I need to disable Index while loading data from Dev to staging ?
Which index, Clustered or Nor clustered ?
If I have 5 Non-clustered Index and 1 clustered Index in a table, Can i disable 5 Non clustered Index by common Script. This Non-clustered Index might change in future So I don't want to hardcore their name while disabling.
How can I Rebuild 5 Non clustered Index for Table ?
Thanks
Bhavesh

It is actually impossible to speak with any certainty because you didn't post any details about the tables. I suspect you are probably fine because unless the missing index is a unique index you should be ok. Again, this is pure speculation based on LOTS of assumptions.

Have you tried it? Did you make a backup first?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ScottPletcher SSC Guru Points: 100947 More actions · Answer 3

>> Do I need to disable Index while loading data from Dev to staging ? <<

Can't say for sure without more details, but if it is a large % of the total rows of the table, it can speed up the inserts.

>> Which index, Clustered or Nor clustered ? <<

You should disable only non-clustered indexes.

>> If I have 5 Non-clustered Index and 1 clustered Index in a table, Can i disable 5 Non clustered Index by common Script. <<

Sure. You'd need to generate and run a separate DISABLE command for each index.

>> How can I Rebuild 5 Non clustered Index for Table ? <<

You'd likewise have to generate and run a separate REBUILD command for each index.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

patla4u SSCrazy Points: 2103 More actions · Answer 4

Now My Question is ,

Do I need to disable Index while loading data from Staging to Development ?

Which index, Clustered or Nor clustered ?

patla4u SSCrazy Points: 2103 More actions · Answer 5

Thanks for your Reply.

I understood that, I don't need to disable clustered index. Can I ask you Why?

Thanks

Bhavesh

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

patla4u (4/9/2014)
Thanks for your Reply.
I understood that, I don't need to disable clustered index. Can I ask you Why?
Thanks
Bhavesh

Why do you think you need to disable the clustered index?