Hi,
I have got the following situation please give me some ideas how to
solve/work around it.
Current situation: Everyday day about 10 million records are processed
and bulk inserted in individual tables. Around 20K records are
inserted at a time. At the end of the day this table is clustered
indexed. The field used for indexing is not a primary key. There is no
primary key in this table. After indexing read access is given and
user run reports from the frontend using the clustered index field.
Hope to achieve: Table will be clustered indexed from the start and
bulk insert (10 mill records/day) will be done on this indexed table.
At the same time users will have access to this table to run reports.
Now we all know inserting data in a an indexed table (specially
clustered) is a bad idea. So is it completely impossible? I have seen
the same thing being done in Oracle database. Is it possible in SQL
Server.
From this blog: http://www.sqljunkies.com/WebLog/afe.../02/41931.aspx
it seems that if I dummy sort the data before inserting then I can
overcome this performance issue. But then bulk-insert will be useless
here.
Please help.
Thanks in advance.
I have got the following situation please give me some ideas how to
solve/work around it.
Current situation: Everyday day about 10 million records are processed
and bulk inserted in individual tables. Around 20K records are
inserted at a time. At the end of the day this table is clustered
indexed. The field used for indexing is not a primary key. There is no
primary key in this table. After indexing read access is given and
user run reports from the frontend using the clustered index field.
Hope to achieve: Table will be clustered indexed from the start and
bulk insert (10 mill records/day) will be done on this indexed table.
At the same time users will have access to this table to run reports.
Now we all know inserting data in a an indexed table (specially
clustered) is a bad idea. So is it completely impossible? I have seen
the same thing being done in Oracle database. Is it possible in SQL
Server.
From this blog: http://www.sqljunkies.com/WebLog/afe.../02/41931.aspx
it seems that if I dummy sort the data before inserting then I can
overcome this performance issue. But then bulk-insert will be useless
here.
Please help.
Thanks in advance.