March 11, 2019 at 1:58 pm
doug.bishop - Monday, March 11, 2019 1:17 PMI guess that is what makes us all unique. I've run across database developers who will always chose to use a table variable over a temp table because table variables are in memory and not written to disk. The first time a MERGE bites me, I'll be more cautious, but won't stop using them. To me, they are much easier to read and understand than replicated code with LEFT JOINS checking for NULLs to do UPSERTS (and possibly add a DELETE in there) and the flexibility of exposing data from the source side of the query in the OUTPUT clause has made them next to invaluable on more than one occasion.
I've learned there is enough room out there in the universe for everyone to have a different opinion about something and that doesn't make them right or wrong or better or worse, just different.
Might be worth noting that table variables aren't in-memory only structures, that's a myth:
Are table variables only stored in memory
TempDB:: Table variable vs local temporary table
Comparing Table Variables with Temporary Tables
Sue
March 12, 2019 at 6:20 am
Sue_H - Monday, March 11, 2019 1:58 PMdoug.bishop - Monday, March 11, 2019 1:17 PMI guess that is what makes us all unique. I've run across database developers who will always chose to use a table variable over a temp table because table variables are in memory and not written to disk. The first time a MERGE bites me, I'll be more cautious, but won't stop using them. To me, they are much easier to read and understand than replicated code with LEFT JOINS checking for NULLs to do UPSERTS (and possibly add a DELETE in there) and the flexibility of exposing data from the source side of the query in the OUTPUT clause has made them next to invaluable on more than one occasion.
I've learned there is enough room out there in the universe for everyone to have a different opinion about something and that doesn't make them right or wrong or better or worse, just different.
Might be worth noting that table variables aren't in-memory only structures, that's a myth:
Are table variables only stored in memory
TempDB:: Table variable vs local temporary table
Comparing Table Variables with Temporary TablesSue
Sorry, I meant to quote that statement. I know that, but they were convinced otherwise 🙂
March 13, 2019 at 11:08 am
doug.bishop - Monday, March 11, 2019 11:23 AMthisisfutile - Monday, March 11, 2019 11:12 AMjohnzabroski - Monday, March 11, 2019 7:53 AMNo article on OUTPUT and MERGE should be complete without referencing Aaron Bertrand's cautionary article on using MERGE.https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
I agree! Here's an excerpt ...
[After listing 29 bugs] is the condensed format of MERGE really worth all of the extra testing that will require?I was so excited when MERGE first came out but after experiencing one anomaly after another, I realized that my list of documented "gotchas" grew longer than my patience. I haven't touched that command in 10 years.In all fairness, it appears Aaron's list is somewhat out of date. I clicked on some of them marked "Won't Fix" and it appeared that, of those I checked, most had been fixed, albeit ppossibly in a later version of SQL Server. Also, just counting the number of issues is not fair either as some as, "Poor error message with MERGE when source/target appear in impossible places" which isn't really a bug but just a confusing error message, and one that is an issue with both MERGE and DELETE.
I have used MERGE since SQL Server 2008R2 for production ETL, loading literally millions of rows of data into tables containing tens of millions of rows of data and have never experienced an issue. I've had people point out to me that if you do A and B and C and D where the conditions are E and F then you'll have problems with MERGE, but I guess I've only done A and B and D without C and gotten lucky.
I recently logged an issue where MERGE failed using the new(er) temporal tables feature. If you look at the comments in the link I provided, you will see my comment there with links to the user voice feedback about such bugs.
I also use MERGE, but I don't assume I can get super-performance speedup on ETL operations thanks to MERGE combining INSERT/UPDATE in a single pass/table scan. I always set expectations with the business (in layman's terms) that it could require two passes over the data to synchronize everything.
I'm actually not even really a DBA (although my knowledge and wisdom about databases is very extensive and I tend to hang out with DBAs more than C# developers). I'm a C# developer, and more often than not I use a library called EntityFramework Extensions from Zzz Projects which automatically generates MERGE syntax just by me typing 8 characters of text.
March 13, 2019 at 11:13 am
doug.bishop - Monday, March 11, 2019 1:17 PMthisisfutile - Monday, March 11, 2019 12:18 PMdoug.bishop - Monday, March 11, 2019 11:23 AMthisisfutile - Monday, March 11, 2019 11:12 AMjohnzabroski - Monday, March 11, 2019 7:53 AMNo article on OUTPUT and MERGE should be complete without referencing Aaron Bertrand's cautionary article on using MERGE.https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
I agree! Here's an excerpt ...
[After listing 29 bugs] is the condensed format of MERGE really worth all of the extra testing that will require?I was so excited when MERGE first came out but after experiencing one anomaly after another, I realized that my list of documented "gotchas" grew longer than my patience. I haven't touched that command in 10 years.In all fairness, it appears Aaron's list is somewhat out of date. I clicked on some of them marked "Won't Fix" and it appeared that, of those I checked, most had been fixed, albeit ppossibly in a later version of SQL Server. Also, just counting the number of issues is not fair either as some as, "Poor error message with MERGE when source/target appear in impossible places" which isn't really a bug but just a confusing error message, and one that is an issue with both MERGE and DELETE.
I have used MERGE since SQL Server 2008R2 for production ETL, loading literally millions of rows of data into tables containing tens of millions of rows of data and have never experienced an issue. I've had people point out to me that if you do A and B and C and D where the conditions are E and F then you'll have problems with MERGE, but I guess I've only done A and B and D without C and gotten lucky.
Thanks for the reply. My experience was uncovering the anomalies personally and that led me to the stance that I was never going to work with it. Over the years I've seen good and bad reviews of the command and I just got the feeling (fear?) that it may get deprecated before it fully matures. So, couple a bad first experience with a guess of early abandonment and I've decided I'll never give it a second try (are my 47 years of age showing?) I remember when I started discovering CTE's and I recalled my MERGE experience and almost decided to avoid them and stick with derived tables just on principle. Fortunately, CTE's worked flawlessly for me and have ever since ... well, until I get wrapped up in recursion.
I guess that is what makes us all unique. I've run across database developers who will always chose to use a table variable over a temp table because table variables are in memory and not written to disk. The first time a MERGE bites me, I'll be more cautious, but won't stop using them. To me, they are much easier to read and understand than replicated code with LEFT JOINS checking for NULLs to do UPSERTS (and possibly add a DELETE in there) and the flexibility of exposing data from the source side of the query in the OUTPUT clause has made them next to invaluable on more than one occasion.
I've learned there is enough room out there in the universe for everyone to have a different opinion about something and that doesn't make them right or wrong or better or worse, just different.
Hi Doug,
I don't agree. We should not be unique with regard to temp tables vs. table variables, at least as of SQL Server 2016.
The primary thing to understand is that temp tables are tables and will be rolled back in a transaction, whereas table variables are variables and therefore there is nothing to "roll back" in a transaction.
DECLARE @intVar INT
SET @intVar = 1
SELECT @intVar BeforeTransaction
BEGIN TRAN
SET @intVar = 2
ROLLBACK
SELECT @intVar AfterRollBackTran
March 13, 2019 at 11:45 am
johnzabroski - Wednesday, March 13, 2019 11:13 AMdoug.bishop - Monday, March 11, 2019 1:17 PMthisisfutile - Monday, March 11, 2019 12:18 PMdoug.bishop - Monday, March 11, 2019 11:23 AMthisisfutile - Monday, March 11, 2019 11:12 AMjohnzabroski - Monday, March 11, 2019 7:53 AMNo article on OUTPUT and MERGE should be complete without referencing Aaron Bertrand's cautionary article on using MERGE.https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
I agree! Here's an excerpt ...
[After listing 29 bugs] is the condensed format of MERGE really worth all of the extra testing that will require?I was so excited when MERGE first came out but after experiencing one anomaly after another, I realized that my list of documented "gotchas" grew longer than my patience. I haven't touched that command in 10 years.In all fairness, it appears Aaron's list is somewhat out of date. I clicked on some of them marked "Won't Fix" and it appeared that, of those I checked, most had been fixed, albeit ppossibly in a later version of SQL Server. Also, just counting the number of issues is not fair either as some as, "Poor error message with MERGE when source/target appear in impossible places" which isn't really a bug but just a confusing error message, and one that is an issue with both MERGE and DELETE.
I have used MERGE since SQL Server 2008R2 for production ETL, loading literally millions of rows of data into tables containing tens of millions of rows of data and have never experienced an issue. I've had people point out to me that if you do A and B and C and D where the conditions are E and F then you'll have problems with MERGE, but I guess I've only done A and B and D without C and gotten lucky.
Thanks for the reply. My experience was uncovering the anomalies personally and that led me to the stance that I was never going to work with it. Over the years I've seen good and bad reviews of the command and I just got the feeling (fear?) that it may get deprecated before it fully matures. So, couple a bad first experience with a guess of early abandonment and I've decided I'll never give it a second try (are my 47 years of age showing?) I remember when I started discovering CTE's and I recalled my MERGE experience and almost decided to avoid them and stick with derived tables just on principle. Fortunately, CTE's worked flawlessly for me and have ever since ... well, until I get wrapped up in recursion.
I guess that is what makes us all unique. I've run across database developers who will always chose to use a table variable over a temp table because table variables are in memory and not written to disk. The first time a MERGE bites me, I'll be more cautious, but won't stop using them. To me, they are much easier to read and understand than replicated code with LEFT JOINS checking for NULLs to do UPSERTS (and possibly add a DELETE in there) and the flexibility of exposing data from the source side of the query in the OUTPUT clause has made them next to invaluable on more than one occasion.
I've learned there is enough room out there in the universe for everyone to have a different opinion about something and that doesn't make them right or wrong or better or worse, just different.
Hi Doug,
I don't agree. We should not be unique with regard to temp tables vs. table variables, at least as of SQL Server 2016.
The primary thing to understand is that temp tables are tables and will be rolled back in a transaction, whereas table variables are variables and therefore there is nothing to "roll back" in a transaction.
DECLARE @intVar INT
SET @intVar = 1
SELECT @intVar BeforeTransaction
BEGIN TRAN
SET @intVar = 2
ROLLBACK
SELECT @intVar AfterRollBackTran
Not sure I completely understand your response. The point I was making (and didn't do it well initially) is that there are some I've run across who will use a table variable exclusively because they think it is a memory only solution and don't realize it will write contents to disk. Both a temp table and table variable can be either completely memory resident or disk resident, depending on available memory and size of data they are holding.
There are times to use one and times to use the other and I was not advocating for one or the other. If you thought I was, I apologize for the misunderstanding. I do understand the difference between the two and when I should/would use one vs. the other.
July 8, 2021 at 1:49 am
ActionType VARCHAR(50)
Seemed excessive ?
Could be ActionType VARCHAR(6)
for:
UPDATE, INSERT, DELETE
?
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply