The problem with all of those 'all-in-one' type queries is that they make query plan reuse a very dodgy affair. What may be suitable for one set of parameters often...

Normally I'd suggest RCSI if lots of blocking is seen. It's not going to reduce IO load, and may even increase it, due to increased usage of TempDB.

If you can,...

From your description, the temp table sounds better, providing there are appropriate indexes on it. Depends on tempdb space, activity, memory size, ...

My normal answer to questions like this is...

Matt Miller (11/15/2007)

---

Gail - as to your test - I will try to dig out and use the XML data() trick, we'll let Jeff come up with the "pure" T-SQL...

What are those 'strange errors'?

The number of pages that the metadata says belong to the table don't agree with the number of pages actually allocated.

As the error says, try running DBCC UPDATEUSAGE and see...

Yup. That'll do it. If @WhereCondition is NULL, then when concatenated to the rest of the @sql string, that also becomes null,...

See my revised reply. Also, make sure that the where conditions you're specifying don't filter out all records.

Can you give an example of how you're calling the proc please?

Edit: Ah, silly me. The output variable's not getting assigned.

ALTER PROCEDURE dbo.SP_NewClassifiedsMain

@PageIndex INT,

@NumRows INT,

@NewClassifiedsCount INT OUTPUT,

@WhereConditions varchar(200)

AS

BEGIN

declare @sql nvarchar(400);

set @sql = 'Select @count = Count(*) From classifieds_Ads

Where AdStatus=100 ' +...

Try this

declare @sql nvarchar(400);

declare @test-2 int;

set @sql = 'Select @count = Count(*) From classifieds_Ads

Where AdStatus=100 ' + @WhereConditions

exec sp_executesql @sql, N'@count...

Matt Miller (11/14/2007)

---

There's a XML trick that might be worth throwing into the mix for this one. it's VERY slick, but I've been reading mixed reviews as to its...

You said it. 😉

What I'll do is grab one of the string functions that I...

Wow. You guys have been busy overnight. I think I'll bow out of this race while I'm behind... 😀

My recomendation would be to find a more appropiate column for the clustered index. Which column it is will depend on how the tables are used.

The problems with guids as...

Were those rows inserted before or after the 9th Nov backup? If before, then restore that backup to another server and copy the data across.

Look on the bright side. It...