Hi,
  We have a tough situation when we migrated one of the view from SQL Server
2000 to SQL Server 2005, we have seen CPU hogging around 100% when we access
this particulat view. It works fine in SQL 2000 but has CPU hogging in SQL
Server 2005. This is the view definition

SELECT CASE WHEN ARC.ContactID IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.ContactID ELSE ARC.ContactID END As ContactID,
       CASE WHEN ARC.ContactName IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.ContactName ELSE ARC.ContactName END As ContactName,
       CASE WHEN ARC.IsValidEmail IS NULL OR MSC.DataSourceCode = 'USER' THEN
       CASE MSC.IsValidEmail WHEN 'Y' THEN MSC.ContactEmail ELSE '' END ELSE
CASE ARC.IsValidEmail WHEN 'Y' THEN ARC.ContactEmail ELSE '' END END As
ContactEmail,
       CASE WHEN ARC.ContactType IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.ContactType ELSE ARC.ContactType END As ContactType,
       CASE WHEN ARC.DataSourceCode IS NULL OR MSC.DataSourceCode = 'USER'
THEN MSC.DataSourceCode ELSE ARC.DataSourceCode END As DataSourceCode,
       CASE WHEN ARC.IsValidEmail IS NULL OR MSC.DataSourceCode = 'USER'
THEN MSC.IsValidEmail ELSE ARC.IsValidEmail END As IsValidEmail,
       CASE WHEN ARC.IsAllowUpdate IS NULL OR MSC.DataSourceCode = 'USER'
THEN MSC.IsAllowUpdate ELSE ARC.IsAllowUpdate END As IsAllowUpdate,
       CASE WHEN ARC.IsAllowElectronicCommunication IS NULL OR
MSC.DataSourceCode = 'USER' THEN MSC.IsAllowElectronicCommunication ELSE
ARC.IsAllowElectronicCommunication END As IsAllowElectronicCommunication,
       CASE WHEN MSC.ContactAddress1 IS NULL THEN ARC.ContactAddress1 ELSE
MSC.ContactAddress1 END As ContactAddress1,
       CASE WHEN MSC.ContactAddress2 IS NULL THEN ARC.ContactAddress2 ELSE
MSC.ContactAddress2 END As ContactAddress2,
       CASE WHEN MSC.ContactAddress3 IS NULL THEN ARC.ContactAddress3 ELSE
MSC.ContactAddress3 END As ContactAddress3,
       CASE WHEN MSC.ContactFaxNbr IS NULL THEN ARC.ContactFaxNbr ELSE
MSC.ContactFaxNbr END As ContactFaxNbr,
       CASE WHEN MSC.PhoneNbr1 IS NULL THEN ARC.PhoneNbr1 ELSE MSC.PhoneNbr1
END As PhoneNbr1,
       CASE WHEN MSC.PhoneNbr2 IS NULL THEN ARC.PhoneNbr2 ELSE MSC.PhoneNbr2
END As PhoneNbr2,
       CASE WHEN MSC.PhoneNbr3 IS NULL THEN ARC.PhoneNbr3 ELSE MSC.PhoneNbr3
END As PhoneNbr3,
       CASE WHEN MSC.PhoneNbr4 IS NULL THEN ARC.PhoneNbr4 ELSE MSC.PhoneNbr4
END As PhoneNbr4,
       CASE WHEN MSC.Position IS NULL THEN ARC.Position ELSE MSC.Position
END As Position,
       CASE WHEN MSC.ContactComments IS NULL THEN ARC.ContactComments ELSE
MSC.ContactComments END As ContactComments,
       CASE WHEN MSC.IsActive IS NULL THEN ARC.IsActive ELSE MSC.IsActive
END As IsActive,
       CASE WHEN ARC.CreatedBy IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.CreatedBy ELSE ARC.CreatedBy END As CreatedBy,
       CASE WHEN ARC.CreatedDate IS NULL OR MSC.DataSourceCode = 'USER' THEN
MSC.CreatedDate ELSE ARC.CreatedDate END As CreatedDate,
       CASE WHEN MSC.UpdatedBy IS NULL THEN ARC.UpdatedBy ELSE MSC.UpdatedBy
END As UpdatedBy,
       CASE WHEN MSC.UpdatedDate IS NULL THEN ARC.UpdatedDate ELSE
MSC.UpdatedDate END As UpdatedDate
  FROM dbo.dat_Contact MSC
       FULL OUTER JOIN ARCommon.dbo.dat_Contact ARC
                    ON MSC.ContactID = ARC.ContactID

dbo.dat_Contact- This has around 50000 rows and ARCommon.dbo.dat_Contact 
has around 40000 rows.

I dont have an option of getting rid of this view as it has been referrend
in more than 50 procs. Is there anyway I can rewrite the logic in this view
so that CPU time comes down in SQL Server 2005. I figured this CASE logic is
the one that is causing CPU hoggging..

Please help me ASAP.

Regards,
Murali

create a sp from query output
Combine records to calculate
Creative solution to relatively simply requirement
JOIN Question...
TOP by GROUP SQL 2000