The problem with that, is that any WHERE clause that doesn't contain DUE_DT, and where the SELECT contains 1 or more columns not in the index, may tablescan.

One other observation - is...

I would also take a look at the size of the clustered index - those 21 bytes get replicated in all non-clustered indexes (for doing bookmark lookups), so in addition...

>>This will result in fewer reads. Say I had 10 rows. In a normalized database, I would endup having to read 310 ( 31 x 10 ) as opposed to...

>>That will most likely be the day I stop using SQL Server and make the move to Oracle.

Hopefully by then, Oracle won't have moved completely to ANSI compliance and...

Careful! SELECT INTO a #temp table with an IDENTITY does not guarantee the ID's will be ordered in the sequence you expect.

This is why it is never a good idea to denormalise and treat a relational record like an array of repeating values.

By denormalising, you've lost the benefit of SQL aggregates...

Columns to Rows is usually accomplished with a UNION:

SELECT 'LessThanOneMonth' As Category, COUNT(*) As CountValue

FROM #AllData

WHERE DurationofClaim = 0

UNION

SELECT '1-2Months', COUNT(*)

FROM #AllData

WHERE DurationofClaim = BETWEEN 1 AND 2

UNION

SELECT '2-3Months', COUNT(*)

FROM...

>> info from the first row for a specific ID.

What defines "first" ? Or is it arbitrary ? If arbitrary:

Select A.AcctNumber, A.Amount, P.PropertyAddress, P.City, P.State

from tblAccount A

Inner Join tblProperty...

Instead of a Join, testing left joined table as null, why not a sub-query ?

FROM       PropMaster A Where A.ListingID NOT IN ( Select LSID FROM tmp_Listings)

DTS supports this functionality via Lookups in the transformation task.

They are configured on a separate tab of the transformation UI:

They can run...

>>Just wondering if there is an advantage of using Truncate over DROP?

The advantage is not having to code for exceptions. What happens if your package fails before completion and doesn't...

Why do you need to open the connection inside script, versus, say, having a connection object in the DTS package with the ODBC properties set at design-time ?

You don't need a GROUP BY if there are no aggregates, nor do you need the complexity of a user-defined function for what is just a simple relational join:

SELECT

  f.Fund_ID,

 ...

>>Fund_ID   Fund1   Acct1_ID   Acct2_ID   Acct3_ID

This breaks normalization rules ... do you have control over the design & can you change it to be in normal form ?

As for the select,...

Good eye, Remi  - I always forget the alias after the closing parenthesis on the vt.