On Tue, 19 Oct 2010 17:54:22 -0700, tshad wrote:

You can use the alias of the derived table as a qualifier for column
names, but not as a table in the FROM clause of a subquery. Just as you
can't write
FROM SomeTable AS a
WHERE xyz = (SELECT ... FROM a WHERE ...)
but have to repeat the table name there.

This is one of the reasons why CTEs were invented, to enable you to
refer to the same subquery multiple times.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Hugo Kornelis wrote:
Umm... would I be wrong if @Status was NULL?

--
Andrew

On Thu, 21 Oct 2010 09:18:02 +0100, Andrew Morton wrote:

Yes, you would. If @Status is NULL, both @Status = 'ALL' and @Status <>
'ALL' evaluate to Unknown.
Unknown AND (dStart = ....) evaluates to either False or Unknown
(depends on the result of the second condition
Unknown OR (Unknown or False) evaluates to Unknown.

And since Unknown means the row is rejected, nothing will ever be
returned from this query when @Status is NULL.

I can't believe I overlooked that.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

tshad (tfs (AT) dslextreme (DOT) com) writes:
Hugo explained why this does not work, and also provided the best
solution. But for completeness sake, I like to add that you can
use the table variable directly as a prefix, but you need to quote it:

from @tfs
WHERE [@tfs].Retailer = Retailer and
[@tfs].StoreID = StoreID and
[@tfs].QuestionID = QuestionID)))



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

That may be the case, but in a recursion case I find that the CTE works
better (faster) than cursors or recursive rountines.

I have found lately, that CTE's can be a problem in many cases.

I have read the it is
"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote


I started using CTEs after reading an article by John Papa where is states:

"Many projects that developers work on involve writing complex SQL
statements that deviate from basic SELECT/FROM/WHERE types of statements.
One such scenario involves writing Transact-SQL (T-SQL) queries that use
derived tables (also known as inline views) inside a FROM clause. This
common practice allows a developer to grab a rowset and immediately join
that rowset to other tables, views, and user-defined functions in a SELECT
statement. Another option is to use a view instead of a derived table. Both
of these options have their advantages and disadvantages.
When working with SQL ServerT 2005, I prefer a third option of using Common
Table Expressions (CTEs). CTEs can help improve the readability (and thus
the maintainability) of the code without compromising performance."

I am finding that this is not the case in many (most cases where I and
others on my team have been using them).

I agree with him that it is useful with recursion. I have taken changed
recursive code that took 5 minutes down to 10 seconds just by changing the
code to a CTE.

But in other cases, I am finding that changing the CTE to a temp table will
fix or speed up the query.

I also am finding that where the CTE will work fine (for months), it
sometimes quits working. After much investigation, I end up changing the
CTE to temp tables to solve the issue.

For example, we had a CTE that worked fine taking about 2 or 3 minutes to
run that was really 2 CTEs joined together. This was working for about 2
months. Then last week it started to take 30 minutes plus ending up with an
error (something lile illegal syntax near '-'). Then we would run it again
and it would take 8 minutes to run but no error. Later, it wouldn't return
at all and after about an hour and half we would just kill it. We did see
that the tempDB had increased to 120GB (not sure if this query caused it).

It looked something like:

WITH myCTE
(
SELECT ...
), myCTE2
(
SELECT ...
)
SELECT .. (joining the 2 CTEs)

We thought maybe there was something with the server but after I changed it
to: 2 SELECT/INTO statments with 2 temp tables, it worked fine and took
about a minute to run.

We also converted a couple regular CTEs that were taking about 50 seconds to
using a temp table (SELECT/INTO):

WITH myCTE
(
SELECT...
)
SELECT ...

to

SELECT/INTO

and it took about 20 seconds.

Not a lot of difference but not quite what John was saying.

My concern is taking the time to write the CTE when it may just be better to
write it the old ways.

Thanks,

Tom