Simons SQL Blog

Neesha Kanji Asked: Could you also save a subquery result set in a temp table and join that on the main query? Would the query be just as quick?

Simon Sabin Answered: Sometimes, however including the join in the query gives the optimiser the option to flatten the query and possibly save reading data multiple times. Often writing as a derived table can be a better solution. Using the temp table can often aid developing the code as its easier to test each section. BUT that doesn't mean you should split up all your queries as that removes the benefits of doing SET based processing.

Phil Pinto Asked: Recommend a performance book, please

Simon Sabin Answered: Personally you need to understand the internals to be able to understand performance. And so the Internals books are good. I learnt using a book from Ken Henderson many years ago so its worth trying his book http://www.amazon.com/Gurus-Guide-Server-Architecture-Internals/dp/0201700476/ref=sr_1_4?ie=UTF8&s=books&qid=1253631035&sr=1-4

Kevin Kline (Qsft) Answered: I also like Kalen Delaney's internals book. If I could only own one SQL Server book, it'd be hers. Itzik Ben-Gan's T-SQL books are also excellent.

Phil Pinto Asked: Thanks for book recommended. Indexes are useful but can be a maintenance overhead if only a very infrequent used query.

Kevin Kline (Qsft) Answered: KK> I also like Kalen Delaney's internal book - in fact, if I could only own one book, Inside SQL Server would be the one.

*** Baker Asked: database query in SSMS (previously QA) allows one do obtain ESTIMATED Query Plan (i.e. Ctrl-L). The question is does the ACTUAL QP always follow that "compile-time" decision strategy, or may it deviate (eg the SET STATISTICS IO ON which is actual after the statement completion)? For example the DEC RdB database (now subsumed into Oracle) would dynamically review several strategies : perhaps pick a simple FAST-FORWARD approach, but might switch to a more complex strategy after some pause (ie jettison the FF workinprogress)

Simon Sabin Answered: The only different between compile and execution time is the parallelism. The engine might decide to remove parallelism and run a non parallel query

Richard Asked: 2008 SSMS suggests missing indexes in Query Plans, regardless of version it is looking at

Simon Sabin Answered: Very good point. Its also worth noting that just because a missing index warning doesn't come up doesn't mean that there isn't a better index option available

Andy Irving Asked: it's only automatic use of indexed views which is EE only, in standard you have to use NOEXPAND hint

Simon Sabin Answered: Very true, often even in EE you need to use NOEXPAND because the indexed view isn't considered until late on in the query optimisation phase

*** Baker Asked: please highlight diff between temp table and temp table variable. can you index a table variable? what about PK (implied CI) perhaps #temp better than @temp for certain cases ? thanks!

Simon Sabin Answered: temp table and table variables use the same structure and are stored in tempdb. Table variables are not in memory structures. Depending on the load of the server either may be held in memory and both are stored on disk. Temp tables don’t have statistics which means that if you join to them you can result in poor query plans. Temp tables do have statistics but that itself can cause problems as it can cause recompilations of your code. You are correct that you cannot create indexes on a table variable, however you can indirectly create them by creating primary key and unique constraints. You can create indexes on temp tables. I generally say that table variables are great for small rows or where you are only going to be scanning from the table variable.