June 22, 2015 at 8:03 am
Hello, My question is about how a cte works.
Looking at a stored procedure written by someone else I see the code:
; with dbtrDups as (select debtorkey,
row_number() over ( partition by matternum, refnum order by debtorcreatedate desc) as rownum
from #tmpcurrentcontactinfo )
delete from dbtrDups where rownum > 1
I assume this will work, but it is not how I would write it.
So my question is: does t-sql know to delete from table #tmpcurrentcontactinfo or should it be re-written to explicitly use #tmpcurrentcontactinfo?
Thanks
June 22, 2015 at 8:19 am
It deletes from the #temp table. If you don't understand this code, don't use it.
-- Gianluca Sartori
June 22, 2015 at 8:32 am
Since I did not write the stored procedure, I wanted to check the behavior before possible re-write.
I have actually run across something similar some place but I did not know you could use it with a cte.
The person who originally wrote the code died a few weeks ago, so could not ask him.
June 22, 2015 at 8:42 am
I formatted the code and added some commenst.
It should be clearer now.
-- this defines a CTE: think of it as an "inline view"
-- that you can reference anywhere in the statement
WITH dbtrDups
AS (
-- this ranks rows grouping by matternum, refnum and
-- sorting by debtorcreatedate
-- duplicates by matternum, refnum will have rownum > 1
SELECT debtorkey
,row_number() OVER (
PARTITION BY matternum,refnum
ORDER BY debtorcreatedate DESC
) AS rownum
FROM #tmpcurrentcontactinfo
)
-- use the CTE to delete duplicates
-- CTEs can be used in deletes, following the same rules
-- that apply to views
DELETE
FROM dbtrDups
WHERE rownum > 1
-- Gianluca Sartori
June 22, 2015 at 8:46 am
djj (6/22/2015)
Since I did not write the stored procedure, I wanted to check the behavior before possible re-write.I have actually run across something similar some place but I did not know you could use it with a cte.
The person who originally wrote the code died a few weeks ago, so could not ask him.
There's nothing wrong with the code, such drastic action was unnecessary.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 22, 2015 at 8:48 am
Thank you!
Did not mean for you to go to so much trouble. I realize the duplicate removal as I have used it many times.
What at first I did not understand was that the cte alias which is deleted from deletes from the underlying table - in this case a temp table as you described in your first reply.
June 22, 2015 at 8:50 am
Thank you ChrisM@Work, I will not be changing the code now that I have conformation of what it does.
June 22, 2015 at 11:25 am
djj (6/22/2015)
Thank you ChrisM@Work, I will not be changing the code now that I have conformation of what it does.
It's always good to understand what code does before you change it, and one of the best ways to understand is to change the DELETE statement into a SELECT and see what records are returned. If a change is planned, create a copy of the code to make changes to, and then change DELETE to SELECT and see if it returns the exact same records as the original code with the change from DELETE to SELECT. If it does, and the logic of the changed query appears to duplicate the logic of the original, then you are probably good to go.
As to not changing the code, I'm not sure that I wouldn't. As written, it requires a deeper understanding of T-SQL than many folks have, and if I have to maintain code that will some day get maintained by the "next guy", then I would much rather opt for simplicity than for technically correct rocket science. I'm not suggesting that this particular DELETE amounts to rocket science - merely that it is coded in a way that isn't necessarily clear.
My guess is, had the query been written as follows:
WITH dbtrDups AS (
SELECT debtorkey,
ROW_NUMBER() OVER (PARTITION BY matternum, refnum ORDER BY debtorcreatedate DESC) AS rownum
FROM #tmpcurrentcontactinfo
)
DELETE CCI
FROM #tmpcurrentcontactinfo AS CCI
INNER JOIN dbtrDups AS DD
ON CCI.debtorkey = DD.debtorkey
AND DD.rownum > 1
there wouldn't likely have been much confusion about which records were going to be deleted. Some may disagree, and that's to be expected. I just prefer to leave the database in a better place than when I encountered it. There are, and always will be, other factors that can end up making the decision for you, that range from politics to deadlines to office control freaks to whatever.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 22, 2015 at 1:07 pm
I don't know Steve. For instance, I don't like to filter using joins as it won't make the intention evident. On the other side, I believe that you would be preventing others to learn something new. Avoiding confusion and making code easier to read is the major reason that I learned to use cursors.
A few comments on the code will help to make the code clear and allow people to learn things by looking at the code. After all, searching for CTE and delete will take them to multiple articles explaining how this works, it's no big secret.
June 22, 2015 at 1:29 pm
Luis Cazares (6/22/2015)
I don't know Steve. For instance, I don't like to filter using joins as it won't make the intention evident. On the other side, I believe that you would be preventing others to learn something new. Avoiding confusion and making code easier to read is the major reason that I learned to use cursors.A few comments on the code will help to make the code clear and allow people to learn things by looking at the code. After all, searching for CTE and delete will take them to multiple articles explaining how this works, it's no big secret.
I understand, but I continue to see the result of developers that learned entirely by bootstrap, who just don't think things through and code with really messy T-SQL, fail to comment their code because they don't have the time (usually traceable to their lack of forethought to begin with), and then code things as "cool" as they can, and what I see so often is usually worse than in this case, but why not teach people to think things through thoroughly instead of just bootstrapping your way forward into whatever mess can be created. No amount of code finesse can make up for thinking things through. Not even what they "learn" in the process. If I didn't see the direct result of that kind of learning on a daily basis, I might think otherwise.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 23, 2015 at 5:16 am
sgmunson (6/22/2015)
djj (6/22/2015)
Thank you ChrisM@Work, I will not be changing the code now that I have conformation of what it does.It's always good to understand what code does before you change it, and one of the best ways to understand is to change the DELETE statement into a SELECT and see what records are returned. If a change is planned, create a copy of the code to make changes to, and then change DELETE to SELECT and see if it returns the exact same records as the original code with the change from DELETE to SELECT. If it does, and the logic of the changed query appears to duplicate the logic of the original, then you are probably good to go.
In this case I new what the delete did and what the cte returned so the select would not have helped with my understanding that the cte table is directly connected (pass through) to the underlying table. I had thought a cte table was more like a temporary table containing data but not connected directly to the table it came from.
As stated I personally would have programmed something like you suggested. As to changing the current code, it comes down to we have too many higher priority projects going for me to get a review for a code change that is not needed. I will keep it on my "possible to do" list.
Thanks everyone for the discussion, I am always learning.
June 26, 2015 at 10:55 am
ChrisM@Work (6/22/2015)
djj (6/22/2015)
Since I did not write the stored procedure, I wanted to check the behavior before possible re-write.I have actually run across something similar some place but I did not know you could use it with a cte.
The person who originally wrote the code died a few weeks ago, so could not ask him.
There's nothing wrong with the code, such drastic action was unnecessary.
You only have to do it once, and the rest fall in line.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
June 26, 2015 at 2:07 pm
djj (6/23/2015)
sgmunson (6/22/2015)
djj (6/22/2015)
Thank you ChrisM@Work, I will not be changing the code now that I have conformation of what it does.It's always good to understand what code does before you change it, and one of the best ways to understand is to change the DELETE statement into a SELECT and see what records are returned. If a change is planned, create a copy of the code to make changes to, and then change DELETE to SELECT and see if it returns the exact same records as the original code with the change from DELETE to SELECT. If it does, and the logic of the changed query appears to duplicate the logic of the original, then you are probably good to go.
In this case I new what the delete did and what the cte returned so the select would not have helped with my understanding that the cte table is directly connected (pass through) to the underlying table. I had thought a cte table was more like a temporary table containing data but not connected directly to the table it came from.
As stated I personally would have programmed something like you suggested. As to changing the current code, it comes down to we have too many higher priority projects going for me to get a review for a code change that is not needed. I will keep it on my "possible to do" list.
Thanks everyone for the discussion, I am always learning.
Think about the CTE as if it were a view. You can update a view, and from that perspective, a CTE works very much the same way.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply