For my 2 cents.

I've found the fastest way to do something like select zip codes within a radius is to use simple betweens for latitude and longitude into a temp...

You can also use COALESCE(ColumnName, 'N/A')

Todd Fifield

I had a similar problem. Access doesn't like nvarchar or ntext in a table that also has a REAL data type on the SQL Server side. I changed...

The following code should work from Access. It's simpler to use ADO but in some cases this isn't possible.

The code below is how to insert a row into the...

Thanks guys. It makes sense I'll give it a whirl.

Todd Fifield

Jeff,

You got me on that one! I didn't check it against a million rows - oops!

I'll try the triangular join next time.

Todd

Jeff,

Wouldn't something like this work faster? I normally use this technique to delete duplicates and I've never had a performance problem:

DELETE T1

FROM dbo.JBMTest T1

INNER JOIN

(SELECT ProductID, CustomerID,...

If you're in a stored procedure I don't see why you wouldn't create a temp table and then make 7 passes at inserts into it using specific criteria each time...

If you are truncating the tables before loading them, I would also drop the indexes (except for the clustered index if the data is loaded in clustered index order). ...

I agree that you have to be very careful when assigning variables to something that might have more than 1 row in it. It's a bad practice. To...

The first thing I noticed was that you're using a function as the first part of your WHERE clause. This makes it difficult for the optimizer to us an...

There's a fairly easy way to do this using Dynamic SQL. If you mostly want all columns and a few that you want to omit then you can get...

I agree with Jeff on this one. I've had to go through 2 systems the used the WHERE IN syntax and replace them with an INNER JOIN. These...

Actually, I made a typo in the above function. It should read SELECT C.Colour. PC is the alias for ProductColour, not the Colour table.

Todd Fifield

The way I've handled this in 2000 (and 2005) is to use a User Defined Function to string the text together. You have to be careful about using this...