Ed--

Might be a good idea to ask about Common Table Expressions rather than
calling it just 'with'. Many clauses in T-SQL start with 'with'.

CTEs are defined in ANSI SQL-99, of course, and that's where Microsoft
found them (SQL Server 2005). They were an IBM innovation, though (as
was SQL itself). I believe they were first in the AS400 variant of SQL
and then made their way, by popular demand, into DB2.

CTEs are neither commands nor functions, but just use a 'WITH' clause to
define a fleeting temporary structure. The advantage is that one need
not use DDL to get result sets that otherwise would have required one to
create a temporary table. The disadvantage is that (as you found) the
language can be opaque and persnickety (hence my need for explicit CASTs
to strictly defined varchar types in the converted code).

As an Oracle guy, I'm sure you know that Oracle's proprietary extension
to do something similar is CONNECT BY, which led to the with clause in
DB2. The with clause did not come into the Oracle database until
version 9.2.0.1.

--Jonathan


To All,
Well, well, well! It's good to be back! Can you hear me now?
What's in your wallet? Hehe. It's Friday! It's quiz time! Can you
win the Coke? Hehe.

1. What is this "with" clause in SQL Server?

2. What version was it first used in SQL Server?

3. What was the first database to use the "with"?

4. Is it a command or function?

5. Is it straight SQL or T-SQL?

6. What are the advantages of using it?

7. What are the disadvantages of using it?

My young, junior DBAs loved this question! I sure do miss them! Let
me end now before tears startup!


Please take a few minutes to provide feedback on the quality of service you received. The Department of Education values your feedback as a customer. Commissioner John L. Winn is committed to continuously assessing and improving the level and quality of services provided to you by Department staff. Simply use the link below. Thank you in advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]

Oracle LazyDBA home page