Johnny,

I'd go along with most of Ivan's suggestions, but I would offer a note of caution about using the index tuning wizard. It can be a very useful...

John,

ANY index on the target table is a performance killer. If an index is present, BCP is forced to log every row insert.

Without any indexes, and if...

John,

I replied to this in your original posting ...

Use the "-b" switch anywhere in amongst your other switch options

e.g. To set a batch size of 1000 rows ...

SET @Dynamic_SQL =...

An indication of the number of table rows would help, but let's assume there are a lot.

Now, your PK:

Firstly, it's potentially very large (varchar(50)), and I really wouldn't recommend using...

John,

Use the "-b" switch anywhere in amongst your other switch options

e.g. To set a batch size of 1000 rows ...

SET @Dynamic_SQL = 'bcp ' + My_Database + '..'+@TabletobeOutput+' IN...

Bryan,

I'm not sure how you're getting your "||" syntax to work (presumably this is an OR statement?). I've tried this and get syntax errors.

More generally, for this particular query...

Thinus,

Are you actually moving your PK to the date column, or just the clustered index (leaving a NON-clustered PK on the identity field)? If you are putting the PK...

Arun,

Can you please post all table and index structures, together with the actual query you are running? An indication of the approx number of rows in the table(s)...

Physically, there is no difference between the two. A clustered constraint is physically implemented as a clustered index.

A Constraint is a logical concept, and the index structure is simply...

I can't see an index seek on col2. There is an index seek on col3, but this is in the Search argument, so doesn't come as a total surprise:

WHERE...

hi Trin,

You don't mention an index on t1.entry_date in your description, yet you say that the query plan does an index seek via this index? What does this index...

Hi Carl,

I don't think this is possible in the way you might expect.

If you try it using SQL2005 Profiler, which is more detailed in its description of settings and filters,...

Hmm. Spooky. Clearly it's coming from somewhere - I'd try running a profiler trace, filtering on DBCC commands only, and including the Host_Name column. This...

Check the properties of the database, under the "Options" tab. I think you'll find the "AutoClose" box is checked. Personally, I'd uncheck it - it should stop...

Here's another approach you could try:

Use [enter your database here]

go

--Turn off auto truncate log

sp_dboption 'your database here', 'trunc. log on chkpt.', false

go

-- Truncate the transaction...