Performance of the SQL MERGE vs. INSERT/UPDATE (SQLServerCentral 2/3/2017)

[SQLServerCentral - www.sqlservercentral.com] A community of more than 1,600,000 database professionals and growing Featured Contents - [Performance of the SQL MERGE vs. INSERT/UPDATE] - [PowerShell Invoke-SQLCmd outputs DataTables you can INSERT into SQL Server] - [Is your SQL Server remote?] - [Setting Up a Productive Work Environment – #TAPtips] (From the SQLServerCentral Blogs) - [What’s a Database Encryption Key (DEK) in TDE] (From the SQLServerCentral Blogs) The Voice of the DBA Your Favorite Feature that Needs Work The SQL Server platform has grown tremendously since I started working with SQL Server v4.2. We've had various subsystems added, lots of enhancements, and even new languages. There have also been a few parts of the platform that have been discontinued, such as Notification Services and English Query. There are items that are deprecated, though far fewer that have actually been discontinued from the product. I'm not sure if I think that's better or worse for all of us. Apart from the deprecated, discontinued, and removed features, there are also lots of features in the platform that are stale. They receive very little development, and perhaps are very lightly featured on the [What's New pages]. Or they never get any development from version to version. This week, I'm curious what features you use or like that need help. [SQL Server v.Next] is in development, and while I don't think major changes will be made between now and RTM, perhaps there are improvements that could be made in some areas. I'm sure there are a few SQL Server developers at Microsoft that have time to ask for a few more pull requests from their particular area of development. With that in mind... What feature in SQL Server do you really like and wish would be improved in the future? Your idea would be large or small. Perhaps you have some language enhancement. Maybe you run setup regularly and there is a change that might streamline that process. It may be that replication conflict resolution is a constant problem for you and you'd like a few development hours spent on this. It could be anything that you use, or would like to use. Perhaps you have an idea for an improvement, or maybe you just want something done. Let us know today. The place to log bugs or feature requests is at the [SQL Server Connect site]. Post your idea there, then put a link in the discussion and make a case for why people should vote for it. Who knows? Perhaps you'll get some improvement for the feature you like in the next version of SQL Server. Steve Jones from [SQLServerCentral.com] Join the debate, and [respond to today's editorial on the forums] --------------------------------------------------------------- The Voice of the DBA Podcast Listen to the [MP3 Audio] ( 3.1MB) podcast or subscribe to the feed at [iTunes] and [Libsyn]. [feed] The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [DLM Dashboard] Track schema changes for free DLM Dashboard tracks SQL Server databases to show you exactly what schema changes have been made, by who, and when. You get a full history, with line-by-line differences, and a clear audit trail of your database moving from development to production. [Download free tool.] Featured Contents  [Performance of the SQL MERGE vs. INSERT/UPDATE] Dwain Camps from [SQLServerCentral.com] The SQL MERGE statement offers convenience, safety and elegance, but how does it perform compared to other methods?[More »] ---------------------------------------------------------------  [PowerShell Invoke-SQLCmd outputs DataTables you can INSERT into SQL Server] Additional Articles from [MSSQLTips.com] Aaron Nelson explains how you can take an existing SQL query, run it against a SQL Server and then take the results and save them to a different SQL Server without using Linked Servers.[More »] ---------------------------------------------------------------  [Is your SQL Server remote?] If the answer is yes, this survey is for you. Redgate is looking at building solutions to the issues associated with remote SQL Servers. They want to know your pain points, and whether there's anything they can do to assist. If you think you could help the team out, please fill in their survey.[More »] ---------------------------------------------------------------  From the SQLServerCentral Blogs - [Setting Up a Productive Work Environment – #TAPtips] Having a productive work environment for you and your team is important to facilitate tasks being accomplished as well as...[More »] ---------------------------------------------------------------  From the SQLServerCentral Blogs - [What’s a Database Encryption Key (DEK) in TDE] Steve Jones from [SQLServerCentral Blogs] The encryption mechanisms in SQL Server are interesting, and they work well, but they are somewhat poorly named. I ran...[More »] Question of the Day Today's Question (by Steve Jones): What improvement in SQL Server 2016 allows for better Always On encryption of the log stream? Think you know the answer? [Click here], and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: Encryption. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center]. ADVERTISEMENT [Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud] With Microsoft SQL Server 2016, a variety of new features and enhancements to the data platform deliver breakthrough performance, advanced security, and richer, integrated reporting and analytics capabilities. In this ebook, we introduce new security features: Always Encrypted, Row-Level Security, and dynamic data masking; discuss enhancements that enable you to better manage performance and storage: TemDB configuration, query store, and Stretch Database [Get your copy from Amazon today]. [e] Yesterday's Question of the Day Yesterday's Question (by Steve Jones): When installing SQL Server 2016 in an unattended setup fashion, if I want to install all features, what switches do I need to accept the license terms? Answer: /IACCEPTSQLSERVERLICENSETERMS and /IACCEPTROPENLICENSETERMS Explanation: If you install all features, then you need to use the two switches: - /IACCEPTSQLSERVERLICENSETERMS - /IACCEPTROPENLICENSETERMS Ref: Install SQL Server 2016 from the Command Prompt - [ --------------------------------------------------------------- [» Discuss this question and answer on the forums] Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums]. --------------------------------------------------------------- [SQL Server 2014] : [Administration - SQL Server 2014] [Any way to guesstimate internet needs for remote DBA?] - I am starting a new remote SQL DBA position. Normally I have high speed unlimited internet. But I may for... [DataBase Creation Error] - I am getting following Error Msg 5105, Level 16, State 2, Server ServerName, Line 1 A file activation error occurred. The physical... --------------------------------------------------------------- [SQL Server 2014] : [Development - SQL Server 2014] [Delete the duplicate records of table having only one column] - Hi all, Kindly suggest  or  share  such query to  me , How we can delete the rows having duplicate records Condition; Table having... [Best way to randomly select rows?] - select SUM(c_amount) from Transactions where s_credit_tran_type = 'cash' and b_cancel = 0 and b_credit = 0 and b_failed = 0 and dt_when between '2017-01-01 04:09:54.213'... --------------------------------------------------------------- [SQL Server 2012] : [SQL 2012 - General] [Latency issues in Transactional Replication] - Hi, We have two SQL server 2012 bidirectional transactional replications working correctly. Instance A (active) <-> Instance B (passive - not in use) Instance... [USERSTORE_TOKENPERM is Huge] - I'm having problems with my USERSTORE_TOKENPERM cache becoming growing. When I came in this morning it was 2.4 gb and... --------------------------------------------------------------- [SQL Server 2012] : [SQL Server 2012 - T-SQL] [Multiple rows to Individual Columns] - Hi Team, I got a scenario : Got two tables Table 1: ID(col1) Number(col2) Description(col3) 1(col1)  000101(col2)  xyz(col3) 2(col1)  000201(col2)  abc(col3) Table 2: ID(col1) Grade(col2) Value(col3) 1(col1)  aa(col2)... [Update from a join] - Hi! A have two tables, dutyrostershift and timeaccountmovement with a lot of fields - I am copying from one db to another,... [DUPLICATING A ROW BY NUMBER OF OCCURENCE OF A PARTICULAR FIELD IN ANOTHER TABLE] - Hello, I have this challenge that has been an issue for weeks now. I have a table A with 7 rows with... [Scripting Definition in sql_modules different from right click > script view.] - I have created a program that refreshes databases automatically, making sure fresh production data is inside an acceptance database. Part of... [Age as of January 1st] - Hello all, I'm really strugling on this age calcualtion. I need to find the age of a patient based off January... --------------------------------------------------------------- [SQL Server 2008] : [SQL Server 2008 - General] [DBCC CheckTable -- Help Please?] - Evening Guys, DBCC ChecTable has come back with the following unpleasant looking message: " Msg 8909, Level 16, State 1, Line 139 Table error:... [modifying a 'replace' function to use Lower and Upper] - I have a function that I'm using that is working with one glitch. It is designed to change non-English characters... [log file keep growing] - Hi guys I have an issue related to log file, it keeps growing until today and I can’t shrink it... [How can I prevent a record from appearing based on two columns] - Sorry for the vague Subject line but, I couldn' t come up with a good summary. In the table the... --------------------------------------------------------------- [SQL Server 2008] : [SQL Server Newbies] [Importing data using bcp] - Does anyone know how I can  import data from a csv file(pipe delimited)  into a sql server database table  using... --------------------------------------------------------------- [Reporting Services] : [Reporting Services] [Using IN in a Where statement with and option of all] - I'm trying to use a Where TS.Skill IN (@TS) and it works fine. :) The user can select one or any number of options... --------------------------------------------------------------- [Data Warehousing] : [Integration Services] [connection manager time of day configuration] - Microsoft Visual Studio Premium 2012 Version 11.0.61219.00 Update 5 Microsoft .NET Framework Version 4.6.01055 Microsoft SQL Server Integration Services Designer Version 11.0.3369.0 Microsoft... [SSIS or ASP.NET?] - I'mtrying to decide the best tech to use to do what I need. I need to have a scheduled task/service that... --------------------------------------------------------------- [SQL Server 7,2000] : [T-SQL] [T-SQL - Adjusting Payments Against Invoices and Calculate Balances] - Hi all, I have been working on the following task where balances of payments exist in one table and invoices in... This email has been sent to [{EMAIL}]. To be removed from this list, please click [here.] If you have any problems leaving the list, please contact the [webmaster@sqlservercentral.com].--------------------------------------------------------------- This newsletter was sent to you because you signed up at [SQLServerCentral.com]. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here]. --------------------------------------------------------------- This transmission is ©2015 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: [webmaster@sqlservercentral.com]