Ok, first, the function. You should get rid of it, and add the join directly in your query. The query that is in the function should be added to the...

It might be hard for the City, zip and Address part, but here's a splitter you can use to get the first address parts:

CREATE FUNCTION dbo.fn_StringtoTable2

...

Here,

I'm no expert, and I think you should learn the basics of querying, grouping and all if you're looking to do some reporting.

Here is an untested solution, since you posted...

Can you post the query and table definitions?

It would also help if you could post some accurate sample data, so we can help you build a query.

It is probably possible...

Why do you need a table with that information. What you need looks a lot more like a report you are trying to produce, that information will be useless to...

Just join the history table adress directly... Of course, when you use a function to get 5 different columns like you are doing, things get pretty nasty slow.

Just join the...

Well thanks, Even though it was only a side question, I'm letting the post to the owner =)

I quite stole the answers there, but it helped me understand why...

Wow, I did not know that. So when I receive a string, for a where Clause, like this one, for example:

CAST(MfrCode AS NVARCHAR(max)) LIKE 'tel%' AND

CAST(VendorCode AS NVARCHAR(max)) LIKE 'sch%'

It...

Can you post the actual queries you are building, it could help. We also need the schema of the tables involved.

We do not know what tables you are using, or...

Grant Fritchey (1/29/2009)You're using CONVERT on your columns in the WHERE clause. This will absolutely prevent the indexes, if any from being used. You're getting an index scan on idx_JobApplication...

And the results are? You did not give the IO or time it took for the queries in your post, 😉

Jeff Moden (1/28/2009)

First, did the code I provide actually get beat? On some machines, it'll win the foot race compared to the memory only solution. Well, except on...

Well, I got a simple 3.0Ghz Dev machine with 4GB of Ram. It's not that quick, trust me. And I ran the query on 200k rows that has around 30...

I'm surprised to see Jeff did not throw in something, I thought RBAR was the trigger for an answer! 😉

Look at the clause B added:

select * from blabla

where (Region = @Parameter) or (Parameter = 'ALL')

This will get you either the region, or if your parameter is 'ALL' it...