Vijaya,

Since you're brand new, lemme just tell you that posting data in the following manner is much more helpful to those that would help you... tell's us almost everything we...

Just from the practical point for ease of troubleshooting, here's from our "standards" book at work...

All column names shall be prefixed with a table alias in the presence of joins...

Phill is spot on.  I'll kick in that BCP has some additional features that make it a bit slower than Bulk Insert (but still faster than DTS) including error logging...

It's not often that I see a query improve performance by applying Index Hints.  Usually, it's the other way around...  When you say you are seeing some impressive improvements in...

Lookup sp_Rename in Books Online... that'll do what you need...

... and, that's just the date... doesn't include time...

But, Serqiy is correct... there's a date format that's based on the number of seconds since the 01/01/1970.  There are other "numeric" date...

SELECT CAST(CONVERT(CHAR(8),somedate,112) AS INT)

...but it's an insane thing to do for so many reasons...

Almost forgot to ask... I know you said you can't normalize the table (would be the greatest space saver of them all here) so I won't bug you about that... but...

Using LTRIM(RTRIM()) will not make NULLs... that may be a part of the problem... here's the proof...

DECLARE @NullOrBlank VARCHAR(8000)

 SELECT @NullOrBlank = REPLICATE(' ',100)

 SELECT DATALENGTH(@NullOrBlank) AS DataLengthBefore,

       ...

It wouldn't be a "sum"... it would still be a COUNT except it wouldn't be DISTINCT.  And, because you are not counting DISTINCT LID's, you already have the sum of...

Michael,

Why don't you write an article here on SQLServerCentral about you number and date range generator functions?  I've always been really impressed with the speed of the darned things...