The tool currently used is Informatica, and we have bookend stored procedures that drop cluster indexes and then add them back to the database. In the stored procedure, where we add the clustered indexes back, we have DDL for the indexes hardcoded in the stored procedure (we do not use sys tables because fear of Microsoft changing sys tables and restoring from there creates a bad index or fails) . This causes problems when people created clustered indexes, but did not think to update the stored procedure, and the next time the data volume of these indexes disappeared. Previously, we did this for all indexes, but switched non-clustered indexes to disable / rebuild. This is not an option, because we can no longer insert into the table if this is done for a clustered index,because it is essentially a table.
Performance is important, but not all. Good performance and ease of maintenance outperform high productivity and sophisticated maintainability.
After reading many sites, I almost universally agree that when performing a bulk insert on data that is not ordered in the same way as your primary key, inserting into a heap and then using pk subsequently happens faster ( http://msdn.microsoft.com/en- us / library / ms177445.aspx
, http://msdn.microsoft.com/en-us/library/dd425070(v=sql.100).aspx ). Most of these sites make assumptions that I cannot use in my organization and with my set of tools.
Currently, due to our current policy standards, we must use the FULL recovery model, so minimal logging will not happen no matter what choices I make regarding heap and clustered index.
In accordance with our informatica, admins indicating a tab or prompts for ordering on bcp are not possible through the interface, and our organization is unfavorable to configure outside the user interface due to maintainability.
So, a question related to all of the above factors, would you recommend us to continue our several unreliable stored procedures, insert them into a clustered index, or have some third excellent solution. I also understand that there are other questions about the stack that are similar to this paragraph, but they do not affect the volume separately and / or make similar assumptions in their answers.