A sub-SELECT within a SELECT will always be a performance hit. It forces the sub-SELECT to be re-evaluated for every row in the main resultset and creates cursor-like behaviour.

Put the...

>>what i need is just UNIQUE SSN's thats all.

Then take the other columns out of the resultset.

If the same SSN has 2 or more different names, that is a data...

>>The problem is I should get unique ssn’s irrespective of first name, last name, DOB.

The problem is not what you should get. The problem...

And also please confirm that your "sample" data is "generated" data and does not reflect real person's SSNs and birth dates ...

>>Except now I can't replace the dates with "@Start" and "@End".

It would help if you expanded on "can't".

Are you getting errors ? If so, what are they ?

Or incorrect...

The IF statement is a T-SQL control flow statement, and is not meant to be embedded within SQL statements. It looks like you're trying to build a different derived table,...

Do you have a unique identifier (eg a Session ID) that can be used to link the event id to the set of data that has been changed as a result of...

There is nothing wrong with UNION queries. If that's the only way to retrieve the data set required, then use it.

The primary optimization concern is whether or not the data...

You can't avoid getting a time component in a smalldatetime column - it's designed to store date & time, so that's what it stores.

Declare @TestDate As Smalldatetime

Select @TestDate = '19...

>>Would you recommend setting up a job to reindex all tables once a week

Depends on the environment (volume of Insert/Update/Delete versus Select), and what your indexes look like - does...

It depends.

Making a column nullable may increase the number of rows per page, thereby reducing overall table size. Potential performance improvement.

Making a column nullable where the column is subject to...

Since this is an import to a staging table, make the staging column a varchar type. Once the import is done, run a subsequent SQL task to convert the varchar...

There is no FIRST() aggregate function, because the business rules defining "first" are so different. In your case, "First" means "earliest date".

In this case, you join to a derived table...

>>Select * From Master

I think an obvious performance culprit has been overlooked: "Select *"

Do you really need all columns from both tables ? If you slap a covering index on the...

Always use unambiguous date formats if using quoted strings and expecting SQL Server to implicitly convert your quoted string to a true datetime.

'3/1/2006' - is that 3rd of January or...