Brain dead today - it can, of course, all be done with integers. Still only one read of the table:

SELECT D.Range

,CASE N.N

WHEN 1

THEN MinYearMonthofValue / 100

ELSE MaxYearMonthofValue / 100

END AS...

You could also combine the Year and Month either as a float, as below, or as a datetime:

SELECT D.Range

,CASE N.N

WHEN 1

THEN FLOOR(MinYearMonthofValue)

ELSE FLOOR(MaxYearMonthofValue)

END AS YearofValue

,CASE N.N

WHEN 1

THEN CAST((MinYearMonthofValue * 100)...

If you can upgrade to SQL2008, you may want to review this recent article:

http://www.sqlservercentral.com/articles/SQL+Server+2008/66554/

INSERT INTO PersonalInfo (firstname, secondname, nationalDno)

SELECT DISTINCT firstname, secondname, nationalDno

FROM StagingTable S

WHERE NOT EXISTS

(

SELECT *

FROM PersonalInfo P

WHERE S.firstname = P.fistname

AND S.secondname = P.secondname

AND S.nationalDno = P.nationalDno

)

Jeff Moden wrote an article which covered this.

(Whether you want to rely on non-relational features is up to you.)

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

If there are not too many trdates for each cusip, I would...

The fundamental problem with the original query is the JOIN in the subquery. (These always seem to end up as nested loops which is rarely a good idea.)

You should avoid...

May something like the following although this will also produce John running:

SELECT *

FROM

(

SELECT P1.[name], A1.activity, COUNT(*) AS CatCount

FROM person P1

JOIN activity A1

ON P1.catid1 = A1.catid1

AND P1.catid2 = A1.catid2

GROUP BY P1.[name],...

UPDATE table1

SET seq =

(

SELECT T3.seq

FROM table3 T3

WHERE EXISTS

(

SELECT *

FROM table2 T2

WHERE T2.mi_class_name = T3.mi_class_name

AND T2.obj_num = table1.obj_num

AND T2.[name] = table1.[name]

)

)

WHERE EXISTS

(

SELECT *

FROM table3 T31

WHERE EXISTS

(

SELECT *

FROM table2 T21

WHERE T21.mi_class_name =...

http://francoisbeaussier.blogspot.com/2007/10/creating-custom-sql-aggregate-function.html

I think this will take out the NULLs in rt

Try:

FROM ttc_room r

CROSS JOIN ttc_event_date_list edl

LEFT JOIN ttc_room_tracker rt

ON edl.date_list = rt.event_date

AND r.room_id = rt.room_id

AND rt.location_id = 2

WHERE r.location_id = 2

AND edl.date_list BETWEEN '20090402' AND '20090409'

While not available in SQL2000, the INTERSECT and EXCEPT operators compare NULLs as being equal even with ANSI_NULLS on.

I...

I have seen it on some sites where query performance tuning programs are used.

I think COALESCE(joinColumn, joinColumn) just acts as a function and stops an index being used.

My understanding is that most table valued functions do not have a usable index and the optimizer might make an inaccurate estimation of the number of rows.

(I think the only...