Yes, Lutz is correct (as usual). I didn't expect the implicit conversion of '-' to be a zero, but that's what happens. The execution engine is smarter than...

give this a try:

SELECT DISTINCT

pt.FIRST_NAME+ ' '+ pt.LAST_NAME AS 'Patient Name',

pt.PATIENT_ID AS...

The '+' is used as a mathematical operator and a string concatenation operator. If the datatypes of the arguments on both sides are numerical (int, smallint, numeric, float, etc)...

Can't see the datatypes from the code, but if either are int, you'll need to wrap them in a cast or convert before you use string concatenation.

Check for the presence of a trigger on the table. My guess is that the trigger contains the subquery and that is only built to handle single row operations.

Simple recovery mode doesn't mean nothing will be logged. The server still needs to log updates so it can back the transaction out if something happens. In simple...

Thinking back on it, the best, most creative IT people I have known, if they had degrees at all, had degrees in somehting other than IT. I used to...

The -CI- in the name means case-insensitive. You can check with:

declare @v1 char(1)

declare @v2 char(1)

set @v1 = 'a'

set @v2 = 'A'

if @v1 = @v2

print 'case insensitive'

This statement:

set @Qurey ='Select Status from Clientprofile where ClientID'+@clientID

isn't doing what you think it's doing. It's not executing the query, it's just setting the variable equal to the string....

The database you took offline is the default database for the id you are trying to log in with. Come in with another id with admin rights, change your...

Is there an update trigger on the table? If so, you might want to make sure it can handle multiple row updates.

a PIG program (production if good)

Just another possible plan of attack.

create another table to hold the 30 million rows you want to keep

copy the 30 million rows to the new table in batches based on...

You should be OK, then. I think the licensing requires each developer to have their own license, but as stated above, they're only about $50.

Your problem may be that...

It's not legal to use a Developer license for 'production'. Is moving to SQL Server 2005 an option?

Have you looked at other ways to optimize/tune the workload?