.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Common Expression table vs Derived table

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :Sql Server

Hello ,

I have read about Common expression table . and I have read the following :

   " Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query"  .

  I want to know what does it mean ? and I need a sample to understand it .




View Complete Post

More Related Resource Links

Reviewer suggested common table expression instead of adding 12 fields

So during a code review a DB Mgr suggested that I use a common table function instead of adding the fields below. No idea how to possibly do this. Anyone have any ideas? Here is a code snippet that he wants to lose: SELECT   LP .leaseID, sum   (paymentAmount1+paymentAmount2+paymentAmount3+paymentAmount4+paymentAmount5+paymentAmount6+paymentAmount7+paymentAmount8+paymentAmount9+paymentAmount10+paymentAmount11+paymentAmount12) as restPayment FROM   leasePaymentScheduleDetails AS L inner join leasePaymentSchedules AS LP ON L.leasePaymentScheduleID = LP.leasePaymentScheduleID WHERE   paymentYear>=@curyear+5 GROUP   BY LP .leaseID   Any input is appreciated.

Common Table expression

Common Table Expression(CTE) is aexpression which will be
called recursively until the condition is satisfied. It's
introduced in Sql Server 2005. This can be used in both
Select and DML statements. The result set generated by the
CTE is same as a hybrid Derived Table meets declared Temporary

How to use the table which is created with CTE(Common Table Expression) more than one select,insert


I faced one problem while working with Common Table expressions in Sqlserver 2008.

the problem is How to use the table(means temporary table)  which is created  by with  CTE's after that one select statement.

Actaully the table(which is created using with CTE) is automatically deleted, one's after execution of any select,insert,update or create view statement .Right!

But I needed that table second time also. How it is possible.

please help if any one knows..........

Thanks in Advance........


Data Points: Common Table Expressions


In T-SQL, views and derived tables serve similar purposes and have their advantages. But when working with SQL Server 2005,there's a third option--using Common Table Expressions (CTEs). John Papa explains.

John Papa

MSDN Magazine October 2007

Regular Expression to Match


Here is the kind of text I want to match via a regular Expression

The id="dgSchedule" is always present in the TAG but its location may differ

The table is span over multiple line/contains white spaces/tabs...

I have the regular expression to match the start and end tag respectively over a single line

\<table .*\>


The problem is to match the whole table span over multiple lines

<table cellspacing="0" rules="all" border="1" id="dgSchedule" style="border-style:None;height:100%;width:100%;border-collapse:collapse;">
	<tr class="blackbar" align="center" style="background-color:#7FB4DE;font-family:Verdana;font-weight:bold;height:20px;">
		<td>From Place</td><td>To Place</td><td>Time</td><td>Bus Type</td>

	</tr><tr style="background-color:#DEECF5;">
	</tr><tr style="background-color:#EFF5FA;">
	</tr><tr style="background-color:#DEECF5;">


How to recover from Derived Column Transformation Editor corrupting table metadata?

Hi there, In attempting to use the DCTE to replace the value in a field with a trimmed version (SSN = trim(SSN)), it seems that my meta-data has become corrupted by the Derived Column drop-down list. As a result, #1: I no longer see my incoming SSN field in the "columns" tree, and any reference to it is deemed "invalid", even though it's value does make it out of the Data Transformation process. How can I get back the reference to this field without redoing this entire task? And, #2: in the process, DCTE created new columns with "SSN" prefixed by the task name, such as "trim character fields.SSN". How can I delete these? It seems that one slip of the mouse in this form can lead to irreversible corruption of the meta-data, which the "debugger" then references and uses to "invalidate" subsequent work. I have tried everything I can think of to refresh this, including using the "Advanced Editor" and reloading the entire package. Any ideas? Thanks, Karl Kaiser

Avoiding a SELECT distinct query generated by SSAS when using dimension derived from fact table

Hi, I am using a dimension derived out ot a fact table and the factt able primary key is dimension key. Issue is, there are large number of rows and so many attributes. SSAS issues distinct query and it takes large amount of time. Without the distinct statement, query takes only 3 min for 4 million rows. With the distinct, it takes 20 min. Becuase the fact primary key is the dimension key there is no need of a distinct statement. I know there is a option in the dimension to say "By Table" to avoid this. But unfortuantely, i breach the 4 GB limit for strings. Any suggestions for optimization? Thanks,  Sambath

CTE doesn't work where derived table does

-- This produces: 0 rows affected
WITH upTasks(tStart, tEnd, BLineId, PlanId) AS 
 SELECT BLineId, tStart, tEnd, PlanId FROM Tasks_temp
 WHERE PlanId = 19 AND BLineId <= 8
UPDATE Baselines 
SET Baselines.tStart = upTasks.tStart, Baselines.tEnd = upTasks.tEnd
FROM upTasks 
WHERE Baselines.BLineId = upTasks.BLineId AND Baselines.PlanId = upTasks.PlanId

-- This produces: 8 rows affected
UPDATE Baselines 
SET Baselines.tStart = upTasks.tStart, Baselines.tEnd = upTasks.tEnd
FROM ( SELECT BLineId, tStart, tEnd, PlanId FROM Tasks_temp
 WHERE PlanId = 19 AND BLineId <= 8) AS upTasks
WHERE Baselines.BLineI

SQL gets corrupted with 'WITH' command and rubbish when editing query containing derived table


When my colleague makes any change to a query containing a derived table, the word 'WITH' followed by a lot of graphical characters appears after the alias, rendering the query unusable.  We have tried this with various existing and new, simple queries, all to no avail.  We are editing the queries in Visual Studio 2003, and he does not get this effect when using Visual Studio 2005.  I can edit the query in 2003 on my laptop with no ill effects.





Expression box only shows the first row of data in a repeating table...

Expression box only shows the first row of data in a repeating table, how can I display all rows of a repeating table in an expression box, preferably without code?

CTE Query as Derived Table


Can a query with CTE be used as derived table? The following attempt fails. Thanks.

WITH CTE AS (SELECT ProductName=Name, Price=ListPrice, ProductID
       FROM AdventureWorks2008.Production.Product)

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

convert query to derived table


I have the query like that:

SELECT orderid, orderdate, custid, empid,
		  ROW_NUMBER() OVER (PARTITION BY orderdate ORDER BY orderid) rownum
FROM [TestSQL].[Sales].[Orders]

How can I do it with derived table?



derived table


It is crazy. But now I need use derived tables obviguously for solving this task: I have table of Employees (empid,mgrid,firstname,lastname). I need,using derived table to write query that Return information about an employee (Don Funk, employee ID 2) and all of the employee's subordinates in all levels (direct or indirect).


empid       mgrid       firstname  lastname
----------- ----------- ---------- --------------------
2           1           Don        Funk
3           2           Judy       Lew

It is so simple task, and how is it possible do it using delivered tables?

SSIS - Derived Column Error Rows will not redirect to table


I am new to SSIS and I have created a package that generally successfully takes a Flat File Source, runs it through a Derived Column transformation and then pushes the data into a SQL Server Database.

I have a Derived Column Error Output set up, but it is not working.  If the data has an error the entire package fails, not just the specific row.  I have tested the package to load successfully and then manually gone into the flat file and changed the data to purposely create an error in one row. Having cleared the load table I rerun the package and it fails entirely rather than redirecting the one bad row.

In the Derived Column Transformation Editor I set the Error and Truncation values to Redirect Row for all the columns.

In SQL Destination Editor for the bad rows I am only mapping the ErrorCode abnd ErrorColumn values to the destination table fields of the same name.  Could this be the problem? Should I map all the fields?  I would like to map all the source (derived) fields into a single text field in the destination table if possible.

The Advanced settings have only the Table Lock and Check constraints boxes checked.  Timeout is 30.

I am not really sure how to set the Advanced Editor properties.  I have left them at the default values.  Any suggestions here?



Derived Table Not Working


I wish

to return just 1 record per DTS Package Name. In this system table there are multiple records for each DTS Package due to the amount of times the DTS P

Is there a work-around to create indexes for a view defined by a sub-query, derived table, or CTE?


I have three versions of an indexed view I'd like to create. I can create the view, but cannot create a clustered index on (CollectionID, ElementID, TimeID) because the definitions use variously a sub-query, CTE, or derived table, which SQL will not allow.

Has anyone figured a workaround for this? Any suggestions much appreciated.



VIEW [dbo].[aonCompositeFactView] WITH

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend