Use

CASE ... WHEN ... THEN ... ELSE ... END

Use an indexed view

http://www.sqlservercentral.com/Forums/FindPost471408.aspx

You can remove duplicates using this

delete from mytable

where exists (select * from mytable t2

where t2.UserName=mytable.UserName

...

I find this really useful

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Try using an indexed view

create view dbo.myview

with schemabinding

as

select TvsFormNo

from dbo.TvsRecords

where TvsFormNo is not null

go

create unique clustered index uix_myview on dbo.myview (TvsFormNo)

Also...

Maybe this ?

select r.SessionID

from Requests r

inner join PageFlow p on p.MatchValue=r.PageUrl

group by r.SessionID

having count(distinct r.PageUrl)=(select count(distinct MatchValue) from PageFlow)

Personally I avoid FOR XML EXPLICIT in SQL Server 2005 and use FOR XML PATH instead

CREATE FUNCTION dbo.GetSubTree(@id int)

RETURNS XML

BEGIN RETURN

(SELECT id AS "@id",

...

Using a different approach

select X.h_id, X.l_id, X.ton

from #t1 X

where not exists (select * from #t1 Y

...

Sample data would help...

select g.ID,g.Name

from Groups g

where exists (select 1

from Search s

...

Maybe this?

select T1.client_id,T1.services_rendered,T1.date

from T1

where not exists (select T2.client_id

from T2

...

Try this. Probably not very efficient on large data sets though

WITH CTE AS

(SELECT checkInOutID,

employeeID,

isCheckInOut,

...

Easier to use FOR XML PATH

select b.externalid as "@externalid",

b.ititid as "@ititid",

b.containstype as "@containstype",

...

See if this helps

create table missingdates(person varchar(10),division varchar(10), dt datetime)

insert into missingdates(person,division,dt)

select 'John','Div1','20080711' union all

select 'John','Div1','20080712' union all

select 'John','Div1','20080713' union all

select 'John','Div1','20080805' union all

select 'John','Div2','20081211' union all

select 'John','Div2','20081212'

select lbound.person,

...

create table #Teams (Id int, ParentId int, name varchar(100), Position int)

insert into #Teams(Id, ParentId, Name,Position)

select 1, 0, 'Items', 1 union all

select 2, 0, 'Standings', 3 union all

select 3, 0,...

Really don't know. It works on the all of the 2005 machines I've tried and fails on the 2000 machine with the exact same error.