How to simulate Case statement in Azure Data Factory (ADF) compared with SSIS? (2020-11-12)

 SQLServerCentral Newsletter for November 12, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [How to simulate Case statement in Azure Data Factory (ADF) compared with SSIS?]( - [Creating a SQL Code Snippet in SQL Prompt and SSMS]( - [Checking the pulse of Kubernetes]( - [From the SQL Server Central Blogs - PASS Summit 2020 Precon – Scripts and Links]( - [From the SQL Server Central Blogs - PASS 2020 Board of Director Endorsements]( Question of the Day - [Which Version for the Backup]( The Voice of the DBA  Daily Coping Tip Give yourself a boost. Try a new way of being physically active 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. Extreme SQL I have made a career of working with SQL and databases. Usually I've looked for interesting companies and people, but I've avoided extreme situations. For me, that often is very large, or very real time environments. I once declined a job for a 13TB database on SQL Server 6.5. My suspicion is that job would have taken me away from my young children and wife far too often. Facebook has a lot of users, and a lot of queries they run. With over 1billion daily users and hundreds of TBs of daily uploads, they really need strong databases. While they have multiple databases, and that includes SQL ones, they have struggled with analytic queries in the past. They started using [Presto]( as a solution, an open source query engine for running analytic queries against data in different storage locations like RDBMSes or in something like Hive/HDFS. This sounds like what Polybase does for SQL Server. The problem with any engine at Facebook's scale is the load. While they like Presto, they needed to make it work better. They initially built a caching layer that required users to build ETL jobs to load data into SSDs attached to the Presto cluster. However, they outgrew this and ended up [turning to a distributed file system called Alluxio](. The article linked above talks a bit about how this works, and allows users to query petabytes of data. Most of us have users that often don't qualify their queries completely, so we expect that some queries that might need to scan 100GB end up reading much more until the users tune them appropriately. The thing I found interesting in here is that some queries were taking up to 10s, which users found unacceptable. The move to Alluxio gave them a 30-50% boost, which doesn't sound like a lot. 5-7s over 10 isn't a great savings to me. The reduction in reads, is impressive, which is good, but I wonder to what expect there is some management and tuning needed here to ensure the cache works well. I have no desire to work on these extreme systems, but I am glad someone does. The lessons and tricks learned here often trickle down to improve the daily performance many of us see in our smaller systems. I think that the Hyperscale work Microsoft is doing, and the Big Data Clusters, are fascinating ways of organizing SQL Server based servers, and some of that tech will likely trickle down and help us continue to improve our smaller systems' performance over time. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [How to simulate Case statement in Azure Data Factory (ADF) compared with SSIS?]( diponkar.paul from SQLServerCentral This post will describe how you use a CASE statement in Azure Data Factory (ADF). If you are coming from SSIS background, you know a piece of SQL statement will do the task. However let's see how do it in SSIS and the very same thing can be achieved in ADF. Problem statement For my […] [Creating a SQL Code Snippet in SQL Prompt and SSMS]( Additional Articles from Redgate Phil Factor's "getting started" guide for turning all your trusted and most frequently used queries into SQL code snippets. [Checking the pulse of Kubernetes]( Additional Articles from SimpleTalk In this article, Michael Sorens describes the many ways that you can monitor Kubernetes. From the SQL Server Central Blogs - [PASS Summit 2020 Precon – Scripts and Links]( kleegeek from Technobabble by Klee from @kleegeek An attendee at my PASS Summit precon on virtualized SQL Server performance tuning on Monday asked a great question. “Do you have a list of the tools and scripts... From the SQL Server Central Blogs - [PASS 2020 Board of Director Endorsements]( Steve Jones - SSC Editor from The Voice of the DBA The election for the next PASS Board of Directors starts today, with a slate of 7 for three spots. I asked them for their vision last week, I’ve read...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Which Version for the Backup I have a .bak file that a colleague sent me. I want to restore this, but I don't know which version of SQL Server I need. What command helps me find out? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by tomaz.kastrun) R function kronecker() What does the following script R return (the function is part of the base R Package): kronecker(1:2,t(1:3))  Answer: A presentation of a matrix with dimensions of: m=2 n=3. Explanation: In R there is a base function called kronecker(). The name is taken from a mathematician, Leopold Kronecker. He is famous for the Kronecker product, also denoted with ?. The result is a matrix with two rows and three columns: [,1] [,2] [,3] [1,] 1 2 3 [2,] 2 4 6 Ref: kronecker() - [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 [SQL input parameters are defaulting to nvarchar causing full table scans]( - When our applications are submitting SQL via ADO.Net, with input parameters, the parameter definitions are defaulting to nvarchar. If that field is defined as a varchar and an index on the database table, the index is not used resulting in a scan instead of a seek. We are converting from Teradata to SQL Server so […] SQL Server 2017 - Development [SQL - How to compare data of a column while iterating row by row and insert new]( - I have the below data in an SQL Server(2017) table POS_ID Term Code Status IsActive TR 101 In Progress true TR 102 In Progress true TR 103 In Progress true CA 151 In Progress true CA 152 In Progress true DA 161 In Progress true The requirement is I want to iterate each row and […] SQL Server 2016 - Administration [PARALLEL_REDO_WORKER_WAIT_WORK.: Not sure how relevant this may be..]( - Attached file shows the top waits... PARALLEL_REDO_WORKER_WAIT_WORK.: Not sure how relevant this may be for this morning Latency reported by multiple users accessing a number of web pages and apps that connect to this server.. just seeing it first time But this wait type has been predominantly much higher than all other waits. Comparing to […] [Distributed Availability group]( - Is there a guideline or does anyone have any step by step process on removing Distributed Availability group between 2 servers properly? I am trying to beak it a part because the secondary server (forwarder) is old and ready to be decommissioned. This is how servers are setup: DAG between SQL1P (forwarder) and SQL2P (primary) […] Administration - SQL Server 2014 [What is reliable method to find all unresolved references?]( - I need to find all stored procedures that point to a table, function, a database that does not exist. I am using sys.sql_expression_dependencies built-in view for this. There are two problems with it. First, in some cases referenced object in this view is just an alias; and second, it happen to be a part of […] [Data type Date]( - Previously we had some issues with deadlocks caused by transaction that happen so fast that the current timestamp column is not accurate enough to prevent a collision. To prevent such an occurrence we need to change the data type used by the timestamp column in order to make it more precise, both for existing tables […] SQL Server 2012 - T-SQL [Put something into each row in a GROUP BY statement]( - I have this script, which gives me correct info : SELECT datepart(yyyy, [Første reg# dato]) AS årgang, COUNT(*) AS antal, Model,variant FROM dbo.[Ark1$] WHERE model <> '-' GROUP BY model,Variant,datepart(yyyy, [Første reg# dato]) ORDER BY DATEPART(yyyy, [Første reg# dato]),model,Variant  A part of the result is: 1960 2 125-3 UOPLYST 1960 2 ES 250 1960 […] [Updating from previous row until next value is found]( - I am trying to create an update that will update a value from previous row until the next none null value is found then update with that value. For instance in my table there are 2 columns SeqNum and AcctNum. The SeqNum will always start with 0000003 and the AcctNum will have a value and […] SQL Server 2019 - Administration [Upgrade SQL 2017 to 2019]( - Hi I guess this question has been asked before - but I failed to find best practice recommendation. We have a SQL Server 2017 and need to upgrade to 2019. The plan is to just install the 2019 instance on same physical SQL Server as a second instance. Then move databases from the 2017 […] [PowerShell - ItemArray]( - Can anyone please explain to me what .ItemArray does in PowerShell? I read the doco but I don't understand it. I have an array of SQL instances using mixed names i.e. server.domain, server.domain\sqlinstance, server.domain\sqlinstance, portnumber I used a foreach loop to pass the items in the array to an Invoke-Sqlcmd statement. I couldn't connect to […] [SQL 2012 AlwaysON migration]( - We have a SQL AlwaysON setup on windows 2012 R2 / SQL 2012 (to latest patch). We want to migrate to 2019. Can we add to the cluster and to the AlwaysON a Node3 and Node4 (Windows 2019 / SQL 2019) Then failover to Node3 and REMOVE Node1 and Node2. Will that migration work? Has […] [Raising errors between 1 and 13000]( - Hello, I've been struggling with a task which requires me to raise a number of errors for testing purposes. Of course the time consuming errors are between 1 and 13000. I've managed to do them, but I need to do it several times on multiple test instances and I wanted to ask if there's a […] [Restore - what happens after 100%]( - Just curious. I restore databases very often as I am a developer. Some of them has a size, that means that it takes some time. I use to backup with the same version as I restore from. I am aware, that if i restore a SQL 2012 with a SQL 2019, the converting takes some […] Amazon AWS and other cloud vendors [AWS Reserved Instance for Old Servers]( - We have a couple of SQL server instances(i2 and r3) from about 5 years ago. We are looking to reserve the instances now without upgrading to new, but I don't see any of these instances listed in the "Reserved Instance" section. In other words, the instances are not there to be purchased. I got to […] Reporting Services 2008/R2 Administration [Table Performance Issues]( - Hi, there I have a SQL Server 2008 R2 database and recently I have had major performance issues (I am not a DB admin). The database is connected to a CRM and the CRM users call many queries through the CRM interface on the various tables in their day to day work. I was made […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -