SQL Server Performance Forum – Threads Archive
Urgent: Index CreationDear All, I have two tables into which I am uploading data through a stored procedure. A combination of four columns, two columns in the first table and two in the second table will contain unique data. I have created a composite index comprising the two columns in the first table and individual non-clustered indexes on the remaining two columns of the second table.These indexes have been created for faster execution of the procedure When I upload data, all the valid data gets uploaded in the first table but only some of the valid data gets inserted in the second table in a shorter duration of time.
If I remove both the indexes or retain the individual non-clustered indexes on the second table valid data is inserted in both the tables in a longer duration of time. what kind of index should be created on the first table to insert valid data in both tables?
Is there any other way I can upload valid data quickly in both tables? Thanks,
If you are saying that 4 columns split across 2 tables indicate your composite key – i.e. these 4 columns are used to indicate a single row of data, then you should really change your table design. If you are stuck with the table design, consider using a staging area – just a single table really – which could contain those 4 columns used to uniquely identify and validate the incoming data as well as the rest of the data. Load your data here, to the staging table, first, and then load from there to the live tables. This will ultimately provide more flexibility too if you find you need to perform any additional steps to clean or manipulate the data and is quite a standard approach. Dave. Small Businesses