Viewing Query Plans

This is more of a reminder to myself. I can’t always remember the commands to show the execution plan in text. It’s nice having the graphical interface but when you have a big execution plan then seeing it in text is much easier. Here are the commands:

– simple estimated execution plan

set showplan_text on

GO

–*** estimated execution plan with more detail. best to use this ****

set showplan_all on

GO

– actual execution plan. runs the query. so it’s better to use showplan_all for a big query 

set statistics profile on

GO

More detail here

Books to Read: an update

Update on books to read:
1. T-SQL 2000 – Itzik Ben Gan
Re-read 3 chapters. Downloaded the SQL scripts in the book from the publisher’s website. I did not know that they existed. That will make a good reference.

3. Fast Track to MDX – Mosha
Almost done. Need to finish the last chapter.

8. SQL Server 2005 Analysis Services Step by Step – Reed
Good read. Read most of the book and found it very useful in building cubes in 2005.

I will concentrate on finishing the above books and move on to the other ones.

CTE in SQL 2005

Common Table Expressions in SQL 2005 rule! One of the best uses of CTE is to do recursive queries. The syntax for it is as follows:

WITH myCTE (id, name, desc) AS

(

select id, name, desc

from table

you can reference the CTE here

)

select * from myCTE

The concept is similar to using derived tables in the way that your query can reference the CTE or derived table. The difference is that in a derived table you can only reference it after the derived table query has run. In a CTE, you can reference it while executing your query. And this makes it easy to build recursive queries.