In the code below I am using a recursive CTE(Common Table Expression) in SQL Server 2005 to try and find the top level parent of a basic hierarchical structure. The rule of this hierarchy is that every CustID has a ParentID and if the CustID has no parent then the ParentID = CustID and it is the highest level. DECLARE @LookupID int --O...
I'm looking at improving the performance of some SQL, currently CTEs are being used and referenced multiple times in the script. Would I get improvements using a table variable instead? (Can't use a temporary table as the code is within functions). ...
I have around 3500 flood control facilities that I would like to represent as a network to determine flow paths (essentially a directed graph). I'm currently using SqlServer and a CTE to recursively examine all the nodes and their upstream components and this works as long as the upstream path doesn't fork alot. However, some queries tak...
I have some hierarchical data - each entry has an id and a (nullable) parent entry id. I want to retrieve all entries in the tree under a given entry. This is in a SQL Server 2005 database. I am querying it with LINQ to SQL in C# 3.5. LINQ to SQL does not support Common Table Expressions directly. My choices are to assemble the data in ...
Is there any performance gain using a CTE over a derived table? ...
Hi, I've following problem, which I've not been able to do successfully. Your help will be appreciated. I'm using SQL 2005, and trying to do this using CTE. Table has following 2 columns DocNum DocEntry 1 234 2 324 2 746 3 876 3 764 4 100 4 3...
I have a table that contains a number and a range value. For instance, one column has the value of 40 and the other column has a value of 100 meaning that starting 40 the range has 100 values ending in 139 inclusive of the number 40. I want to write a tsql statement that expands my data into individual rows. I think I need a cte for th...
I have a table of values like this 978412, 400 978813, 20 978834, 50 981001, 20 As you can see the second number when added to the first is 1 number before the next in the sequence. The last number is not in the range (doesnt follow a direct sequence, as in the next value). What I need is a CTE (yes, ideally) that will output this 97...
I have following issue: I have a table for maintaining the hierarchical data. I'd like to use CTE from SQL 2005. WITH tree (id, parentid, code, name) AS ( SELECT id, ofs.ParentID, ofs.code, ofs.name FROM OrganizationFeatures ofs WHERE ofs.ParentID IS NULL UNION ALL SELECT ofs.id, ofs.ParentID, ofs.code, ofs.name...
I need to do something like this but SQL Server 2008 doesn't like it. My query is actually more complex than this and I realize this isn't the best way to accomplish what I'm doing but my focus is on the functionality of the WITH statement and not the select and where statements. WITH stuff1 AS ( select name, startdate, id from...
I have a Comment table which has a CommentID and a ParentCommentID. I am trying to get a list of all children of the Comment. This is what I have so far, I haven't tested it yet. private List<int> searchedCommentIDs = new List<int>(); // searchedCommentIDs is a list of already yielded comments stored // so that malformed data does not ...
I've got a 'Task' table with the following columns (the TaskOrder is for ordering the children within the scope of the parent, not the entire table): TaskId ParentTaskId TaskName TaskOrder I've got this CTE query to return all the rows: with tasks (TaskId, ParentTaskId, [Name]) as ( select parentTasks.TaskId, parentTa...
Image you are creating a DB schema for a threaded discussion board. Is there an efficient way to select a properly sorted list for a given thread? The code I have written works but does not sort the way I would like it too. Let's say you have this data: ID | ParentID ----------------- 1 | null 2 | 1 3 | 2 4 | 1...
I have a CTE that's doing some recursion. The easiest way to imagine the problem space is a resistor that is used in cars all over the world and you have all that information. Resistor A is used in Board B and C which is used in DashAssembly D,E,F...ZZ which is used in Car AAA, AAB and AAC. I got the CTE working with one part, checked ...
Is it possible to do common table expressions (CTE) (like shown below) in Linq to SQL. I'm pretty new to CTE's as well as Linq to SQL. I'm currently Stored Proc free (but not against them by any means) so i don't want to take the leap to stored procs just for one query unless it's totally necessary. Here's an example of what I'm doing...
I can find all the children of a given record in a hierarchical data model (see code below) but I'm not sure how to traverse back up the Parent/Child chain with a given Child ID. Can anyone point me in the right direction to figure out how to do this? Is this possible in Linq to SQL as well? WITH TaskHierarchy (TaskID, [Subject], Pare...
Simpler Example Let's try a simpler example, so people can wrap their heads around the concepts, and have a practical example that you can copy&paste into SQL Query Analizer: Imagine a Nodes table, with a heirarchy: A - B - C We can start testing in Query Analizer: CREATE TABLE ##Nodes ( NodeID varchar(50) PRIMARY KEY NOT NUL...
Hi there. I'm experimenting with CTE's in SQL Server but have reached a dead end with getting the following scenario to work. I have a hierarchy table similar to this: Node(ID:439) Node(ID:123) Node(ID:900) Node(ID:56) Node(ID:900) Expected results: NodeID ParentNodeID 439 0 123 439 900 123 56 439 90...
Which are more performant, CTE or temporary tables? ...
From this post How to use ROW_NUMBER in the following procedure? There are two versions of answers where one uses a SubQuery and the other uses a CTE to solve the same problem. Now then, what is the advantage of using a CTE (Common Table Expression) over a sub-query(thus, more readable what the query is actually doing) The only advant...