Michael,

I don't know why SQL Server treats them differently, but changing my code to use a specifically named primary key constraint does cause the error, but using the Create Unique...

Check to see if the Databases have the autoclose option is on.

Select DATABASEPROPERTYEX('model', 'IsAutoClose')

Jeff solution will work if you do not have any duplicates in the column where you want the unique index. I have never seen the issue you are describing....

I don't know why the cache would clear other than either a small cache size or activity. You could check the buffer cache hit ratio counter.

Did you try the...

I think Matt covered it very well. Another way to explain it that the included columns should be columns that you don't search on, but will return. I...

You can see that in the slower queries you had more physical reads on the ENQUIRY table which means something else happened that caused it to be flushed from the...

You are getting Clustered Index Scans (basically table scans) on your CaseEvents tables which will cause the query to be slower. Depending on the use of the tables and...

I'm not sure what you mean by excessive joins. If you are working from an OLTP database proper normalization is going to create the need for many joins, so...

JumpStartTV has a decent one here, http://www.jumpstarttv.com/Media.aspx?vid=76

Duplicate post also at http://www.sqlservercentral.com/Forums/Topic509060-323-1.aspx

Is the structure of the temporary table the same in each case? If it is explicitly define the temp table and then do Insert Into instead of select into.

The first thing I would recommend is checking to see if your statistics are being updated. Check the DB to see if autoupdate stats is on.

Second in...

Yes you can use case in the join. A simpler solution would be to use a data out in the future for an end date instead of an...

I agree with Matt that there is not a lot of advice that can be given with the generality of the question. Steve also makes an accurate point, that...

I have only been in one position where I was able to specialize other than specializing in MS development (Windows, SQL Server, VB, .NET). The one position where I...