Hidden Pitfalls with INNER JOIN and NOT IN Operators (2021-05-19)

SQLServerCentral Newsletter for May 19, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Hidden Pitfalls with INNER JOIN and NOT IN Operators]( - [How to choose between SQL and NoSQL databases]( - [Measuring Team Performance: Calculate LeadTime metric using JIRA and GitHub data]( - [From the SQL Server Central Blogs - Don’t Stop at Just Enabling Read Committed Snapshot Isolation (RCSI)]( - [From the SQL Server Central Blogs - Query Compile Time]( Question of the Day - [Multiplying Vectors in R]( The Voice of the DBA  Daily Coping Tip Try a vegetarian dish for a meal today I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Hostage Data Ransomware continues to surprise me in different ways. Recently there was an incident where data was not only encrypted, but also copied back to the criminals. In this case, Apple was the target through a supplier with [the ransom note saying that without a payment, the data would be auctioned off](. That's crazy. Not only might you have business issues where you can't access data, but now you have the stress of the data possibly being released or sold. While not many of us work in organizations where our data would be worth $50mm, it might be worth a significant amount, especially if the data were customer data. This alone might be a good reason to ensure that you have local data already encrypted without the keys present. At least then the criminals couldn't read your data. This doesn't help with SQL Server and TDE. In that case, the certificate would be inside the local master database, and if someone could attach it and get access to the master database, they could read your databases. If you have an SMK and a DMK, perhaps this might offer some protection, but I don't think so. This does mean that Always Encrypted might help, unless you have lots of servers or other machines on your network with the certificates, in which case someone might be able to piece together the keys and read data. Attacks are becoming more numerous and creative. Having backups might have protected you against some ransomware, but not if copies of your files are sent to criminals. Perhaps the access from servers to the outside world needs to be more reigned in. Not much fun for administrators, but this might be the future of protecting systems. The arms race between sysadmins that protect infrastructure and criminals seems to have taken a leap forward here, and I'm not looking forward to the next step. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Hidden Pitfalls with INNER JOIN and NOT IN Operators]( MMartin1 from SQLServerCentral I want to show you how two common operators can deceive you into believing you have constructed a proper SQL statement for your solution. We may want to filter results in a table by using using a IN/NOT IN or a INNER JOIN/LEFT JOIN to another table. There are a couple of hidden dangers here […] [How to choose between SQL and NoSQL databases]( Additional Articles from SimpleTalk Organizations have many choices when it comes to databases. In this article, Robert Sheldon explains how to choose between SQL and NoSQL databases. [Measuring Team Performance: Calculate LeadTime metric using JIRA and GitHub data]( Additional Articles from MSSQLTips.com In this article we look at how to measure lead time for development projects using Python along with GitHub and JIRA. From the SQL Server Central Blogs - [Don’t Stop at Just Enabling Read Committed Snapshot Isolation (RCSI)]( kleegeek from Technobabble by Klee from @kleegeek This past week I had a great Client ask an even greater question, and it got me thinking. The question is straightforward (albeit nerdy, which I love). “We’ve enabled... From the SQL Server Central Blogs - [Query Compile Time]( Grant Fritchey from The Scary DBA A question that came up recently was how to track the query compile time. It’s actually a pretty interesting question because, there aren’t that many ways to tell how...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Multiplying Vectors in R If I run this code in R, what is the result? > i <- c(1,2,3) > j <- (4,5) If I do this, what is the value of k? > k <- i * j Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) ADF Checking the Starting String I want to know if a string in Azure Data Factory starts with a particular substring and return a true or false. What is the best expression to use? Answer: startsWith Explanation: startsWith is used to check for a substring at the start of a string and return a true or false. Ref: startsWith - [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 2016 - Development and T-SQL [Comma separated value string and compare against other values]( - Hey all I have been trying to wrap my head around how to go about doing this for a while now and I'm not really coming up with anything. Here is my query: DECLARE @ATID AS VARCHAR(3) = 12 DECLARE @AssetID AS VARCHAR(7) = 306 SELECT tblXDataA.AssetID AS 'LID', tblA.AssetID AS 'AID', tblEM.[Custom] AS 'ABC/S […] Development - SQL Server 2014 [Between dates SQL help needed]( - Hi, I required duplicate values in the below table For e.g staff date 10192 first record To date is between the second record's From date and To Date SQL 2012 - General [SQL 2012 Standard - Index highly fragmented]( - Hi there, Complete newb. Need some guidance. Running SQL 2012, running INFOR ERP. Indexes are constantly fragmented... it's like whack a mole. Server is set to index nightly but sometimes, queries run slow and we have to re-index on the spot. Once re-index, back SQL queries are back to normal. Can anyone provide me with […] SQL Server 2019 - Administration [Azure SQL Database and SQL Managed Instance Security Best Practices]( - Hello, I have 7 different SQL Managed Instances which were newly migrated over from SQL VM. I also have 7 different Azure SQL Databases which were also newly created. I want to make sure that I completely secure all my SQL MI and Azure SQL to eliminate any security vulnerabilities and also avoid any SQL […] [Export encryption keys to SQL 2019 from MySQL]( - Hi My customer is migrating from MySQL to SQL 2019 using SSMA, thier current MySQL uses PHP and encrypts columns within a few tables, is there a way to get this decrypted or the key copied over to SQL Server. I have not done an MySQL to SQL migration before the reason for the question. […] [Retrieve Encrypted data from SQL in MS Excel]( - Hi I have a customer who has an SQL database and would like to encrypt columns i.e bank details. but they want to retrieve the data using Excel. Is this possible? I cannot find any articles to support this. Thanks in Advance Regards Raxs01 [sql login schema instead of dbo schema]( - Hello, In SQL server, when running select into statement for creating an object, the object is being creating with schema with my sql login ([Domain_Name]\[Sql_Login]) instead of dbo schema. When I use select * into dbo.table_name, the object is being creating with dbo schema, but it is a bit annoying to have to use dbo […] [SQL Server 2019 container kubernetes]( - Hi everyone, can anyone tell me, if the sql server 2019 is ready for container and is there an official image for kubernetes? My colleague told me, he was not able to find an image for 2019, only for 2017. thanks a lot, stay healthy, Andreas SQL Server 2019 - Development [Removing prefix and suffix from a column]( - Hi I have a table with a could that has over 3000 rows for employees name, in some of the rows there is names 'ZZZ Atkin (left)' How can I remove prefix starting with ZZZ and suffix with (left)? Regards Raxso Reporting Services [SSRS - exporting to csv issue]( - I have an SSRS report and have a field called "Ticket". "Ticket" is a VARCHAR(20) field and in my clients DB, they have a "Ticket" value of "00000000007" for example. I created an expression as such for this field in my report: =CStr(Fields!Ticket.Value) I preview my report and it previews the value as I want. […] [ssrs function code to get value from dataset]( - Hello, hopefully this makes sense, but i am a little familiar using SSRS function code to grab build in variables like date or page number and use them from header to body, BUT, now i want to be able to use something that is from the body, into the header... but not sure how to […] General [Linux data and index files]( - Hi Does anyone know where the data and index files are stored on a Linux server? I want to extract the data from a linux database and store it in sql server. Thanks in advance Integration Services [Failed Because Truncation Occurred]( - Dear Group: I am getting the following error on my SSIS import process. "Workbook.Outputs[Excel Source Output].Columns[XYZ]" failed because truncation occurred, and the truncation row disposition on "Workbook.Outputs[Excel Source Output].Columns[XYZ]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component." I understand the issue relates to the column XYZ being […] [Manual tasks in SSIS design]( - Hi, I am getting CSV files with hundreds of columns many of which have irregular characters in the column names, such as |, " ", -, /, etc. I've created table by manually importing the file, which renamed those columns in the table nicely by removing the character and replacing some of them with underscore. […] [Loading a csv file into sql server table]( - Hi, I've created a simple dataflow with flat file source and sql server destination. I am getting the following error messages: [MyDB MyTable table [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: […]   [RSS Feed]([Twitter]( 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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -