June 1, 2005 at 1:42 pm
I have recently inherited a database that has a lookup table of approx 30 million rows. This table is a read only table, nothing is ever written to this table or updated in this table.
We receive a flat file to load this data approx once a week. The existing process works like this: Load the flat file into a 'copy' of the table, let's call it table B, using bcp. Create all the necessary indexes on table B. Once all indexes are built, rename the current production lookup table, let's call it table A, to table C and rename table B to A.
I'm curious what, if any, effect this sort of thing has on the statistics that were just created on table B (which is now table A).
TIA
-A.
June 1, 2005 at 1:50 pm
Internally, the indexes and stats are linked to the id of the object. If you change the name of the object, only its name is changed. So I would presume that if the stats are up to date right after they are created (which we can assume is the case), then the stats should remain unaffected... unless someone else knows otherwise.
June 1, 2005 at 1:52 pm
That's what I thought, I just wanted to confirm.
June 1, 2005 at 2:01 pm
This is just an assumption from what I know of sql server's inner workings. It would be nice if someone else could confirm my deductions...
Anyone??
June 1, 2005 at 3:29 pm
Why not create a dummy table and create stats and then rename, that should give you what you need to know
June 2, 2005 at 8:51 am
Angela
The process that you use is a correct one. As stated by Remi, the statistics follow the internal object_id of the table and do not use the logical table name.
The process that you are using is a common ETL (Extract Translate Load) process that provides for 24/7 availability.
One thing that you may want to check is if you intially load the table with a clustered index (that is not an identity column) is that you may have index fragmentation after the BCP.
Rick
June 2, 2005 at 8:55 am
Thanks Rick.
The load table is actually stripped of all indexes before the bcp happens and then after the bcp load all indexes are put back on.
-A.
June 2, 2005 at 8:58 am
Great!! so you should be smooth stailing from here on out.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply