Berto,

I'd like to know how the (3 actually including the EXCEPT) version perform on large tables where a significant number of rows are returned.

Todd Fifield

In order to set the column to not allow nulls, you will have to do an update to set all of the columns that contain a null to some default...

Thanks for the post on NOT EXISTS. I'm going to try it out on large record sets to see what happens. I've benchmarked the LEFT JOIN WHERE right-table...

You could probably get the same or better performance from:

SELECT R.HWID1, R.HWID2, R.Status

FROM REF_UPD_HWID R

LEFT JOIN HWID H ON

R.HWID1 = H.HWID1

WHERE

H.HWID1 IS NULL

I've found using this...

I just had a weird data problem between Access and SQL Server this week. It involved an ntext data type in a SQL Server table that was linked to...

I would normally do something like this:

LEFT JOIN

(SELECT tb7.Estadoid FROM Tbl_Tmp_Trace

WHERE tb7.Estadoid = 3094610094

AND tb7.Tipo = N'Chamadas' AND tb7.ID = tb4.ID

...

This one's a little more ugly:

SELECT COUNT(*) - COUNT(SID) FROM #temp

The COUNT() function will only count non-null columns if a column is specified.

Todd Fifield

I've bench marked the type of scalar function in Adam's article against correlated sub queries, since they're doing the same type of thing, and they seem to work at about...

There's trouble right here in River City.

That's trouble that starts with 'T'

That rhymes with 'B'

That stands for Big Manager.

I'd certainly get everything in writing and I might even start sending...

Thanks Gail. I'll do this in the future.

Todd Fifield

Since you're kind of at the mercy of what the users enter for parameters, I would do the whole thing in Dynamic SQL. You could get rid of all...

I've used both the 'IN' in the WHERE clause and and the joins to derived tables. In the cases where I benchmarked them, I found using joins to derived...

I used books by WROX to learn SQL Server years ago. I found they were very easy to read and very helpful. The one I mostly used was...

I do this sort of thing all the time. I ended up writing a function passing it the table name and which columns to omit (those being identity, computed,...

I had trouble years ago using this type of correlated sub query to do updates. I found that this type of approach, using derived tables, works much better and...