Handling the Duplication of Records (2020-11-03)

 SQLServerCentral Newsletter for November 3, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Handling the Duplication of Records]( - [Meet Redgate at PASS Virtual Summit]( - [Static Hosting with Azure Blob Storage and Azure CDN]( - [From the SQL Server Central Blogs - What do they want to hear?]( - [From the SQL Server Central Blogs - DAX Logic and Blanks]( Question of the Day - [UTF8 and varchar lengths]( The Voice of the DBA  Daily Coping Tip Think of three things that give you hope for the future 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. All In to the Cloud I was listening to the fall 2020 [GroupBy conference]( recently and heard Gethyn Ellis note that he wasn't aware of any companies that were over some age (a decade?) and had had made a 100% move to the cloud. The comment caught my eye, not because I haven't felt the same way, but because I'd just heard from a company that surprised me. During the [DevOps Enterprise Summit]( recently, one of the keynotes was from Capital One. They are [a 26 year old company]( that is focused on financial services, and they said that this year they are 100% in the cloud. They recently [shut down their last data center](. For a highly regulated company, and one that's not a startup, that is impressive. Almost all customers that I talk with are looking at the cloud in some sense. They might not be looking to move their biggest or most important applications, or even a majority, but they are often looking to hedge their bets, and continue depreciating or using their on-premises assets for some period of time. Not so with Captial One. They have heavily embraced DevOps, flexibility and rapid change in a way that had them open their 8th data center in 2014, only to pivot and look to eliminate that part of their infrastructure. They believe there is sufficient capability, reliability, and perhaps most importantly, security, in the cloud providers. They've embraced cloud-first, after an initial lift-and-shift approach for their thousands of systems. I don't know that most companies need to move in this direction, but this does show it is possible, and no matter what your concerns are, there are models of enterprises that have found ways to take advantage of the cloud. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Handling the Duplication of Records]( spvirtuoso from SQLServerCentral Learn how to easily identify and remove duplicate records when you discover them. [Meet Redgate at PASS Virtual Summit]( Additional Articles from Redgate Redgate are Gold sponsors of PASS Virtual Summit, which this year is available to all online. It's time to accelerate your data career by joining Redgate's Advocates who will be presenting a variety of sessions, as well as demo solutions throughout the week of 11 - 13 November. See what's happening and register today. [Static Hosting with Azure Blob Storage and Azure CDN]( Additional Articles from SimpleTalk No matter what you need, Azure probably has a solution. In this article Diogo Souza demonstrates how to host a static web page in Azure in just a few steps with Azure Blob Storage and Azure CDN. From the SQL Server Central Blogs - [What do they want to hear?]( Kenneth.Fisher from SQLStudies I was helping my 16yo write his first college essay the other day. He’s always struggled a bit with writing ... Continue reading From the SQL Server Central Blogs - [DAX Logic and Blanks]( Meagan Longoria from Data Savvy A while back I was chatting with Shannon Lindsay on Twitter. She shares lots of useful Power BI tips there. She shared her syntax tip of the & operator...   Question of the Day Today's question (by Thom A):  UTF8 and varchar lengths Consider you have below table with the following single value inserted: CREATE TABLE dbo.Test (S varchar(10) COLLATE Latin1_General_100_CI_AI_SC_UTF8); INSERT INTO dbo.Test (S) VALUES(N'?'); What would be the resulting value returned from the below query? SELECT LEN(REPLICATE(S,10000)) AS ReplicatedLength FROM dbo.Test; 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) Reseed Locks I need to reseed a table with this code: DBCC CHECKIDENT('Status', RESEED, 100) I am concerned about blocking and locking in this database. What locks will this code take on my table? Answer: Both an IX and a SCH-M lock Explanation: This action takes an IX and SCH-M lock on the table. Ref: Test this by holding open a transaction and checking the locks. [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 2017 - Administration [Index question]( - We have some indexes we have changed over the last few years to address performance problems. Currently the system is in MySql. But we are planning to do the migration to SQL Server. Not any issues in MySQL. I am thinking of making the changes in SQL Server. Let me know what you think? [Process to failback after an AG failover using asynchronous mode]( - I'm testing the failover process in my AG that is using Asynchronous availability mode. In the scenario where I manually failover from Server A (primary) to B, what is the process to fail back to A? It seems like the only way is to rebuild the entire synchronization from B to A, failover to A, […] SQL Server 2017 - Development [Stored Proc with OPENJSON: How to determine insert or update?]( - This is my 1st attempt at openjson & I'm trying to setup a stored proc that passes in multiple records and either inserts or updates the record in the table. I can setup the basic insert or update query using my openjson, however I don't know how to determine if the current record needs to […] [Question about Data Warehouse]( - Hello. I just rejoined SQL Server Central. I'm here because I'm a solo reports developer for a small business. I use SSRS, Tableau and QlikSense to develop reports and dashboards. I also write complex stored procedures to produce the datasets that I need. These Forums are the only places where I can ask questions or […] SQL Server 2016 - Administration [Looking for good beginner Data Base Administration book for advanced developer]( - Hello SSC, I have been a SQL developer for close to 20 years. I know some DBA tasks like, backup restore, installation, granting access to users, etc. I have been trying to find a beginners book that can slowly walk me through all of the common administration tasks. I think like a developer, so topics […] [Login to SQL Server cross domain AD Group user]( - Our MS SQL Server is on another trusted domain (Trusted Domain T). We created a Universal Group within Domain A with users from different forest domains and added the AD group onto the SQL Server. When the user from Child Domain B tries to connect, they get the error: "Login failed for user". If I […] SQL Server 2016 - Development and T-SQL [table variable]( - deleted code resolved issue  [product version result]( - SELECT SERVERPROPERTY ('ProductVersion') Output: 13.0.4001.0 What does each of those numbers mean.I need to find the most recent hotfixes applied on a server. Is 4001 the most recent hotfix applied 13 signifies sql 2016 First '0' is it sp1 ?? And what the last '0' signify ? SELECT substring(SERVERPROPERTY ('ProductVersion'),6,4)..this results in an error . […] [Help Writing a Udf that get Number of "Years in a row"]( - Hello. I have a table (say orderlines) with orderrows,  There is a column that holds the "Year" of the orderline. I like to write a userdefine function that calculate how many years in a row that item has been sold from today and back.  For examle : If I in the table have: […] Administration - SQL Server 2014 [SQL changing what index it uses]( - Hi, I have a database that has many inserts and was using a specific index and was humming along quite nicely. Then all of a sudden it stopped using that index and everything grinded to a holt. No changes were made on the database and it just suddenly happened at 4:21am. How or why would […] [Cannot remove growth restriction from log files]( - I have an instance where I have sysadmins. There a number of databases created by users and also msdb where I am unable to set log file growth to unlimited. It seems that when I change the setting all is OK but on checking back it has reverted to limited. I have altered the setting […] Development - SQL Server 2014 [Comparing consecutive datetime rows for schedules]( - @Grant Fletcher and @Michael L John. As suggested I looked up the backupset tables and I think we can still come up with schedules and timings for all databases and their respective kind of backups if we can use most recent sample data (last 30 days) assuming there is only one process(sql or 3rd party) […] SQL Server 2019 - Development [Getting the top unique value by group]( - Alright, so I've been banging my head against the wall for a bit on this one, trying to avoid my developers going RBAR. We have two tables, and are trying to populate a third table by selecting the first available processing number for each ID. The two tables are joined like this: TABLE1.ID Table2.Processingnumber 1234 […] Azure Data Factory [Using ADF Data Flow Derived Column transform against nested Delta structures]( - I'm trying (without success) to use a derived column transform within an ADF Data Flow where I've ingested a Delta table with nested structures. I'm struggling with the syntax needed to flatten out these structures and no column info is displayed despite me being able to preview the data. I thought I'd be able to […] Analysis Services [Import a PBIX file into Azure Analysis Services]( - I want to import a Power BI PBIX file into Azure Analysis Services. I don't see any recent information on how to do that. Is it possible? If so, how?   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -