create table dbo.TEST_TEACHER (

MEMB_ID varchar(10),

SELECTION_DT datetime,

TEACHER_ID varchar(10))

insert into TEST_TEACHER select '123', '2009-10-01', 'P654'

insert into TEST_TEACHER select '123', '2009-11-01', 'P987'

insert into TEST_TEACHER select '123', '2009-12-01', 'P321'

insert into TEST_TEACHER select '123', '2010-01-01',...

I would suggest reading these articles, espcially those by Gail Shaw, for guidance.

Finding the Causes of Poor Performance in SQL Server, Part 1 by Gail Shaw

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

Finding the causes of...

To answer questions of this nature, uese the SET STATISTICS TIME, IO ON and you can learn for yourself what the effects of with [ ] or without...

Jeff...

Go to this link for the stored procedures and scripts to run them.

http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-2005-performance-statistics-script.aspx

You can copy the complete scripts from this link

http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-2005-performance-statistics-script.aspx

Jeff using Brad Schulz blog post on using option(Force order,Maxdop 1, Loop Join).

http://bradsruminations.blogspot.com/2010/08/taking-hint.html

Again SQL Server 2008 on my bigger machine

...

Jeff ... ran test with the trace flag on (and yes I turned it off when the test completed).

Posted my results at Posted Today @ 11:25 AM

Brad Schulz

Attempted to view your plans Microsoft Internet Explorer 7 . Result:

The XML page cannot be displayed

Cannot view XML input using style sheet. Please correct the error...

Jeff tested Brad Schulz's code, as posted by yourself, but I added two lines (DBCC TRACEON(8690,-1) per Paul White NZ and then tested

--===== Do this in a nice safe place...

Jeff from the AdventureWorks2008 that was utilized in my tests of Brad Schulz's code, as posted by yourself. Table defs from SSMS 2008 using the "Script As" feature.

[USE [AdventureWorks2008]

GO

/******...

Results using the Brad Schulz (as posted by Jeff)

Again SQL 2005 and SQL 2008 on the larger faster machine, and SQL2005 Express on the smaller slower box.

Jeff Moden (9/19/2010)

---

bitbucket-25253 (9/19/2010)

---

Jeff ran test on the oldest, least amount of memory, slowest desktop, age - older than dirt. Running Windows XP

Result for SQL 2005 Express using code...

Jeff ran test on the oldest, least amount of memory, slowest desktop, age - older than dirt. Running Windows XP

Result for SQL 2005 Express using code after it was...

Results for SQL 2005 Developer edition run on a desk top with slightly more memory and slightly higher speed Running Windows XP

Using the code you edited at 10:48 edit

So...