And if using SQL 2005, there's no need for code to reconstruct procedures from syscomments as you can seelct rather from sys.sql_modules, which does not split objects into 4000 character...

The only thing I can find on storage of Varchar(max) is that 'SQL decides' (Inside SQL Server 2005: T-sql Programming)

SELECT object_name(id) FROM syscomments where [text] like ....

True, which is why I removed the suggestion about INCLUDE (and yes,...

Don't ever put a distinct in an IN or EXISTS subquery. It's meaningless and may cause SQL to do unnecessary work.

In terms of an...

The timer is fairly accurate. If you want accurate timings, either use profiler to monitor your queries, or you can run SET STATISTICS TIME ON before you run the query,...

Agreed. If you love the job, the money will follow.

Check the full scans/sec counter.

From what I know, read-ahead reads are usually done when a table or index is scanned, and the storage engine fetches pages before they are requested,...

Hell, no. The amount of times I...

Perhaps I should have said 'not used in the filters' rather than not in the where clause (since there's no where clause)

The red highlight is a join to a...

Loner (3/5/2008)

---

God knows who designed this database. But the worst thing is somehow it cannot handle the company has more than 5 million shoppers. The query came back...

The index with ProductID first produces an index scan because ProductID is not one of the columns that are filtered on. A query can use an index seek if the...

A competant DBA. Even the Best Practices anaylser isn't foolproof

What does your full scans/sec look like?

I need to confirm this, but I believe it is 'as long as it fits'. Will reseach and confirm or correct later